September 1, 2002 at 6:00 pm
I have a lot of fields of the char type where the size is 2 or 3 characters. I have not broken these fields out into their own dimension tables (linked to the fact table by foreign key constraint) because the advantages didn't seem to offset the disadvantages. The primary disadvantage being that no savings in storage space would be realized by replacing the actual value with a key value of the type smallint or int. Also, breaking them out into a traditional star schema would necessitate substantially more joins to the fact table. Lastly, a sizeable increase in the number of lookups performed during the ETL processes would be realized. Now, my question is this...can a field in a fact table be used as a dimension or as a level in a dimension? Has anyone done this? What, if any, performance hits are there?
Thank you,
Michael
Michael Weiss
Michael Weiss
September 4, 2002 at 9:43 am
Sounds good in theory. AS manager lets you add the same fact table as a dimension table. However, I suspect that it will try to do a self-join when retrieving the result set from SQL Server. That would be very bad! Give it a try and run profiler to capture the query it throws at SQL Server.
I just tried changing a dimension on a test table with your method but I keep getting a timeout error doing the member counts (10 million+ rows). Keep me posted, I'd like to know if this works for you.
September 4, 2002 at 10:11 am
I was concerned that AS would try to do a self-join...the data I am working with does not lend itself at all to a normal star schema as it contains lots of small fields that dont make sense to move off to a dimension table and do all the lookups in the ETL processes that would be required. The timeout issue concerns me as I know AS has a serious problem with this...supposedly was fixed in SP2 but I am not sure of that. Currently, my fact table has 30 million+ rows in it growing at the rate of 10 million+ per month.
I will keep you posted on how things go. However, I am not holding my breath for pleasant, headache free results!
Michael Weiss
Michael Weiss
January 29, 2003 at 6:31 pm
I have done this and in the past have used this setup for what I call "quick_cubes", you can make your ETL processing throw everything to one table and I didn't see a performance hit, the bad part is your SCD's becuase you cannot incrementally update your dimensions
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply