A modern take on the classic data warehouse is Microsoft Fabric’s data warehouse. For analytical and reporting needs, it consolidates and arranges data from many departments, systems, and databases into a single, cohesive perspective. Complete SQL semantics, including the capacity to add, edit, and remove data from the tables, are offered by Fabric’s data warehouse. Because it is based on the Lakehouse, which has SQL query capabilities and is stored in Delta format, Fabric’s data warehouse is distinct. Not just data engineers, but the entire data team is intended to use it.
Identify Fabric’s background with data warehouses
The relational data warehouse offered by Fabric provides all of the complete transactional T-SQL functionalities you would anticipate from an enterprise data warehouse. Data can be stored and queried in the Lakehouse using this fully managed, scalable, and highly accessible data warehouse. You have complete control over building tables, loading, manipulating, and querying data using the Fabric portal or T-SQL commands using the data warehouse. Spark is a tool for processing data and building machine learning models; SQL is used for querying and analyzing the data.
Design a data warehouse
Fabric’s data warehouse has tables to store your data for later analytics, just like all relational databases. These tables are often set up in a schema designed with multidimensional modeling in mind. This method groups numerical data about occurrences (like sales orders) according to several criteria (like date, customer, or store). For example, you might examine the total amount paid for sales orders that happened at a certain store or on a given date.
Tables in a data warehouse
Typically, a data warehouse’s tables are set up to facilitate the effective and efficient analysis of massive volumes of data. This structure of organizing tables into fact tables and dimension tables is commonly known as dimensional modeling.
Your desired analysis’s numerical data is contained in fact tables. Fact tables are the main source of data for analysis and usually contain a high number of rows. The total amount paid for sales orders that happened on a given date or at a specific location, for instance, might be included in a fact table.
The data in the fact tables are described in detail in the dimension tables. Dimension tables are used to give the data context and usually consist of a limited number of rows.
Data warehouse is organized as a star schema, in which a fact table is directly related to the dimension tables
Understanding Data Warehouses
A data warehouse is a large, centralized repository of data that is used for analysis and reporting. It is designed to support business intelligence activities and is optimized for complex queries and analysis.
In the data warehouse experience, you’ll model data using tables and views, run T-SQL to query data across the data warehouse and Lakehouse, use T-SQL to perform DML operations on data inside the data warehouse, and serve reporting layers like Power BI.
In the data warehouse experience in Fabric, you can build a relational layer on top of physical data in the Lakehouse and expose it to analysis and reporting tools. You can create your data warehouse directly in Fabric from the create hub or within a workspace PTCLH. After creating an empty warehouse, you can add objects to it.
In the Synapse Data Warehouse home page, create a new Warehouse with a name of your choice.
After a minute or so, a new warehouse will be created.
You can use T-SQL to construct tables directly in the Fabric interface once your warehouse has been established.
Load data into your data warehouse
A Fabric data warehouse can be filled with data using a variety of methods, such as pipelines, dataflows, cross-database queries, and the COPY INTO command. Several business groups can access and analyze the data after it has been ingested, thanks to capabilities like cross-database querying and sharing.
A relational database with tables and other objects defined is called a warehouse.
Choose the Create tables with T-SQL tile in your new warehouse, then enter the following CREATE TABLE statement in place of the default SQL code:
Press the ▷ Run button to initiate the SQL script that generates a new table called DimProduct within the data warehouse’s dbo schema.
To refresh the view, use the Refresh button on the toolbar. Next, expand Schemas > dbo > Tables in the Explorer window, and confirm that the DimProduct table has been created.
Use the New SQL Query button on the Home menu tab to start a new query, then type the INSERT statement that follows:
Execute the query to import some data and generate a basic data warehouse schema. The script should execute in about thirty seconds.
To refresh the view, use the Refresh button on the toolbar. Next, confirm that the data warehouse’s dbo schema now includes the following four tables in the Explorer pane:
DimCustomer
DimProduct
DimDate & Sales Order Facts
Repeat the process to create many-to-one relationships between the following tables:
- FactSalesOrder.CustomerKey → DimCustomer.CustomerKey
- FactSalesOrder.SalesOrderDateKey → DimDate.DateKey
When all of the relationships have been defined, the model should look like this:
Query data warehouse tables
Most queries in a relational data warehouse involve aggregating and grouping data (using aggregate functions and GROUP BY clauses) across related tables (using JOIN clauses).
Create a new SQL Query, and run the following code
It should be noted that you can aggregate the fact table’s measurements at different hierarchical levels—in this example, year and month—thanks to the attributes in the time dimension. This is a typical data warehouse pattern.
SELECT d.[Year] AS CalendarYear, d.[Month] AS MonthOfYear, d.MonthName AS MonthName, SUM(so.SalesTotal) AS SalesRevenue FROM FactSalesOrder AS so JOIN DimDate AS d ON so.SalesOrderDateKey = d.DateKey GROUP BY d.[Year], d.[Month], d.MonthName ORDER BY CalendarYear, MonthOfYear;
SELECT d.[Year] AS CalendarYear, d.[Month] AS MonthOfYear, d.MonthName AS MonthName, c.CountryRegion AS SalesRegion, SUM(so.SalesTotal) AS SalesRevenue FROM FactSalesOrder AS so JOIN DimDate AS d ON so.SalesOrderDateKey = d.DateKey JOIN DimCustomer AS c ON so.CustomerKey = c.CustomerKey GROUP BY d.[Year], d.[Month], d.MonthName, c.CountryRegion ORDER BY CalendarYear, MonthOfYear, SalesRegion;
Create a view
Many of the features you may be familiar with from relational databases are also present in a Microsoft Fabric data warehouse. To encapsulate SQL functionality, for instance, you can create database objects like views and stored procedures.
CREATE VIEW vSalesByRegion AS SELECT d.[Year] AS CalendarYear, d.[Month] AS MonthOfYear, d.MonthName AS MonthName, c.CountryRegion AS SalesRegion, SUM(so.SalesTotal) AS SalesRevenue FROM FactSalesOrder AS so JOIN DimDate AS d ON so.SalesOrderDateKey = d.DateKey JOIN DimCustomer AS c ON so.CustomerKey = c.CustomerKey GROUP BY d.[Year], d.[Month], d.MonthName, c.CountryRegion;
Run the query to create the view. Then refresh the data warehouse schema and verify that the new view is listed in the Explorer pane
Create a new SQL query and run the following SELECT statement:
Create a visual query
Instead of writing SQL code, you can use the graphical query designer to query the tables in your data warehouse. This experience is similar to Power Query online, where you can create data transformation steps with no code. For more complex tasks, you can use Power Query’s M (Mashup) language.
Choose DimCustomer as the appropriate table to merge in the Merge Queries window. In both queries, select Customerkey, keep the Left outer join type as default, and click OK.
You can see that the FactSalesOrder table now contains the new DimCustomer column in the Preview. Click the arrow next to the column name to expand the column. Click OK after selecting the first name and last name.
you can now filter the query’s data by using the First Name and Last Name columns to see only the product-related information. To view only the data related to Cable Lock, filter the First name column.
By choosing Visualize results or Open in Excel from this point on, you may see the outcomes of this single query. We don’t need to conduct any additional analysis on the findings because you can now clearly understand what the manager requested.
Visualize your data
Expand the FactSalesOrder field in the Data pane. Observe that the columns you concealed are now hidden.
Choose SalesTotal. The column will now be added to the Report canvas. The default graphic is a column chart since the column contains a numeric number.
To add a category to your column chart, make sure the column chart on the canvas is active (it should have a gray border and handles). Next, select Category from the DimProduct table.
To convert a column chart to a clustered bar chart, select the appropriate chart type in the Visualizations window. Next, adjust the chart’s size as needed to make sure the categories are readable.
- In the Visualizations pane, select the Format your visual tab and in the General sub-tab, in the Title section, change the Text to Total Sales by Category.
- In the File menu, select Save. Then save the report as Sales Report in the workspace you created previously.
Lastly, you improved the default dataset’s data model in the data warehouse and utilized it as the basis for a report.
Benefits of Data Warehouse
Building a data warehouse on Microsoft Fabric can provide businesses with valuable insights into their data and help inform key business decisions.
Best Practices
By following best practices for designing and implementing a data warehouse, businesses can ensure that their data is accurate, reliable, and secure.
Implementing a data warehouse with Microsoft Fabric provides businesses with a centralized storage location for data from multiple sources.By analyzing data from multiple sources, businesses can gain valuable insights into customer behavior, sales trends, and other key metrics.A data warehouse can help improve data quality by eliminating duplicates and inconsistencies for more accurate and reliable data.Building a data warehouse on Microsoft Fabric allows for scalable and reliable data warehousing that can be designed to meet the needs of any business.Following best practices for data quality, integration, and security is essential for a successful data warehouse implementation.
Thanks for the read, please share if any comments or suggestions!!