Tracking Intelligent Cube Growth
One of the first concerns you may have after scheduling some Intelligent Cubes to update is their growth rate. Each cube is not only loaded into memory, but also saved to disk, so managing their growth rate will have real impacts on system performance and stability. If you haven’t made use of the 9.2 feature to roll off old data, then each time you add data, the cube will be growing. There are also other factors that can cause a cube to grow in size, including Cube Indexes, Stored Calculations and Cube Info. Keeping an eye on all of these are critical, and today I’ll show you how to build some simple reports in Enterprise Manager to manage it all.
As of 9.0.2, the Intelligent Cube management reports provided in Enterprise Manager are pretty poor. The biggest problem I see is that the Cube sizes are stored in the Report_Fact table, meaning they’re stored by instance created and there doesn’t seem to be a place where you can find what’s currently loaded. The out of the box reports use an Avg metric, which I don’t think is a very accurate representation. Ironically, there is a metric called RP Last Intelligent Cube Size (KB) which isn’t used on any reports, and that’s the metric I’m interested in. Since it’s coming from the Report_Fact table (as indicated by the RP prefix for “Report Processing”), it needs to use the Last() function (also indicated in the name) because if you load the cube more than once in a day, you still only want to count the last load as the “current” size.
Building the Report
The report I chose to build lists all of the cubes in my environment and then shows the current size and the 7 days ago size, along with a % change. This can be done by using the RP Last Intelligent Cube Size (KB) metric as well as making a copy with the Today – 7 Days filter. I also prefer a derived metric that does a /1024 to convert the KB to MB. The report filters should be Today (assuming you’re running this report after the daily Cubes are loaded, otherwise you may want to use Yesterday) as well as whatever your target project is (Production more than likely), or add Project to your template if you want to track Cubes across multiple projects. You may want to group them all together, but be wary of counting cubes from Development, which you may not want to do since it will throw off your growth measurements. Finally, add a derived metric to divide the current by last week, and you’ve got a report that will measure the cube growth.
Personally, I subscribe to this report daily to keep an eye on things. From this report, I’ve found that my 19 cubes grow at a rate of 2.5% per week which I’ve projected out to approximately 1gb per year. I’m currently at 1.8gb total on a 64gb machine, so I’ve got nothing to worry about for a long time. It’s also a good tool to keep an eye out to make sure that the Cubes are functioning properly. If I see a huge increase or decrease in Cube size, then I know there might have been a data problem and I can investigate before user’s start to question the data.
Update: New Statistics in 9.2