Identify the Pivot Dimension and Set up the Fact Table

A fact table in Autonomous AI Lakehouse stores the data of the federated cube. If you don't have a fact table that meets the requirements for federated partitions, and you are not letting Essbase manage the fact table, you must create one.

Note:

If you are letting Essbase manage the fact table, Essbase creates the fact table for you. See Data Load Options for Federated Cubes.

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

Identify the Pivot Dimension

As part of designing a federated cube, 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 AI Lakehouse.

  • 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 cubes 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 AI Lakehouse, 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 federated cubes based on ASO, Oracle recommends that dimensions containing multi-level stored member hierarchies 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).

Create the Fact Table

A fact table in Autonomous AI Lakehouse stores all the data values of a federated cube. If you don't already have the required fact table, and your federated cube is not Essbase- managed, you must create one.

Note:

This topic is not required if you are letting Essbase manage the fact table, because Essbase creates the fact table for you. See Data Load Options for Federated Cubes.

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

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. This exception is called 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 AI Lakehouse 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 AI Lakehouse, you can use SQL.

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

  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 AI Lakehouse, 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 cube. Refer to Set up Credentials for Federated Cubes to set up the credentials and learn about how to export data to DBMS format using the DATAEXPORT command.