I am in the process of building a warehouse and I am unsure how to handle a certain element. We have a query that returns a table of "Quotes" a quote is basically when we have provided a quote to a customer for a service. Some of these quotes will lead to a sale and they will eventually become a customer but not all quotes will have a corresponding customer account.
The quote contains a lot of numeric data like the quote amount, various fees added and other numeric data we would want to aggregate later. So it seems like a typical fact table; a row for each quote. However it also has data relating to do it that it "Dimensional" in nature, although there's not many of them.
For example we have a label field called quote type that is a label, quote purpose which contains a label and a recommendation flag with is simply Y/N. Both "Label" fields do have an ID elsewhere in the database and the labels are fixed labels we use across the board.
So based on this, I feel the "Quote" query should really be a fact table, but I'm not sure what to do with these additional 3 fields that are labels. Should they have a dimension each? as that seems like a bit overkill. Or is it reasonable to have these 3 dimensions inside the fact table? Or should I have a DIM.Quotes and a FACT.Quotes and store these both separately?
Any help is appreciated
What you're asking is a Degenerated Dimension (see definition here : https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/degenerate-dimension/ ).
Yes you can but it's not recommended. you wan to keep a fact table row size as small as possible.
Maybe you want to take a look at the Junk dimension ( https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/junk-dimension/ ) which is a dimension with field that are not necessary related. And since you mentioned that all labels are fixed, you can also pre-build all combinaison.