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. If you don't have a fact table that meets the requirements for federated partition, 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.

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.

For example,

CREATE TABLE "ADMIN"."SAMP_FACT" 
   (	"DIMENSION_PRODUCT" NVARCHAR2(1024), 
	"DIMENSION_MARKET" NVARCHAR2(1024), 
	"DIMENSION_YEAR" NVARCHAR2(1024), 
	"DIMENSION_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 TABLESPACE "ADMIN" PARALLEL 4;

Notes

  • In the example above, the schema user is ADMIN and the fact table name is SAMP_FACT.

  • 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.

  • Do not create any null rows.

  • 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 (such as NON NULL for members), without a definitive need.

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.