Data warehouse FactTable

Question

I am trying to use this to learn how about data warehousing and having trouble understanding the concept of the fact table.

http://www.codeproject.com/Articles/652108/Create-First-Data-WareHouse

What would be some queries that I could run to find information from the faceable, and what questions do they answer.

score 1 · Answer 1 · answered May 17 '20 at 15:01

A fact table is used in the dimensional model in data warehouse design. A fact table is found at the center of a star schema or snowflake schema surrounded by dimension tables.

A fact table consists of facts of a particular business process e.g., sales revenue by month by product. Facts are also known as measurements or metrics. A fact table record captures a measurement or a metric.

Example of fact table - In the schema below, we have a fact table FACT_SALES that has a grain which gives us a number of units sold by date, by store and by product.

All other tables such as DIM_DATE, DIM_STORE and DIM_PRODUCT are dimensions tables. This schema is known as the star schema.

score 0 · Answer 2 · answered Nov 02 '16 at 07:52

Let's translate this a bit.

Firstly, in a fact table we usually enter numeric values ( rarely Strings , char's ,or other data types).

The purpose of a fact table is to connect with the KEYS of dimensional tables ,other fact tables (fact tables more rarely, and also not a good practice) AND measurements ( and by measurements I mean numbers that change frequently like Prices , Quantities , etc.).

Let's take an example:

Think about a row from a fact table as an product from a supermarket when you pass it by the check out and it get's scanned. What will be displayed in the check out row in your database fact table? Possibly:

So all those Keys and measurements are bringed together in one fact table, having some big performance and understandability advantage.

score 0 · Answer 3 · answered May 03 '17 at 06:12

0

Fact Table Contain all Primary key of Dimension table and Measure like "Sales Amount"

answered May 03 '17 at 06:12

Shahidul Islam Molla

541
6
6

score 0 · Answer 4 · answered May 03 '17 at 12:58

A Fact table is a table that stores your measurements of a business process. Here you would record numeric values that apply to an event like a sale in a store. It is surrounded by dimension tables which give the measurement context (which store? which product? which date?). Using the dimensions you can ask lots of questions of your facts, like how many of a particular product have been sold each month in a region.

score 0 · Answer 5 · answered Oct 11 '17 at 13:09

Some further info

All dimension keys in a fact should be a FK to the dimension if a key is unknown it should point to a zero key in the dimension detaialing this.

All joins from a fact to a dim are 1 to 1. bridge tables are is a technique to cater for many to many's, but this is more advanced

All measurements in a fact are numeric, but can contain NULLS if unknown (never put 0 to represent unknown)

When joining facts to dimensions there is no need to do an outer join, due to FKS applied above.

if you have 999 rows in a fact, no matter what dimensions you join to, you should always have 999 rows returned.

Data warehouse FactTable

5 Answers5