Factless Fact Table can contain Flags (Yes or No)

View previous topic View next topic Go down

Factless Fact Table can contain Flags (Yes or No)

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

View user profile

Back to top Go down

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

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

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

View user profile

Back to top Go down

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

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

View user profile

Back to top Go down

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

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

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

View user profile

Back to top Go down

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

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

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

View user profile http://aginity.com

Back to top Go down

Indicators in a fact table

Post  des_77 on 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 : 38
Location : QLD Australia

View user profile

Back to top Go down

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

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