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.

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

SSRS: Parameter Expression for MDX Datasets Having Role Playing Dimension

In SSRS report, when a report parameter is role playing dimension then the parameter value passed to each dataset has to be customized depending on the context in which the dimension is being used. In an earlier post, I explained how this is done in PerformancePoint dashboard. In this post an equivalent solution for reporting services is provided.

Continue Reading

PerformancePoint: Using Connection Formula to Pass Filter Values to Report Having Role Playing Dimension

Date dimension is inevitably used in most dashboards. Due to its role playing nature, a specific date dimension used as slicer in one report is different from the date dimension used for another report on the same dashboard page. For example sales reports are based on order date whereas delivery reports are based on delivery dates. On a dashboard that displays both sales and delivery reports, it would not be good for navigation purposes to have both order date and deliver date filters on the same page. The choice of which date dimension to use as filter among the available dates is a tactical decision. Having chosen a particular date dimension, the challenge is to provide filter value to those reports that use a different date dimension. This is where PerformancePoint filter Connection Formula comes into play as explained in the example below.

Continue Reading

Dimension Modeling: Creating Business Friendly Dashboards with CURRENT attributes in Date Dimension

Date Dimension is the most commonly used dimension in any BI implementation. Often used as role playing dimension, there is practically no BI Implementation without date dimension. Adding current indicator attributes with Y/N values to date dimension like current year, current quarter, current month, current week and today enhances dashboarding capabilities and provide better navigation for business users. Continue Reading

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: Using AMO to Secure Analysis Service Cube

Analysis Management Objects (AMO) opens up the object model of SSAS cube for programming. With a bit of .NET programing, AMO can be effectively used to automate administration tasks. Securing the cube is one such task that can be easily automated using AMO. The rest of this post explains how to automate cube security using AMO. 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

Analysis Service (SSAS) Vs. PowerPivot

This post compares Analysis Services and PowerPivot. Continue Reading…

Securing Role Playing Dimensions in Analysis Services

In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
Continue Reading

Cell Data Access Vs Dimension Data Access in Analysis Services

Analysis Services security model is role based. Within a role, the cube data can be secured by either Cell data or Dimension data. In this post, I compare the relative merits of each method.
Continue Reading