Factless Fact Table can contain Flags (Yes or No)

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.

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

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.

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.

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().

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.