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.
2. 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.
3. 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
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
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
e. 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
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
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);
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.
So, this was all about QlikView Star Schema Tutorial. Hope you like our explanation.
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