Difference between Factless FACT and Type 4 Dimension

[yanivmic]

Hi,
I have 2 tables of Deposits.
1- Deposits table - holds data on the current status of each deposit.
Fields:
Deposit_ID
CreateDate
Customer_ID
Status_ID
DepositAmount


2- Deposit History table- holds deposit status historical changes:
DepositChange_ID
StatusChangeDate
Status_ID
Deposit_ID

* The deposit amount does not change over time and that is why it is not in the Deposit History table.
* Deposit_ID in the Deposit history table is a FK to the Desposit table.



My question - The Deposit table (the first one) is a FACT. But is the Deposit History table (second table) is a factless FACT or a type 4 dimension? Can you exlpain the difference?
Thanks
M

[BoxesAndLines]

What's a type 4 dimension?

[yanivmic]

It's a slowly changing dimension. That holds all the changes with a Is_Current flag.
For example you want to save the country og suppliers and the country changes from UK to USD.
The figure I added describes how the dimension will look before the change of the country and afterwards.

Thanks

[BoxesAndLines]

Deposit History is not a table used in dimensional models. All metric history is maintained in the fact table.

[VHF]

That looks like a SCD Type 2 to me... although it appears to be lacking a surrogate key (SK) as a primary key on the table. It is also lacking Begin_Effective_Date and End_Effective_Date fields, although strictly speaking these are not required; the Is_Current field is sufficent.

[ngalemmo]

Looks like a 2 to me too.

There is a Wiki page that lists various and sometimes incorrect type definitions at http://en.wikipedia.org/wiki/Slowly_changing_dimension. It's kind of silly. I mean, there are various ways you can implement things, but there is no reason to give every minor variation a different number. Has anyone ever implemented a type 0? Or actually called it that?

[BoxesAndLines]

The previous modeler here implemented type 0's. He's no longer here. Model at your own risk.

[yanivmic]

[size=12]

Isn't the second table: "Deposit History" (which hold the Change of Status of a Deposit) a Factless FACT?

It has no measures, we are using it for collecting deposit transaction stats and it has key values with refernce to the dimensions from which the stats can be collected.

I don't think that only because I have a "IsCurrent" flag it makes it a dimension.


[/size]

[Skipjacker]

More philosophically than practically but... addresses don't change. The relationship of a building to an address may change. Some buildings in South Carolina recently got moved to North Carolina when a border was clarified. Did the address change or did the building just get related to a new address while the old address eventually will go inactive. The old address will still exist AND WORK for a while. Since they can both exist simultaneously, it didn't change a new one was invented and the edifice at that location was unassigned from the old and assigned to the new. So couldn't address be a Type 0?

For most business type 1 or 2 would be fine but for the Post Office, they could benefit from a type 0.

[ngalemmo]

In the first edition of Toolkit, Ralph described Junk Dimensions as a dimension whose natural key is the attributes themselves. An address dimension would fall into such a definition as would a true junk dimension made up of leftover attributes. Someone at sometime may have coined Type 0 to describe such a dimension. Personally, I like Junk.

[hang]

You may model deposit as an accumulating snapshot fact. So deposit_id is a degenerate dimension and the change of status will be reflected by respective milestone date dimension (role playing). I don't think a SCD2 dimension can achieve much in terms of status tracking for deposit. You may also complement the model with another transaction fact to track every deposit status change.

[Skipjacker]

I do hear what you're saying. I agree with your overall architectural philosophy to not muddy the waters with unnecessary with new terms. On the other hand, describing an address dimension as a junk dimension would be confusing to vast majority of even proficient modelers. Junk has the connotation of, as you say, left-over attributes.and not of a well understood and easily articulated object like address. I'm not saying it's wrong, just confusing.

To the same point, I probably wouldn't walk around the data shop talking about the Type 0 Address dimension and expect the developers to understand what I wanted. I would explicitly explain how to load that table. After all, these terms are meant to be a short-hand to quickly discuss a concept which everyone agrees to a priori to the conversation. Without mass agreement to the definition, using the term will lead to chaos, not order. As Shaw said, the problem with communication is the illusion that it happened.

[ngalemmo]

Skipjack, it was just conjecture on my part as to where the idea of a type 0 came from. I don't use or like the term myself. Address, to me, would be a type 1. I also don't get the various other numbers thrown around (type 4, type 6 etc...). The three original types pretty much sum up what you need, with a few variations to deal with simplifying getting the current row from a type 2.

[Sponsored content]