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

Factless Fact Table can contain Flags (Yes or No)

4 posters

Go down

Factless Fact Table can contain Flags (Yes or No) Empty Factless Fact Table can contain Flags (Yes or No)

Post  hunain Thu Sep 19, 2013 12:55 am

Dear All,

I have a fact table (Factless) which records degree awarding event. If a student is about to graduate there is a record in this table which is made available initially with Status A and then finally the status gets updated to Awarded. I would like to introduce an indicator which says is this the first time the student has graduated, is this the last degree of the student and so on.

Can I add these indicators(Y/N) into the fact table or would I have to call this table dimension instead. Please advise. As per the below advise it seems its not recommended to have these in a fact table. Appreciate your advise guys. Thanks!

You should also prohibit text fields, including cryptic indicators and flags, from entering the fact table. They almost always take up more space in the fact table than a surrogate key.

hunain

Posts : 19
Join date : 2013-09-15

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Re: Factless Fact Table can contain Flags (Yes or No)

Post  BoxesAndLines Thu Sep 19, 2013 10:09 am

Essentially, you are designing an accumulating snapshot fact table. I would add dates and indicators for all important statuses. Make the indicators numeric so you can add them (they're really metrics).
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Re: Factless Fact Table can contain Flags (Yes or No)

Post  hunain Sat Sep 21, 2013 2:03 pm

Thanks for your reply. Essentially the student can have multiple degrees awarded e.g. Bachelors and Masters.

My indicator would ideally be Is first Credential Awarded, in this case its bachelor

My second indicator would be Is Highest Credential awarded and it would return masters.

The grain of the fact table is student record for each degree they have been awarded or are in the process of being awarded.

Why do you recommend these to be measures. Thanks for your explanation.

hunain

Posts : 19
Join date : 2013-09-15

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Re: Factless Fact Table can contain Flags (Yes or No)

Post  BoxesAndLines Sun Sep 22, 2013 9:01 pm

First, a point on indicators. Indicators are binary valued columns, normally Y and N in OLTP applications. However, in the DW, we use 1 and 0.

If you want an indicator to identify whether someone has a bachelor's degree, your indicator is named something like attained_bachelor_degree_ind. A 1 tells me the student has successfully obtained the degree. This is more informative than First Degree Attained Indicator.

Secondly, you make indicators numeric because you want to count the number of students that have attained a bachelor degree. When you sum a column, you have a metric.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Re: Factless Fact Table can contain Flags (Yes or No)

Post  ngalemmo Tue Sep 24, 2013 2:50 pm

Your indicators are dimensions, be they degenerate or FKs to a proper dimension (preferred). As far a metrics go, either have a column with a value of 1 or use count().
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Indicators in a fact table

Post  des_77 Tue Feb 17, 2015 12:57 am

I know this post is quite old, but I noticed it when considering similar options for storing indicators.  Of course I can create a junk dimension as is the convention, however the fact table (and contained facts in question, namely various order details) are only every likely to be related to one or two, perhaps three indicators, absolute maximum. Is it really necessary to complicate the model and create a separate dimension? At the moment only one indicator column is required for said orders and likely that will be the case for some time to come.  I guess my question is, if it is known that for the foreseeable future, a set of facts in a fact table will never be related to more than a couple indicative measures is it best (simplicity and performance wise) to store those indicators in the fact table (essentially degenerate dimension).  Then, should the unforeseen occur, they could of course be removed and a single FK reference to a junk dimension could be utilized should the number of required indicators blow out and make it worth while.  My indicators would always be numeric 1 or 0.   At the moment my fact table is only 16 columns wide including said indicator, approx 100 000 000 rows.  Any thoughts would be appreciated.

des_77

Posts : 3
Join date : 2014-05-15
Age : 45
Location : QLD Australia

Back to top Go down

Factless Fact Table can contain Flags (Yes or No) Empty Re: Factless Fact Table can contain Flags (Yes or No)

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