Intelligent Cubes: Dynamic Sourcing vs View Reports
MicroStrategy 9 introduced a game changing feature in Intelligent Cubes. Described as “Caches on Steroids”, it’s basically the ability to pre-cache data that can then be queried. Traditional Report Caching in MicroStrategy is done on a matching basis. The Intelligence Server checks to see if there is an existing cache for the same Report Template, Report Objects, Prompt Answers and User Security, and if a match is found, that answer is served up immediately instead of a trip to the database. The catch is that the same report must have been previously executed (today more than likely) and by a user with the same Security Filter (even if that Security Filter doesn’t affect the results of this particular report). Intelligent Cubes are special caches that can be queried. This allows MicroStrategy to treat it as a database in memory which is extremely fast and can be extremely flexible if you know the nuances of how to use them.
Creating an Intelligent Cube is very easy. It’s basically just a report, and can even start it’s life off as a report. If you’ve already got a report that you want to create an Intelligent Cube out of, you simply go to the Data menu and choose Intelligent Cube Options -> Convert to Intelligent Cube. If you’re creating it from scratch, you can simple create a new Intelligent Cube object. There are some restrictions on the types of objects and functions that are supported, but most of the common stuff is covered, and more features are added each version. Once finished, running the Cube will populate it in memory. The cube lives in both memory and as a file on disk, so when the server restarts (or crashes), the can quickly be reloaded into memory. The standard governing settings exist that allow you to limit their size as well as how many to load into memory at once. 64bit platforms are recommended for ample space, but in my project I have an 11 million row cube that’s only 270mb, so it’s not too bad. There is a separate Cube Monitor tool in the same place that you have Job Monitor and Cache Monitor so that you can view the Cubes that are in memory and their status including the number of times they’ve been used, which is incredibly helpful for determining if your cubes are work their salt.
Oh and by the way, when a report comes from an Intelligent Cube, it populates just as fast as a cached report (instantly). This is an incredible user experience when you’re prompting.
You may be familiar with the View Filter in Reports today (part of OLAP Services). They work by letting you filter data out of the report at run time without going back to the database. View Reports are a similar concept. To make one, you simply right click on an Intelligent Cube and choose Create View Report. The report editor is largely the same, but there are a few differences. The only objects you can use are the ones that are in the Cube, so they’re all available to you in the Report Objects window. You also don’t have a Report Filter (since you can’t go back to the database), but you can create View Filters with a few extra tricks at your disposal. The most powerful one is the ability to embed Prompts directly into the View Filter! You can add a shortcut to a standard Element or Expression prompt object, or create prompts on the fly with objects already in the cube. When a user runs the report, they’ll be using the same prompt objects that a “normal” report will use, including having access to their same Saved Prompt Answers. By default, user’s are locked into the Cube and can’t drill out of it, but there is a Project level setting where you can enable drilling outside of the Cube. If that happens, the query is simply sent to the warehouse. Don’t worry though, the View Filters are converted to Report Filters, so you’re not pulling back the whole universe of data.
A unique benefit of View Reports are the use of Derived Elements. Just like Derived Metrics, these are Attribute Elements (think Consolidations) that can be defined and used at run time or saved as Report Objects. Users can build on the fly groupings at run time with all of the dynamic aggregation you’d expect. For some reason, this feature only exists in View Reports and Grids inside Documents, but not normal Grid Reports (even with OLAP Services).
One of the down sides of a View Report is that it is forever linked to that Cube, and as of 9.0.2, there are no ways to break that link. If you want to add new objects or modify the report, you have to first modify the Cube. If you decide to use a different Cube or modification instead, you have to save it as a new Report (and new GUID) which can cause some issues with saved links.
Dynamic Sourcing is the ability of “normal” reports to utilize Intelligent Cubes, and my personal usage preference. When a Report goes to run, the Intelligence Server first checks to see if ANY Cubes are currently loaded and could be queried to get the result. Even if you didn’t originally design a cube with this report in mind, it can still use it! If you decide to drill or modify the report later, no worries, it will hit the data warehouse like normal. If one of your reasons for choosing View Reports is to lock a user into the Cube (perhaps to protect a sensitive source system), you could achieve the same with Dynamic Sourcing by simply disabling drilling on the report (under Data -> Report Options -> Drilling
Updating an Intelligent Cube is incredibly easy. Simply right click on one and choose Schedule -> Refresh Cube. You then choose a Schedule, and that’s it! The system will update it on schedule just like a subscription. The Cube doesn’t expire like a cache does and it will live on as long as you need it.
If leveraged properly, Intelligent Cubes can produce a dramatic performance increase in your project. You can create them at will with very little effort compared to aggregate tables in the database, and they’ll perform infinitely faster. Experiment with sizes before assuming you don’t have enough memory, you may be surprised what you can fit in one of them. You don’t have to always design Cubes with global strategic value. I use cubes that only feed a single report, but that single report gets run 100x a day, and the Cube only takes 4 minutes to load. Instead of waiting 20 seconds for that report 100x a day, every user gets the answer instantly and that saves lots of little hits on the warehouse, opening up processing bandwidth for other reports throughout the day.