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.