Like many of you I have been waiting for SQL Server 2014 to be released to market for quite a while. Now that it has been released it is time to start putting the wealth of new features to good use. From a LeapFrogBI application perspective the memory optimized table feature is very interesting, and from a data warehousing perspective updateable columnstore indexes look promising. I’ll focus on columnstore indexes in this post and leave the memory optimized tables for a later discussion.
What is a columnstore index? It is a data storage method. Why is this important? Because it offers huge performance benefits as compared to rowstore methods for certain use cases. According to Microsoft columnstore indexes offer 10x performance gain as compared to rowstore storage when applied to the proper use case. Why am I blogging about this? One use case that is very well suited for columnstore technology is data warehousing.
Here’s a quote straight from MSDN that sums up the use case for columnstore indexes quite well.
“Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.”
SQL Server 2012 introduced nonclustered columnstore indexes which are not updateable. With SQL Server 2014 we now have updateable clustered columnstore indexes available. Both have their use case, but the latter seems like a great tool to use on fact tables. Fact tables contain foreign keys and measures. These fields contain data that is repeated. For example, if I have a fact table with ten million records which contains a foreign key to a scenario dimension containing only two records (actual, budget), then the cardinality of that field is two. Columnstore indexes will be able to compress this column into a very small foot print as compared to the uncompressed ten million rowstore on disk. That compression alone will make a world of difference in query performance. Columnstore indexes are primarily stored in memory which again offers huge performance benefits. Of course, this is an extreme example, but you get the point. Foreign keys often point to dimensions that are at a much higher grain than the fact records making fact tables very well suited for compression in a columnstore.
You may be wondering if LeapFrogBI supports SQL Server 2014. Yes it does. You will find a new target system available enabling you to generate a SQL Server 2014 data warehouse. You will also find an option to convert an existing LeapFrogBI project’s target system to SQL Server 2014. Gone are the days when we spent weeks or months refactoring SSIS packages just to take advantage of a SQL Server upgrade!
Enjoy SQL Server 2014 & look for upcoming LeapFrogBI features designed around SQL Server’s awesome new features.
