Data Denormalization for Faster Dashboard Query Response

[intapiuser]

Introduction

This post will explain how to denormalize your ElastiCube schema in order to get faster query response in SiSense Web.

Purpose/Benefit

Denormalization of data is used to reduce the query response time in SiSense Web.  It is most useful for large tables of over 100 million records.  It reduces the time necessary to process the inner joins that are created when querying multiple tables.  Please note that this process will lengthen the build time slightly depending on the number of fields you move.  Furthermore, though it is not the main purpose, administration of the ElastiCube becomes easier due to fewer tables to manage.

What is data denormalization?

The standard definition for schema denormalization is the process of attempting to optimize the read performance of a database by adding redundant data.  In SiSense terms, reducing data connections in the ElastiCube so the dashboard queries use fewer joins. Joins are costly on query time, especially when you have large transactional tables.  Reducing the number of joins that are necessary to bring back data to the dashboard will help maximize query performance. 

What data can be denormalized?

One to Many Relationship 

A one to many relationship is defined as each row in the related to table can be related to many rows in the relating table.  In this case the Products table has a one to many relationship with Orders.  ProductID in the products table is unique.  ProductID in the Orders table is not unique.  For every ProductID in the Product table, there are many matching ProductID’s in the Orders table.

One to One Relationship 

A one to one relationship is defined as each row in one table is linked to one and only one other row in another table.  In this case the Orders table has a one to one relationship with the Ecommerce table.  For every OrderID, there is only one corresponding OrderID in the Ecommerce table.

Steps

Example – One to Many

In this example we will walk through the Products and Orders table denormalization. 

Step 1 

Ensure that both tables are included in the ElastiCube.

Step 2 

Create a custom field in the main table of focus (Orders table in our example)

Step 3

Use a lookup function to bring the qualitative data into the main table of focus.

The lookup function definition returns the first matching value for the specified name from a dataset that contains name/value pairs.

The syntax:

lookup(<Remote Table>,<Remote Result Column>,<Current Match Column>, <Remote Match Column>)

Example of ProductName lookup:

lookup(Products, Products.ProductName, Orders.ProductID, Products.ProductID)

Step 4

Repeat this step for each field necessary.

Step 5

Finally, make the table that contains the qualitative data invisible so to not confuse the user (Products in our example)