Hello people, we are back once again for another blog on Microsoft Power BI, today we’re going to focus on two major things, one is to use OData feeds with Dynamics AX 2012 and other one is to demonstrate a Power BI dashboard based on a real world scenario that’lll be really helpful for firms that look forward to using Power BI for their organizations.
To give an overview of what my Power BI reports are going to focus, I’ve selected a limited number of visuals to show in this particular blog, one of which shows the Top Customers that are generating the highest revenue for my organization, the second one, shows the count of Projects by Stage and Type. The last one, shows all the Billable and Non-Billable Timesheet hours by Year.
Now, to start, as already discussed in all my previous blogs, the number of Data sources that Power BI supports is huge, I feel you can literally save your data in any of the available data storing technologies and Power BI will surely have a way to fetch data from there. As an example, In one of my earlier blogs, I tried to fetch On Hand Inventory data of Dynamics AX directly through SQL Server, in this one, I’ll try to use another way of fetching AX’s data i.e. through OData feeds, this can really help if you need to apply some complex logics on your Data model and fetch only the relevant data as needed. To begin, first of all we need to create some AOT Queries according to our needs, the query will be developed like we do in a regular AX scenario. In my case, I had multiple visuals that were related to different Data sets, so I created as many Queries in AX and put them in an AOT Project.
Now, once the queries are created, navigate to the Organization Administration module then Setup Area and click on Document Data Sources to expose your queries through Odata.
A form will open up with some already defined queries and other data sources, create new records for each of your Query and save, don’t forget to Activate your desired queries.
Once the data sources are defined, lets now test it out in a web browser before using it in Power BI, paste the below URL on your browser and you should see all the activated queries can be accessed here.
Let’s now move forward to Power BI and use queries that were exposed through OData here. Like before after opening Power BI Desktop, click on Get Data.
From the available options in the Get Data dialog box, select OData feed.
Paste the same URL that we used in the browser to check our data.
Once you’ve entered the correct URL, it will fetch all the activated data sources that were earlier defined on the AX side. Click on anyone and it will also show a preview of your data.
Click on Edit to open the query in an Excel like formatting utility, here you can format your data anyway you like, you can remove irrelevant columns, add new columns, change columns etc. Once you are done editing click on Close and Apply on the Top Right corner.
It might take a few moments to load the data into your report.
Once data is available, simply drag and drop your required data columns and select an appropriate visual to showcase that, I am attaching snapshots of visuals from my two reports here, as defined in the second paragraph of this blog.
Now, lets publish this into my Power BI cloud so that I can use the sharing and other features in PowerBI. I am going to select the Group workspace of my firm to publish his rather than my personal workspace.
Once published, his is how my dashboard looks like.
That is all, this is just a partial implementation of Project and Accounting module on Power BI, there are more reports being added and are in process. Thanks