Work with Cubes in Cube Designer

About Cube Designer

The basic components of Cube Designer are the Cube Designer ribbon and the Designer Panel. See About the Cube Designer Ribbon and About the Designer Panel.

About the Cube Designer Ribbon

Cube Designer helps you to design, create and modify application workbooks to meet their strict layout and syntax requirements. You can also use options on the Cube Designer ribbon to perform a number of cube management tasks, such as loading data, editing formulas and viewing jobs.

Cube Designer Ribbon Options

Connections: Opens the Connections dialog box, in which you choose the Essbase URL.
Catalog: Opens the Essbase Files dialog box, which contains a selection of prebuilt application workbooks, from which you can build sample applications and cubes.
Also, a catalog toolbar is available in this dialog box from which that you can perform many file operations within the catalog, such as upload, download, cut, copy, paste, delete, rename, and create a new folder.
Local: Provides a drop-down menu with options to open or save an application workbook locally, or to export a cube to an application workbook.
Designer Panel: Opens the Designer Panel, a series of panels in which you can design and edit application workbooks.
Calculation Editors cube designer ribbon icon is context aware. When you’ve selected a formula cell on a dimension worksheet, the Member formula editor option is enabled. When you’ve selected a calculation worksheet, the Calculation script editor option is enabled.
Hierarchy Viewer: Opens the Dimension Hierarchy dialog box, in which you can view the hierarchy for the selected dimension worksheet in an application workbook, and perform tasks, such as renaming members and changing storage settings. See Work with Dimension Worksheets in Cube Designer.
Build Cube: Opens the Build Cube dialog box, where you can build a cube from the active application workbook. In this dialog box, cube designer automatically detects existing data and calculation worksheets, and then pre-selects options to load the data and run the worksheets.
Load Data: Opens the Load Data dialog box, which contains options to clear all data and to load data.
Calculate: Opens the Calculate Data dialog box, in which you can select an application, a cube, and a calculation script to execute.
Analyze: Provides a drop-down menu with options to create a Smart View ad hoc grid, or connect application workbook query worksheets (Query.query_name worksheets) to Smart View.
View Jobs: Opens the Job Viewer dialog box, in which you can monitor the status of jobs, such as data loads, calculations, imports, and exports.
Transform Data: Opens the Transform Data dialog box, which lets you build a cube from tabular data.
Options: Provides options to specify the default working folder and to activate the cube designer log.
Admin Tasks: Opens a menu from which you can delete an application, delete a cube, unlock objects, open the expert mode (optimize cube) dialog box, or view the application log.
Server name: Shows the currently defined connection location. When you click Server name and log in (if prompted to do so), the server name and the client and server versions are displayed.

About the Designer Panel

The Designer Panel uses a manual system of reading and writing to the worksheets in an application workbook. The From Sheet button at the bottom of the Designer Panel reads the entire application workbook’s data and populates the panel with the data. The To Sheet button updates the entire application workbook with the data from the Designer Panel. The Reset button clears the data from the Designer Panel.

One common use of panel is to populate it with information from one application workbook using From Sheet, open a new blank workbook, and then use To Sheet to make a clone of the first application workbook.

You can design and edit application workbooks in the Designer Panel. Each of its five tabs correspond to one of the five types of worksheets in an application workbook. See Design and Create Cubes Using Application Workbooks.

To open the panel, click Designer Panel on the Cube Designer ribbon.

If the Smart View panel displays when you click Cube Designer, then click Switch To , and select Cube Designer from the drop down menu.

The Designer Panel contains the following tabs:

Cube: You can design and modify the Essbase.Cube worksheet in an application workbook.

See Work with the Essbase.Cube Worksheet in Cube Designer.
Settings: You can design and modify the Cube.Settings worksheet in an application workbook.

See:
Dimensions: You can design and modify the Dim.dimname worksheets in an application workbook.

See Work with Dimension Worksheets in Cube Designer.
Data: You can design and modify the Data.filename worksheet in an application workbook.

See Work with Data Worksheets in Cube Designer.
Calc: You can design and modify the Calc.scriptname worksheet in an application workbook.

See Work with Calculation Worksheets in Cube Designer.

Manage Files in Cube Designer

Your access to view and work with Cube Designer files depends on your permissions.

In Cube Designer, you access the file folders in the Catalog using the Catalog option in the Cube Designer ribbon.

The Applications folder requires Database Manager role access to view cubes for which you have permission.

The Gallery folder is read-only access for all users.

The Shared folder is read-write access for all users.

The Users folder is read-write access for the logged in user.

According to your permissions, you can create, move, rename and delete custom folders. Similarly, users with access can import, export, copy, move, rename and delete files.

Related topic: Manage Essbase Files and Artifacts

Download Sample Application Workbooks

Using the sample application workbooks provided in the Essbase Files dialog box, you can quickly create sample applications and cubes. The cubes are highly portable, because they are quickly and easily imported and exported.

On the Cube Designer ribbon, click Catalog .
If prompted to connect, enter your user name and password.
On the Essbase Files dialog box, choose the sample application workbook you want to open.

You can then edit the application workbook to fit your requirements in the Designer Panel. See Work with Application Workbooks in Cube Designer.

You can save this modified application workbook to your private inventory. See Build a Private Inventory of Application Workbooks.

You can upload this modified application workbook to either the user or shared catalog locations. If uploaded to the shared catalog location, the application workbook will be available to all users.

Build a Private Inventory of Application Workbooks

Cube Designer allows you to create and store application workbooks on the client computer. This lets you keep a private inventory of completed and in-progress application workbooks.

Using the Local icon menu items on the Cube Designer ribbon, you can manage your private application workbook inventory:

Open an Application Workbook

Open an existing application workbook from your inventory.

On the Cube Designer ribbon, click Local .
Select Open Application Workbook.
Browse to the application workbook and click Open.

Save an Application Workbook

Save a new or updated application workbook to your inventory.

Open the application workbook.
On the Cube Designer ribbon, click Local .
Select Save Application Workbook.
Browse to your inventory location and click Save.

Export to an Application Workbook

Export a cube to an application workbook and add it to your inventory.

On the Cube Designer ribbon, click Local .
Select Export Cube to Application Workbook.
If prompted to log in to Essbase, enter your user name and password.
In the Export Cube dialog box, select the application and cube you want to export, and from the Export Build Method menu, select either the Parent-Child or Generation build method; indicate if you want to export input level data and calculation scripts, and click Run.
To add the application workbook to your private inventory, click Save Application Workbook.

Work with Application Workbooks in Cube Designer

Using Designer Panel, you can modify an application workbook, and then you can use the modified workbook to create an updated cube, reflecting your changes.

Limitations of Application Workbooks

Current limitations for using application workbooks, are listed here.

The following limitations currently exist when working on application workbooks in Excel using the designer panel.

You cannot set up a dimension worksheet using the generation format. Instead, you must import using the parent-child build method.
Multiple dimension sheets for the same dimension are not supported. You are limited to one worksheet per dimension.
Application workbooks do not support aggregate storage cubes.
Changes to the Cube.Settings worksheet cannot be applied incrementally. Instead, you must rebuild the cube to apply those changes.

Work with the Essbase.Cube Worksheet in Cube Designer

Using the Cube tab in the Designer Panel, you can modify the following fields on the Essbase.Cube worksheet:

Application Name
Cube Name
Dimension Definitions

You can change the application name and cube name, and delete one or more dimensions.

On the Cube Designer ribbon, select Designer Panel .
In the Designer Panel, select the Cube tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
Change the application name or the cube name, if you want to.
Add one or more dimensions by typing the name in the text box and pressing the enter key after each one.
In the Dimensions list
- If you want to delete a dimension, right click the dimension name and select Delete Dimension.
  Alternatively, you can select a dimension name and press the delete key.
- If you want to rename a dimension, right click the dimension name and select Rename Dimension.
Select To Sheet to propagate the changes to the application workbook.
Examine the updated application workbook to see your changes.

Work with the Cube.Settings Worksheet: Alias Tables in Cube Designer

You can add new alias tables in the Cube.Settings worksheet.

In the Designer Panel, select the Settings tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
In the Alias Tables field, enter a name for the new alias table.
Press Enter.
Select To Sheet .
A new alias table name is added on the Cube.Settings worksheet in the application workbook. To add the alias table to a dimension worksheet, open the Dimensions tab in the Designer Panel, and add the alias table to the selected dimension worksheet. See Work with Dimension Worksheets in Cube Designer. After you add the alias table to the dimension worksheet, you must populate the aliases manually, or by copying from a source.

Work with the Cube.Settings Worksheet: Properties in Cube Designer

You can add new properties in the Cube.Settings worksheet.

In the Designer Panel, select the Settings tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
Expand the Properties section.
Make your selections.
Select To Sheet to propagate the changes to the application workbook.

Work with the Cube.Settings Worksheet: Dynamic Time Series in Cube Designer

You can add dynamic time series members in the Cube.Settings worksheet.

In the Designer Panel, select the Settings tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
Expand the Dynamic Time Series section.
Make the changes that you want.
Select To Sheet to propagate the changes to the application workbook.

There are reserved generations names used by dynamic time series. For example, using the generation name of “Year” activates dynamic time series for “Y-T-D.”

Work with the Cube.Settings Worksheet: Attribute Settings in Cube Designer

You change attribute settings on the Cube.Settings worksheet.

In the Designer Panel, select the Settings tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
Expand the Attribute Settings section.
Make the changes that you want.
Select To Sheet to propagate the changes to the application workbook.

Work with the Cube.Settings Worksheet: Substitution Variables in Cube Designer

You can add cube-level substitution variables on the Cube.Settings worksheet.

Enter the name of the substitution variable in column A. Enter the corresponding value of the substitution variable in column B.

You must enclose member names in double quotes.

Work with Dimension Worksheets in Cube Designer

In the Designer Panel, select the Dimensions tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
Make the changes that you want.
For descriptions of the options and valid values, see Understand Dimension Worksheets.
(Optional) If you want to update the Cube.Generations worksheet in the application workbook for this dimension, click the Update Generation Worksheet button.

The Update Generation Worksheet button creates a section in the Cube.Generations worksheet for the dimension selected in the Dimension drop down list on the Dimensions tab of the Designer Panel.

The Dimension section of the Cube.Generations worksheet changes if you add or delete members on the dimension worksheet (Dim.dimname), causing the number of generations in the dimension to change. If you make changes to the dimension worksheet by adding or deleting members, you should always press the Update Generation Worksheet button as part of the editing process.
Select To Sheet to propagate the changes to the application workbook.

After adding alias tables using Designer Panel, populate the alias table column with alias names manually, or by copying them from a source.
Use no more than 1024 characters when naming dimensions, members, or aliases.
The length limit for the dimension worksheet is 30 characters, including 3 characters for the "Dim." at the beginning of the sheet name. So, the name following "Dim." can contain up to 27 characters.

See Understand the Cube.Generations Worksheet.

Work with Data Worksheets in Cube Designer

You can create data worksheets in the Designer Panel for new or existing application workbooks. You can also edit the display of dimensions and members in new or existing data worksheets.

To create a new data worksheet:

In the Designer Panel, select the Data tab.
Enter a name for the new data worksheet in the Data Sheets field.
In Sheet Type, select the type of sheet to create:
- Data Dimension
  In a data dimension sheet, dimensions are represented in the column headers. These indicate the member combinations to which to load the data. The right-most columns are the data columns. The data column headers specify members of the data dimension, which is the dimension to which you are loading data. The data values reside in the data column rows.
- Flat
  In a flat sheet, each dimension is represented in a column header. The last column, *Data* contains the data values for the specified member combinations.
- Sandbox
  In a sandbox sheet, each dimension is represented in a column header. The first dimension is Dimension.Sandbox. The last three column headers display members of the CellProperties dimension: EssValue, EssStatus, and EssTID. Do not modify the EssValue, EssStatus, and EssTID columns, as they are for internal purposes and it is important that they not be changed.
Press Enter.
Optional: Edit the data layout. Change the order of the dimension columns, and (for the Data Dimension sheet type only) select members and set their order. See subsequent sections in this topic for instructions.
Select To Sheet .
After you select a sheet type, and then select To Sheet or From Sheet, you cannot change the sheet type from the Designer Panel.

A new data worksheet is created in the application workbook.

To change the order of dimensions in the data worksheet:

In the Designer Panel, select the Data tab.
In Data Sheets, select the sheet that you want to edit.
In Dimension Column Order, select the dimension that you want to move.
Use the up and down arrows to move the dimension.
Select To Sheet to add your changes to the selected Data tab in the worksheet.

To change the order of members on the data worksheet (Data Dimension sheet type only):

In the Designer Panel, select the Data tab.
In Data Columns, select the member that you want to move.
Use the up and down arrows to move the member.
Select To Sheet to add your changes to the selected Data tab in the worksheet.

To select the members to display in a data worksheet (Data Dimension sheet type only):

In the Designer Panel, select the Data tab.
Click Member Selection.
In the Member Selector, check the members you want to display and clear the members you don't want to display.
Click OK.
Select To Sheet to add your changes to the selected Data tab in the worksheet.

To add data worksheets to an existing application workbook, go to the Data tab in the Designer Panel, click From Sheet , and proceed with the steps in this topic.

Work with Calculation Worksheets in Cube Designer

You can create new calculation worksheets in the Designer Panel.

In the Designer Panel, select the Calc tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
In the Calculation Sheets field, enter a name for the new calculation worksheet.
Press Enter.
Select To Sheet .
A new calculation worksheet is created in the application workbook.

Cube Designer calculation worksheets apply only to block storage cubes.

Work with MDX Worksheets in Cube Designer

You can create new MDX worksheets in the Designer Panel.

In the Designer Panel, select the Calc tab.
Select From Sheet to populate the Designer Panel with the contents of the application workbook.
In the MDX Insert Sheets field, enter a name for the new MDX worksheet.
Press Enter.
Select To Sheet .

A new MDX worksheet is created in the application workbook.

See Understand MDX Worksheets.

Create a Cube from a Local Application Workbook in Cube Designer

Using a sample local application workbook, you can create a cube from Cube Designer.

In Excel, on the Cube Designer ribbon, select Local , and then select Open Application Workbook.
Select an application workbook, then select Open.
On the Cube Designer ribbon, select Build Cube .
On the Build Cube dialog box, verify that you want to use the selected options. Cube Designer detects data worksheets and calculation worksheets in the application workbook, and pre-selects those options for you, however you can deselect those options if you want to:
- Load Data Sheets Contained within Workbook is pre-selected if data worksheets exist in the workbook. You can de-select this option if you do not want to load data.
- Run Calculation Sheets Contained within Workbook is pre-selected if calculation worksheets exist in the workbook. You can de-select this option if you do not want to run the calculations.
Click Run.
After the asynchronous job completes a dialog box is displayed. Click Yes to launch Job Viewer and view the status of the Excel import, or click No if you don't want to launch Job Viewer.
See Work with Jobs in Cube Designer.

Work with Typed Measures Worksheets in Cube Designer

You can add date measures or text list definitions to application workbooks to work with typed measures.

Open an application workbook.
On the Cube Designer ribbon, click Cube Designer to open the Designer Panel.
Click the Settings tab.
Click From Sheet to populate the Designer Panel with the contents of the application workbook.
To add date measures:
1. In the Cube.Settings worksheet, under Properties, modify the Date Format to the format you want to load into the cube.
2. If a Cube.TypedMeasures sheet does not exist in the application workbook, add one:
  1. On the Designer Panel, Settings tab, expand Text Lists.
  2. In the Text Lists field, type a name.
  3. Press Enter.
3. Identify the members in Accounts dimension and add them in cells to the right of Associated Members in the Date Measures section. These are the members that will allow dates to be loaded into the cube as data.
4. Rebuild the cube.
To add text lists:
1. If a Cube.TypedMeasures sheet does not exist in the application workbook, add one:
  1. On the Designer Panel, Settings tab, expand Text Lists.
  2. In the Text Lists field, type a name.
  3. Press Enter.
    The text list name is moved to the text box below the Text Lists field.
2. If a Cube.TypedMeasures sheet already exists in the application workbook, you can create additional text list tables to that sheet by following the steps in 6a and using a new name in the Text Lists field.
3. After you add the text list, you must enter the text list information manually. This includes the associated members for the text list, the valid text items in the list and their related numeric values.
4. Rebuild the cube.

Create a Cube from Tabular Data in Cube Designer

This workflow uses two sample tabular data Excel files to demonstrate the concepts of intrinsic and forced-designation headers (hints). See Transform Tabular Data to Cubes.

In Excel, on the Cube Designer ribbon, click Catalog .
On the Essbase Files dialog box, under Catalog, select Gallery, then select a sample tabular data file:
- Technical > Table Format > Sample_Table.xlsx: Intrinsic headers
- Technical > Table Format > Unstr_Hints.xlsx: Forced-designation headers
Click Open.
On the Cube Designer ribbon, select Transform Data .
On the Transform Data dialog box, enter an application and cube name, if you want to change the default names that are prepopulated. If the application name already exists, you will not be able to preview data or build a new cube, so you will need to enter a new application name.
The application name is based on the source file name without the extension and the cube name is based on the worksheet name.
- Sample_Table.xlsx: Application name is Sample_Table and the cube name is Sales.
- Unstr_Hints.xlsx: Application name is Unstr_Hints and the cube name is SpendHistory.
If you selected Sample_Table.xlsx, do not select Preview Data. Skip to step 8 to create the cube.
If you selected Unstr_Hints.xlsx, press Preview Data. The workbook is sent to Essbase for analysis and the relationships are returned for viewing.
1. Using the tree view, you can drag and drop members (and their children) to different locations in the tree. This changes the default designations and by doing so, creates different dimension hierarchies, measure hierarchies, and skipped members than the default analysis provided. You can also right click on a member name and designate the property of the member: Generation, Attribute, Alias or UDA.
  In some cases, there is special behavior when you change members from one designation to another:
  - When you drag and drop a generation to a measure, all attributes, UDAs, and aliases of the source generation are also moved to measures.
  - When you drag a generation to Skipped, all attributes, UDAs, and aliases in that generation are also moved to Skipped.
  - Dragging and dropping a measure to another designation is only allowed if the measure has no formulas.
2. If you do not want to save your changes, select Options and then select Reset to Original Header.
3. If you want to change the cube type and the type of dimensions to be created, before deploying, select Options, and then select Cube Type. Select Hybrid BSO (block storage option) or ASO (aggregate storage option).
4. You can guide the analysis to detect two kinds of designations: either measures and hierarchical dimensions, or measures, hierarchical dimensions, and attributes. Define these by selecting Options, then Cube Design, and then selecting one of the options. After you've made your selection, click Preview again.
When you are ready to create the cube, click Run.
When asked if you want to create the cube, click Yes.
(Optional) When asked if you want to see the cube job status, click Yes.

The newly created application and cube are listed on the Applications home page in the Essbase web interface and are available in Cube Designer. Now that the cube has been created from the tabular data, you can export the cube to an application workbook.
On the Cube Designer ribbon, select Local , then select Export Cube to Application Workbook.
On the Export Cube to Application Workbook dialog box, select the application and cube, and then select Run .

To create a cube using the web interface, see Create and Update a Cube from Tabular Data.

Update Cubes Incrementally in Cube Designer

Updating a cube is how you load dimensions and members to a cube outline using a data source and a rule file.

You can also use Essbase to add dimensions and members manually (see Creating and Updating Cubes from Tabular Data).

In an existing cube, you can incrementally update a dimension, or add a new one.

You cannot use Cube Designer to delete dimensions or rename members in an existing cube.

In Excel, on the Cube Designer ribbon, select Build Cube. .
Choose an Update Cube option from the Build Option menu.

When an outline was changed by a dimension build, the database may be restructured. Each of these options specifies how data values are handled during restructures:
1. Update Cube - Retain All Data
  
  All data values are preserved.
2. Update Cube - Retain Input Data
  
  All blocks (both upper-and lower-level) that contain loaded data are preserved.
  
  This option applies only to block storage cubes.
3. Update Cube - Retain Leaf Data
  
  Only leaf (level 0) values are preserved. If all data required for calculation resides in leaf members, then you should select this option. If selected, then all upper-level blocks are deleted before the cube is restructured. Therefore, the disk space required for restructuring is reduced, and calculation time is improved. When the cube is recalculated, the upper-level blocks are re-created.
4. Update Cube - Remove All Data
  
  All data values are cleared.
  
  This option applies only to block storage cubes.

Dimension build definitions are contained within the application workbook and automatically generate the necessary rules files. You do not select a rule file when building dimensions in Cube Designer.
When making changes to user-defined attributes (UDAs) while updating a cube incrementally using Cube Designer and an application workbook, you must specify all the UDAs in the dimension sheet, both new ones you are adding and existing UDAs in the outline. If you specify some UDAs (such as those you are adding), but not all of them, those that are not specified are deleted.
When incrementally adding 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:
1. Add the new dimension with the top member as stored.
2. Run a calc script to copy the data from the new top member into another stored member in that dimension.
3. Change the top member to dynamic calc.

Reset a Dimension in Cube Designer

To perform certain dimension editing operations while retaining all data using cube designer, you must use the Reset Dimension Incremental Mode on the dimension worksheet in the application workbook.

Using reset dimension clears the members from the dimension and then rebuilds them, retaining the data.

You must update the entire dimension when using reset dimension, otherwise members and data will be lost.

Use Reset Dimension for the following dimension editing operations:

Re-order members
Insert a new member in a specific location
Remove members and maintain the shared members
Move the members and retain the shared members
Move parent members and have all the children move with them

Leave Allow Moves set to No, otherwise, you will not be able to build shared members.

Renaming members using this technique is not supported.

To perform a dimension reset in cube designer:

Open the application workbook.
On the cube designer ribbon, click Designer Panel .
In the designer panel, click To Sheet .
In the application workbook, select the dimension you want to reset.
In the designer panel, in the Incremental Mode drop-down menu, select Reset Dimension.
In the designer panel, select To Sheet .
In the application workbook, on the dimension sheet, make sure that Allow Moves is set to No.
Save the application workbook.
Rebuild the cube. See Create, Load, and Calculate the Cube.

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:

In Excel, on the Cube Designer ribbon, select Load Data .
In the Load Data dialog box, select the application and cube in which you want to load data.
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.
Click Select Data.
In the Select Data dialog box, click Add.
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.
Under each data file that you selected in step 6, click the Select a load rule file icon to select the matching rule file.
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.
Click OK.
Select whether to Abort on Error.
If you select Abort on Error, the data load is stopped when an error is encountered.
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.
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:

In Excel, on the Cube Designer ribbon, select Load Data .
In the Load Data dialog box, select the application and cube in which you want to load data.
Click Select data.

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.

Click Add to select data and rule files.
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.
Under each data file that you selected in step 6, click the Select a load rule file icon to select the matching rule file.
In the Essbase files dialog box, browse to the rule file for that data file and select it.
Select whether to Abort on Error.
If you select Abort on Error, the data load is stopped when an error is encountered.
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.
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.

Create and Validate Member Formulas in Cube Designer

In the Cube Designer Formula Editor, you can write formulas for specific outline members. You can construct member formulas from operators, functions, dimension names, member names, substitution variables, and numeric constants.

The Cube Designer Formula Editor applies to both aggregate storage and block storage cubes. In aggregate storage, the functions will be MDX functions. In block storage, the functions will be calc script functions.
Validation works against existing block storage cubes in Essbase (validation is disabled for aggregate storage cubes). It does not detect application workbook changes that have not been applied to the cube.
Member selection works with existing cubes only.

Formula Editor provides a formula editing pane in which you can enter a formula. You can use the Tab and arrow keys to move focus within Formula Editor. You can also use a point-and-click approach to select and insert formula components into the formula editing pane. A member selection tree helps you place the correct member names into the formula.

Image of the cube designer formula editor.

Open the application workbook for the cube that you want to modify.
If a dimension worksheet has been defined with the Formula property, select the cell in the Formula column for the member you wish to create a formula.
On the Cube Designer ribbon, click Calculation Editors .
From the drop-down menu, select Member formula editor.
Enter your login credentials for Essbase, if prompted to do so.
In the Formula Editor, create the formula.
- Use the keyboard to enter formula text. Enclose in quotation marks any member names containing blanks or special characters.
- Select a cell containing a member name or alias from any dimension worksheet. Place the cursor in the appropriate location of the editor and right-click to paste that name surrounded by quotes into the editor.
- Double-click on a member in the member selection tree to have that member pasted into the editor.
- To search for a specific member in the tree, enter the member name in the Member Tree text box, and click Search.
- Double-click on a function to have that function syntax pasted into the editor.
Optional: on block storage cubes, click Validate to check formula syntax.
If the validation fails, edit the formula and try again. Be sure to check the error message for guidance.

The Validate button is disabled for aggregate storage cubes.

See:

Create and Validate Calculation Scripts in Cube Designer

In the cube designer Calculation Script Editor, you can write calculation scripts for specific block storage cubes. Calculation scripts specify how cubes are calculated and, therefore, override outline-defined cube consolidations.

See Developing Calculation Scripts for Block Storage Databases.

The cube designer Calculation Script Editor applies only to block storage cubes.
If the cube of the currently open application workbook exists on the server and the communication to the server works, you are in online mode. Otherwise, you are in offline mode. When you are in offline mode, the member tree is disabled.
If you are editing a script locally, and a script with the same name exists on the server, the Save changes to server and worksheet check box is enabled. If you are editing a remote script (one that exists only on the server), the check box is disabled.
Validation works against existing cubes in Essbase. It does not detect application workbook changes that have not been applied to the cube.

The Calculation Script Editor provides a calculation script-editing pane in which you can enter a script. You can use the Tab and arrow keys to move focus within Calculation Script Editor. A member selection tree helps you place the correct member names into the script.

Image of the cube designer calculation script editor.

Open the application workbook for the cube you want to modify.
If a calculation worksheet has been defined, select it. If not, create one. See Work with Calculation Worksheets in Cube Designer.
On the cube designer ribbon, click Calculation Editors .
Select Calculation script editor from the drop-down menu.
Enter your login credentials for Essbase, if prompted to do so.
In the Calculation Script Editor, create the calculation script.
- Use the keyboard to enter text. Enclose in quotation marks any member names containing blanks or special characters.
- Double-click on a member in the member selection tree to have that member pasted into the editor. To search for a specific member in the tree, enter the member name in the Member Tree text box, and click Search.
- Double-click on a function to have that function syntax pasted into the editor.
Click Validate to check calculation script syntax. If the validation fails, edit the script and try again. Be sure to check the error message for guidance.
If you want to synchronize changes to the server and the application workbook, you can check Save changes to server and worksheet prior to saving the script.

Note:
This option works in online mode.
Click Save.
Optional: Click Run to run the script.

Calculate Data in Cube Designer

Calculation scripts specify how cubes are calculated and, therefore, override outline-defined cube consolidations. For example, you can calculate cube subsets or copy data values between members. See Developing Calculation Scripts for Block Storage Databases.

During cube development, it is common to recalculate a cube many times when validating the data and formulas. The calculation script files used in the calculation process must be stored in Essbase. If a Calc worksheet is included in the application workbook, then the calculation script files are automatically generated during the cube build process. Individual calculation script files can also be uploaded to Essbase. See Work with Files and Artifacts.

In Excel, on the Cube Designer ribbon, select Calculate .
On the Calculate Data dialog box, select an application and a cube, and select the calculation script you want to use. To view or edit the calculation script, click Edit. Right click on the calc script name to display options: rename, delete, run, copy, and lock, or unlock.
Click Run to start the calculation.
When the asynchronous job completes a dialog box is displayed. Click Yes to start Job Viewer and view the status of the calculation, or click No if you do not want to start Job Viewer.
(Optional) View the status in Job Viewer.
See Work with Jobs in Cube Designer.

Work with Jobs in Cube Designer

Use the Cube Designer Job Viewer to view, monitor and troubleshoot jobs that you run from your particular client. Jobs are operations such as data loads, dimension builds, and calculations.

A record of all Essbase jobs is maintained in the Essbase instance. Each job has a unique ID number.

The jobs listed in the Job Viewer are for one specific user. If a different user logs into the client, then only jobs for that user are displayed.

View Jobs in the Cube Designer Job Viewer

You can view jobs for the specific user that is logged into the client in the Cube Designer Job Viewer.

In Excel, on the Cube Designer ribbon, click View Jobs .

The Job Viewer dialog box opens, showing a list of jobs that have been run from that particular client.

Monitor Cube Designer Jobs

The Cube Designer ribbon shows when a job is in progress. After the job finishes, you can view the status of the job in the Cube Designer Job Viewer.

While a job is running, the View Jobs icon on the Cube Designer ribbon displays an hourglass .
When the job finishes running a Job Viewer status dialog box displays, indicating the status of the job.

If you close Excel while the job is running, the job continues to run, but you will not see a status dialog when it finishes. The job is a server process, so it runs regardless of whether Excel is open or not.

Troubleshoot Jobs in the Cube Designer Job Viewer

If a job fails, you can view and troubleshoot errors.

In Job Viewer dialog box, select a job and click Details to see the job details.
In the Job Details dialog box, select a file from the Server Error Files drop-down menu and click Open to view and troubleshoot errors.

Clear and Archive Cube Designer Jobs

Clear the Job Viewer or archive job viewer logs periodically to improve performance.

Press Clear All to remove all jobs from the Job Viewer dialog box.
To selectively remove individual jobs, select one or more jobs and press the Delete key.
- Use the Shift key to select multiple contiguous jobs.
- Use the Ctrl key to select multiple non-contiguous jobs.
To archive the job viewer logs, copy and rename the log file and then delete the original.
Job viewer logs are located in C:\Users\username\AppData\Roaming\Oracle\SmartView\DBX\Jobs.

There is a separate log for each user on the client machine.

Removing jobs from the Job Viewer dialog box or archiving job viewer logs only affects the client. You can still view all jobs in the web interface.

View Dimension Hierarchies in Cube Designer

You can view dimension hierarchies in the Cube Designer Dimension Hierarchy viewer. To learn more about hierarchies, see Outline Hierarchies.

Open the application workbook containing the hierarchy that you want to view.
Select the dimension worksheet for the hierarchy that you want to view.
On the Cube Designer ribbon, select Hierarchy Viewer .

When you view a hierarchy in Cube Designer, you can perform some actions on the hierarchy. These include:

To search for a member in the hierarchy, enter a member name in the Find Next text box, and click Find Next .
To find a member of the dimension in the application workbook dimension worksheet, either double-click a member in the hierarchy or right click on a member in the hierarchy and select Go To.

The corresponding member in the application workbook is highlighted.
To rename a member:
1. Right-click a member in the hierarchy and select Rename.
2. Enter the new member name.
3. Press Enter.
The corresponding member is renamed wherever found within the Parent and Child columns of the dimension worksheet.
To set storage for all parents (except members containing formulas or defined as label only) to dynamic calc or to stored:
1. Select the member in the hierarchy and click Edit parents .
2. On the drop-down menu, select Set storage to dynamic calc or Set storage to stored.
To expand or collapse a hierarchy:
1. Right-click a member in the hierarchy.
2. Select Expand All or Collapse All.
To show or hide aliases, storage, or operators:
1. Click Show.
2. Click Alias, Storage, or Operator, to show or hide those items.

Export Cubes to Application Workbooks in Cube Designer

In Cube Designer, you can export any cube that exists in Essbase.

Select the build method, either parent-child or generation format.
In Excel, on the Cube Designer ribbon, select Local , then select Export cube to application workbook.
On the Export Cube dialog box, select the application and cube that you want to export.
- Select Include Data if you want input level data included in the application workbook.
  - In block storage cubes, if the size of the data is 400 MB or less, data is exported to the application workbook, on the Data worksheet. If the data size exceeds 400 MB, data is exported to a flat file named Cubename.txt, which is included in a file named Cubename.zip. The .zip file is created in the specified export directory if the export process is successful.
  - In aggregate storage cubes, regardless of the size of the data, it is always exported to a flat file named Cubename.txt, which is included in a file named Cubename.zip. The .zip file is created in the specified export directory if the export process is successful.
- Select Include Calculation Scripts if you want calculation scripts in your block storage cube included in the application workbook.
  
  Aggregate storage cubes do not have calculation scripts.
- Select Include Member IDs if you want to include Member IDs on the dimension sheets in the application workbook.
Click Run.
When the export is completed, click OK.
The application workbook is saved to the local folder location: C:\Users\username\AppData\Roaming\Oracle\smartview\DBX. Because it is saved to the local folder location, you can open it using the Local icon on the Cube Designer ribbon.

The exported application workbook can be imported to Essbase. See these topics:

Delete Applications and Cubes in Cube Designer

In Cube Designer, you can delete any application or cube that exists in Essbase. Deleting an application or cube cannot be undone.

In Excel, on the Cube Designer ribbon, select Admin tasks .
From the menu, select Delete Application or Delete Cube.
From the Delete Application or Delete Cube dialog box, select the application or cube you want to delete.

Unlock Objects in Cube Designer

Essbase uses a checkout facility for cube objects (such as calculation scripts and rules files). Objects are locked automatically when they are in use and the locks are deleted when they are no longer in use.

You can view and unlock objects, according to your security role. Users with the Service Admin role can unlock any object. Users without the Service Admin role can unlock only those objects that they locked.

To unlock an object in cube designer:

In Excel, on the cube designer ribbon, select Admin tasks
Select Unlock Essbase objects.
Enter your login credentials if prompted to do so.
Under Select an application, select the application containing the object you want to unlock.
Under Select a locked object, select the object you want to unlock.
Click Unlock.

View Logs in Cube Designer

In cube designer, you can view the platform log or an application log.

In Excel, on the cube designer ribbon, select Admin tasks
From the menu, select View Logs.
Select a log to view:
- Select View Platform Log to view the log for the platform service.
- Select View Application Log to view the log for an individual application.