Create new aggregate measurement / Entity store in D365 Finance and Operation to be consumed in Power BI report in D365 Finance and Operations
In D365 there are several option to export/Import data like Data Entities, BYOD, Aggregate measurements/Entity store. In this blog we will learn How to create entity store in D365 Finance and operations.
Aggregate Measurements/Entity Stores are use to create Power Bi reports with nearly Live data where user have option to set its recurrence that is how often you want to refresh entity store there is no need to manually refresh the data as per suggested Batch job will run for respective entity stores. You can also forcefully refresh data by pressing refresh button on entity store page. So lets start with development of aggregate measurements, aggregate dimensions.
- Add New aggregate measurements object to the project where we required
- Add required attributes
- Add required measures
- Add required dimensions
- Add dimensions where view of dimension and aggregate measurements are different
- Build The Model
- Refresh Entity store from D365 Finance and Operation Environment
- Verify that respected view is created for aggregate measurement
In this step we need to right click on new item
Now select aggregate measurement and name it as per requirement in our case its “CFSAggregateMeasrure”
Now assign required views in table property of the aggregate measurement as follow
In my case I have selected “InventOnHandByWarehouse” view.
Add required attributes
Now add required attribute by right click on Attributes and assign required field in attributes as follows
Add required measures
After adding attributes add dimensions same how we added other attributes as follows
After adding measure assign required field to it and operation which you want to perform on that field (for example. :- count, Average, etc. )
Add required dimensions
By default some of the dimensions are provided like company and date which are showed in screen shot. And assign required fields in relation of dimensions
If dimensions needs different view we need to create new aggregate dimension as follows
In my case name of aggregate dimension is “CFSAggregateDimension ”
Now assign required view to dimension as follows.
After this create new attributes and assign fields to that attributes as follows
After adding new dimension attribute if required you can assign more than one field reference as follows
After this step assign fields to the respective field reference as follows.
Now select the respected attribute and select its usage property and change it as key which will make it as dimensional key which will be helpful while making relations.
There are 3 options under usage property description of each as follows
If you specify usage property as “key” system will define the key of the dimension using this attribute
If you specify usage property as “parent” system will parent child hierarchy with this field as parent level.
If you specify usage property as “Regular” ,this is an attribute without any special behavior and it is default value.
After setting usage property attach this dimension to our aggregate measurement by dropping required aggregate measurement on dimension section of it.
Now define its dimension attribute property as follows
After this you need to make relationship among the views as follows
Fact Dimension
If you want to make desired aggregate dimension as fact dimension go to desired dimension in aggregate measurement and make set “is fact dimension” property to yes/No as follows
Build The Model
After this case build the model which is used for this development of project as follows
Refresh Entity store from D365 Finance and Operation Environment
After successful build go to environment page’s Entity store section using following navigation
System administration >> setup >> entity store and refresh the desired entity
On required entity please press the refresh button
You can also set schedule to refresh this entity by selecting edit button and enable its automatic refresh toggle and the set its recurrence as follows
Verify that respected view is created for aggregate measurement
In final step go to your VM’s SSMS and look for Axdw database in which in view section look for views with your aggregate measurement and dimension and name as follows
After select query you can see the data of that view
Now your entity store is ready to consume by power bi reporting service. Thank You!
