Inferred members and dimension changes
Posted by thomasivarssonmalmo on November 29, 2007
In a previous blog post I have described how to configure SSAS2005 to accept fact records with dimension keys that are not valid. For this scenario to happen you will have to build the cube’s starschema without foreign keys constraints, this means without relations between the dimension tables and the fact table.
I think that the business scenario for this feature if you use proactive caching and have less control of the keys between dimensions and the fact table.
In this post I will focus on what will happen if a missing dimension member is added after the fact record. How will the inferred member in the dimension change the connection to the fact record? You will have to run the simple exercise in the previous post in order to follow things here.
Let us start with having a look at the records in the dimension table and the fact table. We can do this by running this TSQL Select in management studio.
The fact records are in the two last columns and the dimension records are in the first.
I have configured the time dimension like this in the properties window.
The time dimension in BIDS look like this.
This is the cube structure.
If I start to process the cube without a KeyErrorLimit,
I will recieve the following error.
I set the KeyErrorLimit to 1000 and process the cube again. This time it is finished and after that I have a look at the cube in the browser.
That is where we finished last time. Let us do an update of the dimension table and add a date that is part of the fact table.
Insert Into DimTime(MyDate) Values(‘2007-11-23’)
This is the result in management studio.
After the insert is finished you process the dimension and the cube in BIDS and reconnect the cube browser to the cube.
The sale of 300 on the date of 2007-11-23 have moved from unknown date to the correct date.