Share via

Power BI and Dynamics AX: Part 3: Create and Publish Visualisations

  • Article

 

This is Part Three, continuing from Part 2: Extract and Transform. This post will focus on the creation and publishing of visualisations.

At this point in the process we have a workbook, with a transformed data model – we are ready to create a visualisation to present our data. This post isn't meant to be a detailed guide of how to create a visualisation, you can find one here.

To get started with a PowerView Visualisation, from the workbook we built our query in we are going to click PowerView from the Insert tab.

Excel will load a blank PowerView sheet for you to begin creating your visualisation. Assuming your query was created you should now see your transformed data set in the "PowerView Fields" pane to the right of the screen. (If you don't – jump back here to see how to setup the query).

You can now start by checking the fields you would like to present in your visualisations, or dragging and dropping them from the field list onto the PowerView sheet. Once you start adding data you will notice a new ribbon "Design" is available, from here you can change visualisations and the presentation of the data.

Some key things to keep in mind when creating your PowerView visualisations:

  • Keep your visualisations simple
    • PowerView is designed to give high impact visualisations that allow a user to answer questions, or prompt action quickly and easily. Complex and detailed visualisations make this extremely difficult to do.
    • Don't try and answer every business question in one single PowerView sheet – don't be afraid to have multiple sheets within your workbook to present data in different ways.
  • Keep the end user device in mind
    • With mobile apps and HTML 5 support, PowerView can be viewed on a variety of devices, with different interfaces of different sizes. If you create visualisations which are a small font size or with bars on the chart to small, users on small touch devices won't be able to interact with them.
    • Some visualisations aren't supported on all modes – web, mobile app, HTML 5. While basic visualisations are, there is limited support across devices for PowerMap – check on the PowerBI team site for the latest support.
  • Leverage Hierarchies for drill downs

    • In Chart, Bar and Pie visualisations you can add multiple fields to the Axis to create a dynamic hierarchy. The visualisation will then allow the user to drill into the values in the chart.

    • While this example is using date values, you can include any fields in the hierarchy – for example Client, Project, Worker to drill into more detail on project analysis.

  • Select your Axis and Legend Fields carefully
    • Keeping in mind the purpose of simple visualisations, if you select a field that has a high number of values (Like 1,000 Customers) and then add this to your legend on your chart – not only is this going to look terrible – but PowerView will also limit the number of customers it displays and provide you with a sample set. In most cases, you won't want to view only a sample set.
    • Leverage hierarchies to group values so a manageable number of values are presented at any one time. For example: Customer Group, Customer

Publishing your Visualisation

Now you should have a workbook, including your data model and visualisation ready to share on PowerBI. The publishing process is quite simple, the main consideration at time of publishing is security.

Once you publish your workbook there Dynamics AX Security model is no longer applied – the data security is only applied at time of refresh. For example, if the designer of the report has access to 10 Dynamics AX Legal Entities, once they publish the report anyone with access to the workbook will see the contents. This is due to the fact that the information is uploaded into the Excel Data Model in Office 365 as part of the refresh process.

A key component of your PowerBI strategy needs to be focused on the plans for security related to your data. The security will be managed by SharePoint security, based on the workbook. As an example, if you create a folder in SharePoint for "Sales – West Region" and provide access to your West Region team, then only this team will have access to the report once its published to this folder.

The PowerQ&A service is also based on this security model, when a user asks a question on Q&A, PowerBI will look across all workbooks that user has access to within PowerBI. For this reason it's even more important to ensure the correct security has been setup from day 1.

Note: To publish your workbook in PowerBI for Office 365, you will need an Office 365 subscription and an active licence for PowerBI.

Firstly, you will need to create a PowerBI site on your Office 365 tenant if you haven't already done so, to create a new PowerBI site follow the detailed steps here.

Once you've created your new PowerBI site, you can upload your workbook by selecting "Add" > "Upload File" under the documents section.

Once the workbook is uploaded, you will notice it will automatically enabled itself for PowerBI. If you uploaded your document through the SharePoint library, or had already loaded it before adding PowerBI to your Office 365 tenant, you will need to enable the workbook manually. You can do this by clicking "Enable" on the ellipsis menu on the workbook.

One last step is to enable the workbook for PowerQ&A, this is also done from the ellipsis menu on the workbook itself. Workbooks will not be automatically enabled, the main reason here is you may have multiple workbooks with the same data source, you don't need to enable all of them for Q&A, only one as long as it's the same data source in the background.

Now that its enable you can click the workbook to see your visualisations on PowerBI. Done!

 

You'll notice in the bottom right hand corner you have an icon to take you to the HTML 5 view. It's recommended to always view your visualisations in HTML 5 to see how they are presented, depending on the device your user is using, you want to make sure the visualisations are clear and easy to understand. In some cases you will see it renders slightly differently in Excel vs HTML5 and may require some tweaking.

 

You've now got a Dynamics AX data source extracted and published in PowerBI. The next post in this series will talk about PowerQ&A and the data refresh options available for the data source.

Thanks,

Clay.