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