ASO Allocations -- Performing a Simple Allocation

If you need to translate BSO calc scripts over to ASO procedural calcs/allocations, the Oracle documentation isn't a lot of help. There is no one for one translation table and the Execute Allocation command has so many options that it's difficult to know where to begin. It does take a while to read through and understand. If you've struggled through it before, hopefully this post will help. I'm going to take a simple BSO allocation and translate it for you, explaining things along the way.

Let's assume we have an outline that looks like the one below. This is based off of Sample.Basic, with a few minor adjustments.

Assume we had an allocation from our old Sample Basic cube which was taking last year's Marketing spend and spreading it to this year's Marking budget based upon last year's sales by market. You can see how the data looks below. In this case the $843 gets spread to the various markets in direct proportion to what the market's sales were.

The Old Way

The BSO calc script to do this could use the @ALLOCATE function as below.

1:  FIX("Marketing")  
2:     "Budget" = @ALLOCATE("PY Actual, @LEVMBRS("Market",0),"PY Actual"->"Sales",,share);  
3:  ENDFIX  

Translating to ASO

The most basic difference between a BSO Calc Script and an ASO Allocation is that the former is a file that can be executed in a number of ways: via MaxL, through Smart View, through EAS, etc. and the latter is a MaxL statement. This is the process for creating my MaxL statement.

The first thing I need to do is to start my execute statement specifying the database name.

1:  Execute Allocation Process on Database ASample.Basic with  

Next I need to define my POV. I use the CrossJoin function to define the Range the allocation will be run on. In this case all Level 0 products and all 12 months.

2:  POV "CrossJoin([Jan]:[Dec],  
3:  {Descendants([Total Products],Levels([Product],0))})"  

Now I need to set the AMOUNT I am using in the allocation. In this case it is the Marketing dollars from last year for all Markets. That can be represented using a tuple.

4:  AMOUNT "([Market],[PY Actual],[Marketing])"  

Then I will set the BASIS by which the data will be spread. In this case the PY Actuals, Sales tuple.

5:  BASIS "([PY Actual],[Sales])"  

I will define my TARGET with a tuple pulling data from the intersection of the Budget Scenario and Marketing Measure.

6:  TARGET "([Budget],[Marketing])"  

Next I will define my RANGE as a set made up of the Level 0 members under Total Markets. This is the group of members to which the data will be allocated.

7:  RANGE "{Descendants([Total Markets],Levels([Market],0))}"

Finally I will specify the Allocation method. In this case I will use SHARE. You can use SPREAD if you want to allocate the data values evenly. If you do use SPREAD then make sure to drop the BASIS (simply remove line 5 from the statement) as it doesn't make sense to use a BASIS when allocating evenly.

8:  SHARE;  

Hopefully when you execute, you'll see the following messages telling you the elapsed time and how many cells were created.

 OK/INFO - 1300006 - Essbase generated [5] cells.
 OK/INFO - 1013374 - The elapsed time of the allocation is [0.13] seconds.
 OK/INFO - 1241188 - ASO Allocation Completed on Database ['ASample'.'Basic'].

Just a Start

There are many other options available for use in more complex allocations. This example will get you started. I'll work on some posts involving more complicated allocations in the future.

ASO Restructure Performance Part 2

As I showed in Part 1, you don't have a lot of control in speeding up ASO restructures. As with BSO, the more data you have in the cube, the longer the restructure will take. One tip for faster ASO restructures is to clear aggregate data. Once the restructure is complete you'll need to aggregate the data again but you can do this while the cube is live if you need to. Keeping this in mind, I thought I'd compare the performance for a few restructure options.

1.  With Data and Aggregations.
2.  With Input Data Only.
3.  Export, Restructure, Load.

For my test model I'm using ASOsamp.Sample with 550MB of input data and when I aggregate I'm using the below command. This will give me aggregate views approximately 50% the size of the input data. To cause the restructure, I will add a member to the Measures dimension.

Execute aggregate process on database ASOsamp.Sample stopping when total_size exceeds 1.5;

Now the results (all times in seconds):

1. With input data and aggregations.

Restructure Time: 51.85s
Total Elapsed Time: 51.85s

2. With input data only.

Clear Aggregate Data Time: ~1s
Restructure Time: 26.12s
Create Aggregate Views Time: 48.71s
Total Elapsed Time: 75.83s

3. Export/Restructure/Load

Export Data Time: 43s
Clear Data Time: ~1s
Restructure Time: 1.35s
Load Data Time: 13.41s + 14.88s
Create Aggregate Views Time: 52.29s
Total Elapsed Time: 125.93s

I do find it interesting that having aggregated views that are 50% the size of the input data causes a nearly 100% increase in restructure time (26.12s vs. 51.85s). I think I need to do some more experimentation on this aspect of restructures.

In this example I wasn't able to gain better performance by restructuring with less data. I did run a few tests turning on parallel loads, calcs and exports. The results were slightly better (method 2 clocked in at about 66s) but the restructure with data and aggregations still won the day. I think there is reason to believe that with some larger input sets and a very powerful machine that method 2 could beat method 1.

Classic Design -- The Essbase Excel Add-In

What's better than a laid back Friday just before a long holiday weekend? Maybe a philosophical discussion on the nature of design.

High quality designs tend to last a long time. I'm a fan of documentaries and a couple of design related films come to mind: Helvetica and Welcome to Macintosh. These films talk about two things that have incredible designs -- one a font and the other a machine. Both have lasted quite a while and are very popular. Macintosh sprang from design work done at Xerox PARC. They essentially invented GUI but couldn't bring it to market at a low enough cost. Ideas are great but execution is the key to success.

Some designs are beautiful. I'm awestruck whenever I enter a Frank Lloyd Wright designed home. Each one I've seen speaks to me as a manifesto on quality design. Attention was paid to every detail from the view you have of the building as you approach all the way down to the lampshades and electrical sockets.

Some designs are ugly, or at least they seem ugly. Put someone in front of a Unix terminal showing the command line and they might cringe. Put me in front of a Unix terminal and I'm at ease. This operating system is older than I am and is essentially unchanged. Sure it has been improved over time, it handles 64-bit hardware now and you can put a GUI in front of it if you really need to, but at it's heart it is the same. A beautiful, long lasting design, held in high esteem by those willing to take the time to learn the power available. The speed of getting things accomplished is amazing when one doesn't need to ever lift their hands from the keyboard to fidget with a mouse.

This brings me to another lasting design -- the Essbase Excel Add-In. This interface is essentially unchanged since I began my Essbase journey. Yes, it has moved on to be called Smart View, but it uses the same principles.

I recall the first Essbase training class I ever taught. I had a lab with about 30 students in it. Essbase was completely new at this company and I'd be teaching people totally foreign to the product. I still remember the sound when people discovered that they could double left-click to zoom-in and double right-click to zoom-out. The room went nearly silent. All I could hear were hundreds of mouse clicks. There was no talking, just clicking. These people had just been given an incredible tool that would change the way they do their jobs. It would improve their productivity.

Here we are twenty years after it was designed and my users do not even want to try Smart View because it means they would have to part with their beloved, beautiful, simply designed Essbase Excel Add-In.

ASO Test Data or: How I Learned to Stop Worrying and Create Hybrid

I wanted to fill ASOSamp.Sample with some data so I could run a few tests. I wanted to use the completely undocumented "filldb" command within ESSCMDQ in order to do so. Unfortunately, it only works on BSO. So I needed to turn ASOSamp into a BSO database first. There's no way to do that, right? Wrong. A very wise Essbase developer points out in this OTN post that one need only run the Aggregate Store Partition Wizard available within EAS in order to do so.

Let's Recap:

1. Run the Aggregate Store Partition Wizard to make a BSO copy of your ASO cube. It may give you some warnings so I suggest first making a copy of that ASO cube and using the wizard on the copy. If you get warnings, go into the ASO outline and fix the issues. You'll need to remove things like solve order and other ASO specific items.
2. If you only need to turn the BSO cube into a Hybrid cube, you can use Celvin Kattookaran's utility available here that changes all upper level sparse members to dynamic calc members. Stop here if you don't need a test bed of data.
1. Don't forget to set ASODYNAMICAGGINBSO in your essbase.cfg file.
3. Download and unzip ESSCMDQ. It's probably easiest to put this file in the same place where ESSCMD or essmsh already run so you don't need to fiddle with paths, libraries and such.
4. Run ESSCMDQ. Login as you would ESSCMD (no semi-colons!):
1. login hostname user password
5. Select the database you need to fill with data:
1. select appname dbname
6. Run the filldb command. See the link for more information on this command. I would suggest starting out with a small size. I had a very small block size in my cube and it went way past the 1 GB mark even though that's what I set it to. With a normal size block it was close (within 10%) to 1 GB.
1. filldb [size density distance]
7. Export the data from the BSO cube.
8. Import the data into the ASO cube.

Now I can easily create a test bed of data for ASOSamp at whatever density I require. I'll be using this in future posts.