Microsoft launched Fabric in the summer of 2023. Fabric is an all-in-one analytics solution for enterprises that covers everything from data movement to data science, real-time analytics, and business intelligence. This article primarily focusses on data warehousing (DWH). The objective is to elaborate on how data warehousing works in the new Fabric solution. Highlighting what is innovative, distinctive, and what aligns with previous Microsoft DWH solutions. Let’s do this by describing Microsoft’s journey for DWH solutions.
Data warehousing as a concept is already very old. Some trace its origins to the 1960s and 1970s when concepts like dimensions, facts and data warehouses were already discussed. Others date it back to the 1980s when IBM built a business data warehouse for decision support. Famous DWH architectures by Inmon and Kimball were published in the 1990s. In this blog we begin our journey during a period when on-premises SQL servers were a standard solution and Microsoft released SQL Server 2005. We will explore the evolution of all ‘next-gen’ modern data warehouse solutions from the early days to the current state of Fabric.
Microsoft’s data warehouse journey:
- Starting with Microsoft’s on-premises SQL Server
- Initial steps towards the cloud with Azure SQL Data Warehouse
- Azure Synapse Analytics, a limitless analytics service
- Advancing to Fabric, the next level of integration
1. Starting with Microsoft’s on-premises SQL Server
SQL Server already existed with a database tool Data Transformation Services (DTS) with which you could automate extract-transform-load (ETL) transactions. However, in this blog we start in the period where Microsoft SQL Server 2005 was released. New add-on services were introduced in this 2005 version that replaced DTS. It now included SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS).
SSIS’ main purpose is to do ETL transactions. Other features also got better, like controlling the data flow with conditional logic, handling errors to define how an SSIS-package should respond and managing logging and sending notifications. SSAS is a service that provides analytical processing and data modeling. It enables organizations to create multidimensional and tabular data models with a data access structure, so tools like Power BI and Excel can be used to explore and analyze data. SSAS pre-aggregates and caches data, resulting in faster query performance. SSRS is a service to create and manage reports, this soon got a lot of competition from Microsoft’s Power BI tooling.
2. Initial steps towards the cloud with Azure SQL Data Warehouse
Microsoft’s commitment to providing a modern, cloud-native data warehousing solution that leverages the power of the Azure cloud platform was presented in 2016 with Azure SQL DWH. It integrated with familiar tools like SQL Server, SSIS, SSAS and SSRS to provide a seamless transition for organizations looking to migrate their on-premises data warehouses to the cloud. The step to the cloud could be realized by choosing from a list of separate services. A lot of effort went into integrating the services into a working platform. Compared to the on-premises solution, there was now the big advantage of scalable cloud resources with separation of compute and storage. This was a logic combination with the pay-per-use concept.
ETL and data flow could previously be orchestrated by on-premises SSIS. A new possibility was to run SSIS packages in the cloud in a separate integration runtime. Orchestration cloud now also be done with Azure Data Factory (ADF) pipelines, which turned out to be one of the core services that still exists. The counterpart of the on-premises SSAS is the Azure Analytics Services (AAS). Although a useful service for analytics processing and data modelling, it nowadays is seen as a costly and complex extra layer in the DWH landscape. The traditional reports from SSRS were replaced by the dynamic reports of the Power BI. The well-integrated Power BI facilitated self-service BI and required less engineering resources.
3. Azure Synapse Analytics, a limitless analytics service
By 2019 Azure SQL Data Warehouse is renamed to Azure Synapse Analytics. Synapse was seen as a next-gen and came with great improvements in performance and capabilities. It had the promise to bring together enterprise data warehousing and Big Data analytics in a unified experience. DWH and Data Lakes existed next to each other and the gap needed to be closed.
Synapse is a single platform that also integrates data orchestration with ADF and data presentation with Power BI. Also, in Synapse there are two analytic runtimes: SQL pools and Spark pools. SQL pools have massive parallel processing capabilities to run queries at petabyte-scale. Spark pools to process Big Data analytics jobs with the Apache Spark engine and notebook support for python, SQL, etc. It’s good to know that for example the popular Databricks is the commercial implementation of the open-source Apache Spark technology. This also enables to build Machine Learning (ML) models in the Synapse platform.
4. Advancing to Fabric, the next level of integration
Integration, integration, integration. Some say Fabric is Synapse in a new coat. Others see the benefits of an all-in-one analytics platform. The out-of-the-box integration in Fabric is much further than Synapse provides. This means no manual deployments to connect all the required services. Also, there is no configuration of Spark pools or SQL pools, dedicated or serverless. Fabric is SaaS, this is now all auto-managed.
Fabric provides two distinct data warehousing experiences: the SQL Endpoint of the Lakehouse and the Warehouse (aka the Synapse Data Warehouse). The two can be explained as follows:
- The SQL Endpoint is a read-only Warehouse that is automatically generated upon creation from a Lakehouse in Microsoft Fabric. A user can transition from the ‘Lake’ view of the Lakehouse to the ‘SQL’ view of the same Lakehouse. Delta tables that are created through Spark in a Lakehouse are automatically discoverable in the SQL Endpoint as tables. The SQL Endpoint enables data engineers to build a relational layer on top of physical data in the Lakehouse and expose it to analysis and reporting tools using the SQL connection string. Data analysts can then use T-SQL to access Lakehouse data using the Warehouse experience. Use SQL Endpoint to design your Warehouse for BI needs and serving data.
- The Synapse Data Warehouse or Warehouse is a ‘traditional’ data warehouse and supports the full transactional T-SQL capabilities like an enterprise data warehouse. As opposed to SQL Endpoint, where tables and data are automatically created, you are fully in control of creating tables, loading, transforming, and querying your data in the DWH using either the Microsoft Fabric portal or T-SQL commands.
OneLake is a single, unified, logical data lake for the whole organization, which comes with Fabric. Data from the Lakehouse and data from the Warehouse are both stored in OneLake. Also, resulting datasets from the Lakehouse and Warehouse can be published and used by e.g. Power BI. With these ‘data products’ the integration of Power BI is greatly enhanced in Fabric.
An overview of the different generations and the services of DWH solutions. Please note that a broader spectrum of services is possible, while we focus here on DWH services.
What’s next?
Keeping up with the constant influx of new innovations can be somewhat challenging. Technology features keep evolving continuously, while your SQL code may remain stable. Even if you are full time in the center of it, staying completely up-to-date remains a formidable task.
Each new service or feature can bring desired benefits. Throughout the DWH journey the platform becomes less and less technical, shifting its focus towards end-users. However, engineers continue to play a crucial role in establishing the data foundation. This shift, along with Fabric’s more Software as a Service (SaaS) approach, enhances the time-to-value. This is why it’s called Microsoft Fabric, rather than Azure Fabric. Similar to how Power BI is a Microsoft product within the Power Platform and not an Azure service.
As of November 2023, Fabric is Generally Available, but there is a roadmap and unpublished features that might be relevant to your business and how should you migrate if you want to? Eraneos Data & AI is here to assist you in understanding how to transition to a modern cloud data warehouse. We provide guidance in introducing and defining modern cloud architectures, as well as realizing and implementing them. At Eraneos we design and implement data platforms for a living and we can help you decide what’s right for your organization. If you want to try Fabric but avoid the pitfalls that will burn you later, give us a call!
Stay up to date!
Are you enjoying this content? Sign up for our (Dutch) Newsletter to get highlighted insights written by our experts.
