I know this sounds like a Dilbert cartoon situation but what if you really did want to find out everything that’s happening in your cube? Seinfeld would say you need to be in charge of your own domain, and you would definitely want to keep the Soup Nazi at bay. If you’re not careful, Kramer will move in permanently or Newman may try to sabotage your cube by going postal. What happens in your cube, stays in your cube. So what can we do?
Well, of course, I am referring to our multidimensional database built by Analysis Services not the comfy abode at work with low walls and no ceilings. This technology, once we get it working correctly seems to take over itself. We call it a cube because as human beings we can only visualize in 3 dimensions. A multi-dimensional database may have 30-40 dimensions or more. That just freaks us out. H.G. Wells tried to help us with the 4th dimension but beyond that we lose it completely. So we still stubbornly call it a cube.
In SQL Server 2005 and 2008, the cube technology is extremely sophisticated. It needs to be. This is where the multi-terabyte installations will expect reasonable performance for ad-hoc queries. Since the cube is regarded as read-only after the load processing, SSAS can take liberties like pre-calculate the most common calculations called pre-processed aggregations. Of course, this takes up extra storage but when faced with a trade-off of storage vs performance we almost always buy off on the extra disk space. In 2005, Microsoft introduced the Proactive Caching option. This allows SSAS to create a cache with the idea of controlling data latency and driving it down to lower levels. Previously, we had to run our incremental load and then re-process our cube before our analysts could do anything with the new data. This may be several hours downtime. With the proactive cache, we just have to load the data into our Data Warehouse and the cache will make it available to the cube before cube processing occurs. We can define our latency requirements such as 30 minutes or 60 minutes or whatever and the proactive cache will make it happen so that all updates are seen within that time.
It also opens the door to real-time analytics which means zero latency. That means that as data is updated in the operational database, it becomes immediately available to the cube. In the Financial Services market this might be desirable. But watch out – this adds a huge performance burden on your already strained legacy systems. It’s only really viable for new applications where brand new hardware can be approved to take the extra strain. Or you can do what a large mutual-fund customer of ours in Boston did. Use Real Time Analytics for only a subset of data, for instance the Foreign Exchange data, which is particularly volatile. You can define these storage options on a per partition basis so you don’t have to go all-or-nothing, you can pick and choose.
Students in my class always have plenty of questions regarding how proactive caching works and I always tell them, let’s find out…The SQL Profiler can now trace multi-dimensional access to a cube. You can actually see what happening under the hood when you convert a default MOLAP partition (pro-active caching turned off) to a low-latency MOLAP solution (pro-active caching turned on). What you’ll see is what actually happens. The proactive cache is used wherever possible, but if it is busy being updated, a ROLAP (relational) request will be issued to at least get some valid data to the user while the cache catches up. Of course, to see this happen quickly, we have to set the latency to something crazy like 1 or 2 minutes to test out the behavior but eventually you’ll see it happen. You’ll have to set the new Profiler event “Get Data from Cache” to see what’s happening there.
But as I tell my students, I could try and explain exactly what happens but there nothing like the real-thing. Test it out, and be the master of your own cube!
Cheers
Brian
Recent Posts:
COBOL Support in SQL Server 2008???
Fast Track to a SQL Server Data Warehouse…
