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.

Sunday, November 2, 2014

Creating The Data Warehouse

When creating the DWNorthwindLite data warehouse, I pulled the information from the DW design and input it into the Table Designer utility to generate code.
I first opened the utility to correctly input the column names and data types into the table. After that, I used the Generate Change Script option within the Table Designer to create some code.
Once the code is created, I copied and pasted the generated code into my DWNorthwindLite script window.
This gave me a good base of code to work with before manually creating the foreign key restraints. This proved to be more difficult, since I decided to do it manually. I ended up reformatting a lot of the code to match the way it appeared in the book, since I was using that for my main reference.
I had one issue with properly creating the foreign key restraints. With the way I had my code written, none of the foreign key relationships would be created. After creating my database, I would get an error about not having a CustomersKey primary key in my DimCustomers table.

On top of that, the table diagram wouldn't show any table relationships.

After reviewing the code that I had generated in the Table Creator and cross referencing that with the PubsDW from the book material, I realized that I had too many primary keys in my dimension tables. 
I reviewed the course material and fixed my code to have only one primary key per dimension table, so the code looked like this: 

Once I had successfully troubleshooted this issue, my database was able to execute successfully.

The lesson that I learned in this exercise is to be careful when using the Table Creator utility. Since I had created my tables incorrectly to begin with, the code that was generated wouldn't allow my script to work.