6 Design and Manage Cubes from Tabular Data

You can create a cube from tabular data by extracting fact tables from a relational database into an Excel file and then deploying the cube. You can also export a cube to tabular data.

Topics:

• Transform Tabular Data to Cubes
• Create and Update a Cube from Tabular Data
• Export a Cube to Tabular Data

Transform Tabular Data to Cubes

You can create a cube from tabular data by extracting fact tables from a relational database into an Excel file and then deploying the cube.

Patterns in the relationships between column headers and data are detected to deploy a multidimensional cube. The process for transforming tabular data into a structure that can be used in a multidimensional cube include these concepts:

• Correlations between columns

• Correlations between column types (such as date, number, and text)

• Header text analysis for common prefixes and business intelligence-related terms (such as cost, price, account)

• Report structure (such as merged cells and empty cells)

• (Optional) Forced-designation headers that are used to explicitly define the shape of a cube and can include formulas to create measures dimensions.

• Measures hierarchies (which can also be generated in Transform Data in Cube Designer).

Sample tabular data Excel files are provided to demonstrate the concepts of intrinsic and forced-designation headers.

When working with tabular data, you should analyze the data before you create a cube from it. Then, after the cube is created, you should determine if the cube outline is the way you want it.

You can create a cube from tabular data in the Essbase instance or in Cube Designer. See Create and Update a Cube from Tabular Data.

Use Intrinsic Headers to Transform Tabular Data to Cubes

Intrinsic headers use table.column format, which is demonstrated in the Sample_Table.xlsx file. In this sample file, the column headers have names such as Units, Discounts, Time.Month, Regions.Region, and Product.Brand.

The transformation process creates this hierarchy:

Units
Discounts
Fixed Costs
Variable Costs
Revenue
Time
   Month
   Quarter
Years
Regions
   Region
   Area
   Country
Channel
Product
   Brand
...

Use Forced Designation Headers to Transform Tabular Data Into Cubes

With forced-designation headers (hints), you can specify how tabular data should be handled during the transformation process.

For example, you can force a column to be treated as a measures or an attributes dimension. Most forced-designation headers require a keyword in brackets [ ]. Forced-designation headers are demonstrated in the templates Unstr_Hints.xlsx and Sample_Table.xlsx templates (available in the gallery).

Supported forced-designation header formats:

Table 6-1 Forced-designation Header Formats

Designation	Header Format	Example
Dimension generation	ParentGeneration.CurrentGeneration	Category.Product
Alias	ReferenceGeneration.Generation[alias]	Year.ShortYearForm[alias]
Attribute	ReferenceGeneration.AttributeDimName[attr]	Product.Discounted[attr]
Measures	MeasureName[measure]	Price[measure]
Measure generation	Parent.child[measure] Top-most parent, if unique, is the account dimension name. If not unique, this member is auto–generated in the account dimension.	Measures.profit[measure] profit.cost[measure] cost.price[measure]
Measures formula	MeasureName[=formula_syntax;]	profit[="price"-"cost";] profit[="D1"-"E1";] price[=IF ("S1" == #MISSING) "R1"; ELSE "S1"; ENDIF;]
Measures consolidation	MeasureName[+] : add to parent MeasureName[-] : subtract from parent MeasureName[~] : no consolidation (equivalent to [measure]) The default is no consolidation.	price.shipment[+] Consolidation can be defined only for measure dim
Formula consolidation	FormulaName[+=<formula>] : add to parent FormulaName[-=<formula>] : subtract from parent	profit[+=price-cost] cost.external[+=ExternalWork+ExternalParts]
UDA	ReferenceGeneration[uda]	Product[uda]
Skip The column is not read.	ColumnName[skip]	column[skip]
Recur The last column cell value is used for empty cells Recur can be combined with other forced designations; include a comma separated list of forced designations within a bracket, ColumnName[designationA,recur].	ColumnName[recur]	Product[recur] Product[uda,recur]

You can specify columns to be measures dimensions and you can use formulas to create measures dimensions with calculated data during the transformation process. The measures and measures formula forced-designation headers are specified with the name for the measures dimension, followed by a keyword or formula that is enclosed in square brackets and appended to the measures dimension name.

You can also consolidate measures and formulas by adding them to, or subtracting from, the parent.

To specify a column to be a measures dimension, in the column header, you enter the name of the measures dimension and then append the keyword [measure]. For example, you can specify the Units and Fixed Costs columns as measures dimensions by using this syntax: Units[measure] and Fixed Costs[measure].

The transformation process creates this hierarchy, with Units, Discounts, Fixed Costs, Variable Costs, and Revenue as measures:

Time
   Year
      Quarter
         Month
Regions
   Region
      Area
         Country
...
Product
   Brand
...
Units
Discounts
Fixed Costs
Variable Costs
Revenue

You can create a measure generation hierarchy ( parent.child[measure] hierarchy), in a similar way that you create regular dimension generations.

For example, to create a measure hierarchy, you enter Measures.profit[measure], profit.cost[measure] and cost.price[measure], which produces the following hierarchy:

Measures
      profit
            cost
                price

To create measures dimensions from formulas, in the column header, you enter the name of the measures dimension and then append the formula syntax in brackets [ ]. Within the brackets, start the formula with an equal sign (=) and end the formula with a semicolon (;). The arguments in the formula correspond to column names or cell coordinates, which must be enclosed in quotes. You can use Essbase calculation functions and commands in the formula.

Assume that you have an Excel file named Spend_Formulas.xlsx with tabular data on the SpendHistory worksheet, which has many columns. For example, there are dimensions named Year (column A) and Quarter (column B), and measures dimensions named Spend (column J) and Addressable Spend (column K). These columns have data. Then there are column headers that use formulas to create a measures dimensions. These columns do not have data. For example, to create the Total Spend dimension, the header in column O uses this Essbase formula: Measure.Total Spend[="Addressable Spend" + "Non-Addressable Spend";]. To create the AddSpendPercent dimension, the header in column P uses this Essbase formula: Measure.AddSpendPercent[="Addressable Spend"/"Total Spend";].

The transformation process creates this hierarchy:

The transformation process can also identify measures dimensions when a dimension name is duplicated. Assume that you have a column header that uses this formula, Meas.profit[="a1"-"b1";], which creates the Meas dimension. If, in another column header, you use the Meas dimension name as the top parent, such as Meas.Sales, the Sales dimension is also considered a measures dimension.

Create and Update a Cube from Tabular Data

In this workflow, you’re using the sample tabular data Excel file named Sample_Table.xlsx, which uses intrinsic column headers. See Transform Tabular Data to Cubes.

1. In the Essbase web interface, click Files.
2. On the Files page, click Gallery, then Technical, Table Format, and then Sample table.
3. From the Actions menu, next to Sample_Table.xlsx click Download.
4. Save the file to a local drive.
5. To create a cube: On the Applications page, click Import.
   1. On the Import dialog box, click File Browser and browse to Sample_Table.xlsx.
   2. On the Import Cube - Excel File dialog box, browse to Sample_Table.xlsx
      The application and cube names are pre-populated. The application name is based on the source file name without the extension (in this example, Sample_Table) and the cube name is based on the worksheet name (in this example, Sales).

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

      • (Required) If an existing application matches the name of the application that you’re importing, then you must ensure that the cube name is unique. For example, if there is already an application named Sample_Table with a cube named Sales, then you’re prompted to rename the cube.

   3. (Optional) Click Advanced Options to modify the cube type and the type of dimensions to be created.
      You can perform these actions:

      • Change the cube type. By default, cubes are set to BSO (block storage) with the Hybrid BSO option. You can keep the block storage type but remove the hybrid block storage option, or you can select the ASO (aggregate storage).

      • Select Enable Sandboxing, if applicable.

      • Click Show Transformations and, on the Transformations pane in the Import dialog box, enter names for the dimensions you want to rename.

      • Change the dimension types.

      If you make any changes, then click OK before proceeding.
      The application and cube are listed on the Applications home page.
   4. (Optional) To view the cube outline, expand the application. From the Actions menu, to the right of the cube name, launch the outline editor.
6. To update a cube with new members or additional data (as an incremental load), from an Excel file: on the Applications page, click Import.

   The tabular data must have forced designation headers, and the Excel properties must have two custom properties selected: database name and application name. Otherwise, it will use the Excel name as the application name, and sheet name as the cube name.

   1. To do the incremental load, select the file with the incremental data and load it to the cube in the application, which are specified in the Import dialog. On the Import dialog box, click File Browser, select the file to add, and click Open. A message reminds you that the cube already exists in the application.
   2. Click Advanced Options. For Build Option, select any update cube option, or keep the default, Update Cube — Retain All Data. Click OK.
      The cube and corresponding tabular data are updated.

You can't add shared members from tabular data.

Export a Cube to Tabular Data

To facilitate moving and sharing data between Essbase and relational sources, it can be useful to generate flattened output from the Essbase cube. To accomplish this, you can perform a tabular export from Essbase.

If you have at least database update application permission, you can export a cube from the Essbase web interface into Excel, in tabular format. This exported tabular data is organized into columns with headers that Essbase can use to deploy a new multidimensional cube.

The exported tabular data differs from data exported into an application workbook. Exported tabular data consists of data and metadata, whereas application workbooks are highly structured and contain more information about the cube, such as cube settings and dimensional hierarchies.

The following is an example of CSV output resulting from exporting Sample.Basic to table format:

The column headers contain intrinsic relationships, in a logical order, enabling Essbase to detect the relationship patterns needed to build a hierarchy.

The cube you export must meet the following conditions:

• It must not be a scenario enabled cube.
• It must have a measures dimension, and the measures dimension must be dense.
• It must not have asymmetric (ragged) hierarchies. See Hierarchy Shapes.

If you export a cube containing shared members, those members are not added to the exported file.

To export a cube in tabular format:

1. In the Essbase web interface, 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 Table Format.
3. Select whether to export dynamic blocks and click OK.

The column headers on the exported sheet are of the forced designation headers (hints) type.

You can import the tabular data file to create a new cube. See Transform Tabular Data to Cubes and Use Forced Designation Headers to Transform Tabular Data Into Cubes.