Creating an aggregate data entity

In this article we will explore the aggregate data entity creation in Dynamics 365 for Finance and Operations, Enterprise Edition (D365FFO). It is important to differentiate between the data entities used in Dynamics.

The first type is the data entity that is used for import and export with the Data management tool in the application. These are based on tables and generate the data in a flat database format. They are covered in a previous blog article at https://dynamicserppros.com called Data Management in Microsoft Dynamics 365 for Operation.

The second type of data entity is different in many ways. These are based on views of data and often consolidate, summarize, or simplify the data structure. A great way to describe them is a mini-cube, or star schema data entity that is processed to enable a smaller sub-set of fields for quick access. For example, you may want customer data, yet you don’t want all 10 tables of fields or standard data entities to get the data. You would like a single data entity that brings the information together with fewer fields or data points. It is much more useful with an application like PowerBI where you can use the aggregate information in a graphical form easily.

To create and share an aggregate data entity, we will need to accomplish two sets of tasks. The first section will be creating aggregate measurements, aggregate dimensions, model dimensions, deployment, and the creation of the data entity.  The second group is the process of making the aggregate data entity public, creating a primary key, and accessing the data entity via a browser to test.

The assumption of this article is that you created a package, model and project to contain your programing within Visual Studio in your own Virtual Image or Azure hosted development environment. Ensure that your model has an association to the Fleet Management package.  Please see Development Basics in Customer Source or subscribe to our training sessions on basic programming if you are unfamiliar with this process.

Create an Aggregate Data Entity

The first step of the process is to have a measurement. These are an important part of the design. You might want to think of these as the intersection point of our aggregate entity. Generally, the measurement is time, money, or some other quantifiable variable. You can use math or statistics functions to apply to the numbers.

Create an Aggregate Measurement

1. In the Solution explorer, right click your project Add New > New Item.
2. Select Analytics > Aggregate Measurement, In the Name field, enter FMModel and click Add.
3. Open Application Explorer, navigate to AOT > Data Model > Views and select FMVehicleExtendedView.
4. Drag the selected item onto MeasureGroup1 of FMModel node.
5. On the FMModel, expand FMVehicleExtendedView > Dimensions. Right click on Date_ and select Delete. Save all.
6. In the Application Explorer, right click on FMVehicleExtendedView and select Open Designer.
7. In the Designer, navigate to FMVehicleExtendedView > Fields and select Mileage, Drag and drop into FMModel > FMVehicleExtendedView > Measures.
8. Navigate to FMModel > FMVehicleExtendedView > Measures right click on Mileage and select Properties.
9. In the Properties window, for the property Default Aggregate select AverageOfChildren.
10. Save all.

The second is to create dimensions. You can use these dimensions as your slicers or items that help carve up your data. In our case we are using Model, yet, could use other features such as color.

Create Aggregate Dimensions

11. In the Solution explorer, right click your project Add New > New Item.
12. Select Analytics > Aggregate Dimension, in the Name field, enter FMModelDim and click Add.
13. Open Application Explorer, AOT > Data Model > Views and select FMVehicleExtendedView.
14. Drag the selected item onto the root of FMVehicleModelDim
15. In the Designer, navigate to FMVehicleExtendedView > Fields and select Model, Drag and drop into FMVehicleModelDim > Attributes.
16. Save all.

The third step is to create dimensional references.  You will need to associate or bring together the dimensions with a relationship back FM Model and link it.

Model Dimension References

17. 17. In the Solution Explorer, select FMModelDim, and drag and drop this element on the dimensions node of FMModel.
    18. Right click on FMModelDim under Dimensions and select New Relation.
    19. In the properties tab, set the value of property type Name to ModelRelation and the value of property type Dimension Attribute to Model.
    20. Highlight the model relationship just created.
    21. In the Properties tab, set the value of property Related Field to Model.
    22. Save all.

The fourth piece is to deploy the aggregate measurement.  We can then use it for our purposes.

Deploy Aggregate Measurement

23. Select FMModel, in the properties tab, verify that the Usage property type is set to InMemoryRealTime.
24. In the Solution explorer, right click on FMModel and select Add Column Store Indices.
25. Save all.

The following are created by this step.

Finally, in step five you can create the data entity based on the pieces that you have created early. This is what all of the work has led up to at this point.

Create Aggregate Data Entity

26. 26. In the Solution explorer, select the project right-click and Add New > New Item.
    27. Select Analytics > Aggregate Data Entity, in the Name field, enter FMModelEntity and click Add.
    28. In the Solution Explorer, under Aggregate Measurements, select FMModel, drag and drop onto the data source node of FMModelEntity.
    29. Under FMModelEntity > Data Source > FMModel > Measures, Select Mileage, drag and drop into the fields node of FMModelEntity
    30. Under FMModelEntity > Data Source > FMModel > Dimensions, Select FMModelDim, drag and drop into the fields node of FMModelEntity
    31. Under FMModelEntity > Fields > select FMModelDim, right click on it and select properties. In the Properties tab, set the value of property type Name to Model and the for property type Attribute click on the drop down and select Model.
    32. Save all and Build Solution.

Enable ODATA Access for Aggregate Data Entity

Once you have created your aggregate data entity, the process is to make the data entity pubic, create a primary key, and finally test your access.

Make the Aggregate Data Entity Public

33. In the Solution explorer, double click on FMModelEntity, if not already open.
34. In the Designer, right click on the FMModelEntity and select properties.
35. In the properties tab, set the value of property IsPublic to Yes, set the value of property Public collection name to FMModelEntity and set the value of property Public entity name to FMModelEntity.

Create Primary Key

36. 36. Right click on Keys node under FMModelEntity and select New Key.
    37. Modify the Name of the Key to ModelIdx from the property sheet.
    38. Save all.
    39. From the Fields node, drag and drop Model onto the ModelIdx
    40. Right click on the root node, FMModelEntity and select properties.
    41. In the properties tab, set the value of property Primary Key to ModelIdx.
    42. Save all.
    43. Build the solution.

Access Aggregate Data Entities with OData

44. Navigate to D365 for Finance and Operations using the browser.
45. Modify the url to add /data/ FMModelEntity and navigate to the url (https:// <your machine name here >.dynamics.com/data/FMModelEntity)
46. Click Open.

The following is a screen shot of the output that you can expect from the aggregate data entity, based on demo data.

This article explored the process of using Dynamics 365 for Finance and Operations, Enterprise edition to create an aggregate data entity.  We accomplished two sets of tasks. First we created measurements, dimensions, relationships, deployments, and the data entity itself.  Although sophisticated in the initial setup, many of these components can be reused for many different entities.  In most cases, the existing D365FFO components will already be created for your use. Second, we setup, published and tested the aggregate entity for use outside of D365FFO.

For more information, I would recommend that you attend our Dynamics AX2012 or Dynamics 365 for Finance and Operations, Enterprise edition training in person or on demand online to learn more.

Plus, keep an eye out for more articles that dive in to the technical aspects of using Excel, X++ code, and PowerBI.  Please visit academy.rsmus.com for more information and training materials that will cover this and 100’s of other topics for AX2012 and Dynamics 365 for Finance and Operations, Enterprise edition.

By: Sean McMikle

Shaun McMikle