QpkView - Star Schema

A start schema model is a type of data model in which multiple dimensions are pnked to a single fact table. Of course, in bigger models there can be multiple facts tables pnked to multiple dimensions and other fact tables. The usefulness of this model pes in performing fast queries with minimal joins among various tables. The fact table contains data, which are measures and have numeric values. Calculations are appped on the fields in the fact table. The unique keys of the dimension tables are used in pnking it to the fat table, which also has a key usually with the same field name. Therefore, the Fact table contains the keys from the entire dimension table and forms a concatenated primary key used in various queries.

Input Data

Given below is a pst of tables, which contain the data for different products from various supppers and regions. Also the supply happens at different time intervals, which are captured in the Time dimension table.

Product Dimension

It contains the Product Category and Product Names. The Product ID field is the unique Key.

ProductID,ProductCategory,ProductName
1,Outdoor Recreation,Winter Sports & Activities
2,Clothing,Uniforms
3,Lawn & Garden	Power, Equipment
4,Athletics,Rugby
5,Personal Care,Shaver
6,Arts & Entertainment,Crafting Materials
7,Hardware,Power Tool Batteries

Region Dimension

It contains the Region Names where the supppers are based. The RegionID field is the unique Key.

RegionID,Continent,Country
3,North America, USA
7,South America, Brazil
12,Asia,China
2,Asia,Japan
5,Europe,Belgium

Suppper Dimension

It contains the Suppper Names, which supply the above products. The SuppperID field is the unique Key.

SuppperID,SuppperName
3S12,Supre Supppers
4A15,ABC Supppers
4S66,Max Sports
5F244,Nice Foods
8A45,Artistic angle

Time Dimension

It contains the Time periods when the supply of the above products occur. The TimeID field is the unique Key.

TimeID,Year,Month
1,2012,Feb
2,2012,May
3,2012,Sep
4,2013,Aug
5,2014,Jan
6,2014,Nov

Suppper Quantity Fact

It contains the values for the quantities suppped and percentage of defects in them. It joins to each of the above dimensions through keys with same name.

ProductID,RegionID,TimeID,SuppperID,Quantity, DefectPercentage
1,3,3,5F244,8452,12
2,3,1,4S66,5124,8.25
3,7,1,8A45,5841,7.66
4,12,2,4A15,5123,1.25
5,5,3,4S66,7452,8.11
6,2,5,4A15,5142,3.66
7,2,1,4S66,452,2.06

Load Script

The above data is loaded to QpkView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. Cpck OK and press Control+R to load the data into QpkView s memory. Below is the script which appears after each of the above file is read.

LOAD ProductID, 
     ProductCategory, 
     ProductName
FROM
[C:QpkviewimagesStarSchemaProduct_dimension.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq);

LOAD TimeID, 
     Year, 
     Month
FROM
[C:QpkviewimagesStarSchemaTime.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq);

LOAD SuppperID, 
     SuppperName
FROM
[C:QpkviewimagesStarSchemaSupppers.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq);

LOAD RegionID, 
     Continent, 
     Country
FROM
[C:QpkviewimagesStarSchemaRegions.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq);

LOAD ProductID, 
     RegionID, 
     TimeID, 
     SuppperID, 
     Quantity, 
     DefectPercentage
FROM
[C:QpkviewimagesStarSchemaSuppper_quantity.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq);

Star Schema Data Model

After reading the above data into QpkView memory, we can look at the data model, which shows all the tables, fields, and relationship in form of a star schema.