How to create and use keys (A work around for Primary Keys) in Fabric data warehouses – FourMoo

When I started looking into using the data warehouses feature in Fabric, I did see that there were limitations on Primary Key columns.

Below is my blog post on how I still use keys in my data warehouse, instead of using GUID’s which to me are long and hard to use.

In my example I am going to create a simple data warehouse which is going to consist of two-dimension tables (Date and Country) and a fact table with the Sales amounts.

I loaded my data into the lakehouse as shown below.

Creating my Warehouse

The first step is I needed to create a warehouse where I could create my tables.

To do this I changed the experience to Data Warehouse.

I then clicked on Warehouse and gave it the name of DW-FourMoo and clicked on Create.

Now in order to access the data I then created my warehouse table by importing the data from my lakehouse into the warehouse (Yes, I know it can be a bit confusing with all these similar names).

I then needed to load my source data into my warehouse table.

To do this I went into my Warehouse, clicked on Get Data and then selected “New Dataflow Gen2”

I then loaded my data via the Dataflow Gen2 into my warehouse table (I am not going to put in how I did it as it is not part of this blog post and I have covered this before in my other blog post “”).

Once loaded I could then my table in my warehouse as shown below.

I could now start creating my dimension and fact tables with the required keys.

Creating the dimension tables

Next, to create my dimension tables I used the following TSQL code below.

The code below is for my Country Dimension

Create table DW_FourMoo.dbo.tb_DimCountry AS
Select distinct country_code, 
-- This is the column that I wanted to create the Key on
ROW_NUMBER() OVER(ORDER BY country_code) as CountrySK
from DW_FourMoo.dbo.staging_sales

I could then see that the query ran successfully.

And I could see the table and the actual Key values in my tb_DimCountry table.

I then used the following TSQL code to create the Date dimension.

Create table DW_FourMoo.dbo.tb_DimDate AS
Select distinct CAST(CAST(time_ref as VARCHAR(10)) as DATE) as Date, 
-- This is the column that I wanted to create the Key on
ROW_NUMBER() OVER(ORDER BY time_ref) as DateSK
from DW_FourMoo.dbo.staging_sales

Once completed I could then see the table and values as shown below.

I now had my Date and Country dimensions.

Create the Fact Table

I am now at the point that I can create my fact table.

To do this I used the following TSQL code below.

Create table DW_FourMoo.dbo.tb_FactSales AS

SELECT   
       Country.CountrySK
      ,SUM([value]) as SalesAmount
      ,[status]
      ,D.Date
  FROM [dbo].[staging_sales] as S
    inner join dbo.tb_DimCountry as Country 
        on S.country_code = Country.country_code
    Inner join dbo.tb_DimDate as D
        on S.time_ref = D.Date

Group by 
    Country.CountrySK
    ,[status]
    ,D.Date

I could then see my fact table as shown below.

The next challenge is what to do when I want to add or update rows in my dimension table. I want to keep my existing keys and only add new keys.

Add rows to my Dimension Table

In the code below I will show you how I added new keys to my dimension table.

Declare @MaxCountrySK as BigInt = (Select max(CountrySK) from DW.dbo.tb_DimCountry)

Insert into DW.dbo.tb_DimCustomer

SELECT distinct
 [country_code]
,ROW_NUMBER() OVER(ORDER BY country_code) + @MaxCountrySK as CountrySK


FROM [DW_FourMoo].[dbo].[staging_sales]
where country_code not in 
    (
        Select country_code
        from DW.dbo.tb_DimCountry
    )