Analysis Services supports the ability to define dimensions based on columns in the fact table – also known as Fact Dimensions. These dimensions are very useful when attributes that exist in the fact table need to be exposed in our OLAP query or Drill through action, such as Invoice Number or Order Reference.
WhereScape RED does not directly support this dimension type, but it is simple to create equivalent functionality using RED and database views. RED provides the ability to quickly and easily modify the data warehouse database using views to accommodate cube design, rather than modify the DSV within a cube database. This approach is useful when equivalent queries are required from both the data warehouse and cubes.
To define and add a degenerate / fact dimension to an OLAP cube already defined in RED follow these steps:
- First we need to define a dimension view built from the fact table, containing the degenerate attribute(s). The dimension view is defined in RED by dragging the fact table attribute(s) into the Dimension list to create a new Dimension View. This dimension view needs to have a single unique key column - perhaps just the degenerate attribute. Next, define the dimension’s key column as an artificial key by checking the artificial key checkbox in the key column properties in RED.
Note: If the degenerate dimension has a composite key, then a single unique key needs to be created in the dimension view by concatenating the composite key columns. This key also needs to be added to the fact table or in a view over the fact table to enable a fact / dimension relationship to be defined using a single column.
- Once created, the degenerate dimension view can be dragged over to the list of OLAP dimensions to create a new OLAP dimension in RED. Ensure the key column is identified correctly in the OLAP dimension properties.
- The OLAP dimension can be added to a cube in RED by right clicking the cube and choosing Add Dimension. Choose the new degenerate dimension.
- To define the relationship between fact and degenerate dimension right click the cube and choose Display Measure Group Dimensions. The relationship between fact and degenerate dimension will be shown as “No Relationship”. Double click this relationship and change the Relationship type to Regular, and choose the key column in the fact that matches the dimension’s key column.
- Now you can create and process the cube. The degenerate attribute(s) will be exposed in the new degenerate dimension.
This method has been tried and tested on client sites. If you run into problems please let us know.