Sunday, November 9, 2014

Creating ETL Script

So far, this assignment has proven the most challenging. There were many issues that brought me to a halt, but eventually got resolved.

The first thing that I did was review the transformation spreadsheet and fill out as many transformations as I could just by looking at the DW design



I wasn't able to get them all, but I did get a lot.
After that, I began creating the dimension tables in the test script.
This test script made it easy to go through, step by step, and test each line of code individually.
Once I had working code, I copied that into the final script.

The date table was the most difficult, because I wasn't able to initially write the code for the transformation and have it show up as the correct data type. However, Randal left us a clue in the spreadsheet on how to write the code to convert a date time into an nvarchar and then cast that into an integer. 


Once this issue was resolved, the only other major issue that I had was the fact table. I initially attempted to use the on-board tools to generate join code, but found that to be more tedious and hassle than it was worth. I much preferred to write the joins manually.

While writing the joins, I learned that the order in which the tables are joined is important. I needed to first write the join to merge the Orders and OrderDetails table from the DB before I was able to write the joins within the DW for the surrogate key information.


After that, my script properly populated the DW with data and was able to be run over and over.

No comments:

Post a Comment