Microsoft Fabric Updates Blog

Build a Data Warehouse schema with Copilot for Data Warehouse

As a data engineer, it is important to be able to efficiently organize, analyze and derive insights from your data so that you can drive informed and data-driven decisions across your organization. Having a well set up Data Warehouse, you can ensure data integrity, improve your query performance and support advanced analytics. Optimizing a Data Warehouse empowers data engineers to overcome data silos and provide scalable infrastructure to meet evolving business needs.

Contoso Retailers, a fictious company, wants to learn how to optimize their Data Warehouse with the help of Copilot to gain insights into their Sales performance and make data driven decisions from these insights.

Using Copilot for Data Warehouse

Contoso Retailers aims to optimize their Data Warehouse to analyze trends and patterns affecting their sales performance effectively. By leveraging Copilot, they intend to gain insights into their data, identify key drivers of sales fluctuations, and make more informed, data-driven decisions.

You can use Copilot for Data Warehouse for code completion, code explanation, code fixes, code generation and more.

1 – Build warehouse schema and load data with Copilot

Before you can build, manage a warehouse schema and load data, you need to create a Data Warehouse where your data will sit. You can learn more about Data Warehouses in Microsoft Fabric by referring to – How to create a Warehouse.

Once you have created a warehouse, you can use the Copilot Chat panel to start creating a warehouse schema and load data into it.
Within this requirement, Contoso Retailers wants to understand the customer behavior and their demographics to analyze sales trends. As an initial step you can use Copilot add the Customer table and other tables to assist the team in creating a warehouse schema.

Example prompt: Add a table DimCustomer with the columns PersonID type BIGINT, FirtstName, LastName, Gender, Address, StoreID, TerritoryID, no primary key.

Copilot will generate a new SQL code on the response card and you can use the Insert Code button to add the code to a new SQL script. It’s important to first review the code generated by Copilot to check for errors or if it meets requirements.

Screenshot of Microsoft Fabric Data Warehouse interface showing a response card with the SQL code to create a table_

You can also add more tables to the schema using Copilot to allow Contoso Retailers to gain insights into their sales performance and which products are most popular with their customers.

Example prompt: Add a table DimProducts with the columns ProductID type BIGINT, ProductCategoryName, Model, no primary key.

Example prompt: Add a table FactSales with the columns PersonID type BIGINT, ProductID, StoreID, OrderDate, OrderQty, UnitPrice, UnitPriceDiscount, GrossRevenue, NetRevenue, no primary key.

Use Copilot to add other tables like DimDate & DimStore to enable Contoso Retailers to gather more insights on the sales performance over a period of time from each store.
Once the tables have been created, you need to load data that Contoso Retailers can use to draw insights from. For this requirement, you will load the data from a Lakehouse (for reference – Enhancing data quality with copilot for data factory) in a new SQL script. You can use the following resource to learn more about other ways to load data into a data warehouse.

SQL Code example:


INSERT INTO DimCustomer (PersonID, FirstName, LastName, Gender, Address, StoreID, TerritoryID)
SELECT
    p.PersonID,
    p.FirstName,
    p.LastName,
    p.Gender,
    p.Address,
    d.StoreID,
    d.TerritoryID
FROM LearningHub_LH.dbo.Person p
JOIN LearningHub_LH.dbo.DimCustomer d 
    ON p.PersonID = d.PersonID;

INSERT INTO DimProducts (ProductID, ProductCategoryName, Model)
SELECT 
    ProductID, ProductCategoryName, Model
FROM LearningHub_LH.dbo.DimProducts;

INSERT INTO FactSales (PersonID, ProductID, StoreID, OrderDate, OrderQty, UnitPrice, UnitPriceDiscount, GrossRevenue, NetRevenue)
SELECT 
    PersonID, 
    ProductID, 
    StoreKey, 
    OrderDate, 
    OrderQty, 
    UnitPrice, 
    UnitPriceDiscount, 
    Gross_Revenue, 
    Net_Revenue
FROM LearningHub_LH.dbo.Sales;

Note: You can replace LearningHub_LH with your Lakehouse name.

To gain accurate insights from the SQL queries, you need to create relationships between the tables using the Model Layouts.

2 – Create views to join tables and summarize data with Copilot

Using the same warehouse you can use Copilot for Data Warehouse to create views that will allow you to get a summary of your data.

For this requirement, you can use SQL to enable you to create a view that will enable Contoso Retailers to get a summary of the monthly revenue by each product category.

SELECT
    dd.Year,
    dd.Month,
    dp.ProductCategoryName,
    SUM(fs.NetRevenue) AS TotalNetRevenue,
    SUM(fs.OrderQty) AS TotalOrders
FROM [dbo].[FactSales] fs
LEFT JOIN [dbo].[DimDate] dd      ON CAST(fs.OrderDate AS DATE) = dd.DateKey
LEFT JOIN [dbo].[DimProducts] dp  ON fs.ProductID = dp.ProductID
GROUP BY
    dd.Year,
    dd.Month,
    dp.ProductCategoryName;

You can use the Explain query in the query editior to enable your team to better understand the data presented in the view.
You can use the Copilot chat panel to modify how the data is presented in the TotalNetRevenue column to ensure that Contoso Retailers gets accurate insights.

Example Prompt: Modify the query to round the TotalNetRevenue to two decimal places.

In an instance of an error when running your queries, you use the Fix query errors button and Copilot will update the query to fix the errors.
Now you can save the view by selecting Save as view button in the query editor to show the Monthly Revenue by Category insights to Contoso Retailers.

Screenshot of Microsoft Fabric Data Warehouse interface showing the Save as view dialog_

Lastly, you can use Copilot to create other views for example, within this requirement you can create a view that calculates the year-over-year growth in revenue changes for each store.

Next steps

There’s more you can do for a retail scenario with Copilot for Data Warehouse, this is a baseline of the thought process of understanding your data, how to use leverage Copilot to create views based on a specific need, building a schema and loading existing data into the new schema. This can be applied to other scenarios; you have to mindful of what is needed based on your requirements.

You can find a deep dive of using Copilot for Data Warehouse in our Copilot Learning Hub for Data professional ’s tutorial.