Intelligent Cube Statistics Bug and Fix in 9.2

There were some new statistics added to Enterprise Manager in 9.2 for tracking Intelligent Cubes.  This new table, IS_CUBE_ACTION_FACT allows you to see (among other things) the size and rows of the Cube the last time it was loaded.  While I’ve talked about building a Cube Tracking Report before, that strategy doesn’t work when you introduce Incremental Refresh.  Since Incremental Refresh only pulls back a subset of the rows which are then applied to a pre-existing cube, you can’t trust the result rows from the update process itself anymore.  This new table resolves that by giving direct Cube statistics instead of having to infer them from the Report Statistics.  Unfortunately, when trying to use these new features I ran into two bugs.  With the help of Technical Support, I was able to work around them both.  In the interest of anyone else faced with these challenges, I’ll share those steps today.

The first problem came with logging the statistics to the raw table IS_CUBE_REP_STATS.  For some reason, this table remained blank.  I found that if I created a fresh metadata it would populate correctly, but after upgrading my system from 9.0.2, it would not load.  After lots of troubleshooting with Technical Support, the MicroStrategy Engineer was able to discover a fix.  The resolution was disable all Statistics Logging by unchecking all boxes under Project Configuration -> Statistics and then restart the Intelligence Server.  Once it was back, we re-checked all boxes and restarted once again.  When the IServer returned, the table was being correctly populated!

Unfortunately the excitement was short lived, because when I went to create my first report I immediately noticed that sizes/counts were too large.  I first noticed that the metric for Intelligent Cube Size was defined as Sum(Size).  I don’t think that’s correct (at least for my purposes), because if I load a cube more than once in a day, I don’t want to see the aggregation of the sizes, only what it’s current size is so I can manage memory utilization and cube growth.  I created my own metric defined as Last(Size) and set it’s dynamic aggregation function to Sum (this way although I’m only showing the last measurement of the Cube size, I can still get a total of Cubes).  This improved the result, but I was still getting values that were too large.

Upon further inspection, I discovered that IS_CUBE_ACTION_FACT was seriously duping rows.  For every 1 row that should have been there, 4 were in it’s place.  The cause appeared to be due to some incorrect SQL in Enterprise Manager’s loading scripts that looked like they were only joining on Report GUID and not Project ID.  Since I had Cubes in my Dev and Production projects on the same IServer with of course the same Report GUID, the results were getting duped.  Technical Support confirmed this bug and it was logged off to Technology to be patched in the future (note, the current latest version is 9.2.1 HF3, but I’ll update back here once it’s fixed in the future).

Luckily this time, I was able to work around it myself.  Depending on what you need from this table, your solution may be difference, but I created a view in the database to sit on top of IS_CUBE_ACTION_FACT to remove the duplicates.

Here is my view:

create view v_Cube_Stats as
select distinct DAY_ID, IS_CUBE_REP_ID, IS_CUBE_SIZE_KB, IS_CUBE_ROWS
From IS_CUBE_ACTION_FACT f
join is_rep r
  on f.IS_CUBE_REP_ID = r.IS_REP_ID and r.IS_PROJ_ID = f.IS_PROJ_ID
where IS_CUBE_SIZE_KB is not null

Basically, I just joined to the IS_REP table on REP_ID and PROJ_ID.  Since some of the REP_ID/PROJ_ID combinations in IS_CUBE_ACTION_FACT were invalid, those rows were dropped.  It still left me with 2 rows of correct data, so I had to do a distinct.  Finally, there’s more in that table than just Cube Updates, so since I was already here, I filtered out the stuff I didn’t care about.

I then mapped the Day and Intelligent Cube attributes to their usual places as well as the Cube Size and Row facts.  I’m finally the proud owner of a report that can track Intelligent Cube Growth in my 9.2.1 environment.

You may also like...