How To Create Fact And Dimension Tables In Mysql

Mysql is a popular relational database management system. Fact and dimension tables are essential for creating a data warehouse or a business intelligence system. In this tutorial, we will learn how to create Fact and Dimension Tables in Mysql.

Step 1: Create a Database and Tables

First, we need to create a database and tables. We can use any tool or Mysql shell to execute these commands. In this tutorial, we will use the Mysql shell to create tables.

Creating Database

To create a database, use the following command:

1
CREATE DATABASE tutorial_db;

Creating Fact Table

To create a Fact Table, use the following command:

1
2
3
4
5
6
7
8
CREATE TABLE fact_table (
    fact_id INT NOT NULL AUTO_INCREMENT,
    date DATE,
    product_id INT,
    quantity INT,
    price INT,
    PRIMARY KEY (fact_id)
);

Creating Dimension Table

To create a Dimension Table, use the following command:

1
2
3
4
5
6
7
CREATE TABLE dimension_table (
    dimension_id INT NOT NULL AUTO_INCREMENT,
    product_id INT,
    product_name VARCHAR(255),
    category VARCHAR(255),
    PRIMARY KEY (dimension_id)
);

Step 2: Insert Data

After creating tables, we need to insert data into these tables. We can use the following commands to insert data into Fact and Dimension tables:

Inserting Data into Fact Table

1
2
3
4
5
INSERT INTO fact_table (date, product_id, quantity, price)
VALUES ('2021-01-01', 1, 10, 100),
       ('2021-01-01', 2, 20, 200),
       ('2021-01-02', 1, 5, 100),
       ('2021-01-02', 3, 15, 300);

Inserting Data into Dimension Table

1
2
3
4
INSERT INTO dimension_table (product_id, product_name, category)
VALUES (1, 'Product A', 'Category 1'),
       (2, 'Product B', 'Category 1'),
       (3, 'Product C', 'Category 2');

Step 3: Query Data

After inserting data, we can query data from these tables. We can use the following command to join Fact and Dimension tables:

1
2
3
SELECT f.date, d.product_name, d.category, f.quantity, f.price
FROM fact_table f
JOIN dimension_table d ON f.product_id = d.product_id;

This will give us the following output:

+------------+-------------+-----------+----------+-------+
| date       | product_name | category  | quantity | price |
+------------+-------------+-----------+----------+-------+
| 2021-01-01 | Product A   | Category 1 | 10       | 100   |
| 2021-01-01 | Product B   | Category 1 | 20       | 200   |
| 2021-01-02 | Product A   | Category 1 | 5        | 100   |
| 2021-01-02 | Product C   | Category 2 | 15       | 300   |
+------------+-------------+-----------+----------+-------+

Now, we have successfully created Fact and Dimension Tables in Mysql.

At the end of the post, the full code is given below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE DATABASE tutorial_db;
 
CREATE TABLE fact_table (
    fact_id INT NOT NULL AUTO_INCREMENT,
    date DATE,
    product_id INT,
    quantity INT,
    price INT,
    PRIMARY KEY (fact_id)
);
 
CREATE TABLE dimension_table (
    dimension_id INT NOT NULL AUTO_INCREMENT,
    product_id INT,
    product_name VARCHAR(255),
    category VARCHAR(255),
    PRIMARY KEY (dimension_id)
);
 
INSERT INTO fact_table (date, product_id, quantity, price)
VALUES ('2021-01-01', 1, 10, 100),
       ('2021-01-01', 2, 20, 200),
       ('2021-01-02', 1, 5, 100),
       ('2021-01-02', 3, 15, 300);
 
INSERT INTO dimension_table (product_id, product_name, category)
VALUES (1, 'Product A', 'Category 1'),
       (2, 'Product B', 'Category 1'),
       (3, 'Product C', 'Category 2');
 
SELECT f.date, d.product_name, d.category, f.quantity, f.price
FROM fact_table f
JOIN dimension_table d ON f.product_id = d.product_id;