Load Data in Cube Designer

You can use cube designer to load either aggregate storage or block storage data into Essbase.

At times, you may need to clear and reload data during cube development. The data and rules files used in the data load process must be stored in Essbase. If a data worksheet is included in the application workbook, then the data files and rule files are automatically generated during the cube build process. You can also upload individual data and rule files to the cube.

Each data file you select to load starts a separate data load job. By default, the number of jobs that can be run at one time is ten, but you can increase that number. To determine the appropriate limit, consider your calculations, application, restructures, and batch windows, and compare the timing of administrative and user activities so as not to overwhelm your shape size.

To increase the jobs limit, set the essbase.jobs.maxCount Provider Services configuration property to the value that you want. See Set Provider Services Configuration Properties.

It is important to understand the differences between aggregate storage and block storage data loads.

Load Block Storage Data in Cube Designer

To load block storage data in cube designer:

1. In Excel, on the Cube Designer ribbon, select Load Data .
2. In the Load Data dialog box, select the application and cube in which you want to load data.
3. Under Select a Job Type, select an option:
   • Load Data: to load data to the cube.
   • Clear all Data: to clear all data from the cube.
4. Click Select Data.
5. In the Select Data dialog box, click Add.
6. In the Essbase files dialog box, browse to the data files you want to add and select them. These files can be in the cube directory or another directory that you choose. You can add multiple files at once, or you can add them one at a time.
7. Under each data file that you selected in step 6, click the Select a load rule file icon to select the matching rule file.
8. In the Essbase files dialog box, browse to the rule file for that data file and select it.

   Note:

   In the case of non-SQL data loads, you must always select a data file. If you select only a rule file (one that is designed for non-SQL loads), and no data file, an error is returned stating that a connection could not be established with the SQL database. The solution is to select the appropriate data file.

9. Click OK.
10. Select whether to Abort on Error.

    If you select Abort on Error, the data load is stopped when an error is encountered.

11. Click Run to start the data load.

    One job is created for each data file. The jobs are run in parallel, so they finish more quickly than if they were run separately.

12. Click Yes to launch the Job Viewer and view the status of each job, or click No if you do not want to start the Job Viewer.

See Understanding Data Loading and Dimension Building.

Load Aggregate Storage Data in Cube Designer

To load aggregate storage data in cube designer:

1. In Excel, on the Cube Designer ribbon, select Load Data .
2. In the Load Data dialog box, select the application and cube in which you want to load data.
3. Click Select data.
4. In the Select Data Files dialog box, select the settings for your data load.

   Property or Field	Values
   Duplicate Aggregation Method	Specify how to handle cases where multiple values for the same cell are being loaded from the data stream in the load buffer. Add – Add values when the buffer contains multiple values for the same cell. Verify that multiple values for the same cells are identical; if they are, ignore the duplicate values. If the values for the same cell differ, stop the data load with an error message. Use last – Combine duplicate cells by using the value of the cell that was loaded last into the load buffer. This option is intended for relatively small data loads of up to 10,000s of cells.
   Load buffer options	Specify how to handle missing and zero values in the data stream from the load buffer. Ignore none – Ignore no values in the incoming data stream. Ignore missing values – Ignore #Missing values in the incoming data stream. Ignore zero values – Ignore zeroes in the incoming data stream. Ignore missing and zero values - Ignore both #Missing and zero values in the incoming data stream.
   Commit option	Specify load buffer commit options to use when committing the contents of the data load buffer to the cube. Store data – Store values in the load buffer. Add data – Add the values in the load buffer to the existing stored data values. Subtract data – Subtract the values in the load buffer from the existing stored data values. Override all data – Store incoming data instead of the existing stored data values. Override incremental data – Reset incremental data (stored in incremental slices). In other words, remove the current contents of all incremental data slices in the cube and create a new data slice with the contents of the specified data load buffer. The new data is created with the data load property add values (aggregate_sum). If there are duplicate cells between the new data and the primary slice, their values are added together when you query for them.
   Term option	Specify final options for committing data slices to the cube from the data load buffer. Incr. to main slice – Store data into the main slice, and don’t create an incremental slice. Incr. to new slice – Write the data currently stored in the buffer to a new slice. This operation speeds up the data load. Incr. to new slice lightweight - Write the data currently stored in the buffer to a new slice in the cube, as a lightweight operation. This option is intended only for very small data loads of up to 1,000s of cells that occur concurrently (for example, grid client data-update operations).
   Concurrency	Sequential – Load data sequentially instead of in parallel. Parallel – Loads data in parallel. Multiple data load buffers can exist on an aggregate storage cube. To save time, you can load data into multiple data load buffers simultaneously. Although only one data load commit operation on a cube can be active at any time, you can commit multiple data load buffers in the same commit operation, which is faster than committing buffers individually.

5. Click Add to select data and rule files.
6. In the Select Data Files dialog box, browse to the data files you want to add and select them. These files can be in the cube directory or another directory that you choose. You can add multiple files at once, or you can add them one at a time.
7. Under each data file that you selected in step 6, click the Select a load rule file icon to select the matching rule file.
8. In the Essbase files dialog box, browse to the rule file for that data file and select it.
9. Select whether to Abort on Error.

   If you select Abort on Error, the data load is stopped when an error is encountered.

10. Click Run to start the data load.

    One job is created for each data file. The jobs are run in parallel, so they finish more quickly than if they were run separately.

11. Click Yes to launch the Job Viewer and view the status of each job, or click No if you do not want to start the Job Viewer.

See Loading Data into Aggregate Storage Databases.