Azure SQL Data Warehouse · Development · Excel

Connecting Tables

Now that we are into our final stage of development for Harvest Data Warehouse, I can now build a new Excel PowerPivot spreadsheet.  I was able to replicate some earlier work I had done in PowerPivot in mere minutes.  It even nicely added related data from MUMD data (Mark Up / Mark Down), where Walmart has specifically changed the price of the product.

My problem was, even though the data related through the Walmart item number, the sales data and the MUMD data couldn’t relate on the timeframe I was choosing.  So I had to setup two different slicers.  I looked for ways to sync the slicers, and hide one of them, however I did not like this approach at all.  It wouldn’t be user friendly.

I already had in my mind that these multi column primary keys in my data tables weren’t going to work, either in SQLDW or PowerPivot, but I wasn’t sure how to solve it.  Enter in Hash encryption.  Armed with these two blog posts I rewrote all the table DDL and dynamic scripting to include hash columns.

  1. http://kejser.org/exploring-hash-functions-in-sql-server/
  2. http://blogs.technet.com/b/canitpro/archive/2012/01/18/the-sql-guy-post-12-understanding-data-hashing-techniques-in-sql-server.aspx

I created a main hash key that a majority of my joins will be on, and other hash keys that I might do joins on.  I tested various encryptions, but with SQLDW it consistently appeared that an MD5 hash was the fastest.  However the 1st blog post listed, mentions that SHA gives you the best distribution.  I’ll be doing testing over the next few days to see how well joins perform in both SQLDW and PowerPivot on the hash.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s