After a dimension is added, you can configure it, process it and browse and modify the result.
Notes - when used in tabular, wide dimensions configuration will change so that each column is an attribute , but these configurations will be used as basis.
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 for target systems like SSAS databases to create the highest level on top of all other hierarchies. 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
When Name and Key Id is selected, you can select source tables for the dimension. Select the [Source Tables] tab and click on [New].
A new row will be added. Select the Data Source Table by selecting the table from the drop down box in the Source Table column. Optionally select rank if more than one table is added, and add a comment.
(PS6.1) Time dimensions can also be used as Sources. After adding a new wide dimension, select Key Id as DateID or select 'Time' as Type and save. The Data Source Table drop-down will then include Time Dimensions as sources. If a Time Dimension is selected as source, you will be asked to import metadata from the Time Dimension, including Attributes, Relations and Hierarchies.
(PS6) To view the Data Source Table, select the row and click on [View].
(PS6.1) Right click the Source View column to browse source or to start edit its definition.
To remove a source select the row and click [Delete].
Note - when adding or removing a source table, the columns that are available for attributes may change. Click [Check Attributes] in the Attributes tab to validate.
To add/configure attributes select the [Attributes] tab. This section contains a column list to the left where columns are grouped and the key column is indicated with a symbol. The centre part list defines the attributes. Drag columns to the centre part to add a column as an attribute. The section to the right contains detailed attribute definitions. Select attributes from the centre section to edit details. Click [Check Attributes] to refresh the column list and to validate the attribute definition when completed.
A dimension is a collection of attributes that is bounded to columns in the data warehouse dimension table. An attribute can bind to columns with its id, value and name. One of the attributes must be the key attribute. This is used to bind the dimension to fact tables.
Attribute details:
[InUse] is a check box for enabling attributes. If [InUse] is checked, it will be included in data warehouse and can be used in relations and hierarchies. If [InUse] is checked and Attribute Hierarchy Enabled is unchecked, it will be included in the data warehouse output but not available in the SSAS database.
[Usage] is a drop-down menu to choose whether the attribute is the key (contains the key column), or if it is a regular attribute. Only key attributes can be defined by using [Usage], and it must be based on the selected Key ID.
(PS6.1) [Opt.type] is a drop-down for setting the SSAS attribute type. This is optional.
Attributes vs columns fields
An attribute maps to columns through:
|
Key |
The column containing the id value |
Non optional |
|
Value |
The column that has a user friendly id (e.g. if key is the surrogate key, the value can map to the original/natural id. |
Optional |
|
Name |
The column containing name or description |
Optional |
|
Unary Operator
|
The column containing unary operators that controls how members of a hierarchy are aggregate up to their parent. (Usually for P/C dimensions)
|
Optional |
|
Custom Rollup |
The column which contains the custom MDX rollup formula (if the unary operator do not give enough flexibility for rollup). (Ref. P/C’s Member Formula). |
Optional |
|
Name translations |
Additional Name columns can be added per language to support different client languages. |
Optional |
Name column can be formatted by selecting values in the [Display Format] drop-down box. Format is done in the data warehouse.
Attributes can be ordered by selecting key or name from the [Order by] drop-down box.
Key, Value and Name columns can have a [Value if null]. This value will be used if a source has no value (null) and this is done in data warehouse. The provided value must correspond to the column's data type.
Is Aggregatable and Default Member
Every attribute in a dimension in SSAS has a default member, in which you can specify by using the Default Member property.
This setting is used to evaluate expressions if an attribute is not included in a query. If a query specifies a hierarchy in a dimension, the default members for the attributes in the hierarchy are ignored. It applies to every hierarchy in which the attribute participates. You cannot use different settings for different hierarchies in a dimension.
If a query does not specify a hierarchy in a dimension, the Default Member settings for dimension attributes take effect.
If the Default Member setting for an attribute is blank and its 'IsAggregatable' property is set to True, the default member is the All member. If the 'IsAggregatable' property is set to False, the default member is the first member of the first visible level. This may result in problems and aggregations.
Other Attribute settings
|
Grouping Behavior |
This is used to give a hint to the client application whether to encourage or discourage users to group on this attribute. |
|
Attribute Hierarchy Enabled |
This is used to determine the absence or presence of attribute hierarchies. This setting affects performance, less is better.. |
|
Attribute Hierarchy Visible |
This is used to set the visibility of the attribute to a client application. |
|
Attribute Hierarchy Ordered |
Specifies whether the members of the attribute are ordered or not. |
To edit, select the [Relations] tab. To the left the attributes are listed. At the centre the attribute relations are drawn. Here the key attribute is the root item. Drag attributes to create relations. Click on an attribute with a relation, and you can edit details to the right. To validate relations, click on [Check relations].
Attributes are organized based on attribute relationships. By default, all attributes are directly related to the key attribute. This enables users to browse the facts in the cube based on any attribute hierarchy in the dimension.
Specifying good attribute relationships results in a cube that performs well because SSAS can take some shortcuts when processing it. It is the 'best practice' to relate attributes that you have placed in a hierarchy because they probably will be used in aggregations. Bad/missing data can give errors or bring down the performance.
Attribute relationships can provide:
The main constraint when you create an attribute relationship is to make sure that the attribute refers to the attribute relationship and has no more than one value for any member in the attribute to which the attribute relationship belongs.
Typical usage of attribute relations
|
For a natural hierarchy
|
Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it. For Analysis Services, this specifies a natural relationship and potential aggregation. |
|
For navigating data in the cube
|
Create a user-defined hierarchy that does not represent a natural hierarchy in the data (which is called an ad hoc or reporting hierarchy). |
When defining a relation the following properties apply:
|
Cardinality |
Indicates the cardinality of the relationship. Values are Many, for a many to one relationship, or One, for a one to one relationship. Default value is Many. In Microsoft SQL Server Analysis Services, the cardinality property has no effect - its use is reserved for a future implementation. |
|
Relationship Type |
Indicates whether member relationships changes over time. Values are Rigid, which means that relationships between members do not change over time, or Flexible, which means that relationships between members change over time. Default is Flexible. If you define a relationship as flexible, aggregations are dropped and recomputed as a part of an incremental update (they will not be dropped if only new members are added). If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is incrementally updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during incremental processing. Specifying the appropriate relationships and relationship properties increases query and processing performance. |
|
Visible |
The Visible setting specifies whether the related attribute is accessible, as a member property of the current member, to the information consumer. |
To edit, select the [Hierarchies] tab. The attribute list is placed to the left. At the center, there are hierarchies with levels list. To the right the hierarchy name and 'all level' names can be set with translations (when hierarchy is selected). To add a hierarchy click [New], to delete select the hierarchy and click [Delete]. Drag attributes to the hierarchy to add as level. Right-click level to delete it.
By default, attribute members are organized into two level hierarchies, consisting of a leaf level and an All level. The All level contains the aggregated value of the attribute's members across the measures in each measure group to which the dimension of which the attribute is related is a member. However, if the 'IsAggregatable' property is set to False, the All level is not created. For more information, see Dimension Attribute Properties Reference.
Attributes can be, and typically are, arranged into user-defined hierarchies that provide the drill-down paths by which users can browse the data in the measure groups to which the attribute is related. In client applications, attributes can be used to provide grouping and constraint information. When attributes are arranged into user-defined hierarchies, you define relationships between hierarchy levels when levels are related in a many-to-one or a one-to-one relationship (called a natural relationship). For example, in a Calendar Time hierarchy, a Day level should be related to the Month level, the Month level related to the Quarter level, and so on. Defining relationships between levels in a user-defined hierarchy enables Analysis Services to define more useful aggregations to increase query performance and can also save memory during processing performance, which can be important with large or complex cubes.
From the [Options]-tab, following processing options can be set:
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. Click the [Process] button and observe the result and log.
After the dimension table is created click [Validate] to check the configuration and result.
On the top of the page, select the [Browse/Modify] tab. The left section contains a drop-down menu with hierarchies. The right section contains a mode drop-down and a table with result data.
The hierarchy section can be used to filter the result. Select hierarchy, check [As filter] and select the node to use as filer.
From the [Mode] drop-down, you can select: