Essbase 19C: Spreadsheet to Cube in 60 Seconds

Published October 28 2020
Back to insights

7 Tips for Using SmartView Cube Designer’s Transform Data Feature to Build Richer Analytics at Nearly the Speed of Thought

Has anyone ever sent you a large spreadsheet dump full of great data that you needed to sum, pivot, crosstab, and report enrichments on such as YTD, and you had 30 mins or less to do it? SmartView Cube Designer can do it! It can be your “Easy” button.

This article will explain the foundation of how to spin up a cube quickly and impart some experience that will increase the trajectory of your learning curve.  

The Source

Imagine a significantly large spreadsheet lands in your inbox.   You need to quickly twist and turn the rows, columns, and summarize them for a pressing management meeting.  

Maybe you need all of them. Maybe you only need a subset. No matter. After a few small changes, you can punch an “Easy” button and spin this data into a cube much faster than you can subtotal and pivot in Excel.

And once it’s a cube, you can share it, chart it, analyze, sum it, sandbox it with scenarios, or easily delete it and move on.

In our example, we will give you some key tips on how to take a large subset of data as shown below:

To this:

This summarized workbook took less than 60 seconds for 10,000 rows of data including, creating the cube, loading the data, and aggregation! No kidding. It is that easy!

Quick Build (no hierarchies)

Intelligence is built into the Transform Data feature of SmartView Cube Builder.   Like anything else, there are limits to what we can expect from technology. The Transform Data feature will respond to some gentle nudges from humans.

Tip 1:   Name Dimensions in Rows and Columns:

Note the headers on above the columns below. Transform Data name dimensions if a name is not provided in the format, as shown below (Region.Region).   Region.Region is the dimension and first eneration that AsiaPacific would roll-up to. The same goes for Client.Client.

Transform Data mandates a Measures dimension in its build process. The measures dimension is flat.   The tool chose periods as measures because periods are in columns of our example worksheet. For now, we will accept the tool’s suggestion keeping in mind a hierarchy is generally desirable in Periods, such as Quarters or even YTD functionality.

 

After clicking on the Transform Data option from the Smart View Cube Designer Ribbon, a prompt will appear for an application name and cube name.

Tip 2: From here, it is possible to right-click rename dimensions in the Transform Data Wizard before the 7App is deployed. It’s also possible to rename the dimension and update the dimensions via Cube Designer or the web after the app is deployed.

Tip 3:   Determine if procedural calculations will run against this cube in the future. If so, choose a hybrid cube from the options menu.   Otherwise, stick to the default of ASO for a simple aggregation.

Tip 4: If the spreadsheet being used as a source has many rows and columns, determine if it is possible to convert any of them to attributes. The selection for doing so is on the Options menu as well.

After we accept all changes, the build process will begin. The jobs log will let us know if our cube deployment with Transform Data was successful.   If everything is good, we can add hierarchies and modify dimensions to our liking in Essbase 19c web Interface, or we can export the cube to the spreadsheet and make further updates using a cube builder.

From here, we should see a cube with flat hierarchies as follows:

Quick Build (With Hierarchies)

Let’s enhance our cube deployment by adding hierarchies on the front end instead of on the backend.   With just a touch more effort on the front end, it is possible to build our hierarchies from our source spreadsheet.

Tip 5: Add columns with hierarchies to our source spreadsheet prior to deploying the cube.

In the example source sheet below, we added a few measures (Units, COGS, Revenue) to columns. We pivoted months to rows in order to show how easy it is to had hierarchies.  

Two columns have been added to the source Worksheet for the Generations of the Period dimension. This prompts the Transform Data feature to understand the intended hierarchy for a Periods dimension.

  • Gen2 - YearTotal is rolling up to Gen1 Period
  • Quarters- Quarters are rolling up to YearTotal
  • Periods – Note that we are naming Gen3 “Periods” plural as it must be unique from the dimension name

After the Transform Data process clicking the preview option will display how the target cube will look after deployment. Our hierarchies are visible.   Quarters and Periods are named Generations.

Tip 6:   if by chance our generations are not renamed, right-click on the generation from the preview and select rename:

In this right-click menu, we can also assign the cotent of worksheet columns as attributes, aliases, or UDAs.

After the cube is deployed, a Periods dimension with Hierarchies will be available. By checking Database options, we will also see that our generations in the Period Hierarchy are named. This is a great feature that aids significantly in the use of tools such as Data Visualizer. (Bonus tip)

Tip 7:   Sort the hierarchies in the source sheet before deployment.

As great as the Transform Data feature is, it needs help with Member Sort Order. Note below that the Quarters and Periods in the deployed cube appear in exactly the same order they appeared in the source Worksheet.  

With just an ounce more prep time, a good pre-deployment sort order for dimensions will yield the desired result.

Voila!

Nominal efforts in the Transform Data tool produce hierarchy enriched analytics and on-demand summarizations and pivots available to Oracle SmartView and Data Visualizer!

 

For comments, questions, or suggestions for future topics, please reach out to us at infosolutions@alithya.com.  Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Contact us