1. Introduction to Dimensions
SSAS dimensions are groups of attributes based on columns from tables or views in a data source view. Dimensions exist independent of a cube, can be used in multiple cubes, can be used multiple times in a single cube, and can be linked between Analysis Services instances. A dimension that exists independent of a cube is called a database dimension and an instance of a database dimension within a cube is called a cube dimension. A dimension cannot be added directly to a cube until it is added as a database dimension.
- Properties: Name column: e.g. Key column is ProductID, while Name Column is Product Name (it helps you to view and understand the data).
- Why you should have a key in each and every dimension table? Because without the key attribute, we cannot join the dimension and fact table, and therefore, cannot analyze the data on the dimensional level.
2. Dimension Structure
Each dimension contains a key attribute. The key attribute is the attribute in a dimension that identifies the columns in the dimension main table that are used in foreign key relationships to the fact table. Typically, the key attribute represents the primary key column or columns in the dimension table. You can define a logical primary key on a table in a data source view which has no physical primary key in the underlying data source. We can add additional columns at this level.
3. Dimension Hierarchies
Hierarchies in SSAS optimize performance of cube by improving cube aggregations. There are two major types of hierarchies:
- Natural (automatically comes within the dimension)
- Balanced: In a balanced hierarchy, all branches of the hierarchy descend to the same level, and each member's logical parent is the level immediately above the member. (Year > Semester > Quarter > Month)
- Unbalanced: In an unbalanced hierarchy, branches of the hierarchy descend to different levels. Parent-child hierarchies are unbalanced hierarchies. (Manager ID à Employee ID)
- Ragged: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.
4. Attribute Relationships
Each and every non-key attribute in a dimension must be directly or indirectly related to Key attribute. Relationship between attributes can be changed to either “Flexible” or “Rigid”
- Flexible -> the relationship will change over time.
- Rigid -> the relationship will not change over time.
5. Dimension Translations
In SSAS a dimension translation is a language-specific representation of the name of a dimension, the name of an Analysis Services object or one of its members, such as a caption, member, or hierarchy level. Translations provide server support for client applications that can support multiple languages. Frequently, users from different countries view a cube and its dimensions. It is useful to be able to translate various elements of a cube and its dimensions into a different language so that these users can view and understand the cube.
Once the dimension is Processed and Deployed, the data and hierarchy behavior can be checked in Browser Pane.
1. Cube Structure
Measures: A measure represents a column that contains quantifiable data, usually numeric, that can be aggregated. Group of measures having same granularity forms a Measure Group.
Dimensions: All dimension added in Dimension folder can be used in instance of a Cube. Attributes that are added and processed at Dimension level are showed in Cube Dimension. Dimensions that are available at DSV are only pulled into Cube Structure. Cube dimensions can be edited to add new attributes or hierarchies.
2. Dimension Usage
After you got the inappropriate data after you processing the cube, the first place to check is the Dimension Usage. The second cube object Dimension Usage describes how a Dimension is related to Measure Group in Cube. (e.g. data from AdventureWorksDW2012)
Dimension Keys are automatically related to respective Measure Group. If there is no relation between a Dimension and Measure Group, there cannot be any aggregations created across that Measure Group. A Dimension and Measure Group can have the following relations:
- No Relation (facts and dimensions are not connected, because no connecting keys)
- Regular (fact referencing dimensions, one-to-one or one-to-many relationship)
- Referenced (fact and dimension table is joined though an intermediate dimension table.)
- Many-to-Many (4 tables involved)
- Fact (a de-generated dimension that is any dimension member which is increasing with the fact measures could be included in a fact table. Indirectly, the dimension and the fact could be combined)
We can add additional measures, dimension attributes, new calculated columns (called calculated members). A calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set in a cube in SSAS. Calculations let you add objects that are defined not by the data of the cube, but by expressions that can reference other parts of the cube, other cubes, or even information outside the Analysis Services database.
4. KPIs (Key Performance Indicator)
In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business success. A KPI is frequently evaluated over time. KPI components are:
- Value: An MDX numeric expression that returns the actual value of the KPI.
- Goal: An MDX numeric expression or a calculation that returns the target value of the KPI.
- Status: An MDX expression that represents the state of the KPI at a specified point in time. The status MDX expression should return a normalized value between -1 and 1.
- Trend: An MDX expression that evaluates the value of the KPI over time. The trend MDX expression enables a business user to determine whether the KPI is improving over time or degrading over time.
Cube partitions are used by SSAS to manage and store data and aggregations for a measure group in a cube. Every measure group has at least one partition; this partition is created when the measure group is defined. When you create a new partition for a measure group, the new partition is added to the set of partitions that already exist for the measure group. A Cube in general stores aggregations, data and metadata. Based on the storage mode, the Cube Partitions are divided as
- Multi Dimensional Online Analysis Processing (MOLAP): Stores Data, Aggregations and Metadata in Cube.
- Relational Online Analysis Processing (ROLAP): Stores Data and Aggregations in Relational Database and metadata in Cube.
- Hybrid Online Analysis Processing (HOLAP): This storage mode is hybrid of MOLAP and ROLAP. In this mode Aggregations are stored in Cube, whereas Data and Metadata is stored in Relational Database.
Aggregations are pre-calculated summaries of data from leaf cells. Aggregations improve query response performance by preparing the answers before the questions are even asked. Dimensions and their hierarchies of attributes reflect the queries that can be asked of the cube. Aggregations are stored in the multidimensional structure in cells at coordinates specified by the dimensions.