When you build your Elasticube, data is imported and accumulated based on the following settings:

  • Build Options: Defines whether to import all data overriding any previous data, or import only data where a change has been made to the schema since the previous build.
  • Field Build Behavior: Defines a specific integer or date field as an index in the table. The index column will be used to determine whether source records are new, and whether to accumulate data in the table based on a higher index value at the time of the build. New builds will only include values greater than the maximum existing value.
  • Table Build Behavior: Defines the behavior on the table level. You can replace all data at the time of the build, or add only additional/accumulated data.

build-settings-1024x731

Build Options

There are two primary Build Options, Build Schema Changes and Build Entire ElastiCube.

To access Build Options, click Build in the main menu of the ElastiCube Manager. See also Building the ElastiCube.

  • Build Schema Changes: Updates the build only if changes were made to the schema since the previous build, and imports data only if new tables were added. Conditions for updating the build include:
    • New, removed or changes to custom tables
    • New, removed or changes to custom fields
    • New or removed tables
    • New or removed table relationships.
  • Build Entire ElastiCube: Imports data according to the build behavior defined. By default, Sisense replaces and overrides all existing data in the ElastiCube. While building entire ElastiCubes ensures you have an exact copy of the underlying data source, it can sometimes take a long time and be expensive on the database depending on the size of your data source.You can customize your builds to update only the tables and fields where data was accumulated 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 build depending on the size of your ElastiCube.
    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 and pastes the ElastiCube on your drive. The downtime is the amount of time it takes to copy and paste the ElastiCube locally. If you require high availability for your data, Sisense provides this through ElastiCube SetsNote: 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.

For more information on how to customize your builds, see Field Build Behavior and Table Build Behavior below.

Field Build Behavior

You can select a specific integer or date field that will be used to determine whether to accumulate data at the time of the build. The field will act as an index, and if the index value is greater in a subsequent build then data will be accumulated for the table.

To select the integer or date field within a table as the parameter to accumulate by, click on the field name, and then select Is Accumulated by.

Note: The source data must be ordered by the index.

build settings2

Accumulation behavior for integers and for dates are as follows:

Integer

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.

Note: Some data sources (for example, csv files) do not support accumulating data on the field level, and in such cases data will be duplicated. See Accumulative Build Support below.

The following image demonstrates this logic (when supported):

build settings3

After Load 1, the maximum index value is 3. In Load 2, the source index value of 2 is not inserted (since it is less than 3), but the source index value of 4 is inserted (since it is greater than 3).

Date

When accumulating by date, you can select one of the following options:

build settings4

Last stored value: This is identical to the integer index functionality. Only source rows where the index is greater than the ElastiCube table’s maximum date index value will be inserted. ElastiCube data is never removed or modified after it has been built.

Note: Some data sources (for example, CSV files) do not support accumulating data on the field level, and in such cases data will be duplicated. See Accumulative Build Support below.

Last Days: This option specifies the number of days preceding the build date to synchronize.

Note: Syncing “Since Last X days” requires the source data to be ordered by the date.

Synchronization consists of deleting all of the rows in the ElastiCube table where the index is in the specified range, and inserting all source rows where the index value is in the specified range. This option does allow for ElastiCube table data to be deleted or modified. The following example will demonstrate this logic using a three-day range.

build settings5

In Load 1, the Ecube table started empty, so nothing was deleted. Both of the source rows are between 12/3/2014 and 12/5/2014 (the 3 day range), so they are inserted. In Load 2, all Elasticube table records with the date between 12/4/2014 and 12/6/2014 are removed. In the source table, the data for 12/3/2014 and 12/4/2014 have been updated. However, since 12/3/2014 is outside of our 3 day range, the Elasticube data for that date has not changed. Since 12/4/2014 is in the range, the original Elasticube table record for that day was removed and was replaced by the source table record for that day. 12/5/2014 data is new, so it was also inserted into the Elasticube table.

 

Table Build Behavior

A table’s Build Behavior lets you define whether to import and replace data for the entire table or bring in only accumulated data at the time of the ElastiCube build.

To select a table’s Build behavior, click the table’s header and then Additional Preferences.

build settings7

The following options exist:

  • Replace Data (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.
  • Accumulate Data: Adds additional data to existing table data, without making comparisons and without omitting data. This is recommended for detailed fact tables, for example: store quantitative information for analysis.
    Note: If one of the fields in the table is set to accumulate data (see below), then data will be accumulated from the selected integer or date field, and not from the table.
  • Ignore When Unchanged: Does not import any data unless changes have occurred in the table schema. This is recommended with summary/snapshot fact tables and with data marts (smaller subsets of data, tailored for specific needs).

Build Options Working Together with Table and Field Behavior

The following table describes how data will be imported using different build options and table behavior combinations.

Replace DataAccumulate DataIgnore when unchanged
Build Entire ElasticubeReplaces all data at the time of build.Only adds additional data to existing table data. If a field in the table is marked as Accumulate by, then data is accumulated only for the selected field. Note: If enabled, an additional checkbox exists to delete all existing data during the build. Ignores tables that have remained unchanged, unless changes have occurred in the table schema. Note: If enabled, an additional checkbox exists to delete all existing data during the build.
Build Schema ChangesAll data remains unchanged during the build.If a field in the table is marked as Accumulate by, then data is accumulated only for the selected field.Ignores tables that have remained unchanged, unless changes have occurred in the table schema.

Note: Tables with accumulative build settings are highlighted with a dark blue color.

mini colors

Accumulative Build Support

The following list displays different data sources and their support for accumulating data on the table and field levels.

Data Source ProviderAccumulate by TableAccumulate by Field
OracleYesYes
MySQLYesYes
MS SQLYesYes
PostgreSQLYesYes
ODBCYesNo
OLEDBYesNo
HiveYesYes
MS ExcelYesNo
CSV fileYesNo
MS AccessYesNo
SalesforceYesNo
Google AdWordsYesNo
Google AnalyticsNoNo
Google SpreadsheetsYesNo
ZenDeskYesNo
Amazon RedshiftYesYes
Heroku PostgresYesYes
DB2YesYes
TeradataYesYes
MongoDB (ODBC)YesYes