Dynamics 365 Aggregate Measurement

An Aggregate Measurement object is to actually create and link the measure and dimensions that you will use for analytics. The measure will be something you create and it will be a sum or a count of data. The dimension will be something you slice the measure by. This is the basic concepts in a star analytics schema. 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 Power BI where you can use the aggregate information in a graphical form easily.

What is a Measure?

The measure is the easy part. This is the numerical values that quantify the data set that you are digging into to understand better. So if you are looking at sales by employee, then “sales” is the measure and employees are the dimension.

What is a Dimension?

A dimension is the lens through which you are looking at your data. It is the way you “categorize” the data. The most common dimension is, arguably, time. For example, if I am looking at sales by month in 2015, then the dimension is the months of the year 2015. However, you can categorize data in non-chronological ways as well: for example, if I am looking at GDP by country, then the dimension is country.

The dimension adds context to your measure and helps you to better understand what your data means.

Can you Have Multiple Dimensions?

I am not referring to the multiple dimensions from Sci-Fi or physics, but from a strictly data analysis angle. It is entirely possible to look at data through different dimensions at the same time. The best way to explain is through an example: perhaps you want to look at GDP by country over the last 30 years, then the last 30 years and the countries are both the dimensions, so in that scenario you have two dimensions and one measure.


The first step of the process is to have a measurement. These are an important part of the design. 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.

Create an Aggregate Dimensions

1. In the Solution explorer, right click your project Add New > New Item.
2. Select Analytics > Aggregate Dimension, in the Name field, enter FMModelDim and click Add.
3. Open Application Explorer, AOT > Data Model > Views and select FMVehicleExtendedView.
4. Drag the selected item onto the root of FMModelDim.
5. In the Designer, navigate to FMVehicleExtendedView > Fields and select Model, Drag and drop into FMModelDim > Attributes.
6. 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

1. In the Solution Explorer, select FMModelDim, and drag and drop this element on the dimensions node of FMModel.
2. Right click on FMModelDim under Dimensions and select New Relation.
3. In the properties tab, set the value of property type Name to ModelRelation and the value of property type Dimension Attribute to Model.
4. Highlight the model relationship just created.
5. In the Properties tab, set the value of property Related Field to Model.
6. Save all.

Aggregate data entities

By using the model-driven approach, you can create data entities by directly referencing aggregate measurements and aggregate dimensions. These are known as aggregate data entities. Aggregate data entities are read-only data entities that are used for reporting purposes. To consume aggregate data when you build charts and other client controls, add the aggregate data to a form as a data source. You can also consume aggregate data entities programmatically in C# or X++ code.