Dimensional Modelling: Excluding Unused Dimension Attribute Members

In a self-service BI platform, every member of a dimension attribute is available for browsing irrespective of whether the member has an associated fact or not. Though the unused members disappear when browsed alongside facts, they are still undesirable when used as filters. This is especially true with role playing dimensions which cover a broad range of data to cover every scenario. No one wants a date filter starting at 1900-01-01 and going all the way to 9999-12-31.

There are different options to exclude the unused dimension attribute members

Option 1: Use a View on Dimension Table

It’s always a good practise to build dimension using view on dimension table because of the agility it offers. A simple view like this would filter out any member that’s never been used in any fact table.

SELECT dateAttributes
FROM dimDate
   OR EXISTS (SELECT 1 FROM factB WHERE dateSK=dimDate.dateSK)
   OR EXISTS (SELECT 1 FROM factC WHERE dateSK=dimDate.dateSK)

Option 2: Use a flag on Dimension Table

Option 1 works like a charm on small dimension tables. With large data volumes, the SSAS processing time on these dimensions tend to increase because the query gets executed for each attribute. This can be overcome by modelling the dimension to include a materialized flag to indicate whether the dimension member is ever referenced by any fact. This flag is set to true by the ETL before the SSAS cube is processed using a simple update statement

UPDATE dimDate
   OR EXISTS (SELECT 1 FROM factB WHERE factB.dateSK = dateSK)
   OR EXISTS (SELECT 1 FROM factC WHERE factC.dateSK = dateSK)

The update statement would execute much faster when compared to SSAS processing time especially if a view is created on the dimension table to include only the dimension members in use.

SELECT dateAttributes
FROM dimDate

Option 3: Customize frequently used Role Playing Dimensions

Role playing dimensions need special attention. Sometimes in addition to option 1 or 2 you will also have to convert a frequently used role playing dimension to non-role playing dimension. This will exclude any unused member from a specific fact table (instead of any fact table). The key point to remember while taking this hybrid approach is regardless of whether the dimension is role playing or not, as long as it is derived from same source it must be a Conformed Dimension. This means the name and number of the attributes, key column, name column and value column, attribute relationships, sort order and hierarchies must be the same between the role playing and non-playing dimensions derived from same data source. The only difference is the number of members in a non-role playing dimension is fewer than the role playing dimension

Option 4: Work some MDX Magic in Presentation Layer

Self-service BI platforms are inevitably augmented by few out-of-box SSRS reports and dashboards. By using MDX expressions and data sets you could customizes the filter to display only the data relevant in that context. Here are some MDX examples for SSRS and PerformancePoint

By combining the above options you could ensure that your self-service BI platform is agile, practical and user friendly.

Benny Austin

SSAS: One Database, many Cubes Vs. One Database per Cube

So you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes. Continue Reading

SSAS: Consider Cube Browsing when Building Role Playing Dimensions

A Role Playing Dimensions reuses its definition to represent same concept in different contexts. Common example of Role Playing Dimension is date dimension. For instance, date dimension can be used to represent order date, delivery date and shipment date. Currency, staff, organisation hierarchy are other dimensions that are usually role played. While Role Playing Dimensions promote dimension reuse and share dimension data security, the effect on cube browsing must also be considered. Continue Reading