Hello,

Slowly Changing Dimension of Type 2 (Kimball-speak) question.

Given: a dimension Customer(ID, CCode, Name, MyFlag, ChangeDate) where ID is
a surrogate key and CCode is the natural key.
If a customer's MyFlag changes, the historical facts related to that
customer should still reference to the previous state of MyFlag.
If this is implemented as a Type 2 Slowly Changing Dimension, this is what
happens:
Customer(1,a,abc,0,1-1-1980) has facts linked via ID 1
Customer(2,a,abc,1,today) will get facts linked via ID 2

With SQL I can clearly see how to draw reports from such a model: if you
need totals per Customer, add a "group by CCode". Then, in the example above
we make correct totals for customer "abc".

But how do you create Cubes and Dimensions from such a model ?
AS will think that there exist 2 customers named "abc".
Most reports should just ignore the MyFlag and make totals per customer. A
few reports however will make totals per MyFlag.


This must be a very common problem... How do you track changes in
attributes of dimension members ? There is probably a straightforward answer
but I don't see it.

Thanks,
Tom

Very quickly without going too deep into the problem could this work...

Customer(RecordID, CCode, CurrentRecordID, Name, etc, etc)

The CurrentRecordID for the record refers to the RecordID that is
active for a particular CCode. e.g.

Customer(1,CustomerA ,3 ,0 ,1-1-1980)
Customer(2,CustomerA ,3 ,0 ,1-1-1981)
Customer(3,CustomerA ,3 ,0 ,1-1-1982)
Customer(4,CustomerB ,6 ,0 ,1-1-1980)
Customer(5,CustomerB ,6 ,0 ,1-1-1981)
Customer(6,CustomerB ,6 ,0 ,1-1-1982)

You could in theory make the CCode the parent of RecordID in your
Customer dimension and have them grouped at that level. This way you
would report at that level whilst retaining type 2 information at the
leaf level of the dimension.

I think..!

Steve

Hi Steve,

Thanks for your answer.
Indeed, it may be a plausible solution!
And with a view I could even add meaningful labels to the leafs, i.e.
translate the MyFlag values into something human-readable...

For a clean implementation I should introduce a second surrogate key and not
depend on the natural key, I guess:

Customer(SurrogateCustomerKey, CCode, Name, SurrogateDetailKey, Details...)
Customer(1,a,abc,1,0,1-1-1980)
Customer(1,a,abc,2,1,1-1-1990)
Customer(2,b,def,1,0,1-1-1985)
....

I'll have to review the ETL for that.

I am not convinced yet that this is the best solution though. In practise
it may work, but semantically it is not correct: the properties that are
stored in the leaves do not belong to a hierarchy. Suppose you store
"IsMarried" as an attribute of a customer and you want to keep the history of
that flag. It is awkward to have a dimension that looks like
- John
--- John IsMarried-Yes
--- John IsMarried-No

That is the way you propose to implement it, correct ?

Regards,
Tom


"Steve McHugh" wrote:

Personally I would treat IsMarried as another dimension. Would that
better suit your scenario?

Steve

I already considered implementing it like that. But what if you need the
exact date when IsMarried changed ?

There are plenty of documents on the net explaining Slowly Changing
Dimensions of Type 2 and how to implent the ETL for that. Yet I found none
that goes to the end and also explains how to model these in a cube. Hm...

Tom


"Steve McHugh" wrote:

if you needed the exact date of the change you would see it in the
trend of the cube when you looked a records where IsMarried is "False"
the IsMarried is "True" for Customer "John" and as long as you time /
date dimensions went to that level of detail.

Again this is in my head, it's probably worth modeling it with a doxen
records and seeing what comes out.

Steve

hi Guys:

Yes Tom, this is a particularly frequent issue. Basically, what you have to
do is model each attribute as an independent dimension. Something called
"attribute based modeling". So , yur cube will end with something like this

Time
Year
Quarter
Month
Customer.IsMarried (Steve approach)
Customer.MyFLag
Customer.Name

I know the argument that comes. What about hierarchies? . From a very
theorical point of view, a hierarchy is just the sequence of attributes so
you cross the data to get more detail. With that in mynd, this model is
"hierarchy" friendly, cause you can drag MyFlag, IsMArried and see details.

About aggregations ? Do not worry, you´ll use them so not a big issue, more
than a ton of dims for the end user. (By the way, better get used to it.
That´s the way you´ll see them in Yukon)

Sincerely


--
Alejandro Leguizamo
MVP SQL Server
Colombia


"Steve McHugh" <steve (AT) oneleg (DOT) net> wrote

Hi Alejandro, Steve,

Thanks for your input. Luckily I did choose that route. :-)
"Attribute based modeling", I'll keep it in mind...

Regards,
Tom

"Alejo Leguizamo (MVP SQL)" wrote:

You always welcome!


--
Alejandro Leguizamo
MVP SQL Server
Colombia

"Tom VdP" <unick (AT) hot_mail_ (DOT) c-o-m> wrote