CODEWITHSUNDEEP
sqldata-modelingdata-warehousebusiness-intelligence
Prabhat G
Prabhat G

Reputation: 3029

Can a fact table act as dimension table?

I was exploring through Kimball's 'The data warehouse tooklit', where I came across scenario where fact table is acting as dimensions, but I wasn't very pleased with explanation mostly, because I am new to dimension modelling.

My question is

  1. what are the instances/examples where a fact table can act as a dimension table? (request to put up some easy example to understand)
  2. Is it a good design?

I read through this tek-tips forum, but it didn't help me well.

original source: Kimball's article

edit:

Along with above link, Kimball's aggregated facts also enabled me to ask scenario where aggregated facts is used as dimension.

Upvotes: 3

Views: 8739

Answers (2)

jmng
jmng

Reputation: 2568

Yes, it can, and sometimes it should, depending on what you are trying to analyze, and how.

An immediate example often happens when you are analyzing sales opportunities. A sales opportunity is a direct candidate for a dimension, but if you look at it from a "analyze the sales pipeline" perspective, and especially, if you are breaking down opportunities into "opportunity stages", the SalesOpportunity will be used as a dimension for the "Sales Opportunity Stage" (the goal here is to analyze how specific sales opportunities evolve through stages) and also as a Fact (the business question in this scenario is "how many opportunities").

This article sheds some light over it: https://www.kimballgroup.com/2011/11/design-tip-140-is-it-a-dimension-a-fact-or-both/

Interestingly, Microsoft Analysis Services enables you to model tables as a dimension, fact and "both".

Upvotes: 2

RADO
RADO

Reputation: 8148

Kimball is not saying that "fact table is acting as dimensions". He is saying that numeric values can sometimes be modeled either as a dimentional attribute (a field in a dimension table), or a fact (a field in a fact table), or both.

A classic example of that is product list price, such as MSRP (manufacturer suggested retail price). Some designers will model it as fact (because it's a number - i.e., in fact table "Sales"). Others will model it as a dimesional attribite (i.e, in dimension table "Product"). What Kimball is saying is that you can have both in the same model. MSRP as a dimensional attribute can be used for filtering, while MSRP as a fact can be used to do calculations (i.e, MSRP - Sale Price = Sale Discount).

Sublte points are these:

  • If you model product price as an atribute, it will make your dimension a "slowly changing dimension", because each time product list price changes, you will need to change the attribute - which adds burden to your ETL process.
  • If you model product price as a fact, you will only have it in star schema if there are records in the fact table. If, for example, some products have no sales in a particular period, than you won't have their list price in the system in case you need it.

Having it in both places gives you ultimate flexibility and simplifies use of the model, but increases ETL work. So, if you need such flexibility, it's a good design. If you only plan to use the value in one specific way, you can save yourself some data warehouse work.

Upvotes: 8

Related Questions