Using Ramdisk with ASO

I wanted to see what Essbase ASO performance would be like if not bound by disk. So I thought I'd start out by creating a ramdisk and storing my ess*dat files on it. First of all, ramdisk is just a file system that you create using RAM rather than a hard drive. So it's fast -- turbo-chicken-fast. If you understand what RAM is then you know that when the power is turned off on a machine you lose everything in RAM. Luckily machines and power and such are much more reliable these days so it's not a huge deal to use. I wouldn't, however, recommend going and creating a whole bunch of ramdisk in production and putting all of your cubes on it. You've got to have a good design for it and I'm not in that line of business so I won't get into all of that here.

The Disclaimer

I ran a few very basic tests using ASOsamp. I understand that not all hardware is created equal so my results might not match your results. This test serves as one point of data that will hopefully further our knowledge of how Essbase behaves in different environments. This test was run on 11.1.2.4.002.

The Digression

I run Essbase on Linux. It's very easy to create a ramdisk on Linux. I used the following command:

mount -t tmpfs -o size=1024m tmpfs /mnt/ramdisk

In order to have the database use the ramdisk, I changed the ASOsamp.Sample storage settings in EAS to point the default and temp tablespaces over to the /mnt/ramdisk directory.

The Test

My test data was generated using dbfill. The data I loaded filled the ess00001.dat file to 285MB. I captured the load times. I then ran queries in Excel using the default retrieve, zoom-in on products to bottom level, pivot products to columns, zoom-in on stores to bottom level. I then created aggregate views based upon the Essbase recommendations. I then ran the same queries again. Note: I did not use a compression dimension in the outline.

The Results

The Analysis

These results make a lot of sense to me and illustrate just how cool Aggregate Views are. Here are my thoughts on some of the results:

The Data Load isn't much different because Data Load seems to involve a high use of CPU. Anywhere that Essbase is CPU bound, the ramdisk isn't going help very much. I assume that when Essbase is writing the data that it's also doing a lot of checking to see that members have the correct names and exist in the outline and such.

The buffer commit is much faster under ramdisk because this seems to be a heavy disk use operation. The data is initially written to the /temp directory and then moved over to the /default directory. There probably isn't a lot of data validation going on during this step.

The queries without aggregations are virtually identical. When I watch server utilization during a long running Essbase query it's almost always CPU bound -- the ESSSVR process pegs a CPU at 100% most times. So this makes a lot of sense to me. Having the data file in memory doesn't help Essbase much since it's still got to do a lot of calculating.

The default agg is quicker under ramdisk as it must rely fairly heavily upon reads and writes. Again, it needs to write the Aggregate Views to the /temp directory and then move them over to the /default directory.

The last four queries are where the magic happens. Our query times with the views created effectively drop to instantaneous -- and you don't need super fast disk for this to happen. Aggregate Views are so cool! It only takes 12.55 seconds to create 13 views but that actually saves over 24 seconds on the Store dimension zoom-in. This shows how powerful Aggregate Views can be. We will really need the ability to create these in Hybrid as soon as possible.

The Conclusions

Super fast disk is great to have but it really only makes sense when you have disk bound operations. Costs and benefits need to be weighed as in most design and purchase decisions. The results might be different as the ASO size scales up so I'd encourage others to try to replicate this test with a larger data set. I need to get more RAM on my home server before I can do that.

The Question of the Day

Smart View or Excel Add-In?

CALCPARALLEL with ASO

If you perform aggregations on your ASO cubes and you have enough resources on your server to allocate more than one processor then, by all means, change the CALCPARALLEL setting in your essbase.cfg file to a number higher than the default (2). Be sure to heed the recommendation to increase your ASO cache as each thread you allocate to the aggregation requires its own amount of cache.

If you were to go by the DBA Guidelines for cache (32MB of cache for each 2GB of input data) you would need 64MB of cache if you wanted to use the default number of threads when aggregating a 2GB ASO database.

Let's see how this translates to the real world.

I have an ASO cube with 9,999,840 KB of input level data. That should require, well, let's do the math:

First figure out how many times larger my input data is than 2GB: 9,999,840/2,097,152 = 4.768...

Now just take the square root of 4.768 = 2.183...

Now multiply that by 32MB = 69.876...

Hang on, if you read further down the page you see:

To improve the performance of building aggregates, take the following steps.

1. Increase the size of the aggregate storage cache to at least 512 MB or 20% of the input data size, whichever is smaller.

All that math for nothing. So I should set it to 512MB or 1,999MB (20% of my input size), whichever is smaller. That's easy. 512MB < 1,999MB.

<scratching head>So do I need to allocate 512MB per thread or is that in total? My guess is that's in total.

Once again, Let's see how this translates to the real world.

Let's just test it out and see the performance based upon a bunch of different settings. My CALCPARALLEL is set to 16 in this case.

Here's the MaxL script I'll use:

1:  alter database TEST.TEST clear aggregates;  
2:  alter application TEST set cache_size 32MB;  
3:  alter system unload application TEST;  
4:  execute aggregate process on database TEST.TEST stopping when total_size exceeds 1.1;  

The Results

The table shows the cache size and respective aggregation process time. Each time 13 views were created. As you can see on the graph, there is a diminishing return after about 164 MB. That's well below the recommended 20% or 512MB guideline.

Cache Size (MB)	Aggregation Process Time (s)
32	753.78
64	383.53
70	334.61
100	283.98
128	257.95
164	241.38
200	236.79
256	233.31
512	234.8
1024	235.01
2000	228.21

Graph: Time in seconds is on the y-axis, cache size in MB is on the x-axis.

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.

Exporting data using MDX Part 2 -- Totals

I feel like adoption of MDX within Essbase is limited because 1) the output format is messy and 2) the documentation is challenging to following and understand. I couldn't find any examples in the Tech Ref for adding column totals to a query (let me know if you find any) so I'll try to address that here.

The following query selects the children of Qtr1 in Sample Basic and totals them in the final column. The difference here is that we're using MDX to do the aggregation instead of Essbase. It might not make sense to do that when you already have Essbase giving you the total but there could be a case where you need a custom rollup not available in your cube.

SET

1:  WITH  
2:  SET [_TimeSet] as '[Qtr1].CHILDREN'  
3:  MEMBER [Year].[_TimeTotal] AS 'AGGREGATE([_TimeSet])'  
4:  SELECT  
5:  {[_TimeSet],[Year].[_TimeTotal]} on COLUMNS,  
6:  {[100].Children} ON ROWS  
7:  FROM Sample.Basic;  

Line 2 creates a SET called _TimeSet. I start it with an underscore so I can easily tell that it's not a member within the Sample.Basic outline.

Line 3 then creates a calculated member called _TimeTotal which is an aggregate of _TimeSet.

Line 5 places the contents of _TimeSet and _TimeTotal onto the columns.

The beauty of the SET command is that if I ever need to change the columns in this extract, all I need to do is change line 2. The other lines automatically inherit the changes. It might not seem like a big deal on a seven line MDX statement, but just imagine one that's 50 lines long.

SUM()

While reading the documentation of the Sum() function I came upon what I think is an awful example. It looks like the developer wanted to print a report with the major markets and a sum of them at the bottom. They use the UDA to pick them out in the report but take the time to write out the individual states that are major markets. Why use UDAs if you're going to type out all of the members with that UDA? You'd have to update your report if any of the UDAs changed. <wagging finger> Also, the total doesn't add up all of the lines but only the lines that are states. Let's see if we can improve on this report.

We need the Level 0 members that are major markets. We can create that set with the following statement:

Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members)

The Intersect() function selects the, ahem, intersection of two lists -- what they have in common. In this case the list of members with a "Major Market" UDA and the list of members that are at Level 0.

We can either choose to keep the Market rollups:

1:  WITH MEMBER [Market].[Sum Expense for Main States]  
2:  AS  
3:  'Sum (Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members))'  
4:  SELECT  
5:  {[Measures].[Total Expenses]}  
6:  ON COLUMNS,  
7:  {UDA([Market], "Major Market"),  
8:  [Market].[Sum Expense for Main States]}  
9:  ON ROWS  
10:  FROM  
11:  Sample.Basic  
12:  WHERE ([Scenario].[Actual])  

Lines 1-3 define a member that will sum the intersection of those members with a Major Market UDA and those members at Level 0.

Line 7 lists the members with a Major Market UDA on the rows and line 8 prints the Sum that we created in lines 1-3.

Or we can print the report with only the states:

1:  WITH MEMBER [Market].[Sum Expense for Main States]  
2:  AS  
3:  'Sum (Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members))'  
4:  SELECT  
5:  {[Measures].[Total Expenses]}  
6:  ON COLUMNS,  
7:  {Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members),  
8:  [Market].[Sum Expense for Main States]}  
9:  ON ROWS  
10:  FROM  
11:  Sample.Basic  
12:  WHERE ([Scenario].[Actual])  

The difference between this report and the last is that now we omit the Major Market members with UDA that are above Level 0. Now the column of figures actually adds up to the sum at the bottom of the report.

If we need to use the same set multiple times within the same query we can write it using the SET command:

1:  WITH  
2:  SET [_MajorMarketSet] as 'Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members)'  
3:  MEMBER [Market].[Sum Expense for Main States]  
4:  AS  
5:  'Sum ([_MajorMarketSet])'  
6:  SELECT  
7:  {[Measures].[Total Expenses]}  
8:  ON COLUMNS,  
9:  {[_MajorMarketSet],  
10:  [Market].[Sum Expense for Main States]}  
11:  ON ROWS  
12:  FROM  
13:  Sample.Basic  
14:  WHERE ([Scenario].[Actual])  

This is essentially the same report as the prior one but now we've employed the SET command. If we ever need to update the list of members in this extract, we just update line 2. Line 5 inherits the change as does line 9. In my opinion, a much cleaner way of writing the MDX.