Fixing Power View to SQL Server 2014 SSAS Multidimensional

Scenario: You have Power View integrated with SharePoint. You attempt to create a Power View report that connects to a SQL Server 2014 cube. The connection fails with “Internal Error: An unexpected exception has occurred”. The connection used to work or works with SQL Server 2012 SSAS MD.

Resolution: On the SSAS 2014 server, install Cumulative Update 2 for SQL Server 2014. This article provides more details about the issue.

Demystifying Clustered Columnstore Indexes

Non-clustered columnstore indexes (NCCI) were introduced in SQL Server 2012 to improve the performance of large aggregate queries (common for data warehousing) with the caveat that there were read-only. Consequently, the ETL process has to drop NCCI, load the data, and recreate the columnstore index.

NOTE Building an index (columnstore or regular) should be a highly-parallel operation. Building a columnstore index in particular should max out all licensed cores. Currently, we have an open support case with Microsoft where a columnstore index allows adding a computed column (while it shouldn’t). Consequently, SQL Server builds the index using a single thread which may lead to excessive index creation times.

SQL Server 2014 introduced clustered columnstore indexes (CCI) which offer two main advantages:

  1. CCI is updatable — Therefore, you don’t have to drop and recreate the index anymore.
  2. Storage is greatly reduced — For example, you might have a fact table of 100 GB. Assuming x10 compression, your table space with NCCI will be 110 GB. However, if you create CCI the table space will be only 10 GB. This is why Analytics Platform System (previously known is PDW) uses CCI.

On the downside, CCI doesn’t support any other indexes that you might need to optimize ETL or speed up joins. Basically, CCI is the only index you can have on a table. However, CCI is not designed for equality and short-range queries that are typical for detail-level SELECT or MERGE queries. It’s a common misconception that you don’t need such indexes with CCI but this is not the case. I hope a future release of SQL Server enhances CCI to support regular indexes as well.

Where is SSDT?

Now that Microsoft has decoupled SQL Server Data Tools from the SQL Server 2014 setup, the quest to find SSDT has started. You can download SSDT from the Microsoft SQL Server Data Tools page. Note that to get the BI project templates, you need the SSDT-BI install depending on the Visual Studio version you have (scroll all the way down the page to see the SSDT-BI links). If you don’t have Visual Studio installed (you probably don’t need it if you don’t code), I suggest you pick the latest, which as of this writing is Download SSDT-BI for Visual Studio 2013.

To make things a bit more interesting, Microsoft threw in another gotcha. When you run the SSDT setup, it will ask you if you want to install a new instance of SQL Server or add features to the existing instance. If you have SQL Server already installed, you might opt to add features to the current instance but you’ll be greeted later on with a rule violation “The CPU architecture of installing feature(s) is different than the instance specified. To continue, add features to this instance with the same architecture.” I have no idea what’s complaining about here given that when I tried this I had a brand new instance of SQL Server 2014 installed. It looks like we have a bug report on this already.

Although it doesn’t make much sense, the correct choice is to create a new instance although the setup won’t be creating any new instances since we are installing only SSDT here. After the install, you can start SSDT and see the beloved BI project templates.


Microsoft SQL Server 2014 CTP1 Product Guide Released

In anticipation of SQL Server 2014 CTP1, which should be out soon, Microsoft released Microsoft SQL Server 2014 CTP1 Product Guide – a collection of white papers, slides, and other resources that discuss the new features to help you evaluate SQL Server 2014.

“The SQL Server 2014 CTP1 Product Guide is now officially available to customers and partners. The guide is intended to help you get the most value out of Microsoft SQL Server 2014 CTP1.”

What’s New for BI in SQL Server 2014?

The short answer is nothing much, which is probably good so we can catch our breath after the SQL Server 2012 and Office 2013 waves. As you’ve probably heard, Microsoft announced SQL Server 2014 at TechEd. SQL Server 2014 will be a database-focused release with no major changes to the BI “pillars” (SSAS, SSRS, SSIS) and PowerPivot, except perhaps bug fixes. However, you might find some other new additions and enhancements interesting depending on your specific projects:

In-Memory Enhancements

This should come to no surprise due to the continuing investment in memory backed-up storage.

  • New In-Memory storage – Code-named Hekaton, this new in-memory storage option will allow you to host SQL Server tables in memory in order to improve I/O. While predominantly targeting OLTP applications, I believe Hekaton will be useful for narrow-case BI scenarios as well, such as for improving the performance of the ETL processes by using in-memory staging tables.
  • Enhanced Columnstore indexes – Columnstore indexes will be updatable so you don’t have to drop and recreate the index when you need to change data. For more information about how we used Columnstore indexes to speed up ETL, read this blog.

Big Data

There is a lot of talk about big data around the following two technologies:

  • Hadoop – As I’ve covered before, Microsoft marketing term for Hadoop-based distributions is HDInsight. I also showed you how to program MapReduce jobs so I won’t repeat myself here.
  • Polybase – The latest version of Microsoft Parallel Data Warehouse (PDW) get extended to support querying Hadoop-based data via a technology called Polybase. Microsoft’s David Dewitt did a great coverage of Polybase at SQL Pass 2012.

For more coverage of the new features, read the SQL Server 2014: A Closer Look blog by the Microsoft SQL Server team. Toward the end, notice that the public CTP1 will be out in a few weeks.