Power BI comes of age

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:

DownloadPowerBIPersonalGateway

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:

ProvideWindowsCredentials

Provide the details of a windows (or domain) account

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:

GetDataSQLServer

The SQL Server data source

I enter the details which allow Power BI Designer to find my SQL database:

SQLDatabase

Enter the location of the 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:

SalesByZipCode

A map showing sales by zip code

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:

RefreshDataset

Refresh a dataset

When I attempt a Refresh, I am informed that I need Power BI Pro, and asked if I want to create a trial:

PowerBIProRequired

Power BI Pro is required

And the first time I refresh, I have to confirm that I want to use Windows authentication:

ConfirmAuthentication

Confirm 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:

NaturalLanguageReport

A report created by Power BI Q&A

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”

And that is not a dream – work has already started. You can even see it demonstrated by Joe Belfiore in the Ignite 2015 Keynote (skip forward to 00:45:30).

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.

Dynamics AX and Power BI

Update – 8th October 2015. Please find my most recent verdict on using Power BI with Dynamics AX here.

Just before Christmas last year, Microsoft announced significant new features under the ‘Power BI’ name. Functionality called Q&A caught my eye. It allows you to type in a question to create a report (or dashboard) and is, frankly, astonishing. Although this had been around for nearly a year, I think it was the first time that it became available to those without an Office 365 subscription. It wasn’t until March this year that those of us in Europe were able to get their hands on it, and I’ve finally got round to finding out what it can (or can’t) do with Dynamics AX.

So what is this new Power BI? The main part is the PowerBI.com website, which you can use to create, host and share your reports. There is also a tool called Power BI Designer, which you can install locally to create reports. These reports can be uploaded to PowerBI.com for sharing. Power BI is FREE (but please check this link for reasons why you might need to pay for it.)

On the blog of the Microsoft Dynamics AX Solution Archtecture team, Clay Wesener has written a series of five blogs about PowerBI and Dynamics AX. Part 1 is here. When I read them, I was surprised to find they didn’t go down the route I was expecting (and more about that next). I also heard that people were finding it was not possible to connect Power BI to their AX data. ‘How hard can it be?’, I thought. ‘We have a SQL database and some SSAS cubes which are ripe for mining’. And this is where Clay’s blog took a different route.

So, to find out just how hard it could be, I thought I’d install Power BI Designer, point it at my SQL database and we’re away. Well, almost. Whilst Power BI Designer can happily connect to the AX database, and you can upload these files to PowerBI.com, there is currently no support for refreshing the data on reports created this way. And equally disappointing – we cannot use the Q&A functionality on Power BI Designer reports. That’s a shame, not least because I feel like we need a screenshot. So here’s a map I created, showing cities which appear in invoice addresses in the AX demo data:

Cities wth sales in the AX Contoso data set

Cities wth sales in the AX Contoso data set

Note – because its a report created using Power BI Designer, there’s no facility here to build reports using natural language questions. And unfortunately, should I want this map to update next month with the latest sales data, I’d have to upload the Power BI Designer file again.

So lets forget Power BI Designer for now. Remember I said you can create reports directly on PowerBI.com? Lets try connecting PowerBI.com directly to the AX database. Nope, that’s not going to work either. To be fair, the idea is somewhat problematic. PowerBI.com is sat somewhere on the internet. My (fictional) AX database is sat in the basement of the building, and I’ve got lots of firewalls making sure my AX data doesn’t go anywhere near the internet!

But PowerBI.com does have a solution for connecting to an on-premise SQL Server Analysis Services database. That’s great – the AX Role Centers and BI use SSAS. You just need to install a Connector. The connector sits on your network waits for PowerBI.com to ask it for data from your cubes. (And you might need to install Azure Active Directory Sync, but only if your users don’t login to PowerBI.com with their work email address).

That’s the answer then! It’s not all of my AX data, but it’s a lot of the juicy stuff, and the cubes are already built for neat reporting. Unfortunately, not! The Connector currently only supports SSAS running in something called Tabular mode. You don’t need to understand what that is; you just need to know that the SSAS database created by AX is ‘multidimensional’, which is different. Oh, and the Connector also does not support Q&A functionality.

So back to the blog written by the AX Solution Architecture team. What does that say to do? The first solution only works for Power BI running on Office 365. It uses something called an OData feed, which allows AX to publish data via a URL. You put this data into Excel, and save this workbook to Office 365. If you install something called Microsoft Data Management Gateway, Office 365 is able to refresh the data from Dynamics AX automatically. I don’t have an Office 365 account – so I can’t use that. (Although if I did, I would be able to use Q&A on my AX data).

One final solution offered by the Solution Architecture team does not require Office 365, and goes back to the SSAS Connector I mentioned two or three paragraphs above. I already said it can’t be used with the AX Cubes, and that is still true. A solution which involves creating a Tabular database and copying data into it from the multidimensional database is proposed. Whilst not impossible, it’s more than I’m prepared to do for this blog post!

So we’ve got to the end, and I still don’t have my AX data in PowerBI.com, and I’m miles away from being able to do Q&A reports.

Should we just forget it then? Well, no. Power BI is amazing and powerful and does much more than just connect to AX data. And it is currently being updated monthly with new features. For example, this month, Google Analytics was added as a data source to Power BI Designer. Yes – include your website stats alongside (or even within) your reports!

Microsoft have already said that work on connecting PowerBI.com to on-premise SQL data has started. They have said that refreshing of reports created in Power BI Designer is planned. Adding support for on-premise, multi-dimensional SSAS databases is also planned. So my advice for now is to hang on and wait. But in the mean time, use the data sources that Power BI does support to get the hang of it.