New Sparsity Advisor within AWM 10.2.0.3

Mark Rittman

Dec 15, 2006 • 3 min read

I mentioned the other day that a new version of AWM was now available following the release of the 10.2.0.3 patch for Oracle Database 10g. One of the new features that this latest version of AWM comes with is the Sparsity Advisor.

I covered the PL/SQL Sparsity Advisor earlier in the year, but at the time it was fairly tricky to run and now the functionality has been incorporated directly into Analytic Workspace Manager. What both these things do - AWM is just a GUI front-end for the PL/SQL version - is works out for you which of your dimensions are sparse, which are dense, and it recommends the order that you should list the dimensions, something that can have a fairly significant influence on the time it takes to load and query your cube.

To take an example, when you create a cube, AWM defaults to making the time dimension dense, and the other dimensions sparse.

This is a reasonable default but in some circumstances, this might not actually be correct - the global cube being a good example, where there are in fact only four channel dimension members and all the data is for one of these four channel values.

Looking at the Implementation Details tab, note how compression is pre-selected for you now, reflecting the fact that compression is now suitable for almost all cubes, and even if there's no benefit, there's virtually no downside either, hence Oracle's removal of the previous warning to only select it "for very sparse cubes".

The Sparsity Advisor is accessed by right-clicking on the cube, and selecting it from a menu.

You need to make sure you've done all your dimension and cube mappings beforehand, as the advisor goes to the source data and works out, based on your actual data, what the best sparsity settings actually are. To run the advisor, you just select it from the menu, let it run its routines, and view the output.

Which of course matches the output from when we ran the advisor manually.

A couple of points I noted. It's nice that it gives a dimension order as well, but of course when choosing this you need to bear in mind the balance between loading and querying - having time as the second-most fastest varying dimension is good for time-series queries, but might not be so smart when it comes to loading a new period's worth of data. Also, if you're putting all the data into a composite, the order is less of an issue anyway as you're actually dimensioning the underlying variables by the time dimension and the composite.

The other thing I noticed was that it had recommended partitioning, but by the top-most level of the time dimension, i.e. it'd create just one partition. Now as partitioning is mainly there to allow you to assign more than one CPU to the aggregation process, and to allow you to selectively aggregate just the partitions that receive new data, I can't really see the point of partitioning on this level - I'd have gone for month, or more likely, quarter, a good balance between improving load times and not having too much data in the topmost partition that I can't then pre-summarise. I wonder if this feature has been completed yet?

Anyway, the Sparsity Advisor is a pretty useful feature, and to implement the recommendations, you just hit the "Recreate Cube" button - AWM needs to rebuild the underlying variables and composite which means dropping your measures and recreating them, not something you'd do each week - and let AWM do the rest. Not bad.

If I get a chance, I'll try and cover the new forecasting and allocation features in a later blog.

Sign up for more like this.