Earlier in the year, I took a look at Power BI, and tried to find out what it could do with AX data. My ambition was to use Power BI’s natural language search to build reports on AX data. Natural language search (called Q&A in Power BI) allows you to create reports by typing english sentences – such as ‘Sales by Month’.
In the end it didn’t go quite as well as I’d hoped. I couldn’t get the PowerBI.com website to look at my on premise SQL database, and I couldn’t get it to look at the standard AX Cubes. But the post did end on an optimistic note – support for the functionality I was looking for was either in development or being planned.
Since then, Power BI has come out of Beta. And it has had lots of updates; specifically, the Power BI Personal Gateway, which allows you to connect PowerBI.com to your on-premise SQL database. (I still can’t connect to the AX cubes – that work is not yet finished). So with my AX 2012 R3 CU9 Demo VM, I thought I’d take a look at it in action.
The link above describes how to install the Power BI Personal Gateway, but the first thing I notice is that the Personal Gateway is only available if you have a Power BI Pro subscription. This cost £9.99/month, and will be required for whoever create the reports, and anyone who views them. Its not loads of money, but I don’t particularly want to spend anything for this exercise. Luckily, a free 60-day trial of Power BI Pro is available, so I’ll go for that. To download the Personal Gateway, I login to PowerBI.com and click the Downloads icon in the top right:
Download the Power BI Personal Gateway
There are some limitations about where you can install the Personal Gateway. It must be on a 64bit Windows operating system, and you’ll probably want to install it on a device which is left powered on (at least, in real use you will. For this test, I installed it into the CU9 Demo VM). Whilst running the install, its worth being signed in as a user with Windows Administrator permissions. If you do this, the Personal Gateway will install as a Windows Service, and will run even if no-one is signed onto the computer.
The install is an easy ‘Next’, ‘Next’, ‘Next’, and when its finished you’re asked to sign-in to PowerBI.com. Use the same login that you used when you downloaded the Personal Gateway, and if you get it right you’ll be asked to provide the windows credentials that the Personal Gateway will use:
When it’s complete, I’m asked to sign-in to PowerBI.com to configure my datasources. We can’t simply go to PowerBI.com and ask to connect to our on premise database. First, we have to create a report which uses that database, and upload it to PowerBI.com. I’ll use Power BI Desktop for this. You can download it from the same place that you downloaded the Personal Gateway. In Power BI Desktop, SQL Server is listed as one of the data sources:
I enter the details which allow Power BI Designer to find my SQL database:
At the next screen, I have to say if I want to use Windows login or SQL login to access the database, and then I’m presented with all the tables (and views) in my AX database. Since I know the views CustTableCube and CustInvoiceTransExpanded are used by the AX cubes, and provides a nice set of data for reporting, I’ve used those to create this report which shows Invoice value by Zip Code in the USMF company:
When I’m done, I publish the report to PowerBI.com. And when I head to PowerBI.com, I see I’ve a new Dataset. If I click the three dots next to the Dataset, I’m able to Refresh it, or Schedule a refresh:
When I attempt a Refresh, I am informed that I need Power BI Pro, and asked if I want to create a trial:
And the first time I refresh, I have to confirm that I want to use Windows authentication:
And after that, my data can refresh. I also have the option to schedule a refresh (up to eight times per day), and as long as my Personal Gateway is running, the data will refresh for me. Result!
But can I use natural language to create a report? Sure I can:
The report above was created by typing ‘Show Value by Customer in a column chart’. I had to add ‘in a column chart’, because it wanted to use a horizontal bar chart, and I didn’t like it. All they need to do now is integrate Cortana, and I’ll be in seventh heaven:
“Cortana, show me contribution by customer group”
This is all very nice, but I live in the AX world. Why should I be bothered? I’ll leave you with one more thought. If you look hard enough, you’ll see news is also coming out about tighter integration between Power BI and the yet-to-be-release AX 7, and that’s enough to keep me interested.
Written by K3 AX Consultant: Guy Terry