Freigeben über

Power BI and Dynamics AX: Part 4: Data Refresh and Q&A

  • Artikel

 

This is part four, continuing from Part 3: Create and Publish Visualisations. This post will focus on Data Refresh within PowerBI and how to get started with Power Q&A.

At this point in the process we have extracted data from Dynamics AX using Power Query, transformed our data, created a visualisation using PowerView and published it to our Power BI site on Office 365. The next step is to setup a data refresh to ensure our data and visualisations stay current. If you don't schedule a data refresh, users will need to open the workbook in a desktop version of Excel, and manually refresh the data. This will also require a connection to the AOS for the user refreshing the workbook.

The refresh process has a few components involved, the initial setup has a few steps involved to connect your AX instance, but after this initial process new workbooks can be setup quite easily.

Firstly you need to install the Data Management Gateway (Download is available here.) the gateway is used to allow PowerBI to connect back to your On-Premise data without a user being involved. You will need to deploy the gateway on a server with internet access and access to the Dynamics AX AOS. Once installed, follow the steps outlined here to configure the gateway with our tenant of Office 365.

Once configured, you can now add a data source. This is done from the PowerBI Admin Centre. You will need to create a data source for each Dynamics AX Instance (Note Instance, not Query – so you will need a data source for Production, Dev, Test, etc).

To create the new Data Source, open the PowerBI Admin Centre from the Settings (Gears) option in the top right hand corner from your PowerBI site. From the Admin centre, select Data Sources. Now click the plus sign to add a new data source and select "Power Query"

You'll now be asked to paste in the connection string used for the connection. You need to get this from your Excel Workbook. Open you excel workbook and open the "Connections" form from the Data tab.

Note: if the Connections option is greyed out, it may be because you're on a PowerView sheet. Insert a new blank Excel sheet and the option will become available. (Don't forget to delete the new sheet later)

You'll see your different data sources in the connection window, you need to select one of your AX data sources, click "Properties". On the Definition tab you will see the connection string, copy and paste the entire connection string into the PowerBI admin centre.

It should now load the list of connections you have in your data source – you need to complete the details for each data source:

  • Name: Use something informative, example "Dynamics AX – Test Environment"
  • Description: For your internal purposes
  • Gateway: Select the gateway for the connection to use, you may have multiple gateways configured in your environment.
  • Set Credentials: These are credentials used for the refresh, this account must have access to Dynamics AX to perform the data refresh. It is recommended to use a system account for this refresh, not a specific users account.

You can now test the connection. The next two steps will allow you to specify users which have admin privileges over the data source and where notifications of errors should be sent.

Once the Data source is configured we can now go and schedule our workbook to refresh. Return to your PowerBI site and navigate to the workbook. From the ellipsis menu on the workbook select the "Schedule Data Refresh option"

From here you can see the refresh history, as well as configure the refresh for this specific workbook. You must setup the data source in the Admin centre first, otherwise this step will fail. You can find detailed steps on the refresh here.

Using Power Q&A: Natural Language Queries

Q&A is an extremely powerful tool, allowing users to use natural language to explore the data that you've prepare in your data model. This is where your transformation steps really pay off, as Q&A can leverage the friendly names you've given fields to allow users to explore your data.

To use PowerQ&A, from your PowerBI site click "Ask with PowerBI Q&A" in the top right hand corner. You'll be presented with a blank canvas ready for questions.

As an example, using my data set I have asked for "Expense amount by client name" – PowerQ&A has prepare a visualisation of my project expense transactions sorted by client name.

Using the "Explore this result" pane on the right hand side you can start changing visualisations, filters and even the fields that are presented.

Q&A does a lot of work on its own to understand natural language, it identifies synonyms, deals with spelling mistakes but you'll notice as you begin to explore that PowerQ&A doesn't always get it right, in the drop down under your question you'll see how Q&A is interpreting your question. In my example you can see it is showing "Show amount where transaction type is expense sorted by client name". What you will notice is sometimes Q&A can't understand what you're looking for and a word will be greyed out – this means Q&A didn't understand your phrasing.

Synonyms are one of the easiest ways to teach Q&A about your business, you can do this through PowerPivot (detailed instructions here.) or through PowerQ&A optimisation in Office 365. To manage this through Office 365 you need to open the Power BI Site Settings from your PowerBI Site. From within your Site Settings you'll see a tab for Q&A which will contain all your workbooks enabled for Q&A. From the ellipsis menu, select "Optimize for Q&A"

You will be presented with a blank Q&A space for you to ask test questions, you'll also notice in the pane on the right hand side a summary of the optimisation that has already take place. The first time you load the workbook you'll notice the synonyms and phrasings already generated by Q&A automatically.

 

Starting with the last tab, Usage, this is extremely helpful for you to understand how your users are using Q&A, as well as what words or phrasing isn't being understood. IT administrators and/or data officers should be regularly monitoring this tab to ensure Q&A is providing the right results, and is continuing to learn about the organisation.

Synonyms: you can use this tab to add a synonym to your column names. For example, you may internally refer to a "Product" as a "Part" – to teach Q&A you can add Part as a synonym to Product, now when users use the work "Part" in their questions, Q&A will be able to provide a response.

Phrasing is extremely powerful, it allows you to teach complex terms or expressions which are used within your organisation. As an example, let's say I asked "Expenses by client" – in my mind I want the same result as my first example, but I didn't say "expense amount". You can see Q&A hasn't interpreted this correctly, what is it showing me is clients which have had expenses, not the actual amount.

This is where optimisation comes into play, where we can teach Q&A that when I say "Expenses" I actually mean the amount of transactions which are of type, Expense. So now under the Phrasing Tab, I can add a new phrasing.

As soon as I click ok and ask the same question again "Expenses by client", the new result is shown below.

 

Some key things to keep in mind for Q&A:

  • Invest the time in PowerQuery transaction to make sure you start with a nice clean data set.
  • Plan for a pilot of Q&A before releasing it to your entire organisation, use this time to optimise your data and ensure you have your synonyms and phrasing worked out.
  • Remember access to data through Q&A is driven by your SharePoint security, so plan accordingly.
  • Q&A understands terms like more than, less than, last year, this month – explore with terminology and learn what works best for you.
  • Use the "featured questions" option to save questions and present them to users as they log in. This not only saves time in retyping questions, but also gives new users an introduction on what they can be asking.

Here are some great resources for Power BI, have a look at this content if you're starting out:

This is the last post in this series focused on PowerBI for Office 365, the next post will be focused on an example of how to use the new PowerBI functionality with on premise data by setting up a tabular SSAS database.

Thanks,

Clay.