Creating Facts - DBA 3: Creating a Data Warehouse

No w that o ur dimensio ns have been created, we can implement o ur f act s. Fact tables are fairly straightfo rward; they co ntain fo reign keys to all dimensio n tables, and a single co lumn fo r the fact value.

Let's get started!

Sales

Our sales data will co me fro m the payment table in the sakila database. Let's take a look. Switch back to the sakila database and run this co mmand:

CODE TO TYPE: describe payment;

Execute the line to see the structure o f payment: OBSERVE:

mysql> describe payment;

+---+---+---+---+---+--- ---+

+---+---+---+---+---+--- ---+

| rental_id | int(11) | YES | MUL | NULL | |

+---+---+---+---+---+--- ---+

7 rows in set (0.00 sec)

We'll pay particular attentio n to the am o unt co lumn. It will be the basis fo r o ur factSales table.

Note

Make sure to review the so urces o f yo ur facts, so yo u do n't implement the wro ng data type. Switch back to yo ur perso nal database. Let's create o ur fact. Run the co mmand belo w against yo ur perso nal database:

CODE TO TYPE:

CREATE TABLE factSales (

sales_key INT NOT NULL AUTO_INCREMENT,

date_key INT NOT NULL, customer_key INT NOT NULL, movie_key INT NOT NULL, store_key INT NOT NULL,

sales_amount decimal(5,2) NOT NULL,

FOREIGN KEY fk_date (date_key) REFERENCES dimDate(date_key),

FOREIGN KEY fk_customer (customer_key) REFERENCES dimCustomer(customer_key), FOREIGN KEY fk_movie (movie_key) REFERENCES dimMovie(movie_key),

FOREIGN KEY fk_store (store_key) REFERENCES dimStore(store_key), PRIMARY KEY (sales_key)

);

Once again, if everything went acco rding to plan, yo u'll see Query OK, 0 rows affected.

A single ro w in factSales will represent the amount of sales for a specific date, for a specific customer, for a specific mo vie, at a specific sto re.

Yo u might think that the primary key sho uld be a co mpo site key acro ss all fo reign keys to the dimensio ns. After all, these co lumns sho uld uniquely identify a fact ro w, right? But the pro blem with that type o f primary key is that it tends to be very wide. To start, create a primary key o n the surro gate key alo ne - sale s_ke y. This will give yo u o ptimum flexibility when evaluating future indexing strategies.

CustomerCount

No w we'll implement o ur factCustomerCount. The factCustomerCount is a tally of the number of

custo mers who created acco unts with o ur sto re. This table do es no t have a fo reign key to dimMovie because the number o f custo mers isn't relative to any particular mo vie.

We'll examine the so urce fo r this data in a future lesso n. Fo r no w, let's create the fact. Make sure yo u are using yo ur perso nal database. Review the fo llo wing CREATE TABLE statement:

OBSERVE:

CREATE TABLE factCustomerCount (

customerCount_key INT NOT NULL AUTO_INCREMENT,

date_key INT NOT NULL, customer_key INT NOT NULL,

store_key INT NOT NULL, customer_count INT NOT NULL,,

FOREIGN KEY fk_date (date_key) REFERENCES dimDate(date_key),

FOREIGN KEY fk_customer (customer_key) REFERENCES dimCustomer(customer_key), FOREIGN KEY fk_store (store_key) REFERENCES dimStore(store_key),

PRIMARY KEY (customerCount_key) );

A single ro w in this table represents a specific custo mer who created an acco unt o n a specific day, at a specific sto re.

Befo re yo u execute the co mmand, take a clo ser lo o k at the cust o m e r_co unt measure. What values might it have?

Since cust o m e r_ke y po ints to exactly o ne custo mer, cust o m e r_co unt will always have the value o f 1.

Since cust o m e r_co unt will always be 1, we co uld o mit the co lumn fro m the table. Ho wever we will leave it in o ur table since it will make it easier fo r business users to query the table.

Since factCustomerCount doesn't have any "real" facts, it is known as a f act less f act . There will be no measure co lumns in this table, o nly fo reign keys to dimensio ns. Factless facts are go o d at sto ring events.

Let's create the table. This time we'll specify a de f ault value o f 1 o n cust o m e r_co unt. Run this co mmand against yo ur perso nal database:

CODE TO TYPE:

CREATE TABLE factCustomerCount (

customerCount_key INT NOT NULL AUTO_INCREMENT, date_key INT NOT NULL,

customer_key INT NOT NULL, store_key INT NOT NULL,

customer_count INT NOT NULL DEFAULT 1,

FOREIGN KEY fk_date (date_key) REFERENCES dimDate(date_key),

FOREIGN KEY fk_customer (customer_key) REFERENCES dimCustomer(customer_key), FOREIGN KEY fk_store (store_key) REFERENCES dimStore(store_key),

PRIMARY KEY (customerCount_key) );

Again, as lo ng as yo u see Query OK, 0 rows affected, you're all set!

RentalCount

Our final fact is factRentalCount. It's similar to factCustomerCount in that it is also a f act less f act . As such, we'll also specify a default value fo r the re nt al_co unt co lumn. (We'll po pulate this table in a future lesso n.) Run this co mmand against yo ur perso nal database:

CODE TO TYPE:

CREATE TABLE factRentalCount (

rentalCount_key INT NOT NULL AUTO_INCREMENT,

date_key INT NOT NULL, customer_key INT NOT NULL, movie_key INT NOT NULL, store_key INT NOT NULL,

rental_count INT NOT NULL DEFAULT 1,

FOREIGN KEY fk_date (date_key) REFERENCES dimDate(date_key),

FOREIGN KEY fk_customer (customer_key) REFERENCES dimCustomer(customer_key), FOREIGN KEY fk_movie (movie_key) REFERENCES dimMovie(movie_key),

FOREIGN KEY fk_store (store_key) REFERENCES dimStore(store_key), PRIMARY KEY (rentalCount_key)

);

Yo u sho uld see: Query OK, 0 rows affected.

A single ro w in this table represents a specific custo mer who rented an specific mo vie, o n a specific day, at a specific sto re.

We co vered a lo t o f material in this lesso n. No w that o ur dimensio ns and facts are implemented, we'll develo p a strategy fo r transferring data fro m o ur OLTP database to o ur OLAP database. See yo u in the next lesso n!

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. See http://creativecommons.org/licenses/by-sa/3.0/legalcode for more information.

In document DBA 3: Creating a Data Warehouse (Page 38-42)