Unlike the previous MS SQL 2012 version which was an important innovation step, MS SQL 2014 does not bring any essential changes. The basic structural elements of BI (SSAS, SSRS, SSIS and Power Pivot) remain practically unchanged and contain only minor adjustments. Nevertheless, there are a few new functions which might be applied in the BI area in specific cases:
- In-Memory Storage
This function permits to store tables in operating memory in order to speed up I/O operations. Primarily, this option is directed at OLTP application where speed can be increased several times without the need to change the code in the application layer. Without any doubt, it can also be applied very well in the ETL area where output of transformation processes may be increased thanks to stage tables saved in the memory.
Unfortunately, this function is only applicable in the Enterprise version; and we must also count with a more complicated hardware sizing process, in this case memory sizing.
- Columnstore Indexing
This technology got improved in the new version: index is now “updatable” and it needs not be dropped and re-created upon every data change. Microsoft introduced this technology in the previous version in order to speed up data queries and new version brings very practical improvements and its application is oriented precisely at data warehousing. Let us remind this through the following link:
- Buffer Pool Extension
The application may also be speeded up through Buffer Pool extension (cache where the SQL Server puts down most frequently used objects) by PCI-Express flash or SSD. This function is oriented at LTP systems, in particular, and can be used in Standard and Enterprise versions.
Microsoft continues pursuing development in this area where it sees a great potential; and, gradually, it is preparing a platform designed to interconnect all types of data available, their cleaning and analysis. The latest version of Microsoft Parallel Data Warehouse supports questions for systems based on the Hadoop technology, plus it may be integrated with the Azure cloud.
Senior Consultant, GLOBTECH, spol. s r.o.