1. Using Oracle Essbase
  2. Integrate Essbase with Autonomous Database Using Federated Partitions
  3. Set Up Fact Table and Identify Pivot Dimension

Set Up Fact Table and Identify Pivot Dimension

A fact table in Autonomous Data Warehouse stores the data of the Essbase cube having a federated partition. If you don't have a fact table that meets the requirements for federated partitions, you must create one. You also need to understand what a pivot dimension is, so that you can select one from your Essbase cube.

Before starting this section, create an Essbase application and cube, if you don't already have one.

Create the Fact Table

For federated partitions, the fact table stores the data values of the Essbase cube. If you don't have the required fact table in Autonomous Data Warehouse, you must create one.

Before you start, ensure you have an empty schema for the fact table. See Create a Schema for Federated Partitions.

The fact table must be in Essbase-ready format, meaning that it meets the following requirements for its content and shape:

  • Each of the cube's (non-attribute) dimensions must represented as a single column header, with the exception that one of the cube's dimensions (typically the one containing measures/accounts) must be pivoted into two or more columns.

    Note:

    Elsewhere in the documentation, the dimension that is pivoted will be referred to as the pivot dimension.

  • The fact table must be comprised of unique records (no duplicates), with one row per sequence of Essbase cell intersections.

If you are familiar with Essbase data exports, you will notice that the shape of the fact table is exactly like an Essbase column export.

Similarly to a column export, the fact table must include:

  • one column for each (non-attribute) dimension of the outline (except for the pivot dimension)

  • one column for each stored member of the pivot dimension

The following is an example of a fact table in which the measures dimension has been pivoted, which means it is the pivot dimension. The pivot dimension affects the shape of the fact table, as that dimension's stored members become column headers: SALES, COGS, MARKETING, PAYROLL, MISC, INTITIAL_INVENTORY, and ADDITIONS.


Fact table with denormalized data and metadata

You can either build the fact table using SQL, or you can create it from an Essbase data export. You can load data to the fact table using Autonomous Data Warehouse tools, or using Essbase data load functionality.

Additional guidelines for building a fact table include:

  • The fact table must have fewer than 1000 columns.

  • Do not include columns that will map in Essbase to attribute dimensions.

  • The fact table should not have lesser precision than IEEE binary64 (double).

  • The fact table should have internationalized strings for dimension members, using NVARCHAR2 type, with 1024 bit character length.

Fact Table Creation Example

To create a fact table in Autonomous Data Warehouse, you can use SQL.

  1. Using SQL Developer or your tool of choice, log in to Autonomous Data Warehouse as the schema owner (from step Create a Schema for Federated Partitions).

  2. Use SQL to create the fact table, if you don't already have one.

    For example, the following SQL creates a fact table from data export from the Essbase cube, Sample Basic. 

    CREATE TABLE "SAMP_FACT"
( "PRODUCT" NVARCHAR2(1024),
"MARKET" NVARCHAR2(1024),
"YEAR" NVARCHAR2(1024),
"SCENARIO" NVARCHAR2(1024),
"SALES" NUMBER(38,0),
"COGS" NUMBER(38,0),
"MARKETING" NUMBER(38,0),
"PAYROLL" NUMBER(38,0),
"MISC" NUMBER(38,0),
"INITIAL_INVENTORY" NUMBER(38,0),
"ADDITIONS" NUMBER(38,0)
) NOCOMPRESS LOGGING PARALLEL 4;

Notes

  • In the example above, the fact table name is SAMP_FACT, and is based on Sample Basic.

  • For best performance, all non-numeric columns in the fact table should be of type NVARCHAR2(1024), and all numeric columns should be of type NUMBER.

  • Oracle recommends enabling parallel creation of the index in Autonomous Data Warehouse, by adding PARALLEL 4.

  • Metadata columns should not allow inclusion of NULL values.

  • Oracle recommends NOCOMPRESS when the usage of the cube includes data generative processes such as incremental data load or batch script updates. If the cube is to be used mostly for read operations, use COMPRESS to optimize the fact table for reporting.

  • If you get the following validation error when creating the fact table, delete null rows.

    ORA-18265: fact table key column ("<DIM_NAME>") with value ('') not in
      dimension("<Name_of_Column") star table key column

  • For best performance, refrain from adding any specific constraints on the table without a definitive need.

  • In the example above, the fact table name is based on Sample Basic, which is available in the gallery in the Essbase file catalog. You can export the data from this sample cube or any other Essbase cube, and load it to build a fact table. Before you can do this, you will need to set up credentials for loading data to a federated partition application. Refer to Federated Partition Data Load to set up the credentials and learn about how to export data to DBMS format using the DATAEXPORT command.

Identify the Pivot Dimension

As part of designing a federated partition, you need to select the pivot dimension. A pivot dimension is a dimension you designate from the Essbase cube outline to represent numeric data values.

  • The pivot dimension does not have to be measures/accounts, but it may be.

  • All stored members of the pivot dimension must map to the fact table columns that represent your numeric data values in Autonomous Data Warehouse.

  • If you need to run Essbase block storage (BSO) calculation scripts, select a dense dimension as the pivot dimension. Calculation scripts are not supported for federated partitions if the pivot dimension is sparse.

  • The pivot dimension should have fairly static member names, and not a very large number of members. Reason: Changing the pivot dimension in the Essbase cube outline (for example, by adding or renaming stored members) necessitates corresponding, manual updates to the fact table in Autonomous Data Warehouse, and also requires recreation of the federated partition.

  • Essbase dimensions that include members requiring complex, dynamic formulas (such as "Opening Inventory" and "Ending Inventory," using Sample Basic as an example) should not be selected as the pivot dimension.

  • You provide your selected pivot dimension at the time of creating a federated partition.

  • Oracle Database has a limit of 1,000 columns, and the pivot dimension inherits this limit. Determine the number of eligible column members in the pivot dimension to ensure that you do not encounter the limit. The number of potential stored member combinations in the pivot dimension plus the number of dimensions in the cube should be less than or equal to 1,000.

  • For aggregate storage cubes, dimensions containing multi-level stored member hierarchies should not be selected as the pivot dimension. Select a pivot dimension with dynamic hierarchies, or a stored hierarchy that is a flat, single-level hierarchy (where all members are level 0 stored members).