How to Create Essbase Cubes with Data Flows in Oracle Analytics Cloud (OAC) 105.2
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
This cube was customized to move quarter and month under the “Calendar Year” dimension using the above copy and paste options.
Save the data flow and run it. Once the data flow is running successfully the cube will be available in the Essbase instance.
The cube outline can be viewed from the “Actions” menu specific to the cube.
This cube is now readily available for reporting in Smart View, as well as in Data Visualization in OAC.
The “Create Cube” feature is only supported for OAC Essbase Cloud. On-premises Essbase does not support this yet, nor does it offer the REST API needed to create a cube using data flows. Data flows only support Essbase 184.108.40.206.1xx version (the cloud version).
This way of creating an Essbase cube is really slick. You don’t need to be an Essbase expert to create these cubes. This is a big add to OAC and a quickest and easiest way to aggregate data from a huge fact table or a dataset with millions of rows.
Need help determining how to provision your instances to make the best use of the changes? Drop us a line below or at firstname.lastname@example.org, and we can figure out a way to chat. Looking for more information on the other functionality that was released as part of the 105.2 updates to OAC? Stay tuned to this blog for additional updates.