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.