Tips for Loading Data and Building Dimensions

To save time, you can perform incremental dimension builds to Essbase, and you can load data subsets. To prevent overwriting consolidated values, avoid loading data into parent members. To avoid data processing errors, ensure each record in the source has the same number of data value fields.

See these topics for more information to help you load data and build dimensions:

Incremental Dimension Builds

To enable Essbase to read multiple sources of data while building the cube outline, use incremental dimension builds. Incremental dimbuild delays outline restructuring until all sources of data have been processed.

Skip this topic if you are loading data only, or are using a single data source for a dimension build.

By default, each time you make changes to an outline, Essbase considers the type of change, and restructures the cube if needed. Restructuring rebuilds the database, which consumes time and disk space.

Incremental dimension builds, also known as deferred-restructure dimension builds, read multiple data sources for dimension builds and delay restructuring until all data sources have been processed.

If you add a dimension that is virtual (Dynamic Calc or label only), then any data existing in the cube is stored with the first level-0 stored member in the new dimension. There must be at least one stored member in the hierarchy, or an error will be returned during outline verification.

When you incrementally add a dimension to an existing cube using an application workbook, the data is automatically mapped to the new top member. There is not a way to choose a stored member to which to map the existing data. If the new dimension has a top member that is dynamic calc, the data is lost because dynamic members can’t store data.

When using an application workbook to add a new dimension in which you want the top member to be dynamic calc, follow these steps:

Add the new dimension with the top member as stored.
Run a calc script to copy the data from the new top member into another stored member in that dimension.
Change the top member to dynamic calc.

MaxL enables you to include all of the data sources within one import statement. You can control whether outline validation is performed for each file. You must enable outline validation for the last file.

In all cases, the data sources are processed in the order in which they are listed.

Note:

MaxL enables you to enforce or suppress outline verification for each file. To ensure a valid outline, ensure that the last build verifies the outline.

Where to Load Data

Before performing a data load, take steps to prevent overwriting data unintentionally. Avoid loading to parent members in a block storage (BSO) cube. If you must do so, take steps to ensure that Essbase does not consolidate #MISSING values from the children into the parent.

Skip this section if you are building dimensions or working with an aggregate storage (ASO) database.

If you load data into a parent member, when you calculate the database, the consolidation of the children’s data values can overwrite the parent data value. To prevent overwriting:

If possible, do not load data directly into a parent.
If you must load data into a parent member, ensure that Essbase knows not to consolidate #MISSING values from the children of the parent into the parent, by changing the aggregate #MISSING setting.

You can set the aggregate missing behavior using MaxL, an application workbook, the outline or calculation scripts.

To change aggregate missing behavior using the Essbase web interface, change the Aggregate missing values setting in the database customization page. Refer to Set Advanced Cube Properties.
To change aggregate missing behavior using an application workbook, change the Aggregate missing values setting on the Cube.Settings worksheet.
To change aggregate missing behavior in a calculation script, use the SET AGGMISSG calculation command.
To change the aggregate missing behavior using MaxL, use alter database appname.dbname enable|disable aggregate_missing.

The methods mentioned above work only if the child values are empty (#MISSING). If the children have data values, the data values overwrite the data values of the parent. See #MISSING Values.

Note:

You cannot load data into Dynamic Calc or attribute members. For example, if Year is a Dynamic Calc member, you cannot load data into it. Instead, load data into Qtr1, Qtr2, Qtr3, and Qtr4, which are not Dynamic Calc members.

Missing Fields in the Source Data

When performing an Essbase data load, each record in the source data must have the same number of data value fields. If data values are missing, the data load processes incorrectly.

If a dimension field or member field is missing, Essbase uses the value that it used previously for that dimension or member field. Refer to Debug Data Loads and Dimension Builds.

If a rule has extra blank fields, join the empty fields with the field next to them.

In aggregate storage cubes, values can be loaded only to level 0 cells. Specifying #MISSING or #MI as a value in the source removes the associated cell, if it is present in the cube.

For information about data load differences for aggregate storage cubes, refer to:

Load a Subset of Records from the Source

You can load a subset of records from the source data during an Essbase data load or dimension build.

To load a subset of records:

Using a text-editing tool, number the records in the data source.
Set the load rule to ignore the column containing the record number.
Define rejection criteria to reject all records except those that you want to load. For example, reject all records for which the ignored column is fewer than 250 or greater than 500.

You cannot reject more records than the error log can hold. By default, the limit is 1000. You can change the limit by setting the DATAERRORLIMIT configuration.