5 Design and Create Cubes Using Application Workbooks

You can design, create, and modify fully functional cubes using Excel-based application workbooks. You can design the cube within the application workbook, quickly import the workbook to Essbase to create a cube, load data into the cube, and calculate the cube. You can also work with application workbooks in Cube Designer, which is a Smart View extension.

• About Application Workbooks

• Download a Sample Application Workbook

• Create a Cube from an Application Workbook

• Export a Cube to an Application Workbook

• Connect to a Cube in Smart View

About Application Workbooks

Application workbooks comprise a series of worksheets, which can appear in any order, and define a cube, including cube settings and dimensional hierarchies. Optionally, you can define data worksheets to be loaded automatically when you create the cube, and calculation worksheets to be executed after you load the data. There are strict layout and syntax requirements for application workbooks, and there are many validations to ensure that workbook contents are complete and formatted correctly. If the application workbook contents are not correct, then the cube building process will not be successful.

You can modify the worksheets directly in Microsoft Excel or by using the Designer Panel.

In Japanese Excel, if you enter Kanji characters directly on the sheet, the characters are not displayed correctly. Instead, use a text editor to type the Kanji characters and then copy the content into Excel.

Essbase provides application workbook templates for creating block storage and aggregate storage applications and cubes.

• Block Storage Sample (Stored): Block storage application workbook. File name: Sample_Basic.xlsx.

• Block Storage Sample (Dynamic): Block storage application workbook. All non-leaf level members are dynamic. File name: Sample_Basic_Dynamic.xlsx.

• Block Storage Sample (Scenario): Block storage application workbook with scenarios enabled. All non-leaf level members are dynamic. File name: Sample_Basic_Scenario.xlsx.

• Aggregate Storage Sample: Aggregate storage application workbook. File name: ASO_Sample.xlsx.

• Aggregate Storage Sample Data: Data for the aggregate storage application workbook. File name: ASO_Sample_DATA.txt.

• Tabular Data Sample: Tabular data Excel file. File name: Sample_Table.xlsx.

Oracle recommends that you download a sample application workbook and examine the worksheets. See Application Workbooks Reference.

Download a Sample Application Workbook

Using a sample application workbook provided in Essbase, you can quickly create sample applications and cubes. The cubes are highly portable, because they are quickly and easily imported and exported.

1. On the Applications page, select a cube and then click Files.
2. Decide if you want to download a sample aggregate storage application workbook, or a sample block storage application workbook:
   1. To download a sample aggregate storage application workbook, under All Files > Gallery > Applications > Demo Samples > Aggregate Storage.
   2. To download a sample block storage application workbook, under All Files > Gallery > Applications > Demo Samples > Block Storage.
3. From the Actions menu to the right of the file you want to download, select Download.
4. Optionally, if you download the aggregate storage application workbook, ASO_Sample.xlsx, you can also download a data file, ASO_Sample_Data.txt.
5. Save the file to a local drive.
6. Open the file and examine the worksheets to understand how you can use the workbook to create an application and cube.

Create a Cube from an Application Workbook

1. In the Essbase web interface, on the Applications page, click Import.
2. In the Import dialog box, select File Browser to browse to a sample application workbook you previously downloaded.
   You cannot import Excel files that contain spaces in the filename.
3. Your application and cube names are populated based on the names you specified in the application workbook on the Essbase.Cube worksheet.

   • (Optional) You can change the application and cube names on this screen.

   • (Required) If an existing application in Essbase matches the name of the application you are importing, then you must ensure that the cube name is unique. For example, if the name of the application and cube in the Excel workbook is Sample Basic and Essbase already has a Sample Basic cube, then you’re prompted to rename the cube.

4. (Optional) Select Advanced Options, which allows you to choose a build option and whether to load data and execute calculation scripts.
5. (Optional) Select View Dimensions, which allows you to view the mapping of workbook columns to the dimensions to be created.
6. Click OK.
   The application and cube are listed on the Applications page.
7. To view the outline, expand the application; then click the Actions menu to the right of the cube name, and launch the outline editor.

When you import an application workbook that was created using the command-line 11g Cube Export Utility, some member names might be rejected. See Review Member Names Before you Import an Application Workbook Created by the 11g Cube Export Utility.

If you import an application workbook and then export the cube you created to a new application workbook, the layout of the dimension sheets in the new application workbook might differ from the original, however the new workbook functions the same as the original workbook.

Export a Cube to an Application Workbook

1. In Essbase, expand the application that contains the cube that you want to export.
2. From the Actions menu, to the right of the cube name, select Export to Excel.
3. On the Export to Excel dialog box:

   • Select Export Data if you want to export the data from the cube. How the data is exported depends on whether the cube is block storage or aggregate storage.

     • In block storage cubes, if the size of the data is 400 MB or less, it 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 on the Files page.

     • In aggregate storage cubes, regardless of the size, data is always exported to a flat file named Cubename.txt, which is included in a file named Cubename.zip on the Files page.

   • Select a build method, Generation or Parent-Child.
   • Select Export Calculation Script if you want to export each of the calculation scripts as a separate worksheet within the application workbook.
4. When prompted, save the exported application workbook to your local or network drive or download the exported application workbook and data .zip files from the Files page.
   File names do not include spaces because files that are imported to Essbase cannot contain spaces in the file name.

If you choose the options to include data, calculation scripts, or both in an export when they do not exist in the cube, the job completes without errors, but no data or scripts are exported.

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

• Create a Cube from an Application Workbook

• Create a Cube from a Local Application Workbook in Cube Designer

Connect to a Cube in Smart View

In Smart View, you can create a private connection using the quick connection method, if you know the URL. The private connection URL is your Essbase login URL with the string /smartview appended to it.

1. From the Smart View ribbon, click Panel.
2. From the Smart View panel, click Home and then select Private Connections.
3. In the text box, enter the login URL ending with /essbase/smartview; for example, https://192.0.2.1:443/essbase/smartview.
4. Click the connect arrow .
5. On the Login dialog box, enter your Essbase user name and password, then click Sign In.