kohera-logo-regular.svg

Transfer AdventureWorksDW2014 into a Document DB Collection

While building data warehouses with a limited budget but where scalability and efficiency are key issues, there are some good reasons why you could look into Document-based NoSQL engines. Normally you would expect this approach for new data warehouses where explosive growth is expected, or the growth is unknown.

This blogpost on the other hand is all about testing and eventually migrating existing data warehouses to a DocumentDB if your current data warehouse is still needed but you also want to harvest the power of the NoSQL engines. Should Pollybase (now available on the high end APS systems) ever become available for everyone this would be much easier, as we don’t know if this will ever happen, we’ll do it the hard way.

The goal of this blogpost is to transfer a dataset from the Adventureworks DB and query it in the same way that you would on your own DWH, but running on a DocumentDB NoSQL database. This blogpost is not intended to compare any RDBMS based data warehousing to Document-based data warehousing. It’s purely to demonstrate that you can actually migrate a dataset from a pure RDBMS data structure and transfer it into a document-based NoSQL Engine and run data warehouse queries.

JSON vs Fact-Dimension

First of all we have to build our Fact-Dimension hierarchy into a JSON-file. Knowing that fact tables are notorious for having many columns, we don’t want to go typing this structure manually to eliminate unnecessary human errors. So we’ll try to use scripting as much as possible.

Query To Transform a Table into JSON Format

To facilitate this process, I’ve written a piece of TSQL that will transfer a table into JSON format, but in a flat hierarchy. This piece of code will generate one line of JSON for each row in the table.

 

DECLARE @SourceTable sysname='DimCurrency'
DECLARE @SchemaName sysname='dbo'
DECLARE @AantalCols int
DECLARE @ColNum int=1
DECLARE @ColName sysname
DECLARE @CMD nvarchar(4000)
DECLARE @ExportRows varchar(15)='(100)PERCENT '
DECLARE @PKName sysname
 
SET @PKName=(Select top 1 c.name from sys.indexes i
    INNER JOIN sys.index_columns ic on i.index_id = IC.index_id and i.object_id=ic.object_id
    INNER JOIN sys.columns c on c.column_id=ic.column_id and ic.object_id=i.object_id
where i.is_primary_key=1 and i.object_id=object_id(@SourceTable))

 

SET NOCOUNT ON;
BEGIN TRY
DROP TABLE #TableData
END TRY
BEGIN CATCH END CATCH

select
    ORDINAL_POSITION,
    COLUMN_NAME
    INTO #TableData
from
    INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @SourceTable AND TABLE_SCHEMA=@SchemaName
    SET @AantalCols = @@ROWCOUNT

 
SET @ColNum=1
SET @CMD='SELECT TOP'+@ExportRows+' ''{"'+@SourceTable+'":[{''+'
WHILE @ColNum <= @AantalCols
BEGIN
    SET @ColName = (select top 1 COLUMN_NAME from #TableData where ORDINAL_POSITION=@ColNum)
    SET @CMD=@CMD+'''"'+ @ColName + '":"''+ISNULL(CAST('+@ColName+' as varchar(max)),''NULL'')+''"'

    IF @ColNum < @AantalCols

    BEGIN
        SET @CMD=@CMD+'''+'',''+'
    END
        SET @ColNum=@ColNum+1
    END
        SET @CMD=@CMD+'}]}''
        FROM '+@SchemaName +'.'+@SourceTable

    PRINT @CMD

EXEC sp_executesql @CMD

Please note the print statement as well, this will be used later on to build the Fact-Dimension relations.

The output of this query is a set of JSON strings containing DImCurrency and its output looks like this:

{"DimCurrency":[{"CurrencyKey":"1","CurrencyAlternateKey":"AFA","CurrencyName":"Afghani"}]}

Building the hierarchy

Ok now we are ready to build a Fact-Dimension hierarchy in JSON by using the print functionality of the previous query

Step 1: Facts

As this is the root node, we remove the FactInternetSales Element Definition and create a select statement that looks like this:

 

select top 1

'{'+

'"ProductKey":"'+ISNULL(CAST(FIS.ProductKey as varchar(max)),'NULL')+'"'+','+'"OrderDateKey":"'+ISNULL(CAST(FIS.OrderDateKey as varchar(max)),'NULL')+'"'+','+'"DueDateKey":"'+ISNULL(CAST(DueDateKey as varchar(max)),'NULL')+'"'+','+'"ShipDateKey":"'+ISNULL(CAST(ShipDateKey as varchar(max)),'NULL')+'"'+','+'"CustomerKey":"'+ISNULL(CAST(FIS.CustomerKey as varchar(max)),'NULL')+'"'+','+'"PromotionKey":"'+ISNULL(CAST(PromotionKey as varchar(max)),'NULL')+'"'+','+'"CurrencyKey":"'+ISNULL(CAST(FIS.CurrencyKey as varchar(max)),'NULL')+'"'+','+'"SalesTerritoryKey":"'+ISNULL(CAST(SalesTerritoryKey as varchar(max)),'NULL')+'"'+','+'"SalesOrderNumber":"'+ISNULL(CAST(SalesOrderNumber as varchar(max)),'NULL')+'"'+','+'"SalesOrderLineNumber":"'+ISNULL(CAST(SalesOrderLineNumber as varchar(max)),'NULL')+'"'+','+'"RevisionNumber":"'+ISNULL(CAST(RevisionNumber as varchar(max)),'NULL')+'"'+','+'"OrderQuantity":"'+ISNULL(CAST(OrderQuantity as varchar(max)),'NULL')+'"'+','+'"UnitPrice":"'+ISNULL(CAST(UnitPrice as varchar(max)),'NULL')+'"'+','+'"ExtendedAmount":"'+ISNULL(CAST(ExtendedAmount as varchar(max)),'NULL')+'"'+','+'"UnitPriceDiscountPct":"'+ISNULL(CAST(UnitPriceDiscountPct as varchar(max)),'NULL')+'"'+','+'"DiscountAmount":"'+ISNULL(CAST(DiscountAmount as varchar(max)),'NULL')+'"'+','+'"ProductStandardCost":"'+ISNULL(CAST(ProductStandardCost as varchar(max)),'NULL')+'"'+','+'"TotalProductCost":"'+ISNULL(CAST(TotalProductCost as varchar(max)),'NULL')+'"'+','+'"SalesAmount":"'+ISNULL(CAST(SalesAmount as varchar(max)),'NULL')+'"'+','+'"TaxAmt":"'+ISNULL(CAST(TaxAmt as varchar(max)),'NULL')+'"'+','+'"Freight":"'+ISNULL(CAST(Freight as varchar(max)),'NULL')+'"'+','+'"CarrierTrackingNumber":"'+ISNULL(CAST(CarrierTrackingNumber as varchar(max)),'NULL')+'"'+','+'"CustomerPONumber":"'+ISNULL(CAST(CustomerPONumber as varchar(max)),'NULL')+'"'+','+'"OrderDate":"'+ISNULL(CAST(OrderDate as varchar(max)),'NULL')+'"'+','+'"DueDate":"'+ISNULL(CAST(DueDate as varchar(max)),'NULL')+'"'+','+'"ShipDate":"'+ISNULL(CAST(ShipDate as varchar(max)),'NULL')+'"'

From [dbo].[FactInternetSales] FIS

Step 2: Dimensions

Now let’s add our Customer information to the JSON Files. By using the printed output from our TSQL generator we create the following statement (Code has been concatenated at the … for readability).

 

select top 1

'{'+

'"ProductKey":"'+ISNULL(CAST(FIS.ProductKey as varchar(max)),'NULL')+'"'+','+'"OrderDateKey":"'+ISNULL(CAST(FIS.OrderDateKey as …

ShipDate as varchar(max)),'NULL')+'",'+

'"Customer":['+

'"CustomerKey":"'+ISNULL(CAST(C.CustomerKey as varchar(max)),'NULL')+'"'+','+'"GeographyKey":"'+ISNULL(CAST(GeographyKey as …

varchar(max)),'NULL')+'"'+','+'"DateFirstPurchase":"'+ISNULL(CAST(DateFirstPurchase as varchar(max)),'NULL')+'"'+','+'"CommuteDistance":"'+ISNULL(CAST(CommuteDistance as varchar(max)),'NULL')+'"'

From [dbo].[FactInternetSales] FIS

INNER JOIN DimProduct DP on DP.ProductKey=FIS.ProductKey

INNER JOIN DimCustomer C on C.CustomerKey=FIS.CustomerKey

By using exactly the same technique we also added the Dimcurrency dimension.

Generating the JSON

Now we have created a TSQL script that is capable of generating a denormalized set of JSON files, where every JSON file has the following structure:


{
    "ProductKey":"310",
    "OrderDateKey":"20101229",
    "DueDateKey":"20110110",
    "ShipDateKey":"20110105",
    "CustomerKey":"21768",
    "PromotionKey":"1",
    "CurrencyKey":"19",
    "SalesTerritoryKey":"6",
    "SalesOrderNumber":"SO43697",
    "SalesOrderLineNumber":"1",
    "RevisionNumber":"1",
    "OrderQuantity":"1",
    "UnitPrice":"3578.27",
    "ExtendedAmount":"3578.27",
    "UnitPriceDiscountPct":"0",
    "DiscountAmount":"0",
    "ProductStandardCost":"2171.29",
    "TotalProductCost":"2171.29",
    "SalesAmount":"3578.27",
    "TaxAmt":"286.26",
    "Freight":"89.46",
    "CarrierTrackingNumber":"NULL",
    "CustomerPONumber":"NULL",
    "OrderDate":"Dec 29 2010 12:00AM",
    "DueDate":"Jan 10 2011 12:00AM",
    "ShipDate":"Jan  5 2011 12:00AM",
    "Customer":
       [{
           "CustomerKey":"21768",
           "GeographyKey":"53",
           "CustomerAlternateKey":"AW00021768",
           "Title":"NULL",
           "FirstName":"Cole",
           "MiddleName":"A",
           "LastName":"Watson",
           "NameStyle":"0",
           "BirthDate":"1952-02-19",
           "MaritalStatus":"S",
           "Suffix":"NULL",
           "Gender":"M",
           "EmailAddress":
           "cole1@adventure-works.com",
           "YearlyIncome":"70000.00",
           "TotalChildren":"5",
           "NumberChildrenAtHome":"0",
           "EnglishEducation":"Bachelors",
           "SpanishEducation":"Licenciatura",
           "FrenchEducation":"Bac + 4",
           "EnglishOccupation":"Management",
           "SpanishOccupation":"Gestión",
           "FrenchOccupation":"Direction",
           "HouseOwnerFlag":"1",
           "NumberCarsOwned":"3",
           "AddressLine1":"601 Asilomar Dr.",
           "AddressLine2":"NULL",
           "Phone":"110-555-0129",
           "DateFirstPurchase":"2010-12-29",
           "CommuteDistance":"10+ Miles"
       }],

    "Currency":
       [{
           "CurrencyKey":"19",
           "CurrencyAlternateKey":"CAD",
           "CurrencyName":"Canadian Dollar"
       }]
}

After exporting and uploading these files into our DocumentDB we’re ready to run DWH like queries directly on the DocumentDB database.

 

Querying the DocumentDB DWH

SELECT C.Title, C.FirstName,I.UnitPrice,Cur.CurrencyName,I.SalesOrderNumber,I.OrderDate

FROM InternetSales AS I

JOIN C in I.Customer

JOIN Cur in I.Currency

WHERE C.FirstName="Cole"

AND Cur.CurrencyName="Canadian Dollar"

AND I.OrderDate >="Dec 29 2010"

AND I.OrderDate <="Dec 30 2010"

This query nicely returns the following data set:

dataset.png

To compare this set with the original, I’ve also ran the query on the original DWH database

SELECT C.Title, C.FirstName,I.UnitPrice,Cur.CurrencyName,I.SalesOrderNumber,I.OrderDate

FROM FactInternetSales AS I

INNER JOIN DIMCustomer C ON I.CustomerKey=C.CustomerKey

INNER JOIN DimCurrency Cur ON I.CurrencyKey = Cur.CurrencyKey

WHERE C.FirstName='Cole'

AND Cur.CurrencyName='Canadian Dollar'

AND I.OrderDate BETWEEN 'Dec 29 2010' AND 'Dec 30 2010'

This query also returns the same dataset:

dataset2.png

To do’s & conclusion

The original JSON generating query could be extended to sniff out all Fact-Dimension relations using the primary key-foreign key relations that exist in a Star-Schema build DWH. By doing this it should be possible to write the completely denormalized JSON equivalent of every fact table of a DWH. This would enabling us to create the JSON files automagically and upload the complete DWH into DocumentDB using a script based approach. This leads to my conclusion that an existing DWH can be transferred to a Document-based DWH in a consequent and controllable manner.

The reworking of these script will be for another, later blogpost where we will try to upload a complete existing DWH into DocumentDB, using scripting technologies.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...