Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

10777: Designing a Data Warehouse: Dimension table indexes:

Hi experts:

i am reading about Dimension table indexes, but i do not understand
1. Create a nonclustered primary key index
2. Create a clustered business key index

I can show you an example of each
0
enrique_aeo
Asked:
enrique_aeo
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please do.
0
 
enrique_aeoAuthor Commented:
The code if it is,
I do not understand is the concept, which means that the difference.
When to use one or the other?
0
 
smiliefaceCommented:
The difference between non-clustered and clustered indexes is well documented elsewhere. However in brief the answers to this might be:

A Primary Key is unique, and for something like a data warehouse dimension table where you have "generational" data (changes to the record are written as a new record, and the old record is retained) we often see a "surrogate key" used. The "natural" keys (such as Customer Id will be repeated on generations of data

The clustered index doesn't have to be unique, but defines where the data is stored, as it is a sorted index. This will mean that other strategies, such as storing all the data updated on a specific date together, can be explored. This would enable faster reporting if the report is based on dates.

Essentially, the clustered index is the one you WANT to use for data retrieval. The Primary Key is just a Unique index.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now