Dimension Table Usage when we have a loaded fact table

Question

i am new to data warehouse and i want to ask that on copying all the foreign key data and to the fact table then why we still use dimension as all the data is present in Fact table , can some please guide me.

Answer 1

Short answer: a typical dimension has additional attributes than only a key. Your fact table has a foreign key to a dimension where additional info is available and even grouping is possible.

Recommended reading: "The Data Warehouse Toolkit" by Ralph Kimball

Answer 2

A fact table should only store 1) the business metric that it models (e.g. a sales order/transaction, or some other business transaction that you are measuring); 2) foreign keys to the related dimensions.

A dimension table should only store the context/qualitative data that is necessary to understand your business transactions (your facts).

Let's say, for example, that you are modelling sales on retail stores; a very simplified dimensional model for this would be something like:

• Store Dimension: name, street address, city, county, etc
• Product Dimension: name, brand, description, sku, etc
• Date Dimension: year, month, day, etc
• Sales Fact table: fkStore, fkProduct, fkDate, unitsSold, salesAmmount

So, the fact table only holds the metrics/measures and foreign keys, but business users need to use the information stored in dimensions to be able to explore the facts. That's how you enable them to explore unitsSold or salesAmmount according to a specific product, or on a specific store/location, or on a specific date.

The fact table by itself only provides quantitative data ("ammount sold") while the dimensions provide the context that a business user needs to interpret that metric ("ammount of product X sold in store Y in 2017").

Answer 3

The decision on what falls into dimension or fact data is not clear cut in many cases. Typically data that is re-usable ( is meaningful in relation to other fact data) can be considered dimension data. A lot of times fact data is the most changeable over time. Fact tables contain the history of these records changing over time Like daily Sales numbers, nightly EndOfDay results, etc. These are often of numeric type i.e. quantitative measures. Datawarehouse analysis then consists of bucketing ( summing / Grouping ) these numerics so they carry the narrative of a trend over time at varying levels of granularity Where dimension data is of more of 'static' nature like Trade , Customer , Product details. I recommend reading: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/