Microsoft SQL Server
Microsoft is one of the world’s largest software companies. Founded in 1975 and headquartered in Redmond, it has become a household name, primarily due to its Windows operating system and Office suite. Aside from these products, Microsoft has a vast range of enterprise software and cloud offerings including its own database, browser, various servers and ERP solutions. In recent years, Microsoft has focused its business on cloud-based solutions such as Azure. AI and machine learning have also become increasingly important in product development. Its highest profile products are still Windows and Office.
BI revenues are growing and Microsoft has a strong presence in the BI market. Its BI offering is part of a strategy to complement existing solutions and to drive cloud revenues. MS Power BI is the market leader for front ends for analytics, dashboards and ad hoc queries.
Microsoft’s strategic development in the BI area dates back to 1996 when it purchased OLAP technology from Panorama. In just a few years, Microsoft moved from being a surprising entrant into the OLAP market to become the market share leader. Its main database technologies are bundled in SQL Server, which many companies use today to build data warehouses, data marts or even data lakes. This package includes data integration (covered by Integration Services, also known as SSIS) as well as multidimensional and relational data management. Reporting Services (SSRS), a solution for formatted reporting, is also included with SQL Server. The SQL Server relational database supports the building of a relational, analytic data model (star or snowflake schema). It also offers functionality for building a relational (tabular) or multidimensional (OLAP) metadata layer in SQL Server Analysis Services (SSAS) using SQL Server Data Tools. This review only discusses Microsoft SQL Server as an analytical relational database.
Over time, SQL Server for BI and advanced analytics has expanded greatly in terms of performance, data support and functionality. Examples of this include its in-memory processing, the extension of the engine with JSON functions for the processing of unstructured data and the integration of R and Python for advanced analytics.
Data and analytics capabilities in the Azure cloud are currently strategic for Microsoft. With Microsoft Azure, further innovative options are available including the cloud-based services Azure SQL Database and Azure Synapse (formerly known as Azure Data Warehouse). The recently announced Microsoft Fabric combines various services in one platform to support end-to-end analytical processes. It is an all-in-one enterprise analytics solution that covers everything from data movement and data science to real-time analytics and business intelligence.
Today, Microsoft recommends a cloud-based modern data warehouse architecture, which is equivalent to a lakehouse infrastructure. It also recommends the Data Factory solution for ingestion, Azure Data Lake Storage, Azure Synapse as a data warehouse and Analysis Services for data marts as well as Microsoft Purview for data governance. Microsoft Fabric is quite new on the market but seems to be the strategic goal for Microsoft.
User & Use Cases
Microsoft SQL Server is a functional all-round package for data management in BI. It is very popular in medium-sized companies although it is also becoming increasingly important in large companies. 34 percent of the SQL Server users surveyed came from large companies, compared to 31 percent last year. SQL Server is usually implemented on premises (60 percent) but can also operated in a virtual environment (13%), hybrid environments (12%) or consumed as software as a service (11%).
Its use as a data warehouse (by 83 percent of respondents) and for data integration (68 percent) stands out. These are parts of its core functionality. With SQL Server Integration Services (SSIS), an appropriate ETL is available, which often makes the evaluation of a third-party solution obsolete. Its use for data integration suggests that most of the survey participants use SQL Server on premises, as these functions are not available in the cloud.
The use of SQL Server for data warehouse automation also seems high at 33 percent. For automation, for instance, database administrators and developers can orchestrate SSIS packages in a workflow so that loads are executed automatically. However, we believe that there is much more to data warehouse automation than that. We do not see any native SQL Server functions that can react to changes in the domain-oriented data model (top-down) or in the sources (bottom-up) and automatically adjust ETL processes.
Want to see the whole picture?
BARC’s Vendor Performance Summary contains an overview of The Data Management Survey results based on feedback from Microsoft SQL Server users, accompanied by expert analyst commentary.Contact us to purchase the Vendor Performance Summary