Indexing/Unique Key

This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql