Slow ASO Data Retrieves in Smart View

A client asked me last week to take a look at one of their ASO cubes and help them determine why their data retrieves in Smart View were taking so long. To set a base, I tested in Excel and it took 81 seconds for the top level of every dimension to return data when doing an ad-hoc analysis in Smart View. With UAT approaching, I knew it was not going to fly.

Wherever I could, I changed dimensions to Stored instead of Dynamic. Stored dimensions in an ASO cube are reserved for those in which no calculations or other arithmetic function will be performed on the members of an outline outside of “+” or “~”. These dimensions are simply aggregated according to the outline unlike dynamic hierarchies that include calculations or consolidation options that go against the simple outline structure. Wherever possible (!), use a stored hierarchy. If you have multiple hierarchies in a dimension and one is a simple aggregation and the other(s) is calculated, set the dimension to Multiple Hierarchies Enabled. This will allow you set the generation 2 members to either stored or dynamic.

Back to my story… Where I could, I set the dimensions to stored or multiple hierarchies enabled to take advantage of the straight aggregations afforded to me by the outline (below, this includes: Products, Years, DataType, Customer and Entity).

The next thing I did was see if there were unnecessary aggregations occurring. What do I mean by this? For example, I have never had a client that wanted to see their years rollup to a total years total. The client’s individual years was set to “+”, so I set them to non-consolidating (^). This way, Essbase was not calculating total years whenever a top level ad hoc query was being performed in Smart View. Along the same lines, if a dimension had a primary hierarchy then had alternate hierarchies, I set the dimension to multiple hierarchies enabled, set the primary hierarchy to stored, the alternate hierarchies to dynamic and set the consolidator on the alternate hierarchies to ignore (~) (see Periods). Sometimes, like in Periods, you may have alternate hierarchies that are doing calculations (like YTD, QTD). If these hierarchies are set to add, you will taxing the database by calculating all the YTD and QTD values, adding them up to the parent, then rolling up the main period hierarchy with YTD and QTD. Never needed and unnecessary.

With these changes, I tested the retrieve in Smart View and got it down to 8 seconds. A 90.1% increase in performance!

Oultine5

Outline8

Outline6

Outline5

Outline3

Outline2

Outline7

Outline4

Oultine8But 8 seconds is still unacceptable to me. The next thing I did was see if creating a default aggregation in EAS would help speed things along. To do this, I right clicked on the database name and chose “Design aggregation…”

Manual1

and clicked my way through the process.

Manual4

Manual3

Manual2

When I went back to Smart View, I was able to retrieve data at the top of the house in .55 seconds! 99.3% faster than the original 81 seconds and 92.1% faster than the outline changes time! I considered this a win and encouraged them to incorporate a script to create a default aggregation after their daily data loads.

MaxLIf they find that some of their other data retrieves are taking a long time, they can always customize the aggregations on focused sets of data, but I have a feeling the default aggregation will work just fine…

5 comments

  1. Sarah,
    In your YEARS dimension changing the members to non-aggregating ^ should have no impact since the Years member is set to label only. when that is set, it always just sets the parent equal to the first Child.
    You might also consider wherever possible with multiple hierarchies to see if the alternates can be set to store as well. Often, we have multiple hierarchies enabled so we can have shared member rollups. In order to do this, minor changes might be necessary to your hierarchy so shared members only appear once per rollup(not all grouped under a single alternate rollups category). I believe when we set multiple hierarchies enabled, the dimension is set to label only so it only picks up the first child rollup (which is required to be a stored hierarchy).
    While you did not have to do it, another optimization is to move formulaic members under a parent that does not consolidate so formulas are only triggered when selected and not when you are trying to get totals.
    Finally, On your materialization of aggregation, by default, Essbase does not take into affect alternate rollups or attribute dimensions. If you run the materialization in MaxL you can tell it to include the alternate rollups (and attributes) The syntax is:
    execute aggregate_process on xxxxx.yyyy enable alternate_rollups.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s