A Dimension is the logical organization of related members and their properties. For instance, you may have a Dimension for Geography, with the members Seattle, Olympia, and Tacoma and with properties such as State, Country, and Region Code defined for each member.
Similarly, you can have a Product Dimension with members representing individual product SKUs with other properties tracking such items as Brands and Product Grouping. When a Dimension is defined within an application, it can be reused in different contexts over multiple Models.
Before You Start
Hover over the the MODELER tab and select the application of your choice.
Once the application has been selected, click on the All Dimensions section.
Once you are in the All Dimensions section, the following options are available:
- Add: Add Dimensions to your application.
- Remove: Remove Dimensions from your application.
- Visual Totals: Configure whether the aggregated value users see in a Form will be filtered by their permission (checked) or not (not checked).
- Show Advanced: Display the advanced menu options.
The Show Advanced menu options are as follows:
- Hierarchy API: When checked, these functions will then be available for you to use as SQL Table-valued Functions. Each time the application is deployed, the hierarchy information will be updated for use by this function.
- All Hierarchy: This setting will control whether to deploy the dimension flat list as part of the SSAS deployment. As a best practice, this setting should be set to false for all dimensions.
- Unique: This setting will control whether to enforce uniqueness for the Member Label for the dimension. As a best practice, this setting should be set to true for all dimensions. The Member Label should be unique given that it can be referenced in Form and rule development.
- Read-Only: Sometimes the Dimensions and hierarchies are maintained through ETL, and at other times, a manual process. By making the Dimension read-only, the ability to edit hierarchy members will be disabled.
- SSAS Type: If integration with other client tools require specific attribute types, this option will allow you to specify the type of data each Member is. For attribute type, please refer to Microsoft's AttributeType Enum page.
To turn on the Dimension Security option, click on the Security checkbox in the same row as the desired Dimension.
Changes to the Dimension affect the database immediately, in order to update your application with changes made in this section either:
1. Deploy the application.
2. Process the Dimension.
Add and Delete Dimensions
In the All Dimensions screen, select the Add button.
Name the Dimension. Click Save when all the information is correct.
Tip: For more information on the options presented on the Add Dimension popup window, please read Part 2 of the Modeler Guide.
Navigate back to the All Dimensions section. Select the check box for the Dimension(s) you would like to remove. Click Remove . Click OK in the pop-up window.
Navigate to any of the [Dimension Name] subsections (excluding Attributes). Dimensions come with the following pre-defined set of properties:
- Member Label: This is the unique ID that identifies a member within the dimension. By keeping the Member Label unique, you can reference the member’s Member Label within rules and form design. The maximum allowed characters for this field is 200.
- Member Name: This is the friendly name that can be non-unique within the dimension. Use this property to give a descriptive name for the member. The maximum allowed characters for this field is 200.
- Input: The Input property determines whether to allow data input for the member. For instance the member Actual may never be required for input and should therefore be set to FALSE.
- Annotate: The Annotate property controls whether the member will display data (not checked) or comments (checked) on the Forms. Make sure the Query includes annotation and text cells option is also checked in the relevant Form.
EXCEPTION: The Account dimension comes with two additional pre-defined properties:
- By Account: Enable By Account logic for the account aggregations that are related to the Time dimension. For instance, balance accounts aggregate differently over Time than regular accounts as they typically aggregate based on the value of the last child. This option is not supported by SQL Standard Edition.
- AccountType: The AccountType property is used to define the GL account type for members of the account Dimension. Setting this property will also define the natural account aggregation behavior of the member within an Account hierarchy. Examples of values for this field include but are not limited to the following: Income, Expense, Liability, Ratio, Equity, Unit, Header.
Add and Delete Member Lists
Add Member Lists
Navigate to and select the [Dimension Name] of your choice.
Click the Member List button.
Name the Member List. Select whether the Member List is a List or Hierarchy. Once everything looks correct, click OK.
Delete Member Lists
Navigate back to the [Dimensions Name] section. Select the check box for the Dimension(s) you would like to remove. Click Remove . Click OK in the pop-up window.
Member List Options
Navigate to a Member List of your choosing.
Note: The All_[Member List] contains a record all the Members within a Dimension, however, this Member List cannot be used to define your Model. All other Member Lists contain a subset of the Members within a Dimension, and can be used to define a Model.
The following options are available in the Member List screen:
- Expand All: Expand all member nodes of the hierarchy showing the full structure. Collapse All will collapse all the members of the hierarchy.
- Refresh: Refresh the Member List definition with the most up-to-date version on the server.
- Add New: Create a new sibling member below the selected member.
- Add Existing: Add to the Member List with members that already exist on the dimension.
- Remove: Remove the member from the Member List. All descendants of the member will be removed from the Member List as well.
- Move Up: Move the selected member up in the hierarchy. All child members will automatically be moved up along with the member.
- Move Down: Move the selected member down in the hierarchy. All child members will automatically be moved down along with the member.
- Move Left: Move the selected member to the left, promoting it up one level.
- Move Right: Move the selected member to the right, demoting it down one level.
Tip: For more information on how to build Parent-Child relationships, please read the Configure Member List Mode article.
- Import: The import feature allows for bulk imports to define the hierarchy. The import requires a CSV file that will contain all the properties and relationships for the hierarchy. For more information, please refer to the Modeler Tutorial Part 2.
- Export: The export feature allows for bulk export of the hierarchy definition to a CSV file. You can also choose to export an empty hierarchy to generate the starting CSV file for use in imports.
- Configure: Change the mode for the Member List, either from List to Hierarchy or vice versa. This option is not included in the All_[Dimension Name] section. For more information, please refer to the Configure Member List Mode article.
- Show MemberId: Show MemberId (read-only) in the Member List
- MemberwithData: Display data members for non-leaf members in the parent attribute.
An administrator can write MDX directly into the definition of a Dimension Member through the Member Formula option. To Enable this option, click on the [Dimension Name].
Select the checkbox next to Member Formula.
Tip: For more information, please read Dimension Member Formula.
Add and Delete Members
To add a member, go to the Member List of your choice.
In the menu, select the Add New button.
Double click the Member Label to name it. Press Enter or click out to save the Member Label name.
To delete a Member from the Member List (not from the entire Dimension), select the Member of choice. Click the Remove button.