Using MicroStrategy as a Historical Data Store with Intelligent Cubes
This is more of an idea I had and not something that I’ve tried or necessarily would even do, but hear me out. Intelligent Cubes work by pulling data from the database and storing it in memory for fast access. The idea is that the in memory indexes and slicing is much faster for small to medium sized data sets than hitting a database that has to do joins and access disk. In addition to storing the data in memory, a copy is also made on disk so that you don’t have to reprocess the data when the IServer reboots. This is effectively a mini-database that is file based and whose storage and operation could be a lot cheaper than your current data warehouse solution. What would be the possibilities for using this feature as an archival strategy and storing data historically? Here are some ideas.
You’ll definitely need 9.2 for this to take advantage of Incremental Refresh of Intelligent Cubes. Say your database keeps 3 years of history, but instead of rolling it off, you load it into an Intelligent Cube first. I don’t have any hard numbers, but I’ve observed that the cube compression is decent (I’ve got a 25 million row x 7 column cube that’s only 600mb). Archiving it off into an Intelligent Cube would then free that space on the database server. The .cube files associated with the cubes could then be saved externally like any other file backup.
I haven’t tested this, so I’ll be making a few assumptions:
Assumption #1: It’s cheaper to store data on the file system than the database
This really depends on your database platform, because in some cases, the two are directly tied together (SQL Server). But in cases where you’re using a Data Warehouse Appliance, this could be an advantage.
Assumption #2: You’ll have enough memory to read the data out
This could be the deal breaker, but in order to make this worth the trouble, you’d need to be saving a lot of space on the database side. This means the cube will eventually get large, and in order to use it you’ll need that much memory. It does no good to store a 1tb cube if you don’t have 1tb of memory. At a quick glance, it doesn’t look like you can extract the data directly from the cube file, though perhaps via the SDK or some fancy engineering could expose that technology.
Assumption #3: Accessing the data will be “good enough”
As with an archival strategy, it may take some manual steps to be able to read the historical data. Currently, MicroStrategy doesn’t support partitioning across Intelligent Cubes and Databases, though hopefully that functionality will be added in the future. In the mean time, it means you’d at least have to have a separate report for accessing older data. One of the nice things about how Cubes are handled is that you can have them loaded to File only, and only pull them into memory when accessed. This allows you to have them “on hand” without paying the memory cost on the IServer until needed. Hopefully since this is archived data, it will rarely be needed anyway.
Assumption #4: You can restore the data to the database if necessary
I didn’t test this either, but it looks like you can set a View Report from an Intelligent Cube to output to a Data Mart. This would be your method of putting data back into the warehouse if you needed it. Perhaps if nothing else, this strategy becomes a cheap backup plan for Data Warehouse Appliances that don’t otherwise have an “oops, undo” button, at least for the high level aggregates.
I’m not sure this idea is practical, but it did occur to me and I thought it was worth sharing. Perhaps as Intelligent Cube features expand and performance continues to improve (it has in every release since their introduction) this will become more of a reality. I’m sure in the end there are better data backup and archival strategies out there, but this could be a low cost, clever alternative.