Create a PowerPivot BI Application for AX Project Postings (Update)

This is an update on the first posting how to create a PowerPivot Application for Dynamics AX. These are the changes and updates:

• Excel 2013
• XML Data Feeds instead of SQL
• Timeline Filter instead of calculated fields
  

  Create and Publish Data Access Services

  You can publish Dynamics AX queries as open data feed. In contrast to direct SQL access it gives you more control on what is published because the builtin Dynamics AX security layer is used. Furthermore the data is delivered as XML and can easily be processed by other applications  Create three queries using these datasources and fields:

  Datasource
  ProjTable	ProjId	Name
  ProjJournalTrans	ProjId	CategoryId	Txt	Qty	Transdate	Worker
  HCMWoker join DirPerson	Person	RecId from HCMWorker	Name

Go to Organization Administration > Setup > Document Management > Document data sources. For each of the queries create a new entry using Module “Project” and Type “Query Reference”. Choose your query from the dropdown and activate them.

You can check your services by opening the ODataService URL in your Browser. Depending on the name of the Server and Ports it might look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService. You should see three available data services.

You can load the data by adding /COLLECTION_NAME to the URL. For example http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPProjTable to view the data from the ERPProjTable collection based on the ERPProjTable query.

Build the Data Model

PowerPivot and PowerView reports are builtin with Excel 2013. However, these addins need to be activated manually. Go to File > Options > Addins > Dropdown COM Addins. Activate PowerPivot and PowerView.

Go to PowerPivot Tab > Manage > Get External Data From Data Service. In the wizard copy the data service URL (without collection name) and click next. Select the data feed collections and finish the import

Switch to the Diagram View using the small button on the right bottom. Create the relations between the tables by drag and dropping the related fields. ProjTable.ProjId on ProjJournalTrans.ProjId and ProjJournalTrans.Worker on HcmWorker.RecId

Switch back to Excel. From the PowerPivot tab, select Calculated Fields and create 2 new calculated fields. One for the Qty sum and another sum of postings on project Holiday and Illness.

• sum(ERPProjJournalTrans[ProjJournalTrans_Qty])
• CALCULATE(sum(ERPProjJournalTrans[ProjJournalTrans_Qty]);
  ERPProjTable[ProjTable_ProjId] = "50002" || ERPProjTable[ProjTable_ProjId] = "50003")

 

Create a KPI to visualize Absence vs. Productive

In the PowerPivot Tab, create a new KPI. Use the C_Absence as base value and the C_Total as Target value. Change the color scheme to Gree-Yellow-Red and set the sliders to 5% and 10%.

Go to Insert Tab, from the Tables Group select PowerPivot Table. In the Dialog select “External Datasource”. Switch to Tables and use Workbook Data Model.

Select the ERPWorker and drag the DirPers_Name field in the rows. Select the ERPProjJournalTrans and drag the C_Total calculated field in the Values box. Expand the C_Absence KPI. Drag the Value(C_Absence) and the Status in the values box.

Go to the Analyze Tab (only visible if the pivot table is selected), from the group Filter, create a new Timeline Filter and select the ProjJournalTrans_TransDate field. Place the Filter above the table (or wherever your want)