The Peformance Architects team is very excited that with the Oracle Analytics Cloud (OAC) release 105.2 in March 2019, we can finally create an Essbase cube using Data Flows in the OAC interface! This was available in prior releases of OAC 18.1.3, but in the 105.2 release, this functionality was extended to allow Essbase cube customization. This has been a huge ask in the marketplace since the Oracle Business Intelligence Enterprise Edition (OBIEE) 12c on-premises version was released.
Data flows can be used to both to build an Essbase cube and to load data as well. You can use data from a spreadsheet or any data source available in OAC to create an Essbase cube. It’s as simple as using tabular data by extracting fact tables from a relational database and deploying the cube in the Essbase cloud.
OAC is smart enough to determine the patterns in the relationships between column headers and data to deploy a multidimensional cube like 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 Units, Revenue, and Account),
Column headers can include:
A table.column format (also called “Intrinsic Headers”)
A table.column format also called “Forced Designated Headers” or “Hints”). You can specify how tabular data should be handled during the transformation process in the brackets; for example, to specify an alias for the “Month” column, you can include another column in the dataset “Month.Long Name[alias]”
Let’s go through an exercise to create an Essbase cube with some sample data:
Create a connection in DV for Essbase OAC. The cube you create will be stored in this instance. The DSN format is http(s)://<host_name>/Essbase/agent
Import your dataset in DV. This sample dataset is from the Essbase OAC gallery.
Create a data flow in DV.
Add a step (+) to use “Select Columns.” In this, select the list of fields to be included in the cube. Here I removed fixed and variable costs.
Add a step (+) to use “Aggregate.” Specify any aggregation rule, do the renaming, and/or choose the group by fields.
Lastly Add a step (+), and select Create Essbase Cube. In the “Create Essbase Cube” pane, specify the values for creating the cube such as “Choose a Connection” and an application name.
If no configuration is made, and if you click on “Configure,” this will show you the way data flows by default would look to create the cube outline. This allows you to see it treated by quarter and month as one dimension and calendar year as another.
To customize the cube, do the following:
Move the slider to enable the “Customize Cube” option.
Select the number of rows you want to analyze and click “Configure.”
Perform the following actions for each column in the “Dimensions,” “Measure,” and “Skip” sections:
Paste as Sibling
Paste as Child