Dmension growing along with FACT

[jcoira]

Hi,

I am designning a data mart and have a problem with one dimension table growing as fast as the fact table, that is slowing down the response times. I am using SQL 2000 and MS Analysis Services.

We receive trucks loaded with oranges and they are graded to get some quality attributes. I have one fact table to keep track of each graded load. The structure is something like (all keys are surrogate keys)

Time_key,
Variety_key (variety of orange carried in the load),
Load_key (key of load received),
NetWeight, Quality1, Quality2,...(all the facts)

The problem is that I have a dimension table to store attributes of the load such as LoadType, Status, RegistrationNumber, etc. For each load I receive and grade I insert one record in the fact table with all the quality attributes, BUT at the same time I insert all attributes of that load in the dimension table. Therefore, the dimension table grows as fast as the fact table.

With less than 200,000 loads the response of my Analysis Services cubes is really bad.

Any suggestions of what I can do to improve the design or/and the response time from MSAS?

Thanks,
// jcoira

 

[johnherman]

see thread960-812938 - same question.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[VerdaFolio]

What you are doing now, namely "...at the same time I insert all attributes of that load in the dimension table" doesn't sound right. Dimension tables should not be used like this. Study a few sample star-schemas in "text book cases", you will figure out the right way in designing your dimension tables (and how they are linked to the fact table).
Good luck!
Colin

 

[AshishVinny]

Can u not identify the groups of load types on the basis of load patterns and try to convert this into a dimension which would not be rather not be dependent on daily transactions.

ANyway the thumb rule is dimensions represent the entities and their attributes. It does not contain any transactional strain. If there is any then that should be converted into fact objects/measures.

Moreover A fact table is always focused on one type of data analysis.

Moreover have u thought that u might be heading towards multi star schema solution...........

 

[jcoira]

Hi AshishVinny,

What is a multi star schema solution?

Thanks,
// jcoira

 

[AshishVinny]

1 Fact for analyzing the load types if there is a need for that...


second fact for load quality analysis.

U could have them merged into 1 fact if u can........