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.

Note:

All timestamps are based on the UTC time zone. If an accumulative build is defined by a timestamp column that contains time zone information, this accumulation will still be calculated based on the UTC timezone. However, if you are using the Salesforce connector, you can change the time zone to match your Salesforce database. For more information, see Connecting to Salesforce.

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 is 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. 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:

  • New tables that were added
  • Tables where columns were added or dropped
  • Custom tables where the expression has changed
  • Tables with custom columns where the expression has changed
  • Custom tables that have SQL expressions which reference other tables that have changed

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

If there are no new tables or changed tables since the last build, Sisense does not rebuild the ElastiCube . Rather, it retains the existing one.

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:

  • New tables that were added
  • Tables where columns were added or dropped
  • Custom tables where the expression has changed
  • Tables with custom columns where the expression has changed
  • Custom tables that have SQL expressions which reference other tables that have changed

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 lists the data providers that do not support accumulative builds and appending data to tables.

Note:

Data providers that are not listed in this table support accumulate builds both by appending to table and accumulating by column.

Sisense Desktop ElastiCube Manager
Data Source Provider Append to Table Accumulate by Column Append to Table Accumulate by Column
CSV file Yes No Yes No
Google AdWords Yes No Yes No
Google Analytics Yes Yes No No
Google Spreadsheets Yes No Yes No
MS Excel Yes No Yes No
OLEDB Not supported Yes No
Salesforce Yes Yes Yes No

.r.