Issue with Cube Build with huge dimesnions

[cognos_dev]

Hello all,

I have an enormously huge cube (1GB) with 10 measures and 40 dimensions. Each of 40 dimensions having categories under it for e.g., address dim has country,state,city,postal cd, etc. This cube accessing nearly 10 mega dim & 1 fact table and brings out 2 million records finally. Since each and every dim table connected are really huge, I am creating a package to have QS with 10 table columns connected through the SQL and conditions. In this way I am pushing the entire load to the backend database server for joins and sort.

If I need to have 10 different data sources in cube model, then connecting through the common key as the last level in each dim is making life more troublesome as in some case like gender, address, indicators, groups etc need a unique common column at the end which multiplies my categories to more than 2 million.

Now the cube, which was running usuing alot of database resource, falied to get created as it hits the DB system temp space. I am getting error saying DB system space is full.
How can I remodel the cube to run using optimal resources and not load the DB too much and avoid filling the temp space(100GB).

If I have 10 dims as seperate data sources and then suppress the unique columns will avoid the unnecessary categories? Please share your comments which could really help me solve this issue.
I am sure this is a practical situation and some one who have faced might have a better solution for this.

Regards,
S

[rockytopmark]

Dimensions... suppress unique... what is that???  Dimensions SHOULD, by NATURE, already be unique!!!

Are you sure your Dimension Queries are truly JUST Dimensions or are they built from Facts, to act as dimensions?

Dimensions, if at all possible (and this is indeed usually very possible), should be build from a Master/Dimension tables and NEVER from a Fact table.  If Facts are part of the dimension queries, then this explains your problem.

[cognos_dev]

I meant my dimensions are too big. They dont have facts in them but have too many records in millions. All my dimensions are having millions of records not like usual dimensions.

My question reg. the suppress is that,

For detailed e.g.,

Student -Dim (Student_Id,stud_gender,stud_age,stud_country,stud_city,stud_state,stud_member,stud_active) - millions of records

Class - Dim (Class_Id,class_strength,class_active,class_structure,class_program), - millions of records

Course-Dim (Course_Id,course_length,course_chapter,etc) & - millions of records

Student Marks-Fact (Stud_id,Class_Id, Course_Id, marks-fact). - (millions of course- transaction data)

if i need to bring 3 dims and 1 fact as seperate datasources and if in the cube i have to model in such a way that student gender from student data source is to be in the model. then my levels will be student gender & stude_id. As Stud_id is the common column between the 2 tables. but if there were too many records in student dim (millions) and also in Student marks fact(millions), so how will be the performance?

Similarly i have to bring stud_address -> address-country-state-city-stud_id and other dimensions in course, class ,etc.

I am having some DB issue as it occupies alot of space due to this dimension table size.

[almeids]

Something sounds amiss here.  This must be quite a large institution to be offering millions of courses and classes...or there is something wrong in your dimensional queries.
Also, design-wise, 40 dimensions is way too many to be practical in a PowerPlay cube.  You should really consider stripping that down to the essentials and/or using drillthrough to get to the details.