Number of Cubes in Analysis Services-Any Limits ? 1

[jophyv]

Hi,

Can anyone please tell me the maximum number of cubes that can be included in Microsoft Analysis Services 2000.

We are doing a data warehousing project which will be an extension to an existing product.This product is higly customizable and ideally each client should have seperate cubes to address their independent data warehousingrequirements.We require atleast 600 cubes to be built in Analysis Services !(This number may go up over a period of time) PLease let me know the feasibility of doing that in Analysis Services (based on your experience).And also let me know the hardware required to suppport such an application.

Also please let me know for the business requirement mentioned above,if it is better to keep seperate databases for each clients or to maintain seperate cubes for each client in one database

Somebody please give me some inputs.
Thank you in advance

Regards
Jophy

 

[MDXer]

SOL shows no limi to the number of cubes you can have to a server or DB. however there does exist a 65,535 limit to the number of dimensions in a database. I believe you will hit a hardware wall before you hit software limitation wall. 600 cubes in a single database? Do able but probably not recommended. 600 Cubes split among simliar databases on the same server? Do able but again probably not recommended.

Analysis services does some things to increase performance that can be gotchas in large implementations.

1) Memory Analysis Services currently will only address 3gig of memory.

2) Meta-data. All meta data for all objects in Analysis services is read into memory upon the start of the service.

3) Dimensions. All dimension members are read into memory upon the start of the service. This is why shared dimensions are preffered over multiple private dimensions.

4) Custom security (Cell level or Dimension member) Does not filter out the members of the dimension the user is restricted from seeing but rather creates a copy of the dimension in memory with the membewrs allowed to be seen. This copy remains in memory until either restart of the service or reboot of the server.

5) Partitioning. Increases build and query perfrormance but adds to the meta-data.

6) Distinct Count. If you build a cube that contains a distinct count measure the cube is processed in memory where as non distinct count aggs are processed to disk. Any aggregations placed upon these cubes are also proccessed in memory. I have also worked on cubes that contained only a distinct count measure and could have no more than 15-30 aggs due to memory usage.

I have personally worked on servers that have had so many AS objects that the server has ran out of memory just doing editing functions.

Processing performance on a single server with 600 cubes would be less than desirable. custom applications would allow you to achieve some parallel processing but probably not to the degree to hit any predefined build window. TO ensure data updates occured in a timely manner you would have to process very small amounts of data. if 1 month took 15 minutes to processes you could be looking at 150 hours of processing. Yes again you can build custom apps to allow you to parallel process but remember then the time of each process will slightly increase.

Cube querying could also be an area where this number of cubes would impact performance. Remember it isn't how many users can a cube support it is how many users can the server support. You would probably need to increase processing thread and such which requires detailed knowledge of the Analysis Services Registry and it keys as this is where tuning would need to occur.

I am not saying that Analysis services is not able to satisfy your needs, I'm saying you may want to increase the scope and scale of your hardware requirements.

Personally having worked on one of the Largest Analysis Services implementations, I can say things never scale the way you want them to. You may consider Multiple databases per server for security but then if you have shared dimensions you eat the overhead of duplicating them to another DB. Also multiple servers is a definate way to go. The biggest and fastest servers you can afford lots of cpu power, lots of HD (with these numbers a san is a good idea) and 4gigs of memory until the mem cap is removed 3 for AS and 1 for the os and backup software.

Feel free to email me at

nobley@no.spam.cox.net
<<remove no.spam>>

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[Acius]

There's a limit of 255 levels in a cube.
We were trying to build a cube with a heap of different hierarchies (ragged and parent-child so could use virtual dimensions) and hit this one.