Data Warehouse Script Generation in Microsoft Fabric: A Comprehensive Guide

A data warehouse in Microsoft Fabric is a centralized repository that aggregates and stores data from various sources within an organization. It is meticulously designed to facilitate efficient querying, analysis, and reporting, serving as a strategic asset for businesses across diverse industries. 

Sometimes developers intend to reuse the capabilities of the table creation scripts that are generated in SQL Server Management Studio (SSMS) to create tables within the Datawarehouse, since they require making some changes to the schemas of said tables, or they simply want to reuse only some of the tables. 

If you use a table creation script generated by SSMS, you will find that when you run that script in Fabric, errors appear related to the data types used in the source table and the use of PRIMARY KEY clauses. 

Therefore, we will first discuss what table creation scripts are in SSMS and why errors occur in Fabric, which are not actually errors. 

This article describes a workaround technique that allows you to use the scripts generated by Fabric with the same purpose, that is, the creation of new tables in the Datawarehouse with only slight differences with a similar existent table. 

Using CREATE TABLE scripts in SSMS and similar scripts in Fabric. 

SQL Server Management Studio allows you to generate, for any existing table, a script with the CREATE TABLE statement, in which you want to change the table name, columns names and so forth.  

Figure 1 shows how you can generate this type of script and Figure 2 shows the resulting script. 

Figure 1. Obtaining the CREATE TABLE script in SQL Server Management Studio. 

After selecting “New Query Editor Window”, the script is shown at the right:  

Figure 2. The resulting query script. 

If you try to copy and run that script to create a table in a Fabric’s Datawarehouse, several errors occur: 

The data type 'nvarchar(100)' is not supported in this edition of SQL Server. 

The PRIMARY KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server. 

Why do these error messages occur? 

1. Fabric does not support several data types as SSMS does. 

It is very common to select the NVARCHAR data type that SSMS proposes, when one is creating a column that will store variable length text. However, NVARCHAR is not supported in Fabric, VARCHAR can be used instead. 

Data types - Microsoft Fabric | Microsoft Learn explains what data types are being supported by Microsoft Fabric for the tables in a Datawarehouse.  

Consider the following table to know how to substitute data types conveniently: 

It is important to note that Fabric performs automatic conversion of disallowed data types when ingesting tables and their data into the associated Lakehouse, so if you are not planning on reusing table creation scripts, this issue should not worry you. 

2. PRIMARY KEY clause is not accepted by Fabric’s CREATE TABLE queries. 

Fabric allows defining constraints only through ALTER TABLE. There is not a visual user interface for defining such constraints as in SSMS, neither primary nor foreign keys constraints. 

Primary Keys. 

Fabric’s syntax for defining a Primary Key is: [2] 

CREATE TABLE PrimaryKeyTable (c1 INT NOT NULL, c2 INT); 
ALTER TABLE PrimaryKeyTable ADD CONSTRAINT PK_PrimaryKeyTable PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED; 

For instance, within Fabric, we can generate the script to create the 'Course' table, as illustrated in Figure 4. 

Selecting “Table” produces a script that is shown in Figure 5. Notice that this script refers only to columns definitions. 

Figure 4. Creation of CREATE TABLE script of the “Course” table that belongs to a Datawarehouse in Fabric. 

Figure 5. The CREATE TABLE script can be managed as a reusable query.  

You can reuse the above script to create another table named Course2 and write inside that query the ALTER TABLE sentence capable of defining the PRIMARY KEY constraint, as is shown in Figure 6. 

Figure 6. ALTER TABLE clause added inside the CREATE TABLE script to define the PRIMARY KEY constraint for the “Course2” table. 

Foreign Keys. 

The use of foreign keys is essential in the design of a Datawarehouse, since the dimension tables contain the primary keys that will be foreign keys within a fact table. [3] 

Let's consider the following example: 

SSMS does not automatically generate foreign key scripts, however, the definition of these restrictions is necessary in the Datawarehouse that will be managed in Fabric. 

Figure 7 shows what you do in SSMS to create, in a visual interface, the FOREIGN KEY constraint in the Course table. 

Figure 7. A FOREIGN KEY definition for the table Course in SQL Server Management Studio. 

Figure 8 shows DepartmentID within the Course table as a Foreign Key with respect to the Primary Key DepartmentID of the Department Table. 

Figure 8. The FK Constraint created for the Course table in SSMS. 

 The syntax to create Foreign Keys in Fabric is: [3] 

CREATE TABLE ForeignKeyReferenceTable (c1 INT NOT NULL); 
ALTER TABLE ForeignKeyReferenceTable ADD CONSTRAINT PK_ForeignKeyReferenceTable PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED; 
CREATE TABLE ForeignKeyTable (c1 INT NOT NULL, c2 INT); 
ALTER TABLE ForeignKeyTable ADD CONSTRAINT FK_ForeignKeyTablec1 FOREIGN KEY (c1) REFERENCES ForeignKeyReferenceTable (c1) NOT ENFORCED;

First, you must specify that Department has DepartmentID as its primary key, because if not, this error happens: (see Figure 9) 

Figure 9. Error in Fabric when you try to create a foreign key without defining the primary key that must be referred. 

So, the primary key “DepartmentID” is declared in Department (see Figure 10). 

Figure 10. Definition of the primary key in Department table adding the ALTER TABLE PRIMARY KEY clause to the script generated by Fabric. 

When you select Run, the query is executed successfully, as you see in Figure 11. 

Figure 11. Query defining PRIMARY KEY constraint in Department table succeeded. 

After the primary key has been applied, the Foreign Key constraint is added to the Course table, as you see in Figure 12. 

Figure 12. ALTER TABLE clause FOR the FOREIGN KEY constraint to the Course table, added to the script generated by Fabric.

Recommendations and other trends to follow. 

With Fabric, you can develop your business intelligence solution from start to finish. This includes utilizing diverse data sources, ingesting data from these sources, and storing it using advanced data integration features provided by Data Factory. Fabric also supports real-time analytics, allowing you to analyze data directly from its sources. Finally, Fabric enables seamless visualization of your analyzed data. 

Therefore, you do not have to proceed the other way around: from the origin to Fabric to try to reuse features of transactional database managers. 

Instead of using connections to a Fabric Datawarehouse within SSMS, we should consider Fabric as the overall solution manager. 

If you are new to Microsoft Fabric, the following resources are useful. 

Resources: 

1. Data types - Microsoft Fabric | Microsoft Learn 
2. Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn 
3. Tables in data warehousing - Microsoft Fabric | Microsoft Learn 
4. Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft Learn