BIR106 –Migrate an upgraded Dynamics AX 2012 R3 sales cube to the entity store

The entity store will support near real-time Power BI integration scenarios as shown in the following diagram. For an overview of PowerBI integration with Entity store, see the blog post here: https://blogs.msdn.microsoft.com/dynamicsaxbi/2016/06/09/power-bi-integration-with-entity-store-in-dynamics-ax-7-may-update/

New Power BI features included in the May update

This lab requires the Dynamics AX May 2016 update. You will use the following new capabilities in this lab:

• Stage an aggregate measurement in the entity store and refresh the data from Dynamics AX. You might prefer this option over in-memory real time aggregate measurements when:
  • You upgrade an AX 2012 cube.
  • Your aggregate measurements are very large.
  • Data freshness (latency) from a few minutes up to a few hours is acceptable for reporting.
• Use the Dynamics AX batch framework to schedule a recurring refresh. For this release, only a full refresh is enabled.
• Create reports using Power BI desktop in a developer/test environment.
• Leverage the direct query option when creating Power BI content. For example, you can create larger models without relying on OData as the data refresh mechanism.
• Migrate reports from your development environment to a production environment using Lifecycle Services (LCS).
• As a partner or an ISV you can distribute Power BI content as part of an LCS solution to your customers.

Change upgraded aggregate measurement properties

As part of the code upgrade process, Analysis services projects from the AOT in Dynamics AX 2012 can be migrated to the new aggregate measurements metadata format.

1. Launch Visual Studio and create a new project in Application Suite.
   NOTE: You may create a model and include the customized aggregate measurement within that model. For more information, see Customization: Overlayering and extensions.
2. Open Application Explorer. Navigate to Analytics > Perspectives > Aggregate measurements. You will notice a set of aggregate measurements that were upgraded from Dynamics AX 2012 R3, as well as the measurements that ship in the current version of Dynamics AX.
3. Select SalesCube. Right-click and select Duplicate in project.
4. An aggregate measurement with the name SalesCubeCopy will be added to the project.
5. We will rename this measurement. Select SalesCubeCopy in Solution explorer. Right-click and select Rename. Enter SalesCubeV2 as the new name.
6. Double-click SalesCubeV2 to launch the Aggregate measurement designer. Notice the structure of the aggregate measurement that was migrated from Dynamics AX 2012.
7. The Sales cube in Dynamics AX 2012 encompassed a broad subject area related to Sales. In this case, let’s assume that we want to create a smaller, more focused Power BI model using the metadata that was upgraded. Expand the Sales Order Lines measure group and review the list of measures and dimension references.
   NOTE: Leveraging the modeling capabilities introduced in the current version of Dynamics AX you can quickly make a few enhancements to this model. Suggestions for improvements:
   • Replace views/tables that have been used to model the measure group (and/or dimensions) with an entity. You can model an entity using the underlying view and replace the view with the corresponding entity. This will enable you to leverage upcoming features such as incremental refresh and security.
   • Remove unwanted dimension references by adding the corresponding field to the attributes node. For example, the Sizes dimension reference can be removed because the Size field in the measure group is sufficiently descriptive. This will improve the runtime performance of queries as well as refresh times.
8. Select the SalesCubeV2 root node in the Aggregate measurement designer. Right-click and select Properties.
9. During upgrade, aggregate measurements are set to the legacy property flag, SSASCube. You need to change this property to one of two supported usage types. Previously, Dynamics AX supported InMemoryRealTime as usage for aggregate measurements. In Dynamics AX platform 7.1, StagedEntityStore is supported as a new usage type.
   NOTE: Modify the usage property to InMemoryRealTime if you plan on using the Aggregate Measurement for embedded BI scenarios as well as Power BI integration. If you are using the Aggregate measurement only for Power BI or Cortana Intelligence Suite integration, select StagedEntityStore.
10. Save the project. Right-click the project in Solution explorer and select Rebuild.
11. After the rebuild operation is finished, save the project, and then close Visual Studio. As a Dynamics AX (metadata) developer, your work is complete. You will author reports as a report developer or a power user.

Refresh the entity store

As an administrator you can configure the refresh of the aggregate measurement using the Dynamics AX client.

1. Launch the Dynamics AX client and navigate to System Administration > Setup > Entity Store. The Entity store form shows a list of aggregate measurements that are available for deployment to the entity store.
2. Notice that Sales Cube (which was upgraded from Dynamics AX 2012) is not available for deployment to the entity store. SalesCubeV2, which we created in the previous step, can be deployed to the entity store.
3. Select SalesCubeV2 from the list, and click the Refresh button. The Refresh dialog box will display. Expand the Run in the background tab.
4. Provide a descriptive name in the Task description field. Optionally, you can select the Recurrence tab and create a recurring schedule instead of a one-time refresh. Click OK.
5. The system will create a batch job for refresh of the aggregate measurement in the entity store.

Authoring a report on Sales by State with Power BI desktop

NOTE: This step requires the user to install Power BI desktop tool that can be downloaded using the URL: http://www.microsoft.com/en-us/download/details.aspx?id=45331.

1. Launch Power BI desktop. You may need to apply updates. A welcome page will display. Click Get data.
2. Alternatively, when Power BI desktop launches, on the Home tab select Get Data > SQL Server.
3. In the SQL Server Database dialog box, enter the server name and the name of the entity store database. If you deployed a developer environment, you can enter “.” as the server name and AxDW as the database name. If you are working in a test environment, you need to get these parameters from your system administrator
4. Select 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. Click OK.
5. Next you will see the Navigator dialog box. Navigator enables you to select tables and views from the entity store that you want to report on. Enter Sales in the search box. The system will filter entities that are related to the SalesCubeV2 aggregate measurement that was previously created.
   NOTE: The entity store stages the aggregate measurements that you (or an AX developer/data modeler) created. While entities within each aggregate measurement are prefixed and stored as individual tables, Power BI desktop enables you to combine data from multiple aggregate measurements.
6. You will create a report that shows sales by state. Select SalesCubeV2_Customer and SalesCubeV2_CustomerInvoices from Navigator and click Load.
7. You will notice Power BI designer with Fields present in the entities that you have chosen (on the far right), as well as available visualization.

Create a surrogate key that links customers and invoices

Power BI desktop does not enable you to relate table joins using multiple fields (also known as, composite keys). The SalesCubeV2_Customer entity does not have a surrogate key (such as AX RecID) defined in it. Next, you will create a surrogate key that enables relating a customer entity to invoices.

1. Select the ellipsis (…) icon next to the SalesCubeV2_CustomerInvoices entity. Right-click and select New Column.
2. Enter the following expression in the Formula editor window.

FKCustomer = CONCATENATE(CONCATENATE(SalesCubeV2_CustomerInvoices[DATAAREAID], "-"), SalesCubeV2_CustomerInvoices[ORDERACCOUNT])

NOTE: When you enter the first few letters of the field name or function, the editor will display a list of candidate fields. This is called a type-ahead feature. You can either copy and paste this expression or use the type-ahead feature.

3. When completed, your formula should look similar to the following.

4. Notice that a new field, FKCustomer, is shown in the list of fields for the SalesCubeV2_CustomerInvoices table. Because this field is used to relate two tables, you can hide it from end users by right-clicking the field and selecting the Hide option.
5. Next we will create a similar field in the SalesCubeV2_Customer table. Select the ellipsis (…) icon next to SalesCubeV2_Customer entity. Right-click and select New Column.
6. Enter the following expression in the Formula editor window.

FKCustomer = CONCATENATE(CONCATENATE(SalesCubeV2_Customer[DATAAREAID], "-"), SalesCubeV2_Customer[CUSTOMER])

5. Notice that the field FKCustomer is shown in the list of fields for the SalesCubeV2_Customer table. Because this field is used for relating two tables, you can hide it from end users by right-clicking the field and selecting the Hide option.

Relate invoices and customers

Next you will create a relationship between SalesCubeV2_CustomerInvoices and SalesCubeV2_Customers entities.

1. Click the Manage Relationships button on the Power BI ribbon. You will see the Manage Relationships dialog box. Click the New button.
2. In the Create Relationship dialog box, select SalesCubeV2CustomerInvoices as the first table in the drop-down list. Scroll to the right and select the FKCustomer field as the column to relate to.
3. In the second drop-down list select SalesCubeV2Customer as the table. Scroll to the right and select FKCustomer as the column to relate to.
4. Select the Make this relationship active option if it is not already selected. Click OK to continue.
5. You will notice the newly created relationship in the Manage Relationships dialog box. Click the Close button.

Create a Sales by state report

1. To create a report that shows sales by customer group, drag the CustomerInvoiceAmountAccountingCurrency field from the SalesCubeV2_CustomerIncoices table and drop it to the Power BI desktop canvas. Next drag the CustomerGroupName field in the SalesCubeV2_Customer table to the same grid.
2. Change the chart type to a doughnut chart. You should see a report similar to the following.

3. You can create additional visuals using the Power BI desktop. When you save, you will notice that the file has a PBIX extension.
4. Save the report to your desktop.
5. At this point the report is fully functional (with data from your environment) and you can continue to use the Power BI desktop or upload this report to PowerBI.com and continue with data exploration.
6. Next, you will migrate this report to a production environment using LCS so that you can see this report with production data and share it with other users.

Publish the report and the model

Publishing a report and model requires uploading the report to Lifecycle Services, migrating the aggregate measurement to your production environment, configuring your Dynamics AX client to point to the correct LCS library, and publishing your reports in your production environment.

Upload the report to Lifecycle Services

Microsoft Dynamics Lifecycle Services (LCS) is the tool used to migrate Dynamics AX development artifacts from developer to production environments. In Dynamics AX May update, LCS supports migrating PBIX files (authored using the entity store) between Dynamics AX environments.

1. Launch LCS (http://lcs.dynamics.com) from the developer environment. If you haven’t created a project in the LCS environment, create a project.
2. 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.

3. Select the plus (+) icon to add a new asset.
4. Provide a name and a description. Click Upload and then locate the file that you saved in an earlier step.
5. After you successfully upload the file, click Confirm. Notice that the file is uploaded into LCS as an implementation asset. LCS supports managing versions and releases for Power BI reports. You can maintain several versions and publish reports to other environments, just as you would for other Dynamics AX implementation artifacts. Because you added the PBIX files as an asset within an LCS project, Dynamics AX environments that you deployed using that project will have access to this report.
6. Optionally, you can publish this report so that all of your projects can access the shared assets. If 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.

Migrate the aggregate measurement to a production environment

1. You need to migrate the aggregate measurement that you modified in the developer environment to the production environment. You can follow the documentation here: https://ax.help.dynamics.com/en/wiki/create-and-apply-a-deployable-package/
2. After you successfully publish the model, perform the steps outlined in the Refresh the entity store section of this lab, so that the entity store is updated with data.

Configure an LCS project in Dynamics AX

If you haven’t already done so, associate your Dynamics AX environment with an LCS project so that Dynamics AX is able to consume assets within the project.

1. Launch the Dynamics AX client from the Dynamics AX instance that you want to use to deploy the Power BI reports. Typically this is the test or a production instance of Dynamics AX where you want to see a report with a different set of data than what you worked with as a report developer.
2. Open System Administration > Setup > System parameters. Select the Help tab. Using the Lifecycle services help configuration list box, select the LCS project that you uploaded the PBIX file to. Click Save.
   NOTE: This form will only show the LCS projects that 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 the administrator has access to.

Publish Power BI reports to a production environment

1. Open System Administration > Setup > Deploy PowerBI from the Dynamics AX client. You will see the file that you uploaded to LCS.
2. 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 the PowerBI.com service. Click the link to provide consent. When consent is complete, you need to go back to the original browser window and click the Close button.
3. After you successfully publish the file, the Power BI report will appear in your PowerBI.com subscription. You will notice that the report now points to the entity store in the production environment.

Continuing with PowerBI.com

As an administrator or a power user, you have successfully authored and published a Power BI report to the production environment using the entity store. You can perform several additional steps using Power BI functionality.

• Optionally, you can apply record-level security to the dataset to restrict users from seeing data they are not allowed to view in Power BI.
• You can create an organizational content pack and share it among users in a group.
  • You can export datasets, reports, and dashboards from your PowerBI.com instance as a new content pack to a selected group of users.
  • Note that organizational content packs respect any RLS rules that you defined at the dataset level.
• Users can personalize their workspaces by adding Power BI tiles or reports.