Essbase Calc Syntax to SQL Comparison

Many folks have a bit of trouble getting their arms around the Essbase Block Storage Calculation Script syntax – especially if they have spent time previously working in relational databases. Essbase BSO calculation syntax looks nothing like structured query language.

In the Essbase Bootcamp and the Essbase Calculations for Block Storage Databases classes, I will sometimes walk through a comparison of the two languages’ syntax for a very simple calculation.

To keep this explanation short, I am only comparing a single update operation. There could easily be followup on this to describe the process of making this much more dynamic to calculate the measure for all customer-product combinations.

Using the Bootcamp class case study as an example, we have a requirement to calculate the Unit Mix by Channel account for Bigcorp, a fictitious company that manufactures and sells hard drives. The measure is calculated as the number of units sold to a company as a percentage of the total units sold to that company’s sales channel. Essentially, “units of one product sold to one company” / “units sold of the same product to all companies in the same channel”.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

To keep this explanation short, I am only comparing a single update operation. There could easily be followup on this to describe the process of making this much more dynamic to calculate the measure for all customer-product combinations.

The calculated data set will look like this in a flat table:

A SQL update statement to populate one row of fully de-normalized table would be:

UPDATE [Fact]
 SET [Fact].[Unit Mix by Channel] = [Fact].[Units] /
 (SELECT [Fact].[Units] FROM [Fact]
 WHERE [Fact].[Scenario] = 'Actual'
 AND [Fact].[Customer] = 'OEM'
 AND [Fact].[Product] = 'Lightbolt 365 A'
 AND [Fact].[Year Tot] = 'Jan')
 WHERE [Fact].[Scenario] = 'Actual'
 AND [Fact].[Customer] = 'O-IBM'
 AND [Fact].[Product] = 'Lightbolt 365 A'
 AND [Fact].[Year Tot] = 'Jan'

Compare this to a line in an Essbase calculation script.  In this example, I have fully qualified both sides of the assignment operator (=) with member references from all five dimensions in the cube: Scenario, Customer, Product, Period, and Account.  In the above SQL, I had to use a subquery to look up the denominator in order to determine my ratio.  Below is a simple calculation script line that accomplishes the same task using explicit references to members from each dimension similar to the WHERE clause in the SQL above.

"Actual"->"O-IBM"->"Lightbolt 365 A"->"Jan"->"Unit Mix by Channel" = "Actual"->"O-IBM"->"Lightbolt 365 A"->"Jan"->"Units" / "Actual"->"OEM"->"Lightbolt 365 A"->"Jan"->"Units"

In this second example, I am using a FIX statement to eliminate the need to fully qualify the dimension references in the subsequent statement:

FIX ("Actual", "O-IBM", "Lightbolt 365 A", "Jan")

     "Unit Mix by Channel" = "Units" / "OEM"->"Units"

ENDFIX

There you have it.  A very simple statement comparison between SQL and Essbase Calculation Script syntax.

Have a nice day.