Essbase calculation engine - Black magic demystified – Part 2

Introduction

I assume all of you read part 1 of this blog and found it to be useful. Here is the link

https://www.essbaselabs.com/2019/08/essbase-calculation-engine-black-magic.html

When Calculation trace was presented in KScope19, one of the questions we got was whether it can tell when the block does not exist and when we need to use @CREATEBLOCK. In this post, I want to just address that question. For getting continuity I want to use the model from my previous blog. It will make the example a bit artificial, but will still show the feature working well.

Extend the Model

In this model I only had one sparse dimension, so in order to create an example where CREATEBLOCK is needed, I added another product P1-a5 as child of P1.

Now I want to write a calculation script to copy the half of shipped units from P1-a1 to P1-a5 just to get an artificial shipped percentage created for FY19. This is so that I can forecast for FY2020 based on this. I will have to recalculate shipped percentage as well before rerunning forecast calculation. At least that is the plan. But let us get started with assigning shipped units of P1-a1 to P1-a5 to get started.

Running my new script

Ok, I ran my new script and I still had my calculation trace ON. So I decided to make sure P1-a5 is being copied correctly. I selected that cell from smart view and then click Essbase and then calculate and run fill_P1a5, which is the calculation script I created earlier. I was expecting to see how the number was computed in the script using the formula and line I wrote and the numbers being copied over. But instead I found something else. 

 It is telling me that the cell was not modified and the reason is that the block containing the cell that I was planning to write did not exist. If you are familiar with Essbase you would know that dense formulas only works on existing blocks. This is so that we avoid creating entire database. Since P1-a5 blocks were not created since I made changes to the outline, those blocks did not exist and hence the formula on ShippedUnits in the calculation script did not write anything to this cell.

In this case we need to make sure the blocks are created before executing this formula. One way to do that is walk through all the blocks in P1-a1 and then use @CREATEBLOCK to create the corresponding P1-a5 blocks. This way P1-a5 blocks will be created only for the cases where corresponding P1-a1 blocks exist. Ok let me try that. Let us see how the script looks like now.

My modified script

Let me try this and see what the tracing says and what actually happens.

Wow! It’s telling me lot of things. In the first pass its saying that the cell’s previous value is “None”. Note that this is different from the new value, which is #Mi. It then shows the line on which this block was created. Then it shows that in the next pass the values are now being assigned using which formula.  Please note one more point, it says “Cell update count: 2” for the second pass. This is telling us that the cell was modified twice during this calculation. You can use this sometimes to understand performance problems or inefficiencies in your calculation scripts. If the same cell is getting modified several times in the same script, perhaps most of the modifications except the last one is not needed and we can use this to understand how to get rid of them.

Also let us look at the results.

Yes, the values are now written to P1-a5. Great!

Conclusion

CALCTRACE works well to indicate whether the lack of block existence is the reason why certain values are not written. It educates much more and gives lot of insights. Please keep trying and let us know what more you like to see from this feature.

Essbase calculation engine - Black magic demystified

Introduction

In Essbase user’s world how a number is computed by the engine was a mystery all these years. In some simple to moderate cases it was possible to understand how and why a number was derived that way, but the more complicated the model the harder it was to understand what it is doing. This is not so surprising considering the nature of multi-dimensional modeling. Database designers used to wonder why is this number this instead of what they thought it would be. Sometimes they used to get surprised to see that nothing was calculated at all and it is returning #Missing. So every one used to call this “black magic” and did experiments to derive certain behaviors and blogged about it. The correct modeling was completed through trial and error or you can say try and cry if you want to be funny.

The Essbase team understood this and wanted to change this. We want to make this easy for the user. We introduced this feature called calculation tracing that will help you understand how a particular number was derived or why it was not calculated at all and is still #Missing.

Let me walk you through how you can use this. I am going to represent our model through our new innovation called the DBX workbook.

The Model

Let us say you have model with three dimensions Calendar, Product and Measures. They are shown below.

Now I loaded some data into ShippedUnits for products P1-a1, P1-a2,  P1-a3, P1-a4 for FY19 months (FY19-Jan, FY19-Feb so on and so forth). Similarly for P2-bx combinations and P3-cx combinations.

I also loaded ParentForecastUnits for P1-Clone, P2-Clone, P3-Clone for both FY19 months and FY20 months.

The calculation scrips

Now I wrote two calculation scripts one called shipped_percent.csc

And the next one called forecast.csc

Test and validate the numbers

Now I went to smart view and created the following query sheet. Note “-“ are all #Missing in these pictures.

Examine the query sheet. Products are listed in column A. P1-a1 and P1-clone are products. As you can see if you look at the Dim.Products sheet, both are children of P1, a product category.

Measures are named in column B of the query sheet. You can see details about them on the Dim.Measures sheet; for example, notice that ShippedPercentage and ParentForecastUnits do not consolidate.

Columns C:G on the query sheet are for fiscal year 2019, and columns H:L are for fiscal year 2020. In this exercise, assume we are in FY2019, trying to forecast for FY2020.

In C2:E2, data exists for units shipped in the first quarter of FY2019. Based on those initial numbers, we’ll do two calculations:

 a) C3:E3: the percentage of total shipped units (C10:E10) that is attributable to P1-a1 (for example, we expect that C3=C2/C10, or about 40%)

 b) H5:J5 -- a shipped-units forecast for the same quarter a year later. The allocations for FY2020 are based on the Shipped Percentage for the same product in FY2019. For example, we expect that H5=H8*C3, or about 18,261

Now Go to the Essbase ribbon, click Calculate, and run the shipped_percent calculation. Then run the forecast calculation also. Refresh the sheet. I am surprised. Cell C3 is not what I thought it would be. Its #Missing.

CALCTRACE to the rescue

Alright, let me see how I can use Calculation tracing to get some help. I went to the application configuration, and added a configuration setting CALCTRACE TRUE. Restarted the application.

Then came back to query sheet and reconnected. Now I kept my mouse on cell C3 and ran shipped_precent calculation. Now the output shows me what happened.

Essbase displays the calculation tracing information, which shows you that cell C3 was not affected by the calculation. This usually means the cell is not in the calculation’s scope. Errors pertaining to scope often mean that you need to debug a FIX or IF statement.

I looked at my shipped_percent.csc. Ah! I got it. The error is in the IF statement. For a historical allocation for Jan-2019 to be in scope, the IF statement should reference 2019, not 2020. Correct, validate, and save the calculation.

Re-run the shipped percentage calculation, from cell C3, to compute C3:E3. Tracing information displays, showing you the effect the calculation had on C3. Refresh the sheet. The numbers should change for C3:E3 (C3 is approximately 0.399 or about 40%).

Execute the forecast script to get the right numbers for H5:J5 (Hint: H5 should be about 18,261).

To turn off calculation tracing when you don’t need it anymore, return to Essbase in your web browser. Select the application, launch the inspector, and click Configuration. Double click the ON value next to the CALCTRACE property, change it to OFF, and click Apply and Restart.

Conclusion

As you can see Calculation trace clearly helps with understanding a lot more about what is happening inside the Essbase engine.

Currently CALCTRACE should only be reliably used in application development phase. Meaning only one user should be running calculations on this database while you have this enabled for this to reliably show you what happened.

If you liked it, let us know. If you want to see more capabilities from this feature, let us know as well. 

Announcing Layouts, Reports and Impersonation in OAC - Essbase

In this post, I am going to explain what are the layouts, reports in the Essbase Analyze UI and how that will help the users to view and export the data in few clicks.

This feature is only introduced in the web UI but soon will be part of the SmartView as well.

Layouts
Layouts are nothing but the saved grids or bookmarked grids. SmartView is great and it allows to zoom in, zoom out and type to view the data required. When we work on the same or few grids again and again, every time launching SmartView and navigating by clicking ad-hoc buttons does not make any sense. It not only waste users time but also waste the resources on server to compute various operations.

Layouts are introduced to address this problem and save the grid as we go and navigate so that can be viewed anytime.

Reports
Reports are nothing but saved MDX queries but will be extended to data source queries as well. It would be interesting to join the MDX query with any relational or files data and view that to get what we need. For now, you can type the MDX queries and save them and run anytime. You can export the result into various formats as well like Excel, CSV, HTML and JSON.

You can run these reports as other user too just to make sure security filters are applied correctly on that user. You have to be system administrator to use impersonation.

Lets dive and see them in details:
1. Click on Analyze data on applications page - > Cube actions
2. You see the default grid.
3. Do some operations and close the browser tab.
4. repeat 1
5. Now you see the report you built in step #3. Its called session layout so system is smart enough to save your last navigated stuff.

If you are a DB manager, you can mark the layout as database default. Once you mark it, every user will see the same on launch unless they have their own default layout.

So algorithm is simple:
1. Looks for my default
2. if not #1, Looks for DB default
3. if not #2, Looks for Session
4. if not #3, Default grid

Here are the few screens:

All the REST apis can be impersonated provided that login user is System administrator.
Just add the X-Essbase-LoginAs header in REST call.

Sandbox and Scenario Workflow in Essbase for 1000's of users

In Essbase, one can always create a new dimension and clone the data and give to different users. This bloats the cube and doesnt scale to large number of users.  One needs to introduce strict limits on number of members in that sandbox/version/scenarion dimension. Also, since it is a metadata, business users cannot create new sandbox members.

We introduced new sandboxing and scenario management capabilities in Essbase in cloud. This is light weight and highly scalable and provides necessary isolation like Excel on your laptop for users. Moreover, end users can create new scenarios without involving admin.

Please watch the video for more details:

Essbase and light weight sandbox

Thanks
Kumar

Announcing Multi-Cell, Multi-Region Drill through

As we have seen in earlier blog posts, how easy it is to create a drill through reports in OAC - Essbase. If you have not read the earlier post yet then read it here.

In this post I am just focusing on multi-cell/multi region drill-through reports. Don't worry, there is no change required on your earlier reports. They will just keep working with single as well as multi selections.

Requirement:
You need latest SmartView for trying this out. If you need time to update SmartView on all your user machines then you can keep old SmartView and single cell based drill through will keep working as earlier.

Make sure that your instance is OAC 5.2 or later.

What is Multi-Cell :
Multi-Cell is when you select continuous cells in the Excel like B3, B4

What is Multi-Region:
Multi-Region is when you select multiple non continuous multi-cells. :)
In other words, when you select any number of regions(use ctrl+select to select multiple non-continuous ) you want in Excel.
e.g. It's very common for us to see the detailed report for all the months till September  excluding quarters like below.

Now go ahead and select such cells where drill through reports are defined and run drill through.

What should you expect when you click on drill through toolbar button:

Listing:
When you select multiple cells or regions, and there are multiple drill through reports, intersections of those reports are shown.
e.g. If you select A1, A2 and A3  and here is the cell to reports mapping
A1: R1
A2: R1, R3, R2
A3:  R1, R3, R4

then only R1 is common so it will be executed directly.

If you select only A2 and A3 then both R1 and R3 are listed. You can select one and then execute it.

Same will happen with multi region. Report(s) should be available for all the cells you selected.

Execution:
For Multi-Cell:
Think of it as running a SQL like select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
Where r1,r2 and r3 are the report columns and f1 and f2 are mapped columns for A and B dimension.

For single cell the query was like:
select r1,r2,r3 where f1=a1 and f2=b1

So only change is the replacement of equal operation with IN operation.

If you want sorted data etc then its better to define the data source query itself with sorting or use excel sorting once report is landed.

For Multi-Region:
Now think of it as running multiple SQL statements in parallel (one per region).
select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
+
select r1,r2,r3 where f1 in (i1,i2,i3) and f2 in (j1,j2,j3)
+
select r1,r2,r3 where f1 in (x1,x2,x3) and f2 in (y1,y2,y3)

As these queries are run in parallel and appended together in the result to improve the performance, data will remain unsorted. You can sort it in the Excel.

The combination of recursive, multi cell and multi region drill through is so fascinating! 

Try it out!

Autonomous Database support in Essbase

Happy New Year 2019!

In my earlier post, we learnt to use existing CLI to connect to ADW and ATP. This was just a workaround and new OAC-Essbase CLI does not need it anymore.

However the workaround was only for CLI and there was no support through UI and REST.
From OAC 5.2, Essbase CLI, UI and REST support to create connection to autonomous database(Autonomous data warehouse and transaction processing).

This post will demonstrate to create a connection to ADW and that connection can then be used to data load, build dimension, drill through or any other use cases using Essbase data sources abstraction.

Step 1. Download the wallet ZIP from your ADW/ATP instance.

Step 2. If you need any proxy settings to connect to the database then extract the zip, change/add proxy details in the tnsnames.ora. This is specified in the post I mentioned at the start of this post.

Step 3: Create an Oracle Database - Autonomous Connection like below:

Now create a data source and use it to load data, build a dimension, drill through ....

Rest :

To upload wallet file for a connection to be created called myconn:
curl "http://host/essbase/rest/v1/connections/myconn/wallet" -X PUT
-H "Content-type: application/octet-stream"
 --data <Wallet file path>

To create:
curl "http://host/essbase/rest/v1/connections" -H "Content-Type: application/json"
--data "{""name"":""myconn"",""description"":"""",""type"":""DB"",""service"":""essbaseadw"",""walletPath"":""/system/wallets/myconn"",""user"":""ADMIN"",""password"":""password""}"