Drags to Build Time

[shanah]

I am looking for ways to cut some of the drag on my build times.  Which connection is more tasking on a build? Looking up a value to make a custom column or connecting tables to access the attributes needed?

[HamzaJ]

Hey Shanah,

I struggled with this aswell. What helped me out was downloading the EC-build log and parse it into columns. Then you can see per (custom) table or field the time it took to process. It gives an indication on where things could be improved. In some cases it could be a custom column while in other cases it could be a connection

[Liran_Elnekave]

Hi Shanah,

Below you will find the steps and recommended actions to take to improve Elastic Cube build performance: 

Optimize ElastiCube Performance 

To optimize Elasticube performance there are two focus areas:

• Decreasing Build Times
• Optimize Queries

Decreasing Build Time

In order to optimize the build time, reduce:

Many fields, long strings:

• Don't import long string fields if they will not be used in the model
• Always question the need for columns with long strings before adding them to the model (URLs, very long comments)

Many dates:

• Removing time-based data that is not needed will reduce build time (ie. don't import old data if you don't need to)
• Consider the date range in the requirements of the dashboard and data model
• Import a Dates File instead of a Custom table to create a date dimension

Use the source database when possible:

• Create views to replace custom tables and import the View
• Filter out irrelevant data (history, in-active, etc.)
• Customize the query when adding the data to the Elasticube

Optimize custom tables

Avoid Processing Power and Time Expensive Operations:

• Replace UNION with UNION ALL when possible
• Left and Right joins
• Consider lookups
• Filter data within the table

Avoid Redundant Operations:

• Consider sub queries
• Avoid SELECT *
• Order by

Optimizing Queries

In order to optimize the queries in the ElastiCube, do the following:

Consolidate:

• Look up “translation” tables
• Avoid unnecessary joins
• Consolidate Facts

Calculate custom columns:

• In large data sets, it may be significant

Sum and DUPCOUNT are faster than count 

Joins on indexed fields:

• Check for casting in custom tables
• Cast fields in source tables instead of using  casting functions

Join on numeric fields:

• Numeric dates
• Join on Date with no Time component

Surrogate Keys:

• When possible, create in the DB
• Avoid surrogate keys when big data
  (consider numeric using https://sisense.wixanswers.com/en/article/generating-a-numeric-key)

Good luck,

Liran