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!
But 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…”
and clicked my way through the process.
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.
If 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…