Microsoft Copilot is utilized in different technologies in Microsoft; in Microsoft Fabric, one of the places that you can use Copilot is in the Data Warehousing workload, which will guide you through your SQL commands and enhance your SQL coding experience. I’ll show you how the Copilot in the Data Warehouse works in this article and video.
Video
Prerequisite: License
Before we start, it is important to know that the Copilot in Fabric Data Warehouse is only available in capacities that are F64 or higher (the equivalent of P1 or higher in Power BI Premium). It is not available in PPU, and it is not available in the Fabric Trial license.
The tenant administrator in your organization should have enabled the option to use Copilot (this option is enabled by default unless the tenant admin disables it)
After setting up the prerequisite, you can use the Copilot in the Data Warehouse.
SQL Analytics Endpoint
To work with the Copilot in the Warehouse, you have to go to the SQL Analytics Endpoint, which you can do by just clicking on your Warehouse.
Once in the Warehouse, you will see the Copilot icon there (assuming you have the prerequisite met)
Copilot Experience in Warehouse
Once you select Copilot, you will see the Copilot window added on the right side,
As you see in the above screenshot, there are multiple ways that the Copilot can help here;
Create a Date table using Copilot
You can ask Copilot to create a table for you. For example, I asked the question below;
create a date table using t-sql that includes columns for year, month, week, weekday, weekend, half year, start of the year, start of the month for the period of the past 20 year
And here is the response I got:
The generated code is as follows;
-- Step-by-step plan:
-- 1. Use a loop to generate dates for the past 20 years.
-- 2. For each date, extract the year, month, week, weekday, weekend, half year, start of the year, and start of the month.
-- 3. Create the final date table with the desired columns and insert the generated date information into it.
-- 4. Optionally, add any additional constraints or indexes to the final date table.
-- Use a loop to generate dates for the past 20 years
DECLARE @StartDate DATE = DATEADD(YEAR, -20, GETDATE());
DECLARE @EndDate DATE = GETDATE();
-- Create the final date table with the desired columns
CREATE TABLE [dbo].[DateTable] (
[Date] DATE,
[Year] INT,
[Month] INT,
[Week] INT,
[Weekday] INT,
[Weekend] BIT,
[HalfYear] INT,
[StartOfYear] DATE,
[StartOfMonth] DATE
);
WHILE @StartDate <= @EndDate
BEGIN
-- For each date, extract the year, month, week, weekday, weekend, half year, start of the year, and start of the month
INSERT INTO [dbo].[DateTable] (
[Date],
[Year],
[Month],
[Week],
[Weekday],
[Weekend],
[HalfYear],
[StartOfYear],
[StartOfMonth]
)
SELECT
@StartDate,
YEAR(@StartDate),
MONTH(@StartDate),
DATEPART(WEEK, @StartDate),
DATEPART(WEEKDAY, @StartDate),
CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END,
CASE WHEN MONTH(@StartDate) <= 6 THEN 1 ELSE 2 END,
DATEFROMPARTS(YEAR(@StartDate), 1, 1),
DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1);
SET @StartDate = DATEADD(DAY, 1, @StartDate);
END;
-- Optionally, add any additional constraints or indexes to the final date table
Not only did it generate the code, but it also added some commentary at the top of the code and inline explaining what is done. Then,
when I run the code, I get the table generated in my Warehouse;
Copilot in SQL Editor
One of the most exciting uses of Copilot is inside the SQL query editor of the Warehouse. Here, you get the Copilot inline experience. It starts with helping you write your SQL code.
For example, if I start a query like the below;
As you see, I just mentioned the two tables I want to join, and it picked up the key columns as the joining criteria and suggested that I accept the suggestion or keep writing the code. It is an amazing way to speed up writing T-SQL code.
Copilot to Fix Errors in the Code
Copilot can also help in fixing the errors you have in your SQL scripts. For example, the script below generates error when I run it.
The code above has two errors: One in using the row_number function and another in the joining criteria syntax.
I can use the Fix option of Copilot, which then fixes both errors for me!
The code above now runs perfectly fine;
Copilot to Explain and Document
Last but not least, you can use Copilot to explain an SQL script for you. This is especially helpful when you are given a long piece of SQL code that you don’t know what it is doing;
As you can see in the above script, not only did Copilot add the heading comments about what the script is doing in general, but it also added inline commentary for each step and formatted the code in an easier-to-read style.
Copilot to write the Query
Not only can Copilot help you when you write your query or fix the errors, but it can also help you write the entire query. Because Copilot has knowledge of your tables in the warehouse, it can generate a query if you ask a question about it. Below is my question, and then the Copilot suggested a query.
Summary
Copilot in Microsoft Fabric is a big improvement for your development activities in the data analytics tasks. The Copilot usage in the Warehouse is helping you with functions such as creating tables, writing SQL scripts for you, auto-completing the SQL scripts for you, fixing the errors, and explaining the code and formatting it. The copilot usage in the Data Warehouse can be a big time saver for database development tasks. However, always remember that this is a copilot; you should still be the pilot and just use the copilot to help.
Here are links to study more about some of the concepts mentioned in this article:
- What is Microsoft Fabric
- Microsoft Fabric Warehouse
- Licensing for Microsoft Fabric
- Copilot in Data Factory
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.