After a dimension is added, you can configure it, process it and browse and modify the result.
Note - when used in modules for Profitbase InVision and SSAS tabular, P/C dimensions must have the [Also Create As Wide] checked.
A Parent/Child Dimension will have a fixed Parent Item attribute with a self-referencing relationship. This attribute has columns that can be added from the source or from the [Property] tab.
Columns that are used in the fixed attributes are:
Select the [Dimension Definition] tab at top.
The [Key ID] (or root or binding ID) field must be selected first. It is the dimensions primary key column, and it is used when the dimension binds to a fact table in Modules. It is a Name Definition and it can be a surrogate key.
(PS6.1) The [Type] is the type of the dimension used in SSAS. Type is optional and if nothing set, 'Regular' will be used.
The ['All Level' member name] can be set with translations. It is used in dimensions in target systems like SSAS databases to create the highest level on top of the P/C hierarchy. If left empty it will not be created.
The [Unknown Member Mode] selects how unknown member is handled and shown by SSAS. In SSAS, dimension key attributes with errors can be set as unknowns (detailed control through through error handling).
Modes are
The [Unknown Member Name] can be set with translations. It is used in dimensions for target systems like SSAS databases the set the caption and translation for the unknown member item.
When Name and Key Id are selected, you can select sources for this dimension. Select the [Level Definition] tab and click on [New]. Enter values in the new row:
| Enabled | If this source should be used when processing/creating this dimension. |
| Hide | If hide is used, the items from this level will be hidden. They P/C relation will be skipped going from these items child directly to the parent item. |
| Source | Select a P/C Dim Data Source Table from the drop-down. |
| Item | Read only, it will get its value from the selected [Source]. |
| Parent | Optionally select a column (from the Data Source Table definition). The Item/Parent columns will be used to create a hierarchical structure. |
| Rank | When multiple source tables are mapped to the dimension, they must be ranked. When the highest priority Source does not contain the required information, then the second ranked Source is used and so on. Lower rank number means higher priority. |
| Display Format | Optionally select display format from the drop-down. This is used to format the [Description] (or name) column. |
| Description | Optional description. |
| Comments | Optional comment. |
| Source View | This shows the name of the source. Right click to browse or to start edit the source definition |
| Status | This shows the status of the source. |
To remove a source select the row and click [Delete].
Data can also be imported to dimension. Click on [Import] and a wizard dialog will be shown. This wizard will take data from the clipboard, Resource Library databases or other sources and add these into a new Data Source Table in a Library Data Source.
From the [Manual Mapping] tab, mappings between Item and Parent Items can be added and maintained. The mapping consists of a list with ‘From ItemID’s (or part of the ItemID) and ‘To ItemID’s (the Parent ID). The list can be imported or entered manually. Example of use is to map predefined standard account group structures to accounts.
Click on [New] and enter values in the new row:
| Source Table | From the drop-down select whether it applies to all sources or a specific one. If [Use for All Sources] is selected the mapping is used for all sources. |
| Map From | Select the Item it maps from. |
| Map To | Select (Parent) Item it maps to (can be the same as [Map From]). |
| Override | Set checked if this definition should override mappings defined in Level Definitions. |
| Map Items |
This shows an empty symbol (new symbol) if no data is defined. If data exists, a symbol with a pencil and notebook is shown. Click on these symbols to bring up a dialog for importing and editing mappings. See Using Mapping Dialog for more. |
| Comments | Optional comment. |
To remove a maping select the row and click [Delete]. Click on [Import] to import mappings.
From the [Properties] tab, additional columns can be added to the dimensions. These properties can be added in two modes:
Click on [New] and enter values in the new row:
| Use | Select option box if this Property is to be included in Dimension Tree generation. |
| Property | Select a Property or alt.parents from a drop down menu. |
| Inherit | Select if this Property value is to be inherited by children (value assigned to a Parent can be inherited down to Children if they do not have values. |
| Inherit Value |
Select if this Property can inherit values from the same ItemID with lower rank. This is if there are several ranked sources that can supply the value for this property or the property only exist in sources with lower rank. |
| Hierarchy |
Select if this Property or alternate Parent is enabled for use in hierarchy. |
| All Item Name | All item name for this property (if not Description_NN translation). |
| Describing Tree | If an alternate Parent is used as a Property, a Dimension Tree can be selected here as a source for Description. |
| Default Value | The value used in result if source/mapping result is null. |
| Map Properties | This shows an empty symbol (new symbol) if no data is defined. If data exists, a symbol with a pencil and notebook is shown. Click on these symbols to bring up a dialog to add/edit item id to property value mapping. |
| Comments | Optional comment. |
Note! Following applies to proerties in SSAS:
To remove a property select the row and click [Delete].
[Sort Type] Optional – Dimension Tree may be sorted by ItemID, Description or by a Property value (select from included properties, except language ‘Description’ properties).
[‘From Fact’ folder name] Optional – Select if and what folder to put ‘unnamed from fact’ items. These are items found in Fact tables while processing in Cubes or other Data Warehouse outputs, that do not exist in the Dimension Tree. New folders can be added in the Name Definition editing.
[Hierarchy Name in Cube] - When applied to a Parent/Child type of dimension this is the name of the parent attribute hierarchy. When applied to a Parent/Child Level or Level Hierarchy type of dimension this is the name of the dimension hierarchy in the cube. It is typically useful when you have several versions of a dimension (like one for each country) and you want to be able to write MDX formulas referring to a common dimension hierarchy name.
[High Parent Rank] - When selected, the system will find a parent among the lower ranked elements in the dimension hierarchy using the full key for the item. Note: This is different from the Autofind that only uses the ItemID (even if the key is defined to contain CompanyID and/or DataSourceID).
[Auto Find Parent] - When selected, the system will look for parents based on the ItemID key and when two (2) children have the same ItemID the child without a parent (from another Data Source or Company) will adopt the parent of the child with identical ItemID. Auto Find Parent is used when the Dimension Key method is other than Item.
For example:
Data Source 1 has the following Items: Parent A: - Item1 with parent A - Item2 with parent A Data Source 2 has the following Items: : - Item1 with no parent - Item2 with no parent - Item3 with no parent If the Dimension Key method is set to .D
the Dimension Tree will look as follows:
Parent A:
-Item1.DSource1
- Item2.DSource1
- Item1.DSource2
- Item2.DSource2
- Item3.DSource2
[Also create Wide version of this dimension tree] - when checked a wide version of the dimension is created based on the P/C dimension. It will have levels as LEAF, L1,L2, L3 ..,
In SSAS and other consumers, it can be handled as a Wide dimension.
Level Names only applies to Wide versions of P/C dimensions. Click on [New] and enter values in the new row:
| Level ID | Unique ID of the level |
| Level Name | Name of the level |
| Translation_NN | Translation of the Level Name per language NN |
To remove a level select the row and click [Delete].
Scrips can be created as SQL or PowerShell against the data warehouse database. In this section scripts can be connected to processing of the dimension.
For more information on adding scripts click here, and for more on linking script extensions click here.
SQL and PowerShell scripts can be run on results in DW before and after processing.
Click on [Tasks and Data] to open a processing dialog. The dimension will be processed automatically. Observe the result and log. Click the [Process] button o re-process.
After the dimension table is created click [Validate] to check the configuration and result.
At the top of the page; select the [Browse/Modify] tab.
The generated Dimension Tree structure in different modes:
Notes:
Tree w/Modifications mode
The Dimension hierarchy is displayed in an expandable and collapsible tree, assuming a hierarchy exists. Editing includes the ability to drag and drop Dimension Items nodes within the hierarchy or from the Tree Link tree. When a node is selected, it is displayed in the Dimension Item Editor section.
Nodes are displayed with different symbols. Items of Binding ID Type, is displayed with gray bullet, others with a folder w/bullet. If the item is modified it will have a orange star included within the symbol.
Level based trees has limitations on where Items can be dropped (only on the same level), and Tree Link will be disabled.
The tree view includes a right click menu:
Header toolbar includes command buttons for:
Dimension Item Editor
The Dimension Item Editor is only active when Tree w/Modifications mode is selected. Use this Editor to edit parts of the Dimension Item, to add new Nodes or to remove Modifications.
When a item is select in the Dimension Tree View or added, Description, Base Description (raw/non formatted), Unary Operator, Member Formula, Allow Transaction and optional Property values can be edited.
Member Formulas or MDX expressions has its own editor dialog. This editor provides some help for MDX functions/expressions and a list of dimension trees with a tree view that can be used to select items for use in formulas. See Microsoft documentation for MDX expressions.
Notes on Unary Operator
Note: Portions taken from “SQL Server 2005 Books Online”
In a dimension that contains a parent-child relationship you can specify a unary operator that determines the custom rollup for all non-calculated members of the parent attribute. The unary operator is applied to members whenever the values of the parent members are evaluated.
You can create and specify a named calculation on a dimension table in the data source view as a unary operator column. The simplest expression, such as '+', returns the same operator for all members. But you can use any expression as long as it returns an operator for every member.
The default setting for the Unary Operator property on a parent attribute is (none), which disables the custom rollup operators. The following table lists the unary operators and describes how they behave when they are applied to a level.
+ (plus sign) The value of the member is added to the aggregate value of the sibling members that occur before the member. This operator is the default operator if no unary operator column is defined for an attribute.
– (minus sign) The value of the member is subtracted from the aggregate value of the sibling members that occur before the member.
* (asterisk) The value of the member is multiplied by the aggregate value of the sibling members that occur before the member.
/ (slash mark) The value of the member is divided by the aggregate value of the sibling members that occur before the member.
~ (tilde) The value of the member is ignored.
Blank values and any other values not found in the table are treated the same as the plus sign (+) unary operator. There is no operator precedence, so the order of members as defined determines the order of evaluation.
Tree Link
The Tree Link tab is only active when the Tree with Modification view is selected and the Tree Type is Parent/Child. Use the Tree Link display to view the Dimension Trees in your solution and to drag and drop any dimension into the Dimension Tree display.
This capability along with the capability to create new nodes in the Dimension hierarchy enables report views to be created independent of how the raw hierarchy is constructed.
Dimension Table Editor
The Table Editor lists items in a table view. Cells with white background are editable. Rows with blue text are modified.
Columns in the table:
Header toolbar includes command buttons for: