Difference between Factless FACT and Type 4 Dimension

View previous topic View next topic Go down

Difference between Factless FACT and Type 4 Dimension

Post  yanivmic on Tue Mar 01, 2011 3:42 am

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

yanivmic

Posts : 3
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines on Tue Mar 01, 2011 3:25 pm

What's a type 4 dimension?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  yanivmic on 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



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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  VHF on Thu Mar 03, 2011 6:00 pm

yanivmic wrote:

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo on 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?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  yanivmic on Sun Mar 06, 2011 4:23 am




[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

View user profile

Back to top Go down

I've been thinking lately that Address may be a type 0

Post  Skipjacker on 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.
avatar
Skipjacker

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  hang on 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

View user profile

Back to top Go down

One man's junk

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

avatar
Skipjacker

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

View user profile

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Difference between Factless FACT and Type 4 Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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