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.















No comments:

Post a Comment