'Data warehouse, could a dimention be a fact?
We have a data warehouse that, between other things, has to store the product sold by the user. Everytime a user is added to our system, we have an ETL process that persist the user in .. a DM_CUSTOMER table. Every time a user by a product, we have a row in FT_SALE, with a FK of the DM_CUSTOMER. The problem here is that in our dashboard we have to show many KPI related to the number of the CUSTOMER in our DW; how many new customer with daily base, montly, hourly ... and we have to fetch this data from the DM_CUSTOMER.
So, ONE CUSTOMER is a business metric ... so it could be a FACT but ... it is a DIMENSION for SALE table so ... what's wrong with this? is it correct to perform query on DM Tables? Should I create another FT_CLIENT with a FT of DM_CLIENT ?
NOTE: in out system EVERYTIME a user came into the system, for us is a NEW CUSTOMER, also if that user was already in our system. We do not perform any kind of deduplication.
Solution 1:[1]
If you just want to know information that can be calculated from just the Customer Dim table then there is no point building a fact table. However, if you want to slice-and-dice customer information by other dimensions (e.g. date) then it probably makes sense to construct a fact table for customer analytics.
Facts and dimensions are different object types so the fact that you have both a customer fact table and a customer dimension table is not an issue and is, in fact, a common pattern
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | NickW |