re-order dimension result

[wuruimao]

I tried the re-order function in a big cube, here's the test result: Percent Change: 20228151.412903%

Would anyone tell me what this result mean?? Thanks.

[lotsaram]

I tried the re-order function in a big cube, here's the test result: Percent Change: 20228151.412903%
Would anyone tell me what this result mean?? Thanks.

If is was a big cube then it is now A VERY BIG CUBE. When reordering dimensions / optimizing cube structure what you want is negative percent change, that is memory consumption AFTER the reorder is LESS than memory consumption BEFORE the reorder. What your result says is that after the reorder the cube is 200,000 times larger than is was before. This is about as far from the desired result that I think it would be possible to get and it goes without saying that you should put the dimension order back to whatever it was.

I haven't ever seen numbers of that magnitude (either way) from a dimension reorder, doesn't mean its not possible though.

[wuruimao]

I tried the re-order function in a big cube, here's the test result: Percent Change: 20228151.412903%
Would anyone tell me what this result mean?? Thanks.

If is was a big cube then it is now A VERY BIG CUBE. When reordering dimensions / optimizing cube structure what you want is negative percent change, that is memory consumption AFTER the reorder is LESS than memory consumption BEFORE the reorder. What your result says is that after the reorder the cube is 200,000 times larger than is was before. This is about as far from the desired result that I think it would be possible to get and it goes without saying that you should put the dimension order back to whatever it was.

I haven't ever seen numbers of that magnitude (either way) from a dimension reorder, doesn't mean its not possible though.

Thanks for your reply.

I thought this was an abnormal result. However, I tried several times, all the results were the same.
We expect to get a negative percent change as usual so that we can get some memory saving.however, several tests got the same terrible result.

Our problem was:We got a big cube, which was about 20G,when it was loaded to the memory, it occupied abt 60G memory. That's so terrible.

[wuruimao]

One more point,

Athought the test result was terrible, the memory usage was just the same as that one before re-order.

[David Usherwood]

I think it would be a very good idea to rebuild the cube from scratch, by exporting stored values to a flat file, saving the rules, recreating the cube and re-applying the rules.
Then, when you reorder and are told you will get a size increase of 20 million percent....
Select Cancel

[wuruimao]

Thanks for your opinion.

I am confused now,abt the data amount in the cube,does it affect the reorder result?

eg: 2 cubes with the same dims and the dims are in the same order. one has 1G data, while the other has 50G data.

does the reorder result just the same?

[qml]

does the reorder result just the same?

No, the gain of any dimension reordering is directly connected with the data you are actually storing in the cube. And not only the amount of data, but also how the data are stored against all of your dimension elements, making your dimensions "dense" or "sparse".

[MarenC]

Hi,

I have been checking our dimension orders and notice that I can gain -20, -50 on some cubes.

But some of the cubes are quite small, so 100mb and another is 1gb.

So my question is, when does dimension reordering become worth it based on the size of the cube?

Maren

[lotsaram]

Hi,

I have been checking our dimension orders and notice that I can gain -20, -50 on some cubes.

But some of the cubes are quite small, so 100mb and another is 1gb.

So my question is, when does dimension reordering become worth it based on the size of the cube?

Maren

This is one of those ultimate "it depends" type questions.
- it depends if Memory is a scarce resource for you and how much of it you have spare
- it depends if other factors may be more important than memory such as ...
. - speed of write back (especially in high volume parallel batch situations)
. - end user query performance

There is a trade-off between the 3 factors of memory, write-back speed & query speed. You can read more here. Whether you decide to optimize for memory depends on which factor is most important. (and by optimizing for one factor you aren't necessarily getting a worse outcome in the other 2).

But all that aside, for any cube that is less than 1 - 2 GB minimum, then no, optimizing dimension order isn't worth it. (Different story in 32 bit server days when the total server memory was limited to 2 GB, but those days are now long gone.)