QlikView Star Schema – Viewing Table in Star Schema Model

In this QlikView tutorial, we are going to study a component of QlikView database management system called Qlikview Star Schema. It is a way of representing and managing complex data sets comprising of more than one data files or tables. In this QlikView Start Schema tutorial, we will discuss how to view Star Schema.
So, let us explore QlikView Star Schema.

What is QlikView Star Schema?

QlikView star schema is a way of representing a data model which has a central fact table to which individual dimension tables are linked. It is a relational database model which represents a multi-dimensional database. It is called a star schema because the entity relationship diagram of the fact table and dimension table looks like a star.

The QlikView star schema can range from being a simple five table star structure or can get as complex as having multiple fact tables attached to multiple dimension table making it a complex network of tables.
Do you know about QlikView Database Connection & Inline data

The fact table usually contains measure fields and values, which are numeric values on which calculations can perform. While on the other hand, the dimension table as the name itself suggests contains descriptive text values known as dimensions. Each dimension in the fact table is linked to the respective dimension table with a unique key. Collecting all the keys of individual dimension tables, the fact table makes a primary key.

QlikView star schema model is very significant as it helps in fast and efficient data processing and generating queries. It also makes comprehending complex data very easy for users as they get to visualize data in a well-structured manner. QlikView Star schema model is highly flexible and does not carry the limitations of a traditional OLTP system which slows down data processing. Professional developers also suggest structuring data in the star schema model for speed and efficiency.

Viewing Star Schema in QlikView

Let us see how to view your tables in a star schema model. We have loaded sample data of sales records (CSV file) of a store and have created star schema for the same. We will guide you in doing it step by step.

a. Loading Data

In the first step, we will load our dimension tables and fact tables into Qlikview memory.

b. Order Dimension

This table has details of orders placed in the store. It contains descriptive field values like order ID and order date. Here, the unique key connecting this dimension table to the main fact table is Order ID.
Table. 1 QlikView Star Schema – Order Dimension

Order Dimension

Learn about QVD Files and How to Create them?

c. Customer Dimension

This table has customer details like customer ID and customer name (name of the customer who buy the respective products). Customer ID is the unique key.
Table. 2 QlikView Star Schema – Customer Dimension

Customer Dimension

d. Place Dimension

This dimension table contains details about the city and state of United States where the orders were delivered. Place ID is the unique key.
Table. 3 QlikView Star Schema – Place Dimension

Place Dimension

Let’s discuss the Major Properties of Documents in QlikView

e. Product Dimension

Product Dimension

In this table there are details of the product that has been bought. Here, the unique key is Product ID.
Table. 4 QlikView Star Schema – Product Dimension

Product Dimension

f. Sales Fact Table

This table is a fact table as it contains all the unique key fields . It also contains all Measure fields like sales, quantity, discount, profit which have all the numerical values.
Table. 5 QlikView Star Schema – Sales Fact Table

Sales Fact Table

g. Script Loading

All these tables then loaded into QlikView script by INSERT option, then LOAD STATEMENT and then choosing the data file LOAD FROM FILE. Save the script. Reload it with CTRL+R.

LOAD [Order ID],
     [Order Date]
FROM
[C:\Users\HP\Desktop\Dataflair\order details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
LOAD [Customer ID],
     [Customer Name]
FROM
[C:\Users\HP\Desktop\Dataflair\customer details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
LOAD [Place ID],
      City,
      State
FROM
[C:\Users\HP\Desktop\Dataflair\Place details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
LOAD [Product ID],
     Category,
     [Sub-Category],
     [Product Name]
FROM
[C:\Users\HP\Desktop\Dataflair\Product details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
LOAD [Order ID],
     [Customer ID],
     [Place ID],
     [Product ID],
     [Sales ID],
     Sales,
     Quantity,
     Discount,
     Profit
FROM
[C:\Users\HP\Desktop\Dataflair\Sales details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Let’s explore What is QlikView Scripting & Features of Script Editor

h. Viewing Star Schema

In order to view the table structure in star schema press CTRL+T, once you have reloaded the new script. You will see your dimension and fact table arranged in a star-like structure with the dimension table connected with fact table with lines.

Viewing Tables in QlikView Star Schema

So, this was all about QlikView Star Schema Tutorial. Hope you like our explanation.

Conclusion

Hence, we studied QlikView Star Schema and how to view table in Star Schema. In addition, we discussed the star schema in QlikView proves to a very useful method of visualizing and representing the data in a well-structured manner.
Related Topic – QlikView Careers Opportunities
For reference