Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Difference between Factless FACT and Type 4 Dimension

+2
BoxesAndLines
yanivmic
6 posters

Go down

Difference between Factless FACT and Type 4 Dimension Empty Difference between Factless FACT and Type 4 Dimension

Post  yanivmic Tue Mar 01, 2011 3:42 am

Difference between Factless FACT and Type 4 Dimension Factle13Hi,
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

yanivmic

Posts : 3
Join date : 2011-03-01

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines Tue Mar 01, 2011 3:25 pm

What's a type 4 dimension?
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  yanivmic Wed Mar 02, 2011 2:02 am

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

Difference between Factless FACT and Type 4 Dimension Factle14


Last edited by yanivmic on Wed Mar 02, 2011 2:13 am; edited 1 time in total

yanivmic

Posts : 3
Join date : 2011-03-01

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines Thu Mar 03, 2011 9:30 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  VHF Thu Mar 03, 2011 6:00 pm

yanivmic wrote:Difference between Factless FACT and Type 4 Dimension Factle14

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo Fri Mar 04, 2011 1:06 pm

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?
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines Fri Mar 04, 2011 2:58 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  yanivmic Sun Mar 06, 2011 4:23 am


Difference between Factless FACT and Type 4 Dimension Factle15

[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]




yanivmic

Posts : 3
Join date : 2011-03-01

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty I've been thinking lately that Address may be a type 0

Post  Skipjacker Fri Jun 01, 2012 5:46 pm

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.
Skipjacker
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 54
Location : Baltimore, MD

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo Fri Jun 01, 2012 6:47 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  hang Fri Jun 01, 2012 7:56 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty One man's junk

Post  Skipjacker Tue Jun 05, 2012 12:09 pm

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.

Skipjacker
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 54
Location : Baltimore, MD

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo Tue Jun 05, 2012 2:23 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Difference between Factless FACT and Type 4 Dimension Empty Re: Difference between Factless FACT and Type 4 Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum