Authoring and distributing Power BI reports with Dynamics AX"7"

This is the second bog post on May update enhancements for Dynamics AX"7". You can read the first blog post here: https://blogs.msdn.microsoft.com/dynamicsaxbi/2016/06/09/power-bi-integration-with-entity-store-in-dynamics-ax-7-may-update/

As we discussed earlier, we are making the Power BI integration feature much stronger with Entity store. There are several features that will enable great end-to-end Power BI reporting experiences in Dynamics AX. You can see a list of features here: https://ax.help.dynamics.com/en/wiki/whats-new-or-changed-in-dynamics-ax-7/#may-2016-platform-features  (scroll down to the Analytics area)

In this post, we will go deeper into Power BI authoring and distribution features enabled in Dynamics AX "7". In the next blog post we will see how Power BI tiles and Reports can be embedded within AX workspaces using personalization capabilities.

Create reports in Power BI desktop with AX data

If you are a Power user or a Business analyst, you probably create many reports for your users. Perhaps you create them in Excel: exporting data from AX to an Excel file, formatting and relating data and then creating a report or a chart before you e-mail it to them. Your users probably come back to you if they need to make a modification to the report (and yes, you may be very busy with a queue of requests waiting in your inbox; so let’s get to the point quickly!).

We want to help you by providing an easy way to Author rich interactive reports. As a report writer, you could use Power BI desktop as the reporting tool and the reports you create can be published to PowerBI.com. You can read more about Power BI desktop here: https://powerbi.microsoft.com/en-us/desktop

In the February 2016 release, you could author Power BI reports by using OData end points that are exposed via data entities (both Aggregate Data Entities as well as "detailed" or "regular" Data entities). Although this approach is still supported, with Entity store, Power users or Business analysts can create reports using the DirectQuery option enabled for SQL server databases.

As we discussed in our previous blog post, this provides 2 immediate benefits;

• You can leverage PowerBI DirectQuery capability and author reports that execute directly on the Entity store database. DirectQuery based PowerBI reports reflect live data in the Entity store.
• You have the ability to author PowerBI reports over larger data volumes than what was possible with OData

Using PowerBI desktop, you could create a report in your development or test environment by connecting directly to Entity store. When you are satisfied with the report you can migrate this report to your production environment with the help of your Administrator.

Let's quickly see how this is done. First, let’s stage the RetailCube aggregate measurement into the Entity store using the steps explained in our previous blog post.

Launch Dynamics AX client (May update) and navigate to System Administration > Setup > Entity store form. Select RetailCube aggregate measurement and select the Refresh button on the menu.

You can monitor the progress of the job used to stage the data using the batch job monitoring form. Once the data is populated into the Entity store (it should take a minute or so with AX demo data), you are ready to write reports.

Launch Power BI desktop – you may need to download PowerBI desktop and apply updates if any. You will notice a welcome splash screen as showb below. Click the Get data icon

Alternatively, when PowerBI desktop launches, you can select Get Data > SQL Server from the menu

In the SQL Server Database dialog. Enter the Server name and the name of the Entity store database. If you deployed a developer environment, and you are working on the same machine, you can enter “.” as server name and AxDW as the database name. If you are working on a test environment, you need to obtain these parameters from your system administrator.

Check the DirectQuery option – in this exercise, we want to create Power BI reports that are executed directly on the Entity store. If you had used the Import option, Power BI would cache data from the Entity store and you would need to periodically refresh the Power BI model. We don’t support Import mode with Entity store at this point in time.

Select the OK button.

Next you will see the Navigator dialog. Navigator enables you to select Tables and views from the Entity store that you wish to report on. Enter Retail in the search box as shown below. System would filter Entities that are related to the RetailCube aggregate measurement that you staged previously. Select the RetailCube_RetailTransDetailsView table shown in the navigator and click the Load button - you are ready to author a Report.

You can drag and drop measures and fields into the canvas and explore data and trends interactively. PowerBI desktop also supports creating calculations and also it enables you to combine data from multiple aggregate measurements if you like.

In a few minutes you should be able to create a report with the data available in development environment as shown below. First, you need to save the report as a PBIX file.

Now that you are pleased with the report, let’s see how you can migrate this report to the production environment so that your users can use this report to interact with production data.

Publish the report into production

As you may have guessed, this step requires an Administrator. You must send your PBIX file to the administrator and he would upload the report to Dynamics Life Cycle Services (LCS) as an implementation asset.

Or you could upload the report to an LCS project and share with an administrator for publishing to production. This is the same process adopted for migrating AX artifacts from developer environments into production – so you can follow the process adopted by your organization.

Upload the report to Life Cycle Services

Dynamics Life Cycle Services (LCS) is the tool used to migrate AX development artifacts from developer to production environments. In May update, LCS supports migrating PBIX files (DirectQuery reports authored using Entity store) between AX environments.

Launch LCS (<lcs.dynamics.com>) from the developer environment. If you haven’t created a project in LCS environment, create a project. Scroll to the right and you will notice the Asset Library Icon. Click the icon and launch Asset Library

Notice that the Asset Library enables adding PowerBI report models (PBIX files) as implementation artifacts to a project.

Select the + icon to add a new Asset.

Provide a names and a description. Select the upload button and locate the file you saved in the earlier step.

On successful upload, select confirm. Notice that the chosen file is uploaded into LCS as an implementation Asset. LCS supports managing versions and releases for PowerBI reports. You can maintain several versions and publish reports to other environments as you would in case of any other AX implementation artifact.

Since you added the PBIX files as an asset within an LCS project, AX environments deployed using that project have access to this report.

Optionally, you can publish this report so that all your projects can access the shared assets. In case you are a partner or an ISV, and want to share this report with your customers, you would share this asset to your global library and enable your customers to import the asset into their respective LCS projects. To do this, select the “Save to my library” option.

Deploying the report in production

Your administrator should have to associate your AX environment with an LCS project such that AX is able to consume assets within the project. It’s very likely that this step is done already in your production environment.

Launch AX client from the AX instance that you want to deploy the PowerBI reports. This is typically, the test or a production instance of AX where you want to see you report with a different set of data than the ones you worked with as a Business Analyst.

Launch System Administration > Setup > System parameters form. Select the Help tab. Using the LifeCycle services help configuration list box, select the LCS project that you uploaded the PBIX file. Select the Save button.

NOTE: This form will only show the LCS projects where the current user has access to. If this step is being performed by an Administrator, either the Administrator needs to have access to the project, or the PBIX artifacts need to be imported into a project that Administrator has access to.

Launch System Administration > Setup > Deploy PowerBI files option from the AX client. You will see the file that you uploaded into LCS.

Select the Sales Report File and select the Deploy Power BI files option on the menu bar.

NOTE: You may be asked to consent publishing to PowerBI.com service. Click the link to provide consent. When consent is complete, you need to go back to the original browser window and select the close button.

After successful publishing, the PowerBI report will appear in your own PowerBI.com subscription. You will notice that the report now points to the Entity store in production environment.

Partners and ISVs can distribute Power BI reports as LCS solution assets

In the previous section, you uploaded the PBIX file into LCS to migrate from the developer environment to production. Since PowerBI reports (PBIX files) are recognized as implementation assets within LCS, you can bundle PowerBI reports with other AX solution assets.

If you are working for a partner or an ISV, this opens up a host of opportunities;

You can ship PowerBI reports with your ISV solution that includes AX models, demo data etc. You can "wow" your users with rich interactive reports that are shipped as part of your solution.

You can ship reports "out of band" with your solution. Since PBIX files are stand-alone implementation assets, you can continue to ship reports as regular updates to your customers - get feedback and continue to improve them. Since developing reports is easy (as you have seen above), you can iterate quickly

You can also build and share customized PowerBI reports with specific customers - as you always have.

Comments

• Anonymous
  June 30, 2016
  Can you please provide more Information how to enable access to the AX-Database?When i work on the DEV Environment I can connect local with ".". But when I upload the pbix file to assets library and further to app.powerbi, I'm not able to refresh the data as the connection is not local any more...
  • Anonymous
    July 21, 2016
    Hi - this feature is designed to work with DirectQuery. So firstly, you should create Direct query models using PBI desktop. As yo know already, In case of direct query models, you do not need to refresh the model since the report is run against Entity store directly. So the report should work when you upload to app.PowerBI.com. when you upload directly to app.PowerBI.com, the report points to data in your DEV environment.You should use the "deploy to powerBI" form in Dynamics AX to when you are ready to deploy to production. When you use this form, the system will change report connection strings to point to production instance of Entity store.
    • Anonymous
      August 05, 2016
      Thank you for the hint! I was using the import option...
• Anonymous
  July 18, 2016
  The comment has been removed
  • Anonymous
    July 21, 2016
    Hi Paul - the hotfix you reference is an option called "bring your own DB" - this feature enables you to create your own Dwh and stage AX entities into it. Entity store that is shipped with Dynamics AX in May update is configured for PowerBI integration while you could use the "bring your own DB" option for using other BI tools or for integration.You can't provide Entity store (ie. AXDW) as the database connection to "bring your own DB" if that's what you intended. You would need to create a SQL Azure DB and provide the connection string. More documentation on this hotfix is coming soon.
    • Anonymous
      July 21, 2016
      Thanks Milinda for the details! Did I get it right, the Entity store to export full data entities has to be SQL Azure DB? So I am not able to connect my local Dev-VM to the local AxDW database?
    • Anonymous
      July 23, 2016
      Today I again tried to use the „bring your own database” feature of the entity store. Again with no luck! Today I used an Azure SQL DB with a connection string which work in C# ADO.NET connection, but the validation of the connection within AX always fails. I tried the process on a local OneBox and on an Azure Hosted Demo environment. I definitely need a documentation from Microsoft to get this working…
• Anonymous
  January 09, 2017
  Hi. I tried to deploy the Training.pbix file out of Dynamics 365 for Operations. Before I change the database to . and entered the user and password information out of LCS.I´m able to the the report in Dynamics 365 but when I push "Deploy Power Bi file" after a view minutes I receive the massage: "Deployment failed for file Trainin". There is no additional information regarding the problem. Do you have any hint?Thank youChristoph
  • Anonymous
    March 28, 2017
    Hi, I too get the "Deployment failed for file" message when trying to deploy a file. The report does show up in my powerbi.com account but when I try to use it I get this message in Power BI: "This data source can’t be accessed by a data gateway: Server: build4794ff9e-0; Database: axdw" Any help would be appreciated. Thanks, John
• Anonymous
  December 07, 2017
  The comment has been removed
• Anonymous
  December 11, 2017
  I might have misunderstood this... but if I want several users to have access to a power BI report that I have modified and uploaded to LCS, they will each need to deploy the report to their own Power BI subscription? This doesn't seem ideal. My other thought was once the report was uploaded to my Power BI workspace that I could copy it to a shared app workspace. I can't find a way to do this. if I download the .pbix file and then try to upload to the workspace the report reverts to using my dev on premise gateway. Is there another way?