With the release of SQL Server 2012 SP1 CU4 and the announcement of SQL Server 14, I think it’s time to take a step back and think about what have been Microsoft’s BI innovations for the last two years and try to correlate that with our daily experience of selling and implementing BI projects with Microsoft technology.
- Enterprise OLAP In-Memory with SQL 2012 (GA was a little over a year ago);
- Evolution on Analytics and Self-Service with Excel 2013 which included Power View (GA was this year);
- More evolution on Analytics and Self-Service with Geoflow and Data Explorer (still in Beta);
- Big Data with HDInsight (still in Beta) and by partnering up with HortonWorks (HortonWorks Data Platform for Windows - released this month);
- Office 365 BI capabilities (good sum up here) (this year);
- Cloud - intentionally split from Office 365 – allows SQL Server Database and Reporting Services for some time now, but services such as Analysis Services or Integration Services can only be made available through IaaS;
- DW Appliances Refresh with PDW v2 which builds upon SQL Server 2012 and allows mashing-up structured and unstructured data with Polybase (released some months ago).
These are a lot of new features, in a short amount of time, but, let’s break these apart a little bit:
“Scotty, we need more power!”
So we have Enterprise In-Memory with ColumnStore (CS) indexes and Analysis Services tabular (and PowerPivot for SharePoint, ufff…) but we don’t have:
- CS Indexes which can be a reliable source for ROLAP partitions in Analysis Services Multidimensional (and probably DirectQuery) – although you can have very good performance with some query patterns (future post, hopefully with SQL Server 14 CTP1), with some basic patterns you just can’t get batch mode to kick-in;
- CS indexes with Decimal and Numeric data types which have a precision superior to 18 – I have found this limitation in a project and hope this will go away in vNext;
- All of the others shortcomings I can think of, related to CS indexes, are going away with SQL Server 14 (no need for rowstore/updatable…);
- Now, for Tabular, what I would like is to have DirectQuery support for more data sources than SQL Server, which would be the basis of a governed operational reporting strategy, for instance;
- But what I would really like is for Analysis Services to become a single product again where we would have a single semantic layer that would allow us to have top functionality and performance with aggregated and detailed data (this would have to be coupled with a proper client, of course) and doing closed loops with PowerPivot (almost there).
Analytics and Self-Service BI
So we have Excel 2013 being released alongside very cool stuff such as GeoFlow and Data Explorer and Power View being “integrated” inside Excel, but we don’t have:
- A tool which is simple and efficient doing analytics and, let’s face it, can face standards such as Tableau and Qlikview - I have always been an advocate that Excel should take its part in the analytics game, but I don’t think that turning Excel into a bucket of loosely “integrated” products is the answer. The release cycle of office, apparently, is just too slow to keep up with the needed innovation pace and we get new functionality “integrated” through separate add-ins, and that, in my opinion, just makes the entire experience confusing even for IT folks, let alone business people;
- An integrated story between that analytics tool and an enterprise dashboarding tool and ultimately a seamless visualization experience across all platforms. This would be coupled with the single semantic layer I’ve referenced earlier;
- And while we wait for that integrated analytics tool, it would help for Power View for Excel to keep up with Reporting Services Power View – this, of course, is related to the fact that only the SSRS version is DAXMD capable - and why not merge Report Builder with Power View while we are at it? This would also be coupled with the single semantic layer I’ve referenced earlier;
- A say in the advanced analytics game – again with the add-in story! Why can’t we have a good data-mining and statistical solution truly integrated with our analytics solution?
- Let’s not forget we also need a social analytics story!
This is a very recent trend and I haven’t had the opportunity to develop a real project with it yet. Nevertheless, what I immediately find is that more information is needed in order for us to sell this new technology (mainly on-prem). Most of our customers will be SQL 2012 for a while (at best) - how do we integrate older SQL Server versions with this?
Office 365 BI and Cloud
Like Big Data, we are still looking at ways to sell cloud. We all know that Microsoft’s mobile strategy will be highly dependent of O365, though, which I am not sure is a good thing.
And we also need…
Mobile support is something we are REALLY lacking - We need an offer for all platforms and it must be coherent across all devices. Worst of all, this isn’t event mentioned for SQL 14… SQL 2012 R2 before SQL 14, maybe? :)
I know corporate BI is going to change in the future with technologies like HDInsight/Azure… but I don’t think it will disappear. So we need to boost Integration Services and make it work seamlessly with those technologies and we will need, more than ever, I think, to be able to govern our information (what happened to project Barcelona?). Master Data Services and Data Quality Services are mere checkpoints at this point, I think, when and how will they evolve?
In a perfect world, SQL 14 would bring more than what we have been told it will, so far.
UPDATE 01  – The limit on precision for numeric types will be removed in SQL 14 as can be read here.
UPDATE 02  – In SQL14 batch processing will be seriously enhanced as can be read here.
Improvements in query execution were focused mainly on the batch-mode hash join operator