Create new aggregate measurement / Entity store in D365 Finance and Operation to be consumed in Power BI report in D365 Finance and Operations | CloudFronts

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.

  1. Add New aggregate measurements object to the project where we required
  2. Add required attributes
  3. Add required measures
  4. Add required dimensions
  5. Add dimensions where view of dimension and aggregate measurements are different
  6. Build The Model
  7. Refresh Entity store from D365 Finance and Operation Environment
  8. Verify that respected view is created for aggregate measurement


                    Add New aggregate measurements object to the project where we required.

     

    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

           


              
                              Add dimensions where view of dimension and aggregate measurements are different

            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


                                          Key

              If you specify usage property as “key” system will define the key of the dimension using this attribute


                                            Parent

                If you specify usage property as “parent” system will parent child hierarchy with this field as parent level.


                                              Regular

                  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!


                        Share Story :

                        By continuing to use the site, you agree to the use of cookies. more information

                        The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

                        Close