How to: Include a hierarchy in a cube

Organizational hierarchies enable an organization to define various hierarchies among legal entities and operating units for reporting and operational purposes. You can define an organizational hierarchy to establish a relationship between legal entities or between operating units within a given legal entity. For examples, see Example organizational hierarchies.

A specific example of an organizational hierarchy can be found in the Retail cube in Microsoft Dynamics AX 2012 R2. The Organization Unit dimension in the Retail cube enables the user to navigate the Retail channel hierarchy. If a change is made to the channel hierarchy, such as an addition of a new store, the changes are reflected automatically the next time the cube is processed.

A hierarchy that shows a recursive relationship, such as in the example mentioned above, is referred to as a parent-child hierarchy in SQL Server Analysis Services (SSAS). You can add parent-child organizational hierarchies to other cubes.

It is possible that there are many organizational hierarchies defined in your AX 2012 installation. Some organizational hierarchies may be used by specific business processes, such as centralized payments from different legal entities, while others may be used for representing a corporate hierarchy. Since there are many different types of organizational hierarchies, you need to select the relevant hierarchy defined by your organization to be included in the cube.

In this article, we will add a legal entity hierarchy to the Accounts Receivable cube. This includes:

1. Create an organizational hierarchy dimension

2. Add the organization ID to the measure

3. Create the business intelligence project

4. Edit the business intelligence project in Visual Studio BI

Create an organizational hierarchy dimension

1. Create a hierarchy query using the following steps. You’ll create the new query based on the DirPartyTable, OMInternanlOrganization, and OMHierarchyRelationShip tables.

   1. In the AOT, right-click the Queries node, and then click New Query.

   2. Select the node for the query.

   3. In the Properties window, type OMHierarchyQuery in the Name property.

   4. Select the Fields node. In the Properties window, set the Dynamics property to Yes.

   5. Expand the Data Sources node.

   6. Right-click the Data Sources node, and then click New Data Source.

   7. In the Properties window, type DirPartyTable in the Table property.

   8. Select the Field node. In the Properties window, set the Dynamics property to Yes.

   9. Right-click the Data Sources node under DirPartyTable, and then click New Data Source.

   10. In the Properties window, type OMInternalOrganization in the Table property.

   11. Select the Field node. In the Properties windows, set the Dynamics property to Yes.

   12. Right-click the Data Sources node under OMInternalOrganization, and then click New Data Source.

   13. In the Properties window, type OMHierarchyRelationship in the Table property.

   14. Select the Field node. In the Properties windows, set the Dynamics property to Yes.

   15. Right-click the Relations node under OMInternalOrganization, and then click New Relation.

   16. In the Properties window, specify:

       Property	Value
       JoinDataSource	DirPartyTable_1
       Field	RecId
       RelatedField	RedId

   17. Expand the OMHierarchyRelationship_1 node.

   18. Right-click the Relations node under OMHierarchyRelationship, and then click New Relation.

   19. In the Properties window, specify:

       Property	Value
       JoinDataSource	OMInternalOrganization_1
       Field	RecId
       RelatedField	ChildOrganization

2. Create a hierarchy view using the following steps.

   1. In the AOT, expand the Data Dictionary node, and right-click Views. Then click New View.

   2. Select the node for the query.

   3. In the Properties window, type OMHierarchyView in the Name property.

   4. In the view, expand the Metadata node.

   5. Drag and drop OMHierarchyQuery under the Metadata node.

   6. Right-click the Fields node, and then select New Field.

   7. In the Properties window, specify:

      Property	Value
      DataSource	DirPartyTable_1
      DataField	Name

   8. Right-click the Fields node, and then select New Field.

   9. In the Properties window, specify:

      Property	Value
      DataSource	OMHierarchyRelationship_1
      DataField	ParentOrganization

3. Use the following steps to add the hierarchy view to the cube perspective.

   1. In the AOT, expand the Data Dictionary > Perspectives node.

   2. Find the perspective representing the cube that you want to add the organizational hierarchy to. This example uses the CustCube (Accounts receivable cube).

   3. Expand the CustCube perspective and then the Views node.

   4. Drag and drop OMHierarchyView under Views.

   5. Select OMHierarchyView.

   6. In the Properties window, specify:

      Property	Value
      AnalysisDemensionLabel	Org
      AnalysisKeyAttributeLabel	Org

   7. Expand the Fields node and select the Name field.

   8. In the Properties window, specify:

      Property	Value
      AnalysisUsage	Attribute

   9. Select the ParentOrganization field.

   10. In the Properties window, specify:

       Property	Value
       AnalysisUsage	Attribute
       AnalysisLabel	Parent org

Add the organization ID to the measure

In order to analyze the cube based on the newly created dimension, add the organization ID to the measure cube query.

In this example, we will add the organization ID to the Customer transaction measure. In the CustCube perspective, we can see that the Customer transactions table is created from the CustTrans table. Therefore, we will have to create the query based on CustTrans and add the CompanyInfo reference.

1. Create an extended customer transaction query using the following steps.

   1. In the AOT, right-click the Queries node, and then click New Query.

   2. Select the node for the query.

   3. In the Properties window, type CustTransExt in the Name property.

   4. Select the Fields node. In the Properties window, set the Dynamics property to Yes.

   5. Expand the Data Sources node.

   6. Right-click the Data Sources node, and then click New Data Source.

   7. In the Properties window, type CompanyInfo in the Table property.

   8. Select the Fields node. In the Properties window, set the Dynamics property to Yes.

   9. Right-click the Relations node under CompanyInfo, and then click New Relation.

   10. In the Properties window, specify:

       Property	Value
       JoinDataSource	CustTrans_1
       Field	dataAreaId
       RelatedField	DataArea

2. Create a view of customer transactions using the following steps.

   1. In the AOT, expand the Data Dictionary node, and right-click the Views node. Then click New View.

   2. Select the node for the query.

   3. In the Properties window, type CustTransExt in the Name property.

   4. In the view, expand the Metadata node.

   5. Drag and drop the CustTransExt query under the Metadata node.

   6. Drag and drop the following fields from the CustTransExt query to the view. (You’ll drag and drop the same fields which were mentioned in the CustCube perspective in the CustTran table).

      From data source CustTrans:

      • AccountNum

      • TransDate

      • AmountCur

      • SettleAmount

      • AmountMST

      • SettleAmountMST

      • CurrencyCode

      • DueDate

      • LastSettleDate

      • Closed

      • TransType

      • Approved

      • DocumentDate

      • PaymMode

      • DefaultDimension

      • CustBillingClassification

      From data source CompanyInfo:

      • RecId

        Note

        For this field, change the Name property to Organization.

3. Replace the CustTrans table with the CustTransExt view in CustCube perspective.

   1. In the AOT, expand Data Dictionary and then expand the Perspectives node.

   2. Expand the CustCube perspective, and then the Tables node.

   3. Select the CustTrans table and note the values for the following properties: AnalysisDimensionType, AnalysisDimensionLabel, AnalysisMeasureGroupLabel.

   4. Expand the CustTrans table and then Fields. Note the values for the following properties: AnalysisLabel, AnalysisUsage, AnalysisDefaultTotal, ExchangeRateDateField.

   5. Remove the CustTrans table from the view.

   6. Drag and drop the CustTransExt view under Views.

   7. In the Properties window, specify the following values. (These values were noted in step c above.)

      Property	Value
      AnalysisDimensionType	Transaction
      AnalysisDimensionLabel	@SYS618
      AnalysisMeasureGroupLabel	@SYS9305

   8. Specify the following properties on fields in the view. (These values were noted in step d above.)

      Field	AnalysisLabel	AnalysisUsage	AnalysisDefaultTotal	ExchangeRateDateField
      AccountNum	-	Auto	Auto	-
      TransDate	-	Attribute	Auto	-
      AmountCur	@SYS317640	Measure	Sum	-
      SettleAmountCur	@SYS317641	Measure	Sum	-
      AmountMST	@SYS317642	Measure	Sum	TransDate
      SettleAmountMST	@SYS317643	Measure	Sum	TransDate
      CurrencyCode	-	Auto	Auto	-
      DueDate	-	Attribute	Auto	-
      LastSettleDate	-	Attribute	Auto	-
      Closed	-	Attribute	Auto	-
      TransType	-	Attribute	Auto	-
      Approved	-	Attribute	Auto	-
      DocumentDate	-	Attribute	Auto	-
      PaymMode	-	Auto	Auto	-
      DefaultDimension	-	Attribute	Auto	-
      CustBillingClassification	-	Auto	Auto	-
      Organization	-	Auto	Auto	-

Create the business intelligence project

1. Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. Click Next.

2. Select Create and enter the project name. For example, CustCube Hierarchy.

3. Select Account receivable cube and shared dimensions. Then click Next.

4. Select required Microsoft Dynamics AX dimensions. Then click Next.

5. Select calendars for date dimensions. Then click Next.

6. Select required languages. Then click Next.

7. Add foreign currency conversion, if needed. Then click Next.

Edit the business intelligence project in Visual Studio BI

Edit the business intelligence project in Visual Studio BI. You’ll need to:

• Change Org dimension to a parent-child.

• Add Org dimenision to the Dimension usage of tab of the CustCube cube.

1. Change Org dimension to a parent-child dimension.

   1. Open created project in Visual Studio BI.

   2. Double click Org dimension.

   3. Select the Parent org member.

   4. In the Propertise window, set the Usage property to Parent.

   5. Process and browse the dimension.

2. Add the Org dimenision to the Dimension usage tab of the CustCube cube.

   1. In Visual Studio, double-click CustCube in Solution Explorer.

   2. Click the Dimension Usage tab.

   3. For the Org dimension and the Customer transactions measure group, click add relation and do the following:

      1. Set Relationship type to Regular.

      2. Set Granulity attribute to Org.

      3. Set Dimension Columns to RecID.

      4. Set Measure Group Columns to Organization.

   4. Process the cube.

   5. Browse the cube.