Is it a good idea to include flags in Fact table

Question

The transactional fact table of one ofthe star schemas need to anser questions like Is the first application is final application.This is associated with one of the business process. Is it a good idea to keep this as a part of the fact table with a column name, IsFirstAppLastFlag. There are not much flags to create a seperate dimension.Also this flag(calculated flag) is essential in the report writing.In this context do we need to keep it in Dimension or in Fact!

I assume the creation of junk dimension is for those flags /low cardinality columns which are not so useful can kept it inside a dimension?!

score 3 · Answer 1 · answered Sep 08 '15 at 11:32

This will depend on your own needs but if you like the purest view of the fact table then the answer is no, these fields should not be included in your fact table.

The fact table should include dimension keys, degenerate dimension keys, and facts.

IsStatusOne, IsStatusTwo, etc are attributes and as you rightly suggest would be well suited to a junk dimension in the absence of them belonging to a more suitable dimension, e.g., IsWeekDay would be suited to dimension "Date" table.

You may start off with only a few "Is" attributes in your fact table but over time you may need more and more of these attributes, you will look back and possibly wish you created a junk dimension.

Performance: Interestingly if you are using bit columns for your flags then then there is little storage difference in using 8 bit flags in your fact table then having one tinyint dimension key, however when your flags are more verbose or have multiple status values then you should use the junk dimension to improve performance on the fact table, less storage, memory, more rows in a page, etc..

Personally, I would junk them

Do you think we should combine these attributes in only one table? I mean if there are two attributes of true/false, then the two attributes should be put in the columns, and get 4 rows to indicate the 4 different combinations. — mingchau, Apr 30 '19 at 09:04
@mingchau yes you should put all combination possible to one dimension table (just for flag filed). that dimension name is JunkDimension. — Ardalan Shahgholi, Aug 19 '20 at 13:15

score 1 · Answer 2 · answered Sep 08 '15 at 11:30

That seems fine, as long as it it an attribute of the fact, not of one of the dimensions. In some cases I think you might have a slowly changing dimension in which it would be more appropriately placed.

I would be concerned that this plan might require updates on the fact table, for example if you were intending to flag that a particular fact was the most recent for a customer. If that was the case it might be better to keep a transaction number in the fact table, and a "most recent transaction number" in the dimension table, and provide an indexing method to effectively retrieve the most recent per-customer.

score 0 · Answer 3 · edited Mar 24 '21 at 02:51

You can use Junk Dimension.

Instead of creating several dimension with few rows you can create on dimnsion with all possible combination of value then you add just one foregion key in your fact table.

you can populate your junk dimension with a query like below.

WITH cteFlags AS
(
    SELECT 'N' AS Value
    UNION ALL
    SELECT 'Y'
)
SELECT
    Flag1.Value,
    Flag2.Value,
    Flag3.Value
FROM 
    cteFlags Flag1
    CROSS JOIN cteFlags Flag2
    CROSS JOIN cteFlags Flag3

Is it a good idea to include flags in Fact table

3 Answers3