re-order dimension result

Post Reply
wuruimao
Posts: 16
Joined: Fri Sep 30, 2011 5:17 am
OLAP Product: tm1
Version: 9.6
Excel Version: 2003

re-order dimension result

Post by wuruimao » Mon Apr 16, 2012 3:04 am

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
MVP
Posts: 3409
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: re-order dimension result

Post by lotsaram » Mon Apr 16, 2012 7:13 am

wuruimao wrote: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
Posts: 16
Joined: Fri Sep 30, 2011 5:17 am
OLAP Product: tm1
Version: 9.6
Excel Version: 2003

Re: re-order dimension result

Post by wuruimao » Mon Apr 16, 2012 7:40 am

lotsaram wrote:
wuruimao wrote: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
Posts: 16
Joined: Fri Sep 30, 2011 5:17 am
OLAP Product: tm1
Version: 9.6
Excel Version: 2003

Re: re-order dimension result

Post by wuruimao » Mon Apr 16, 2012 7:44 am

One more point,

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

David Usherwood
Site Admin
Posts: 1411
Joined: Wed May 28, 2008 9:09 am

Re: re-order dimension result

Post by David Usherwood » Mon Apr 16, 2012 8:01 am

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
Posts: 16
Joined: Fri Sep 30, 2011 5:17 am
OLAP Product: tm1
Version: 9.6
Excel Version: 2003

Re: re-order dimension result

Post by wuruimao » Mon Apr 16, 2012 8:22 am

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?

User avatar
qml
MVP
Posts: 1086
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: re-order dimension result

Post by qml » Mon Apr 16, 2012 11:26 am

wuruimao wrote: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".
Kamil Arendt

MarenC
Posts: 90
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: re-order dimension result

Post by MarenC » Wed Jul 01, 2020 10:33 am

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
MVP
Posts: 3409
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: re-order dimension result

Post by lotsaram » Wed Jul 01, 2020 2:02 pm

MarenC wrote:
Wed Jul 01, 2020 10:33 am
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.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Post Reply