PK's

I just started a new job and the data warehouse was constructed a bit
different than what I have seen.
Some of the dimension tables had so many FK's that they had to create a view
on top of 3 dimension tables to simulate it as one.
For example - there are assesments questions for clients whith aproximately
45 unique questions. To accomodate the dimension table the model was
Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
simulate it as one. The reason is all the FK's were part of the PK. The PK
limitiation is 16 columns.Hello Joe,
This sounds like one of the following:
1. This could be a Junk Dimension use to join other fact tables and
other dimensions. Junk dimensions are generally non-conforming.
2. This is possibly a snow flake star schema. The principle is to
normalize the dimensions. The idea is make the ETL process easier to
maintain and save space. But really it creates a complex query
environment for users and doesn't save space.
3. Most likely this is Bad design, if the dimensions have the same
columns and unique keys across the three tables then creating one
dimension table should improve query performance and reduce the load
times.
The 16 column Primary key limit is a limitation of SQL Server 2000. In
SQL 2005 you can extend non clustered indexes with the new include
index function. The Maximum number of columns is 1023.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Joe wrote:
> I just started a new job and the data warehouse was constructed a bit
> different than what I have seen.
> Some of the dimension tables had so many FK's that they had to create a vi
ew
> on top of 3 dimension tables to simulate it as one.
> For example - there are assesments questions for clients whith aproximatel
y
> 45 unique questions. To accomodate the dimension table the model was
> Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
> simulate it as one. The reason is all the FK's were part of the PK. The
PK
> limitiation is 16 columns.|||Hello Joe,
This sounds like one of the following:
1. This could be a Junk Dimension use to join other fact tables and
other dimensions. Junk dimensions are generally non-conforming.
2. This is possibly a snow flake star schema. The principle is to
normalize the dimensions. The idea is make the ETL process easier to
maintain and save space. But really it creates a complex query
environment for users and doesn't save space.
3. Most likely this is Bad design, if the dimensions have the same
columns and unique keys across the three tables then creating one
dimension table should improve query performance and reduce the load
times.
The 16 column Primary key limit is a limitation of SQL Server 2000. In
SQL 2005 you can extend non clustered indexes with the new include
index function. The Maximum number of columns is 1023.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Joe wrote:
> I just started a new job and the data warehouse was constructed a bit
> different than what I have seen.
> Some of the dimension tables had so many FK's that they had to create a vi
ew
> on top of 3 dimension tables to simulate it as one.
> For example - there are assesments questions for clients whith aproximatel
y
> 45 unique questions. To accomodate the dimension table the model was
> Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
> simulate it as one. The reason is all the FK's were part of the PK. The
PK
> limitiation is 16 columns.