What is fact table?

1 Answer

In context of Data Warehouse -
A fact table is the central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often denormalized.

This is what Wiki is saying
In data warehousing, a Fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables. Where multiple fact tables are used, these are arranged as a fact constellation schema. A fact table typically has two types of columns: those that contain facts and those that are a foreign key to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables contain the content of the data warehouse and store different types of measures like additive, non additive, and semi additive measures.

Facttable can often confused with Dimensions Table please go through this
http://databases.about.com/od/datamining/a/Facts-Vs-Dimensions.htm

answer Jan 28, 2015 by Salil Agrawal

Similar Questions

0 votes

Populating fact table with key's from other tables

I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).

My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;

ContractTable:

+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01        | 1       |
+------------+---------+
| C02        | 2       |
+------------+---------+
| C03        | 3       |
+------------+---------+
| C04        | 4       |
+------------+---------+
| C05        | NULL    |
+------------+---------+

PartyTable:

+---------+
| PartyId |
+---------+
| P1      |
+---------+
| P2      |
+---------+
| P3      |
+---------+
| P4      |
+---------+
| P5      |
+---------+
| P6      |
+---------+
| P7      |
+---------+
| P8      |
+---------+

RentalObjectTable:

+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1             | C5         | O1       |
+----------------+------------+----------+
| R2             | NULL       | O2       |
+----------------+------------+----------+
| R3             | C4         | O3       |
+----------------+------------+----------+
| R4             | C1         | O4       |
+----------------+------------+----------+

ObjectTable:

+----------+
| ObjectId |
+----------+
| O1       |
+----------+
| O2       |
+----------+
| O3       |
+----------+
| O4       |
+----------+
| O5       |
+----------+
| O6       |
+----------+

Together they should form the Key/FactTable

+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1      | C01        | R4             | O4       |
+---------+------------+----------------+----------+
| P2      | C02        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P3      | C03        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P4      | C04        | R3             | O3       |
+---------+------------+----------------+----------+
| P5      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P6      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P7      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P8      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| NULL    | C5         | R1             | O1       |
+---------+------------+----------------+----------+
| NULL    | NULL       | R2             | O2       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O5       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O6       |
+---------+------------+----------------+----------+

Uptill now i tried the following:

CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);

INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable

UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;

Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.