Cloud

# 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”.

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

##### Cris Dunn

Cris Dunn is the manager of Perficient's EPM SupportNet practice which provides direct support for applications and infrastructure surrounding many organizations' EPM software implementations. He is also an Oracle University certified instructor and teaches everything around Essbase as well as the EPM installation and configuration classes. When he is not working, you will find Cris engaged in His church serving as the treasurer, musician, nursery worker, and, when necessary, toilet scrubber.

More from this Author