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.

CREATE VIEW vdimDate
AS
SELECT dateAttributes
FROM dimDate
WHERE EXISTS (SELECT 1 FROM factA WHERE dateSK=dimDate.dateSK)
   OR EXISTS (SELECT 1 FROM factB WHERE dateSK=dimDate.dateSK)
   OR EXISTS (SELECT 1 FROM factC WHERE dateSK=dimDate.dateSK)
GO

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
SET DATE_IN_USE_FLAG=1
WHERE EXISTS (SELECT 1 FROM factA WHERE factA.dateSK = dateSK)
   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.

CREATE VIEW vdimDate
AS
SELECT dateAttributes
FROM dimDate
WHERE DATE_IN_USE_FLAG=1
GO

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