Analysis Services 2005 Design Challenge

Hi to all, I’m designing a SSAS 2005 cube based on an Oracle data source

We have a classical star schema with several millions of records into the fact table.

Now everything goes fine since dimensions are quite small (the greatest one has 100000 records), but users are asking me the possibility of view also more detailed data such order_number, client fiscal code, etc…and this data it has been stored into the fact table as a fact degenerate dimension.

Now I don’t want to load into the cube this degenerate dimension because it contains as I said too many records.

I was thinking to drill-through functionality (achieved setting the storage mode of the degenerate dimension to ROLAP) but after several tests it seems very very slow (it launches queries that incredibly big grouping all the dimensions and all the fields of the fact table or other times launches several heavy queries catching all the distinct values of order_number, client fiscal code, etc…) and some times the client itself (OWC) hangs and I have to kill it.

Note that these tests are done on the development environment where the fact table contains more or less only 70000 records!

I tried also to query directly this degenerate dimension (without drill-through) but the results are the same.

Any suggestions?

Thanks.

Alberto

Try using MOLAP dimensions. They should scale pretty well in AS2005.

You should be able to handle several milion members with MOLAP dimensions.

Hope that helps.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.