Creating Powerful Analytic Dashboards Using Advanced Business Intelligence & Power BI

Visit Website View Our Posts

One of the modules  delivered within the Advanced Supply Chain Software suite is Advanced Business Intelligence. This extends the reporting and analysis capabilities within Dynamics AX by adding 90+ additional reporting measures and key performance indicators including Cost To Serve, Gross Margin %, Profitability by Customer and many more. When you pair this with PowerBI, the sky is the limit when it comes to building your own dashboards for reporting on the performance of your company.

To show you how easy this is we thought that we would create a simple example for you.

One of the reasons why PowerBI is such a great tool is because you just have to have Excel in order to create your very own dashboards. So to start off, we just have to open up Excel and start querying the data. To do this, select the PowerPivot ribbon bar and then click on the Manage button within the Data Model group.

This will open  the PowerPivot designer. From here click on the Get External Data button within the Home ribbon bar, and then select the From Database option and then select the From Analysis Services menu item to tell the system that you want to query the data from the cubes that are part of the Advanced Supply Chain Software suite.

This will open up the Table Import Wizard and you will be able to select the Dynamics AX ASCS cube from the list of available reporting sources. When you are done, just click on the Next button.

This will open up a MDX Query window where you can build your query. Don't worry though you don't have to write any MXD at all, all you need to do is click on the Design button.

This will take you to the designer where you can see all of the new dimensions, measures and key performance indicators that you can use to create your dashboard .

If you expand  any of the dimension groups (like the Customer) then you will see that there are a number of different dimensions to choose from. All you have to do is drag over the dimension into the main pane.

Repeat the process for all of the other dimensions that you want to slice and dice with.

Now you can add some data to the query by expanding up all of the measure groups and dragging the measure onto the query pane. You will see now that all of the dimensions also populate as well now that there is data form them to report off.

You can add additional measures if you like, and when you are done, click on the OK button which will take you back to the MDX Query window with all of the query built for you. From there, just click on the Finish button.

PowerPivot will process for a little while and gather all of the data for you and when the data has been transferred, you can click on the Close button.

This will return you back to the PowerPivot window with all of the data being displayed. The only problem is that the columns are showing all of the default headings, so we may want to rename them.

To do that, just double click on the headings and change the heading name.

Another benefit of PowerPivot is that you can add your own formulas directly within the table – in this case we created a column that show a note based off the GM%.

Also, calculated fields don't have to be numbers or text, they can also be image URL's.

After creating any additional computed fields we just close out of PowerPivot and return to Excel.

When we are back in Excel, we can create a dashboard just by clicking on the PowerView icon within the Insert ribbon bar.

This will open up the PowerView canvas and we will see all of the fields from the PowerPivot query that we just created.

All we need to do is start selecting the fields that we want to add to our dashboard.

With a little bit more clicking and organizing we can convert the raw data into more of a dashboard showing all of the Advanced Business Intelligence measures.

Now you can just save the worksheet away and it will refresh every time you re-open it.

How easy is that?


Murray Fife, Microsoft MVP and Solution Architect at I.B.I.S. Inc. (A Microsoft Dynamics AX Partner in Georgia)

Original Post:

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons