Data Partitioning is a very useful feature in SQL Server 2005 and beyond. I have discussed this database feature in previous blog entries but it is also available in SQL Server Analysis Services which has its own features. For instance, you can mix and match storage options by partition. Very useful…
The advantages of data partitioning are numerous. Most importantly it can give significant performance gains as your data is spread across multiple volumes allowing parallel processing. If your query hits a subset of data then the database engine will only access the partitions necessary, again optimizing the access. Bulk Inserts are an order of magnitude faster when performed into an empty partition. Archiving is a snap since a partition can be switched to an archive table via a meta data change without any copying of data. Partitioning is imperative in our largest databases such as data warehouses and multi-dimensional databases or Cubes.
In Analysis Services, when a cube is created, the cube wizard by default creates a partition per measure group. A measure group is a group of measures or facts and usually corresponds to a fact table. This default is called horizontal partitioning and allows us to spread our measures across disk volumes. However, we may choose to partition vertically, for instance, by year or month. This is done by specifying a partitioning query for each partition which defines which data corresponds to that partition.
SSAS also supports different storage options which are also applied by partition. The options are namely MOLAP, HOLAP and ROLAP. Multidimensional OLAP is the fastest storage option but takes the most storage since the pre-processed aggregates and the detail-level values are stored in multi-dimensional format. This is the default. Relational OLAP can use the data warehouse for the pre-processed aggregates (through indexed views) and the detail-level values therefore saving the duplicate storage but ROLAP is generally slower than MOLAP. Hybrid OLAP is a compromise since it is faster than ROLAP but does not use as much space as MOLAP. The selection of these storage options does not affect the way the end-user performs their analysis but ROLAP and HOLAP may be useful to reduce the latency of updated data.
The nice thing about SSAS partitions is that you can mix and match storage options on an individual partition level. For instance, one large financial services company I was working with in Boston wanted their foreign exchange data to be delivered through real-time Analytics (zero latency) but the rest of the cube would be ok with scheduled MOLAP for high performance. So we implemented horizontal partitioning with the foreign exchange data in a ROLAP partition and everything else in MOLAP partitions in the same cube. The end-user has no idea of the underlying physical storage so is unaffected except that they see the foreign exchange data the instant it changes.
Recently, I was asked by a customer at a large TV company in NYC (no name dropping) whether this feature could be used with vertical partitioning. I was skeptical but said I would test it out. I took a cube with vertical partitions by year and switched the most recent year’s partition to ROLAP leaving the older years as MOLAP. I was encouraged that the cube designer allowed me to do this. I ran the aggregation design wizard to convert the aggregations from the MOLAP default and re-processed the cube.
I then started the SQL Profiler connected to the SSAS Server to watch what the system would do when I ran a query across vertical partitions, for instance analyzing sales across all years. Lo and behold, the trace showed me that the ROLAP partition data was satisfied using a SQL query and the rest of the data was retrieved via MDX. Amazing. Of course, the end-users are just doing their analysis in the normal way using front-end tools like Excel, but under the hood, SSAS is working overtime.
Once again, the Microsoft developers have impressed. They have put some extra special intelligence in their Business Intelligence solution. Now that’s what I am talking about.
Cheers
Brian
Recent Posts:
Logins and Users - SQL Server security fundamentals
SSMS Debug 2008 - Comments please?
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.
The Leader in Network Knowledge
Post new comment