Inferred members in SSAS2005

This is about you can handle new fact records that have one or many dimension keys that is not part of your dimension table or a dimension key is null or blank.

Either you can solve this in the ETL process by adding an inferred member in each dimension, like ‘N/A’ or ‘unknown’, or you can let SSAS2005 handle this.

This post is about the last scenario.

Run this script in Management Studio to create a simple dimension- and fact table.

Create Table DimTime

(MyDate Datetime)

Insert Into DimTime (Mydate) Values(‘2007-11-20’)

Insert Into DimTime (Mydate) Values(‘2007-11-21’)

Insert Into DimTime (Mydate) Values(‘2007-11-22’)

Create Table MyFact

(MyDate datetime,

MySales decimal(9,2))

Insert Into MyFact Values(‘2007-11-20’,100)

Insert Into MyFact Values(‘2007-11-21’,200)

Insert Into MyFact Values(‘2007-11-23’,300)

Insert Into MyFact Values(‘2007-11-24’,100)

Insert Into MyFact Values(Null,100)

I will have two fact records with dates that are not included in the time dimension and one fact record without a date.

Open BIDS and create a new cube project, add a connection to the data source and build a data source view with these two tables. Make a logical key of MyDate in the time dimension and join the fact table MyDate key with the time dimension key. Build the time dimension with this single level(MyDate) and do not create a user hierarchy. Build a cube with the fact table and the single dimension table.

In the picture above you will see the final result in the cube browser.

First you will adjust you time dimension. Open the time dimension point at the dimension name in the top left window and choose properties. Do the adjustment according to the property window, for the ErrorConfiguration property you choose Custom in the next picture. You will also have to set the UnknownMember property to visible and give that member a name(UnknownMemberName)

Finally you will have to set the ErrorConfigurationProperty to custom for each measure group. You can see this in the last picture from the Adventure Works cube project.

(I will change this to my simple cube shortly and make the details of the screenshots more clear)

This is how you can handle missing dimension keys in the fact records nativly in SSAS2005.

In the real world you will have to keep records of non complete fact records in the ETL system, when complete information arrives,