ElastiCube Build Settings

When you build your ElastiCube, data is imported from your data sources into the ElastiCube according to settings you define on the model level and on the table level.

This topic describes how data can be imported on each level and the implications of each of the ElastiCube build settings.

Model Level

On the model level, you have three choices for importing data, Replace All, By Table, and Changes Only.

Replace All

When you replace all your data on the model level, any existing data imported into your ElastiCube is overwritten and any custom tables or columns are recalculated. Sisense imports an exact copy of your data from the source. If the size of your data is large or the connection speed to the source slow, it can take a long time to build your ElastiCube.

The diagram below illustrates what happens before and after a Replace All build:

Note: While building the ElastiCube with schema changes or entire ElastiCube builds, you can continue to run queries. In the event that the build fails, Sisense restores the original version of the ElastiCube and attempts the build again. While Sisense restores the original ElastiCube, some downtime may occur while Sisense copies the ElastiCube to your drive. The downtime is the amount of time it takes to copy the ElastiCube locally. If you require high availability for your data, Sisense provides this through ElastiCube Sets. When Sisense restores an ElastiCube a copy of the original ElastiCube is created. You should verify that your server can store multiple copies ElastiCubes until the restoration process is complete.

By Table

When you build By Table, all your data is overwritten except for tables where you have specifically defined their build behavior in the schema.

This option is only displayed when a table’s build behavior has been defined in the schema. When you have customized a table’s build behavior, an icon is displayed that indicates the table’s build behavior, for example, Changes Only or Append . These icons are also described in the legend in the bottom-right corner of the schema.

In the Build Settings dialog box, you can click the table links to see which tables have been configured or are set to be overwritten.

For more information about table build behavior, see Table Level below.

Changes Only

When you build by Changes Only, you build only new tables or tables that have changed since the last build. This is useful when you frequently need to refresh a large data source. For example, if you have a data source that is updated daily, rather than rebuild the entire ElastiCube daily, you can just import the new data added each day. This option can significantly reduce the amount of time it takes to complete a build.

Tables that have changed since the last build include the following:

If your table meets any of the above criteria, then the table’s data is overwritten and rebuilt from scratch.

Table Level

You can define your build’s behavior per table by selecting the table’s menu > Build Behavior, and selecting the relevant option.

Then, when you define what type of build you want to do, select the By Table option.

You can set the build behavior of a table to one of the following options:

Replace All (Default)

Replaces all data at the time of the build. This is recommended for dimension tables, for example: store attributes, or dimensions, which describe the objects in a fact table.

Append

Adds all the data from the source table and appends it to the existing data in the ElastiCube.

The diagram below illustrates what happens before and after an Append build:

On Day 2, the data taken from the source was appended onto the existing data in Sisense without ignoring any rows of data.

Changes Only

Does not import any data unless changes have occurred in the table. This is recommended with summary/snapshot fact tables and with data marts (smaller subsets of data, tailored for specific needs).

Tables that have changed since the last build include the following:

Accumulate By

Adds additional rows of data incrementally to an existing table according to the data in a specified integer or date column that acts as an index. You can select a specific integer or date column that will be used to determine whether to accumulate data at the time of the build. The column acts as an index, and if the index value is greater in a subsequent build, then data is accumulated for the table. This is recommended for detailed fact tables, for example: store quantitative information for analysis.

Note: Only date and integer columns are supported.

To select the integer or date column within a table as the parameter to accumulate by, click on the column’s menu, and then select Accumulate By.

Accumulation behavior for integers and for dates are as follows:

When you select an integer, only source rows with a value greater than the maximum index value in the ElastiCube table will be inserted. Existing data in the ElastiCube table will not be modified or deleted.

The diagram below illustrates what happens before and after an Accumulative build:

On Day 2, the integer value 3 was set as an index so new rows that are less than the integer value are not added in the next build. In this case, D and E of Column 1 were excluded as 2 and 1 of the new rows are less than the index value 3.

Accumulative Build Support

Some data sources (for example, CSV files) do not support accumulating data on the column level, and in such cases data will be duplicated. The table below describes which data providers support accumulative builds and appending data to tables:

Data Source Provider Append to Table Accumulate by Column
Oracle Yes Yes
MySQL Yes Yes
SQL Server Yes Yes
PostgreSQL Yes Yes
ODBC Yes No
OLEDB Yes No
Hive Yes Yes
MS Excel Yes No
CSV file Yes No
MS Access Yes No
Salesforce Yes No
Google AdWords Yes No
Google Analytics No No
Google Spreadsheets Yes No
Amazon Redshift Yes Yes
Heroku Postgres Yes Yes
DB2 Yes Yes
Teradata Yes Yes
MongoDB (ODBC) Yes Yes