How to: Include a hierarchy in a cube [AX 2012]
Updated: October 5, 2015
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:
-
Create an organizational hierarchy dimension
-
Add the organization ID to the measure
-
Create the business intelligence project
-
Edit the business intelligence project in Visual Studio BI
-
Create a hierarchy query using the following steps. You’ll create the new query based on the DirPartyTable, OMInternanlOrganization, and OMHierarchyRelationShip tables.
-
In the AOT, right-click the Queries node, and then click New Query.
-
Select the node for the query.
-
In the Properties window, type OMHierarchyQuery in the Name property.
-
Select the Fields node. In the Properties window, set the Dynamics property to Yes.
-
Expand the Data Sources node.
-
Right-click the Data Sources node, and then click New Data Source.
-
In the Properties window, type DirPartyTable in the Table property.
-
Select the Field node. In the Properties window, set the Dynamics property to Yes.
-
Right-click the Data Sources node under DirPartyTable, and then click New Data Source.
-
In the Properties window, type OMInternalOrganization in the Table property.
-
Select the Field node. In the Properties windows, set the Dynamics property to Yes.
-
Right-click the Data Sources node under OMInternalOrganization, and then click New Data Source.
-
In the Properties window, type OMHierarchyRelationship in the Table property.
-
Select the Field node. In the Properties windows, set the Dynamics property to Yes.
-
Right-click the Relations node under OMInternalOrganization, and then click New Relation.
-
In the Properties window, specify:
Property
Value
JoinDataSource
DirPartyTable_1
Field
RecId
RelatedField
RedId
-
Expand the OMHierarchyRelationship_1 node.
-
Right-click the Relations node under OMHierarchyRelationship, and then click New Relation.
-
In the Properties window, specify:
Property
Value
JoinDataSource
OMInternalOrganization_1
Field
RecId
RelatedField
ChildOrganization
-
-
Create a hierarchy view using the following steps.
-
In the AOT, expand the Data Dictionary node, and right-click Views. Then click New View.
-
Select the node for the query.
-
In the Properties window, type OMHierarchyView in the Name property.
-
In the view, expand the Metadata node.
-
Drag and drop OMHierarchyQuery under the Metadata node.
-
Right-click the Fields node, and then select New Field.
-
In the Properties window, specify:
Property
Value
DataSource
DirPartyTable_1
DataField
Name
-
Right-click the Fields node, and then select New Field.
-
In the Properties window, specify:
Property
Value
DataSource
OMHierarchyRelationship_1
DataField
ParentOrganization
-
-
Use the following steps to add the hierarchy view to the cube perspective.
-
In the AOT, expand the Data Dictionary > Perspectives node.
-
Find the perspective representing the cube that you want to add the organizational hierarchy to. This example uses the CustCube (Accounts receivable cube).
-
Expand the CustCube perspective and then the Views node.
-
Drag and drop OMHierarchyView under Views.
-
Select OMHierarchyView.
-
In the Properties window, specify:
Property
Value
AnalysisDemensionLabel
Org
AnalysisKeyAttributeLabel
Org
-
Expand the Fields node and select the Name field.
-
In the Properties window, specify:
Property
Value
AnalysisUsage
Attribute
-
Select the ParentOrganization field.
-
In the Properties window, specify:
Property
Value
AnalysisUsage
Attribute
AnalysisLabel
Parent org
-
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.
-
Create an extended customer transaction query using the following steps.
-
In the AOT, right-click the Queries node, and then click New Query.
-
Select the node for the query.
-
In the Properties window, type CustTransExt in the Name property.
-
Select the Fields node. In the Properties window, set the Dynamics property to Yes.
-
Expand the Data Sources node.
-
Right-click the Data Sources node, and then click New Data Source.
-
In the Properties window, type CompanyInfo in the Table property.
-
Select the Fields node. In the Properties window, set the Dynamics property to Yes.
-
Right-click the Relations node under CompanyInfo, and then click New Relation.
-
In the Properties window, specify:
Property
Value
JoinDataSource
CustTrans_1
Field
dataAreaId
RelatedField
DataArea
-
-
Create a view of customer transactions using the following steps.
-
In the AOT, expand the Data Dictionary node, and right-click the Views node. Then click New View.
-
Select the node for the query.
-
In the Properties window, type CustTransExt in the Name property.
-
In the view, expand the Metadata node.
-
Drag and drop the CustTransExt query under the Metadata node.
-
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.
-
-
-
Replace the CustTrans table with the CustTransExt view in CustCube perspective.
-
In the AOT, expand Data Dictionary and then expand the Perspectives node.
-
Expand the CustCube perspective, and then the Tables node.
-
Select the CustTrans table and note the values for the following properties: AnalysisDimensionType, AnalysisDimensionLabel, AnalysisMeasureGroupLabel.
-
Expand the CustTrans table and then Fields. Note the values for the following properties: AnalysisLabel, AnalysisUsage, AnalysisDefaultTotal, ExchangeRateDateField.
-
Remove the CustTrans table from the view.
-
Drag and drop the CustTransExt view under Views.
-
In the Properties window, specify the following values. (These values were noted in step c above.)
Property
Value
AnalysisDimensionType
Transaction
AnalysisDimensionLabel
@SYS618
AnalysisMeasureGroupLabel
@SYS9305
-
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
-
-
-
Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. Click Next.
-
Select Create and enter the project name. For example, CustCube Hierarchy.
-
Select Account receivable cube and shared dimensions. Then click Next.
-
Select required Microsoft Dynamics AX dimensions. Then click Next.
-
Select calendars for date dimensions. Then click Next.
-
Select required languages. Then click Next.
-
Add foreign currency conversion, if needed. Then click Next.
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.
-
Change Org dimension to a parent-child dimension.
-
Open created project in Visual Studio BI.
-
Double click Org dimension.
-
Select the Parent org member.
-
In the Propertise window, set the Usage property to Parent.
-
Process and browse the dimension.
-
-
Add the Org dimenision to the Dimension usage tab of the CustCube cube.
-
In Visual Studio, double-click CustCube in Solution Explorer.
-
Click the Dimension Usage tab.
-
For the Org dimension and the Customer transactions measure group, click add relation and do the following:
-
Set Relationship type to Regular.
-
Set Granulity attribute to Org.
-
Set Dimension Columns to RecID.
-
Set Measure Group Columns to Organization.
-
-
Process the cube.
-
Browse the cube.
-
Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
