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
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.
Written by K3 AX Consultant: Guy Terry