As a pre-requisite to create analytics artifacts, the base tables (generally transactional) & underlying data types are needed. For this walkthrough, I have four tables – TransData, ProductGrade, AreaMaster & GenderMaster. The latter three tables are master tables each having two fields Id and Description. ‘TransData’ table is a transaction table which have ‘TransDate’ as date type field and ‘Amount’ as a real type field and three more fields which are pointing to the Ids of three master tables respectively. The idea is to have transactional data based on area, product grade type & purchasing person’s gender.
Aggregate Dimensions
Dimensions are slicers on which aggregate data is divided for analysis. In our case it will be area, gender and product grade.
Create an aggregate dimension by choosing to add a new item to project and choosing ‘Aggregate Dimension’. Name the new item as ‘Area’. Choose the ‘Table’ property of the dimension to ‘AreaMaster’. Under attributes node, create a new dimension attribute node. Name it ‘AreaId’ and choose ‘Usage’ property as ‘Key’.
Under dimension field reference node, choose property of ‘Dimension Field’ to ‘AreaId’.
Similarly create aggregate dimensions for ‘Gender’ and ‘ProductGrade’.
Aggregate Measures
Once the dimensions are created, measures need to be created. It will be sum on transaction amount in our example.
Create a new item in the project and select ‘Aggregate measurement’ type for it. Name it ‘TransAggregateMeasurements’. Select the ‘Measurement group’ node and in properties, Choose ‘TransData’ in ‘Table’ property and set ‘TransData’ in ‘Name’ property.
Expand the Measures node and create ‘New measure’. Type name ‘SumAmount’, choose field ‘Amount’ and default aggregate ‘Sum’ in the properties.
Similarly create another measure for number of transactions.
Name – NoTrans
Field – RecId
Default Aggregate - Count
Now expand the Dimensions node. Two dimensions – ‘Company’ and ‘Date_’ will come by default. These dimensions can be used or deleted as per business requirement. In our case, ‘Company’ dimension is deleted and ‘Date_’ is linked to ‘TransDate’ in ‘TransData’ table. For that, expand the dimension node fully and choose ‘TransDate’ in the ‘Related Field’ property of ‘DimensionRelationConstraint’ Node under ‘Date_’ dimension.
Drag the area, gender and product grade dimensions from already created dimensions from the project under ‘Dimensions’ node.
Aggregate Data Entity
Aggregate data entity is a representation of aggregate measures and dimension in a tabular form. The data structure makes it easy to be used in forms, reports and code like any other normal AX table.
Create a new item in the project and choose ‘Aggregate data entity’. Name it ‘TransDataAggregate’. Drag the measure ‘TransAggregateMeasurements’ to the data source node of the data entity.
Expand all the nodes of data source. Drag the measures to the ‘Fields’ node. This will expose these measures as data entity’s fields. Next drag the dimensions to the ‘Fields’ node. For each such field, choose the ‘Attribute’ value in property window.
Now build the project & synchronize the database. Synchronizing is very important after you make any change to analytic artifacts. Otherwise, you may start seeing unwanted error messages.
The data entity is now ready to be used further. In the next post, I’ll walkthrough to use the data entity in user interface.
Tags:
AX 7, BI, Perspective, Measure, Dimension, Aggregate data entity, Walkthrough