Microsoft BI & SQL Server

Microsoft first started working on a BI solution back in 1996, originally with a team of own developers. The strategy however quickly changed into acquiring technology, resulting in the 1996 Panorama OLAP solution & software development team incorporation. It still took until 1998 before a product was actually shipped. OLAP services 7.0, part of the SQL Server 7.0 tool set, was launched. SQL Server 7.0 also included the new and improved DTS -Data Transformation Services. As a front end tool to visualize all this information Microsoft Excel was used, including the Pivot Table functionality.

In August 2000, a new version of the complete SQL Server toolset was launched. DTS became more robust and richer in features & functionality. OLAP services changed names and became "Analysis Services" (SSAS). The reason behind this name change was the addition of a new component for Data Mining, introduced in the 2000 release. Excel still was used as the Business Intelligence front-end solution. The lack of decent front-end tools in a then flourishing Business Intelligence front-end tool market resulted in vendors like ProClarity being very successful.

In 2004, Microsoft introduced Reporting Services as an additional and alternative front-end reporting tool, next to Microsoft Excel. The new product, distributed as an add-on to SQL Server 2000, made it possible to create reports on data residing in the database, but also in the Analysis Cubes. Next to the authoring and creation of reports, a complete Reporting management and delivery system was also provided. Reports were centrally stored on a reporting website. Users could render the information in several formats. Finally, it was possible to have the reports delivered to the users in a shared folder or via mail.

In October 2005, a new version of SQL Server was launched. DTS was replaced by SSIS SQL Server Integration Services, a complete new and improved tool for Extracting, Loading and Transforming information. The new solution was more compliant for loading vast amounts of information and had more ETL DWH concepts integrated in the software. Moreover, Analysis Services underwent a complete makeover. The concept of Unified Data Management was introduced and the engine became more robust to handle larger amounts of information. The Microsoft BI tool stack had grown up and could finally compete -also on volume- with most of the other large BI vendors in the areas of the database, the ETL and the OLAP engine components.

Another important new feature was introduced in Reporting Services: Report builder. This solution made it possible for end users to create their own report. In 2005, Microsoft also introduced its first Scorecarding solution: Business Scorecard Manager.

At that moment, the biggest gap in the Microsoft Performance Management stack was the missing CPM functionality, mainly in the exploding Planning and Budgeting market segment. This functional gap was filled with the introduction of
Microsoft PPS Performance Point Server 2007. Next to new Planning and Budgeting functionality, the scorecarding capabilities of Business Scorecard Manager and analysis & visualization functionality from the acquired ProClarity was added. Beginning of 2009, Microsoft announced the end of life of Performance Point Server as a separate solution and decided to integrate next versions of the individual components into Microsoft SharePoint.

Late 2008, Microsoft finally launched SQL Server 2008 with further builds on SQL Server 2005, making the RDBMS and Analysis Services engines even faster and better. New and improved functionalities to SSIS and SSRS additionally improve performance and ability to compete in the BI market. Improved Reporting Builder functionality gives end users additional ways to build their own reports. Tight integration with Office 2007 leads to an even further enhanced user experience.

A few years later, Microsoft launched SQL Server 2012. This release builds further on the existing building blocks of the SQL Server platform, and extends them with in-memory models (Analysis Services Tabular), self-service capabilities by extending the Power Pivot component and adding a new visualization tool called Power View. Power View is integrated into Excel 2013 and is also available in SharePoint 2013. SQL Server 2012 also introduced columnstore indexes, which are based on the Power Pivot technology. These indexes give vast performance increases due to their column based nature. Master Data Services has also been greatly improved. Next to all that, the integration with Microsoft's cloud services was also extended.

Meanwhile, Microsoft also released Power Query and Power Map. Both are free add-ins for Excel. Power Query allows you to quickly import, transform and integrate data into Excel, while Power Map allows you to plot geographical data onto a globe. All of these add-ins - with the exception of Power Map - have been integrated in the self-service cloud BI solution called Power BI. Most add-ins are now fully integrated into Excel 2016.

With SQL Server 2014, Microsoft introduced Hekaton, also called in-memory OLTP. But there were also great improvements in the columnstore technology, such as clustered columnstore index which allow you to put an entire table into an updateable columnstore index. Storage savings of over 50% are not abnormal.

In June 2016, Microsoft released its latest edition of its data platform: SQL Server 2016. This version comes with big changes in almost all of its components. Reporting Services has for example the addition of mobile reports and the use of HTML5, which allows you to view reports on any device using any browser. Master Data Services has a better user interface and the manageability has been enhanced, along with better security and performance. Integration Services has also better maintainability and manageability, but also better connectivity with the cloud and big data. Analysis Services Tabular has also matured by the introduction of dozens of new DAX functions and the ability to support many-to-many relationships.

SQL Server 2016 fully supports advanced analytics with the introduction of Microsoft R Server. You can either install the R language directly into the SQL Server database engine allowing you to run R scripts with the power of SQL Server or you can install the R services separately on a dedicated server. This can also be a Linux, Teradata or Hadoop server.

Power BI is a constantly evolving platform, where new features and capabilities are introduced around the clock. Power BI integrates seamlessly with dozens of data sources and can work with big data and data science solutions. It's a great platform for self-service BI, with good support for mobile BI by using native apps for Android, iOS and Windows Phone.

Contact us to learn more about Microsoft Business Intelligence and Microsoft SQL Server.