---

Unleashing the Power of Fabric Data Warehouse Copilot

Microsoft Fabric has introduced a groundbreaking AI assistant designed to revolutionize your data warehousing tasks: Fabric Data Warehouse Copilot. This innovative tool integrates seamlessly with your Fabric warehouse, providing intelligent insights and streamlining operations to enhance productivity.

This article is the first of a series of articles on the topic of Copilot integration with Microsoft Fabric.

Introduction to Fabric Data Warehouse Copilot

Fabric Data Warehouse Copilot is an AI assistant specifically designed to assist with data warehousing tasks. It leverages the power of generative AI to give intelligent insights, automate key aspects of data management, and enhance the overall efficiency of SQL developers and analysts.

Key Features of Fabric Data Warehouse Copilot

1. Natural Language to SQL: One of the standout features of Copilot is its ability to generate SQL queries using simple natural language questions. This feature allows users to interact with their data warehouse in a more intuitive and user-friendly manner.
2. Code Completion: Copilot enhances coding efficiency by providing real-time, context-aware code completions directly in the SQL query editor. This feature helps reduce errors and speeds up the development process.
3. Quick Actions: Copilot offers quick actions such as “Fix” and “Explain” for SQL queries. These actions allow users to quickly address errors and understand their queries better, making the development process smoother and more efficient.
4. Intelligent Insights: Copilot provides smart suggestions and insights based on the warehouse schema and metadata. This feature helps users make informed decisions and optimize their data management processes.
5. Chat Pane: Users can ask questions to Copilot through the chat pane using natural language. Copilot responds with generated SQL queries or natural language explanations based on the questions asked.

This article will focus on the natural language to SQL mainly and the code completion features.

Prerequisites

Here is a short list of what is needed to use copilot with a data warehouse in Fabric:

• A Fabric capacity with a F64 size or more.
  • The trial Fabric capacity will not work
  • You need to sign up to get access to Copilot with Fabric Data Warehouse
• A Fabric data warehouse
• Tenant switch enabled in Fabric Tenant settings

The data model

I created a data warehouse for this article series to show how code completion can help with all the functionalities. I designed in a star schema format, originating from the AdventureworksLT database. Here is the model:

Very important: I defined all the foreign key relations as they help Copilot to better understand the data model and returns better results.

Natural language to SQL

This data model looks nice, but it is missing role-playing dimensions for the various date columns in the FactOrders table:

• IDDueDate, we’ll create a view on top of the DimDate dimension called DimDueDate
• IDOrderDate, we’ll create a view on top of the DimDate dimension called DimOrderDate
• IDShipDate, we’ll create a view on top of the DimDate dimension called DimOrderDate

To create those views, I opened a new SQL query window in Fabric and types the next command:

Copilot generated a preview (in gray) and I saw what SQL statement should be issued. I didn’t like the “SELECT *” in the SQL statement. So I was more precise in my second try and asked Copilot to avoid using the “SELECT *” in the code to be generated:

The generated SQL looked better but I didn’t like the format of the code, every column on a single line. I tried to be more specific again and my 3rd try was quite successful:

I am not a big fan of using the dbo schema in SQL. So, I asked Copilot to create a schema called “Enriched”:

For fun, I tried to be even more precise, I told Copilot to alias the DimDate table and rename the IDDAte column:

I accepted the result this time. I repeated the same process for the two other view. Here’s a screenshot of the final model.

If you are new to a data model like that one (even if it’s simple), Copilot can be used to describe the data model. I opened another SQL query pane, and I typed the next command:

— create a description for all the database objects

Using the Code Completion feature

As mentioned in the above section, I am not a big fan of the dbo schema. But all my tables were created in it. So, I decided to use code completion to make me more productive in that area. The syntax to move table from dbo to my Enriched schema is the following:
ALTER SCHEMA Enriched TRANSFER dbo.<my table>;

In a query pane, I started typing the command for the first table that I wanted to transfer. It didn’t work as intended at first. But Copilot learned what I wanted to do after a few attempts, and I got the following help from it:

I accepted and cleaned up the code as not every tables/views needed to be transferred. But again, Copilot was a great helper here.

I then tried to create a stored procedure that return the result of all my tables joined:

Interestingly, a query was created instead of a stored procedure 😊. The result is not perfect but good enough to get me started with a stored procedure creation.

Maximizing Productivity with Copilot

The few examples of this article demonstrated how Copilot can make the next personas more productive:

• The data analyst
  • Understand and document the database content
  • Create queries using natural language
• The data architect to create database objects, move and rename database objects
• The TSQL developer can use it to create queries and use them in stored procedures or functions.

Conclusion

Fabric Data Warehouse Copilot, while still in public preview, will become a powerful AI assistant that can transform the way we manage our data warehouse. By leveraging its natural language to SQL capabilities, code completions, we can streamline our data warehousing tasks and boost productivity.

Whether you’re a seasoned SQL developer or a business analyst, Copilot is here to help you unlock the full potential of your data warehouse. I will write a few other articles on Copilot with Fabric Data Warehouse to fully experiment and explain the various features. I will certainly come back to the code completion and show more of it from a TSQL developer perspective as the product evolve and gets better.

Whenever we create a Delta table using the write(). format(‘delta’).saveAsTable(‘DimCustomer), the table will be named “dimcustomer”. The table’s columns can use mixed case, but it seems that saving a table using mixed case – DimCustomer with capital D and capital C cannot be done with Spark default options. If we use Fabric Data Factory, we can specify DimCustomer in the target table and the table name will not be lowercased.

 

 

And the resulting table will be named with mixed-case.

 

But when we use a notebook (code) to create the table, things are a bit different as shown below. The table name is dimcustomer, no upper-case letters, no mixed case letters.

This is a bit annoying to me. I looked on the web and I couldn’t find a way to name a Delta table with mixed or uppercase. The I found an article that talked about case sensitivity with Spark by using a config command : spark.conf.set(‘spark.sql.caseSensitive’, False).

I used it in my code and voilà! I now have a table name with mixed case! The only thing I had to do was to drop the table before. I could not use the “overwrite” option to simply rename the table. But I don’t have to do it in my subsequent loads.

 

Hope this article will help people that had the same issue I had with Delta table names!

When using dataframes and save it to SQL Server using JDBC, the resulting data type for a string column is nvarchar(max). This happens when let JDBC creating the table without supplemental instructions. We seldom need to have our string columns using with this data type as it limits the functions we can use with it such as substring().

This article will show some options to avoid having a nvarchar(max) column in our table

A simple query

First thing first, we’ll connect to a database. For this article. I am using a sample database; AdventureworksLT from Microsoft Git web site. With Databricks, we connect to the server using JDBC and query the “SalesLT.Product” table to bring back two columns:

• ProductID: integer column;
• Name: navarchar column, it will be seen as a string in the dataframe;

Now that we have retrieved data in our dataframe, we’re going to save it back in a different table. In real life, we would have transformed the data quite a bit but yhe goal of this article is to explain how to save a dataframe back to SQL Server with correct data types.

Save the dataframe; the simple way

The first thing we will do is to simply save, overwriting the destination table. Overwriting the table without any other options means we are going to drop and recreate the table.

The resulting table structure is shown below. I did the screenshot with Azure Data Studio. As we can see, the “ProductName” column’s data type is nvarchar(max). This is not the best option but, it ensures that whatever is stored in the dataframe’s string column will fit; it’s a safe choice.

There’s a way to specify the data type of the dataframe’s columns while saving it to the database. But in order to find the right length, we need to profile the data. Still in Databricks, we’ll find the maximum length of the “ProductName” column in our dataframe as shown below.

Specifying a data type

Now that we know the maximum value in the column, we use the “createTableColumnTypes” option when we save the dataset. However, specifying “nvarchar(100)” fails. The “nvarchar” data type is not supported for now.

Using the “varchar” data type executes successfully as shown below.

When we look at the table structure in Azure Data Studio, we clearly see that the command created a table with the specified structure.

 

However, I am not satisfied with it. I dislike using “varchar” data type as we live in a connected world now and sooner or later, this data type will limit us with international character sets. To circumvent this, we have to create the table first as shown below.

Truncate the table

Next, we’ll use the “truncate” option with the save command. This will NOT drop and recreate the table but simply empty (truncate) the table.

Going back to Azure Data Studio, we notice that the table’s structure has not been altered by the save with overwrite command.

 

But we have to be careful because if the structure of the dataframe changes by adding/renaming a column, the command will fails as shown below.

 

To sum up

There are some valid options to avoid be stucked with a “nvarchar(max)” column while saving a dataframe with Databricks An improvement I would like to see is the support for “nvarchar” data types. This might come in a near future.

I was recently deploying a SSIS project to another server recently and I encountered the following error:

Microsoft Visual Studio

——————————

Cannot process request because the process (114328) has exited.

I searched on the web to find out what could be causing this error. Project opened on my development machine executed flawlessly. Of course, we cannot use my development machine as an execution server , so I had to find the answer.

When we create a SSIS project in SSDT/Visual Studio 2017, the deployment target server is set to SQL Server 2017. I hadn’t change it when I created the project and was tryin to execute a package on SSIS 2016…this was my mistake.

 

 

 

 

 

 

I simply set the Deployment Target Server (right-click on the SSIS project à Configuration Properties) of my dev machine SSIS project to SQL Server 2016 and the issue was gone!

Simple things like that sometimes make us loose lots of valuable time! Hopefully this article will help some of you as the error message I got was not really helpful.

 

Databricks-cli is used for the Databricks administration. With it, we can manage the following items:

• Clusters:     Utility to interact with Databricks clusters.
• Configure:     Configures host and authentication info for the CLI.
• Fs:         Utility to interact with DBFS.
• Groups:        Utility to interact with Databricks groups.
• Jobs:        Utility to interact with jobs.
• Libraries:    Utility to interact with libraries.
• Runs:         Utility to interact with the jobs runs.
• Secrets:     Utility to interact with Databricks secret API.
• Stack:        Utility to deploy and download Databricks resource stacks.
• Workspace :     Utility to interact with the Databricks workspace.

The remainder of this page will describe how to setup Databricks-cli.

Requirements

We need an access to a Databricks workspace’s token. Also, Python has to be installed on the developer’s PC.

Python installation

From https://www.python.org/downloads/windows/, download the latest Windows version as shown in the following screenshot. You can opt for the web or full installer, One or the other doesn’t matter.

Once the setup starts, you’ll get the following screen (the Python version may differ).

Make sure that the two checkboxes are selected :

• Install launcher for all users
• Add Python to PATH

Once the installation completes, go to the environment variables of your PC:

Click on environment variables

Select Path in the “User variables” (upper window) and  click on “Edit” as highlighted below.

Click on New as shown below

Enter the following string:

%USERPROFILE%\AppData\Local\Programs\Python\Python37\Scripts

And click on OK in both windows to save the path as shown below.

Open a command window and type the following command:

pip3 install databricks-cli

The installation will go on. At the end of it. Open to the %USERPROFILE%\AppData\Local\Programs\Python\Python37\Scripts folder and you should see  “Databricks.exe” as shown in the following screenshot.

Now, back to the command window, type the following command:

databricks --version 

The version of databricks should now be displayed as shown below.

We’re now ready to configure databricks-cli.

Databricks-cli configuration

Once databricks-cli is installed, we have to connect to an existing Databricks workspace. In order to do so, we’ll use the following command:

databricks configure --token

as shown in the following screenshot.

The Databricks host is where  Azure region where the workspace has been created; in our case, East US. The token can be obtained from the workspace itself. Here’s the procedure to do it. Open the Databricks workspace you want to link to the databricks-cli and follow this link to create the token for the workspace.

AT my client’s place we’re using Databricks in conjunction with Azure Data Factory to transform data coming from HTTP connections. These connections data end up in an Azure blob. When we’re receiving JSON data, Databricks and most Azure components knows how to deal such data. Databricks have JSON libraries already available for us to use.

We start receiving XML files from a provider lately. Using Databricks, I thought that I would be able to load the data in a data frame as easily than I am doing with JSON from the Azure blob storage. Here’s the command I use in Python to load JSON data:

df = spark.read.option(“multiLine”, True). json(completeFilePath)

The command tells Databricks to load my “completeFilePath” content from my blob storage into a data frame called “df”. I use the “multiline” option because the JSON data is spanning on multiple lines.

Naively, I thought that reading an XML file would be as easy. I just have to issue a command like the following in Python:

df = spark.read.option(“multiLine”, True). xml(completeXMLFilePath)

Unfortunately, this is not that easy. There’s no native XML library available with Databricks when we create a workspace. We need to load one. After some research on the web, I found this link on Github: https://github.com/databricks/spark-xml. In the document, we can download the library and we need to load it in the notebook. Here are this instructions on how to refer to the libfrary in the notebook.

Spark compiled with Scala 2.11
$SPARK_HOME/bin/spark-shell –packages com.databricks:spark-xml_2.11:0.4.1

From a Databricks notebook perspective, this is not obvious how to attach the library to it. We cannot use this code anywhere in the notebook. I don’t pretend to be a Databricks expert though but even when I looked at the cluster configuration, I could not find a way to attach such library as shown in the following screenshot

Even not from the Libraries tab:

Doing more research on the web pointed me to the right place. From the notebook folder menu, we click on the drop down arrow, select Create and then Library from the submenu that appears as shown below.

The following screen appears. Select Maven Coordinate as source and click on the “Serach Spark Packages and Maven Central button after.

From the search page, type “xml” and click on the search magnifier icon as shown below. From the list that appears in the center, select “spark-xml”, choose the release that matches your cluster, 2.11 in my case and click on “+ Select” right beside it as shown below.

The screen close and you’re back to the “Create Library” screen. Click on the “Create Library” button at the bottom of the screen.

The next screen allows us to attach the library to a cluster. Click on the “Attach automatically to all clusters.” If you want to be able to use it with any cluster you create whether it’s a job or interactive cluster. Since we’re calling our notebook from Azure Data Factory, it’ll be a job cluster. This checkbox has to be checked for us.

When we select the checkbox, we get a confirmation window, click on “Confirm”. You should see that the library is attached once done.

Going back to the Cluster’s library tab, we now see that the library is attached to it.

Voilà! The library is attached and ready to use. From our notebook, we can now load a file any issuing the following command:

df = sqlContext.read.format(‘xml’).options(rootTag=”‘<my root tag>”).load(completeXMLFilePath)

And the dataframe “df” gets loaded with the “completeXMLFilePath” file content from our blob storage.

Hope this article will save you some time as I spend a couple of hours to find out how to attach the XML library to my clusters.