Scribd
Upload
83 views4 pages

SQL Constraints & Fact Tables Guide

The document discusses SQL constraints including primary keys and foreign keys. It then defines different types of facts that can be stored in a fact table such as additive, semi-additive, and non-additive facts. Various types of fact tables are described including fact-less, centipede, conformed, incident, snapshot, and cumulative fact tables. Dimension tables are defined as containing descriptive attributes that are linked to fact tables using primary keys. Finally, star schemas and snowflake schemas are summarized as database structures used in Power BI, with star schemas linking one or more fact tables to dimension tables and snowflake schemas extending dimension tables with additional sub-dimension tables.

Uploaded by

Chaitanya IT Returns
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
83 views4 pages

SQL Constraints & Fact Tables Guide

The document discusses SQL constraints including primary keys and foreign keys. It then defines different types of facts that can be stored in a fact table such as additive, semi-additive, and non-additive facts. Various types of fact tables are described including fact-less, centipede, conformed, incident, snapshot, and cumulative fact tables. Dimension tables are defined as containing descriptive attributes that are linked to fact tables using primary keys. Finally, star schemas and snowflake schemas are summarized as database structures used in Power BI, with star schemas linking one or more fact tables to dimension tables and snowflake schemas extending dimension tables with additional sub-dimension tables.

Uploaded by

Chaitanya IT Returns
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Trainer Name: Jabivulla Vanalli

Email: [email protected]
Mobile: +91 7829533577
YouTube Channel: https://www.youtube.com/channel/UCIUV3lP4RQEOqbY1funMH3w

1. SQL PRIMARY KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a table.


 Primary keys must contain UNIQUE values, and cannot contain NULL values.
 A table can have only ONE primary key; and in the table, this primary key can consist
of single or multiple columns (fields).

2. SQL FOREIGN KEY Constraint

 A FOREIGN KEY is a key used to link two tables together.

 A FOREIGN KEY is a field (or collection of fields) in one table that refers to the
PRIMARY KEY in another table.

 The table containing the foreign key is called the child table, and the table containing
the candidate key is called the referenced or parent table.

Example Explained:
http://www.cems.uwe.ac.uk/~pchatter/resources/html/emp_dept_data+schema.html

3. What is Fact Table? Type of Facts and Fact Table Types?

 A fact table holds the measures, metrics and other quantifiable information.

Types of Facts

There are three types of facts:

1. Additive Facts

Additive facts can be used with any aggregation function like Sum (), Avg() etc.

Example is Quantity, sales amount etc.

2. Semi Additive Facts


Semi-additive facts are those where only a few of aggregation function can be applied.

For example, Consider bank account details. You cannot apply the Sum () on the bank
balance that does not give useful results but min () and max () function may return useful
information.

3. Non-Additive Facts

You cannot use numeric aggregation functions such as Sum(), Avg() etc on Non-additive
facts.

For example of non-additive fact is any kind of ratio or percentage. Non numeric facts can
also be non-additive facts.

Types of Fact Tables

Below are detail types of fact tables:

1. Fact-less Fact Tables

A fact table that does not contain any measure is a fact-less fact table. This table will only
contain keys from different dimension tables. This is often used to resolve a many-to-many
cardinality issue.

For example, a fact table which has only productID and date key is a fact-less fact table.

2. Centipede Fact Table

Centipede fact table is a normalized fact table. Modeller may decide to normalize the fact
instead of snow flaking dimensions tables.

3. Conformed Fact Tables

They are measures re-used across multiple dimension models.

For example, KPI such as profit, revenue etc

4. Incident and Snapshot Facts

A fact table stores some kind of measurements and are captured against a specific time.
Now it might so happen that the business might not able to capture all of its measures
always for every point in time. Then those unavailable measurements can be kept empty
(Null) or can be filled up with the last available measurements. The first case is the example
of incident fact and the second one is the example of snapshot fact.

5. Cumulative Fact

This type of fact table describes what has happened over a period of time. For example, this
fact table may describe the total sales by product by store by day.

4. What is Dimension Table?

 Dimension Table Contains Description of the product or Table.


 It contains PRIMARY KEY relationship with Fact Table.

5. Star Schema in Power BI?

 Star Schema can hold 1 or more Fact Tables and Dimension Tables
 Fact Tables can have Foreign Key relationship with Dimension Tables and
Measurable or Calculated things hold.
 Dimension Tables hold total information about the Table and Primary
Key relationship with Fact Tables.

6. Snowflake Schema in Power BI

 Snowflake Schema contains 1 or more Fact Tables and Dimension Tables


contain at least 1 more Sub-Dimension Table.

Source: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

You might also like