Writeback bug or feature in SSAS 2008 R2

I wrote on my last project a .net desktop application used for forecast and

monitoring in contact centers as reflected in the blog entry 'Cube writeback with multiple values in one shot'.

I won't reveal all the details of this application but it is based on OWC on a windows form, cube actions intercepted in events fired when user invokes a context menu and finally writing the changes to the cube.

You may recall that cube changes are not written directly to cube cells but to a relational table

called writeback table.

    

The writeback table in my case has columns for all measures in the measure group

- [OfferedCalls_0], [ForecastedOfferedCalls_1], [AdvisorsAbsent_2] ,

 [Distribution_4], [EmployeesPlanned_5], [CallsPerAdvisorDistribution_6] -,

the interecting dimensions :

[SKTime_7] foreign key which points to a Time dimension covering all the 96 quarters of the hours during a day ,

[SKCallFlowDepartment_8] foreign key which points to an organisational dimension  

[SKDate_9] foreign key pointing to the Date Dimension

and two additional columns for auditing purposes - MS_AUDIT_TIME_10] ,  [MS_AUDIT_USER_11] .

The table looks like :

CREATE TABLE [dbo].[WriteTable_Forecast Measures](

 [OfferedCalls_0] [int] NULL,

 [ForecastedOfferedCalls_1] [int] NULL,

 [AdvisorsAbsent_2] [int] NULL,

 [Support_3] [int] NULL,

 [Distribution_4] [float] NULL,

 [EmployeesPlanned_5] [int] NULL,

 [CallsPerAdvisorDistribution_6] [float] NULL,

 [SKTime_7] [bigint] NULL,

 [SKCallFlowDepartment_8] [bigint] NULL,

 [SKDate_9] [bigint] NULL,

 [MS_AUDIT_TIME_10] [datetime] NULL,

 [MS_AUDIT_USER_11] [nvarchar](255) NULL

) ON [fgCurrent]

What it really happens is that the delta changes written to the writeback table inserts a row with a null value for the [SKTime_7] column and this is certainly neither expected or desired.

Cube processing with the default settings fails due to the null key. If you ignore the errors and succeed in processing the cube, the numbers the users entered once are different then the ones displayed by the client, so you have a very serious problem.

You have no influence of this process: playing with the mdx update cube ... statement, changing among the four allocation options ( USE_EQUAL_ALLOCATION is the default option) or

trying to enforce the foreing key constrains will not help you.

So I had to find a work-around based on the following steps : 

• Retrieve the row with the null key in a temporary table with a select into statement.
• Update the writeback table by adding the values retrieved from the temporary table to the values of the corresponding measure for a particular row generated by the client.
• Delete the 'orphan' row.

 as implemented in the bellow script .

select ForecastedOfferedCalls_1 , SKCallFlowDepartment_8 , SKDate_9 , SKTime_7, MS_AUDIT_TIME_10 , MS_AUDIT_USER_11
into #Write_Temp   from dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

update  dbo.[WriteTable_Forecast Measures]
set ForecastedOfferedCalls_1 = a.ForecastedOfferedCalls_1 + b.ForecastedOfferedCalls_1
from dbo.[WriteTable_Forecast Measures] a, #Write_temp b
where a.SKDate_9 = b.SKDate_9
and a.SKCallFlowDepartment_8 = b.SKCallFlowDepartment_8
and a.MS_AUDIT_TIME_10 = b.MS_AUDIT_TIME_10
and a.MS_AUDIT_USER_11 = b.MS_AUDIT_USER_11
and a.SKTime_7 in ( select SKTime from dim.LocalTimeOfDay
where MNQuarterInterval = '00:00-00:15' )

delete dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

This seems to solve the problem. But I did not encounter this issue when running the application in the previous SSAS release and this makes me think of the never ending dilemma : bug or feature of the latest SSAS release. This is the question...

 

SCD Type 1 , 2, 3 or 1 + 2 + 3

Question: When you define a SCD (slow changing dimension) as type 1, 2 or 3 ?

I experienced some confusion during the past, so that is why I'd like to put the 'selection' process into perspective, although the discussion may seem a little bit theoretical:
For each SCD dimension :

• Define the business key, the key that identifies your identity, as for instance the Social Security Number ( US) or Cpr. nr (DK) for a customer dimension.
• Pick-up each attribute you have to track changes over time. Do not include her the business key, the surrogate key, and focus only to the attributes that describes, in other words add pieces of information to your business key. ( Addresses, birth day, number of children, marital status , etc. for a customer.)
• Start by diving the attributes you idendified from above in two categories first : attributes where you are not interested at all in tracking the changes (birth date for instance), let' call it category 1 and attributes where you are interested in tracking the changes ( name, adresses, ) , as category 2
• For the attributes in category 2, make a clear decision from the start whether:
• when an attribute value changes you are only interested in the last value. That is an in place override of the previous value and no posibility to track the historical changes. Such an attribute is a type 1 attribute and might be a name or address.
• if you have to keep the historical chages, then you have to choose between two practical ways to implement it :
• type 2 - the most common . You may add either ( StartDate, EndDate) or IsCurrent or even both of them.
• type 3 - less common

Keep in mind that the business requirements decide whether your attribute falls into one category or type.

You will often end with a dimension having attributes of category 1 and category 2 and the latter having both type 1 and type 2 and in rarely cases a mix of type 1, 2 and 3.

So the answer to the original question may sound like:

If you keep all the attributes in your dimension, and your dimension has at least one type 2 attribute , than people call it a SCD type 2 dimension.

    

But what about if you have at least one type 2 and at least one type 3 attribute ?       

It's both a type 2 and type 3 and often not to mention type 1.

In that case, by only calling it type 2, you may hide the representation of the two other types of attributes.

So why not call it : 1 + 2 + 3 ......