Cube with about 40 dimensions + BI layer anyone ever tried?

[Jeroen Eynikel]

Hi,

short: wondering whether anyone has ever tried this and if so what the results were.

TM1: single very sparse cube, no calculation rules except for a few C: level ones, but about 40 dimensions. Varying in number of elements between a few, to tens of thousands for the largest 10 or so.
I am guessing it's size is going to be 20-40 GB of RAM.

Front-End:

Business Insight Advanced. End-users will freely drag and drop from these 40 dimensions as they chose, though on average I'd say they will probably only chose a few at the same time.

There will be about 20-30 concurrent users querying this cube.

(The problem I am facing is that I have been called in on a project whose UAT was supposed to have started already and without going into details on what they have done exactly, the system above is about the only option I can think off right now that would get their current system to a state that would actually meet the business requirements they agreed on, considering I have very little time and have to make do with what is already there.)

[garry cook]

I've had to deal with a 21 dim cube recently which I inherited and promptly chopped into three cubes as the performance was awful for the level of data it tried to pull back (really, really awful). Suspect this will be even more pronounced if going through an additional BI layer and approach 40 dims.

I tend to think that if you're over the traditional 16 dim limit you need a very good reason to be going over it and if you're getting to over 20, you're probably using the wrong design or tool. Realise from your post that you're in the classic "None of this works and nobody's raised the red flag until the last minute so get on your shining white armour and ride that horse over here to save us!" position but I'd put the 40 dim cube to one side and if there really is no option other than this, tell them that's the case and they need to extend the project.

Tough to say, tough to hear but in the long run, I wouldn't want to be supporting a beast like the one you describe and post release it'll be difficult to claw it back and redesign if there's a reasonable sized userbase. Last thing is that if anyone tries to access the cube through traditional TM1 access routes, that's going to be an incredibly difficult experience to navigate through such a large cube.

Just my 2p.

[qml]

I have seen some monster cubes in production that had around 40 dimensions and Cognos BI as the front end. I cannot say much good about this design except that it's a miracle that it kinda-sorta works (and definitely tests how far you can push TM1). You can expect to count query run times in minutes and you can forget about using rules. Just because some things can be done, doesn't mean they should be done.

[Wim Gielis]

Hello

I did some quick tests.
I created a cube using the first 40 dimensions of my pet TM1 model.
Then I opened up the cube viewer in the Server Explorer. Most of the dimensions have 1 consolidated element as the top consolidation, and this element is in the default subset.
But certainly not all dimensions are like that: a good number of the title dimensions in the cube viewer are just n level elements.

To show 1 cell (a 0 since no data and rules in the cube) took about 2 seconds.
Looking for a certain dimension: a nightmare (as Garry already pointed out).

So I go with what was posted above.

FYI - Excel Perspectives:

Slicing a one-cell cube view to Excel: I get an Application-defined or object-defined error. After that, an invalid procedure call or argument. After that, the spreadsheet is ready.
cell B1 remains empty (not the servername:cubename type of thing). After filling this in manually, the 1 DBRW formula evaluates correctly.
Mind in the DBRW formula: there are brackets to cope with that many arguments:

=DBRW($B$1;($B$2;$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;$B$9;$B$10;$B$11;$B$12;$B$13;$B$14;$B$15;$B$16;$B$17;$B$18;$B$19;$B$20;$B$21;$B$22;$B$23;
$B$24;$B$25;$B$26;$B$27;$B$28;$B$29;$B$30;$B$31;$B$32;$B$33;$B$34;$B$35;$B$36;$B$37;$B$38;$B$39;$A43;$B$40))


Wim

[PlanningDev]

Not to say TM1 can't do this but if there is no write back required you should look into Dynamic Cubes or Powercubes

[Jeroen Eynikel]

As said I am well aware that this is very bad design and that if this is what is really needed (*) there may be other tools more suitable than TM1 for this.

What happened as far as I can tell is that an inexperienced development team let the business users define what should be built. Since these people have no experience with multidimensional analysis yet I think they just though of every single axis that they could potentially want to watch their data against. My guess is that a lot of these things would be much better done using drill-through or may not be needed at all but I currently have neither the time nor the luxury to do a complete redesign and as usual there are a lot of politics involved in this...

(*) which I doubt but I haven't talked to the business users yet.

[Jeroen Eynikel]

And @Wim, I already knew that this would never be workeable using server explorer.

In business insight I hope to get away with it as the dimension selection will be less confusing in there (I think :p)

Anyways I'll test it and see how far I get.

[Wim Gielis]

And @Wim, I already knew that this would never be workeable using server explorer.

I know, I only did a few quick tests as to how long it would calculate, what would happen with a slice, ...
I imagined that, with 40 dimensions, the top half of the Server Explorer cube viewer screen would be filled with dimension elements But that wasn't the case (not to say it is a workable solution in the SE).

[Jeroen Eynikel]

For anyone interested.

I still think it is horrible design but it worked as good as I had hoped (and then some).

The only report we failed was one which would have generated a result set of 250000 lines

Next step is trying to get some more reasonable business requirements though...