Over time, the Microsoft SQL Server Platform has evolved tremendously. Next to the big improvements related to OLTP, Microsoft has been putting a lot of effort in expanding the SQL Server capabilities for BI. As a result, the SQL Server data platform has now become one of the top performers for BI, and is often selected due to its cost price, performance and extensive feature list.
The Microsoft SQL Server 2014 release brought some nice new features, both for OLTP and for BI. To mention some of the key improvements of the platform for Business Analytics :
- Columnstore indexes can now also be used on updatable tables. Columnstore indexes were introduced in SQL Server 2012, and are based on in-memory technology and advanced compression techniques. The downside was that it could only be used on read-only tables. In SQL Server 2014, this is no longer the case.
- Introduction of the new SQL Server Data Tools for BI (SSDT BI). This toolset replaces the Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT) in the previous versions. SSDT BI is used to create SSAS models, SSRS reports and SSIS packages. It runs on Microsoft Visual Studio 2012 and 2013 and is backwards compatible, with the exception of SSIS packages which is limited to SQL Server 2014. SSDT BI needs to be downloaded and installed separately from the Microsoft Download Center, as it is not part of the Standard installation.
- High availability capabilities have been expanded. Introduced in MS SQL Server 2012, the AlwaysOn Availability Groups have been further refined with additional support for more secondary replicas and Windows Azure integration, which basically provides a cloud backup of your system. This will ensure your data platform is always available.
- Introduction of Hekaton. In-memory OLTP (code name Hekaton)
allows for tables to be stored in-memory, which of course boosts performance tremendously. You can chose which tables will be stored in memory and which tables will be stored on disk, so you can balance between performance and memory availability. You can also compile stored procedures into native compiled C code, again increasing performance.
- Power View integration with OLAP. On SQL Server 2012, Power View
would be restricted to tabular models in Power Pivot or SSAS Tabular. With this new version, OLAP cubes can also be addressed and it also handles MDX queries. This feature is only available in SharePoint.
- Power BI is now fully integrated with Office365. The Power BI tools (Power View, Power Pivot,
and Power Map) are now integrated with Office365, the Office cloud solution offering. Furthermore new features have been integrated, such as additional visualizations for Power View, HTML 5 support, forecasting capabilities in Power View and Natural Language query. This last feature, also called Q&A (Questions & Answers), allows you to query your corporate data using natural language and see almost-instantaneous results in the form of interactive visualizations. As it is based on HTML5, it can also be deployed to mobile devices.
- Further Big Data integration. SQL Server 2014 can scale up to 640 logical processors and 4 TB of RAM in a physical environment. In a virtual environment it can scale up to 64 virtual processors and 1TB of RAM. Next to that, Hadoop integration is further improved using the HDInsight connector. The Parallel Data Warehouse (PDW) or Analytics Platform System (APS) as it is now called, is also further refined, to make integration easier between relational and non-relational information such as Hadoop. Polybase allows you to query relational and non-relational data at the same time. The Azure cloud platform is also constantly expanding its Big Data capabilities, for example with DocumentDB and Storm.
- Enterprise Information Management (EIM) enhancements. Since SQL Server 2012, Data Quality Services (DQS) and Master Data Services (MDS) have been included in the platform. These tools are of course also available in SQL Server 2014.
element61 offers a vast variety of service based on Microsoft SQL Server 2014, from training & coaching to end-to-end implementations. For more information on this matter, feel free to contact us.