Great discussion guys.....that comment about the rounding upon division was something I had not considered.

It sounds like division on DECIMAL(9,2) would produce a result that could be "off" by 1...

Yes, but couldn't a fact table have a surrogate with a secondary composite key that is declared unique ?

Try unchecking those options and do a manual refresh.

However, based on your description, it sounds like this query is not a pass-through and therefore not well optimized.

You should create a...

What are the Connection Properties set to: ??

[ ] Enable background refresh

[ ] Refresh every XX minutes

[ ] Refresh data when opening the file

How long does the query run natively...

Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.

The only problem :

when summing large numbers of rows....that could result in overflow if it is not re-cast,...

Thanks guys (and especially Jeff) for the great responses.

There's a data warehouse manager here who practically made everything varchar(1000).

There are no indices in any of the tables yet, so when...

re: "but they're not suitable for the fact table"

That was my feeling all along.

Thanks for chiming in Eric...and thanks for the reference to the book.

Thanks for chiming in Eric....that was my feeling all along.

Thanks so much for your feedback Steve.

It would really be a great study in design and performance....I wish someone ELSE had already done it !!!

I see what you mean, but what about the FACT table ?

The idea was to place the converted BigInt keys to the dimension tables in there.

Thus, when loading either FACT...

Thanks Steve.

re: "lineage back to the source record".

Good point, but to do the conversion to BigInt, we would have a reference table with the GUID's and new BigInt references.

There would...

Sounds as though you got a bad download of the install package....and/or an error occurred when burning the installation to DVD.

Try re-downloading or burning a new DVD.

re: "with distributed systems"

And I now I finally "see it".

Thanks Steve..... and everyone else for responding.....

re: "GUIDs give you a large space of possibilities."

I understand, but I would never use an Autonumber/Sequence as a key reference to a product or client, etc.

Instead, I would have...

Thanks.....

How does the newid() function work ?

How does it know to create a unique value for a particular table ?

Why use a GUID vs. Autonumber/Sequence ?