Currency / Versions storage limitations in Planning

Currency / Versions storage limitations in Planning

PlanningAdmin
PlanningAdmin

December 8th, 2011, 3:20 pm #1

We have a Planning application where the Currency, Versions plus some user defined dimensions are defined as dense.

The Currency dimension is made of just 2 members: Local / Euro.

Making Currency label only instead of the default Never Share setting would make the blocks 1/3 smaller but we get this error message when trying to change it:

"Data Storage cannot be set to Label Only, Dynamic Calc, or Dynamic Calc and Store for Entity, Versions, Currencies, and User Defined dimensions in multi-currency applications."

Does anyone know a work around?

Thanks!
Quote
Share

Cameron Lackpour
Cameron Lackpour

December 8th, 2011, 5:05 pm #2

;)

That wasn't very helpful, was it?

I'm sorry, I don't know of a way to set these members to label only. Planning's currency conversion is...interesting. You could *try* setting it in Essbase and see if Planning overwrites it on refresh. As this is 100% unsupported and probably unsuggested (I just created that word) I would be very, very cautious. Would the performance improvement really be that great? Would the cc within Planning still work? Only you will know but if you do it please follow up within this thread.

It's probably too much work, but have you thought about ripping out Planning's cc? Of course that essentially means a full rebuild but given that you are really only getting rid of cc, maybe it's not too horrible?

Regards,

Cameron Lackpour
Quote
Share

Jake Turrell
Jake Turrell

December 8th, 2011, 6:40 pm #3

We have a Planning application where the Currency, Versions plus some user defined dimensions are defined as dense.

The Currency dimension is made of just 2 members: Local / Euro.

Making Currency label only instead of the default Never Share setting would make the blocks 1/3 smaller but we get this error message when trying to change it:

"Data Storage cannot be set to Label Only, Dynamic Calc, or Dynamic Calc and Store for Entity, Versions, Currencies, and User Defined dimensions in multi-currency applications."

Does anyone know a work around?

Thanks!
I can think of very few situations where I would want Version to be a dense dimension. If you think about it, users typically retrieve and calculate data for one version at a time. By making Version dense, you're saying to the user, "I know you only want data for one version, but I'm going to make you pull a block into memory that contains ALL versions". Here's an analogy . . . it's like you're commuting to work in an 18 wheeler, when you really just need a Smart Car.

This would of course be false if you had some business requirement that users typically retrieve data for (and calculate) data for all of their versions at the same time. I've never seen anything like that, but I suppose it could happen.

So the bottom line is this . . . I don't think a few top-level members are your problem. I think your overall design might be questionnable. (Unless of course you have the odd business requirement that I described above.)

So tell us . . . how are you using Versions?

BTW - I typically set Currency to be sparse as well. Like Versions, my users are typically looking at one currency at a time. No need to burden the block with data they don't care about. It's just extra I/O that I'd rather avoid.

- Jake
Quote
Share

PlanningAdmin
PlanningAdmin

December 8th, 2011, 9:17 pm #4

;)

That wasn't very helpful, was it?

I'm sorry, I don't know of a way to set these members to label only. Planning's currency conversion is...interesting. You could *try* setting it in Essbase and see if Planning overwrites it on refresh. As this is 100% unsupported and probably unsuggested (I just created that word) I would be very, very cautious. Would the performance improvement really be that great? Would the cc within Planning still work? Only you will know but if you do it please follow up within this thread.

It's probably too much work, but have you thought about ripping out Planning's cc? Of course that essentially means a full rebuild but given that you are really only getting rid of cc, maybe it's not too horrible?

Regards,

Cameron Lackpour
Hey Cameron,

we've actually been considering it since it might make our blocks much smaller. Our dense dimensions are as follows:
Accounts : 464
Periods : 13
View : 3 (MTD / YTD)
Currency : 3 (Local / Euro)

If I could get View and Currency as Label Only, this would make my blocks about twice smaller. We have pretty good calc times so far with these settings, but I just hate the idea of having blocks bigger than 400k for no good reason.

We already tried to change the setting in EAS but it was overwritten as soon as we refreshed. I kinda hoped there would be UDA like HSP_UDF or HSP_NOLINK that would prevent Planning from overwriting the storage properties but couldn't find anything.

So I'm considering re-creating the app as a single currency and then create user defined custom Currency and HSP_Rates dimensions to keep the same dimensionality I currently have. I'm just concerned with migrating all the objects / forms / rules and I wonder if LCM would work or if I'll have to recreate everything from scratch...

Anyway, I'll keep you posted....


Quote
Share

PlanningAdmin
PlanningAdmin

December 8th, 2011, 9:23 pm #5

I can think of very few situations where I would want Version to be a dense dimension. If you think about it, users typically retrieve and calculate data for one version at a time. By making Version dense, you're saying to the user, "I know you only want data for one version, but I'm going to make you pull a block into memory that contains ALL versions". Here's an analogy . . . it's like you're commuting to work in an 18 wheeler, when you really just need a Smart Car.

This would of course be false if you had some business requirement that users typically retrieve data for (and calculate) data for all of their versions at the same time. I've never seen anything like that, but I suppose it could happen.

So the bottom line is this . . . I don't think a few top-level members are your problem. I think your overall design might be questionnable. (Unless of course you have the odd business requirement that I described above.)

So tell us . . . how are you using Versions?

BTW - I typically set Currency to be sparse as well. Like Versions, my users are typically looking at one currency at a time. No need to burden the block with data they don't care about. It's just extra I/O that I'd rather avoid.

- Jake
Hey Jake,

Sorry for the confusion. Version is not dense. I just made a mistake in my post.

I have four dense dimensions in the app: Accounts / Periods / View / Currency. The conversion & agg calc runs MUCH faster when Currency is set as dense since I only have the Local and EUR currency for a given entity can do all the calcs within a block.

But the blocks are pretty big and could be considerably smaller if I could override this default Planning setting that doesn't make any sense to me. I just don't understand the purpose of it.....

Thanks for the help!
Quote
Share

Joined: November 26th, 2001, 10:15 pm

December 9th, 2011, 12:00 pm #6

Hey Cameron,

we've actually been considering it since it might make our blocks much smaller. Our dense dimensions are as follows:
Accounts : 464
Periods : 13
View : 3 (MTD / YTD)
Currency : 3 (Local / Euro)

If I could get View and Currency as Label Only, this would make my blocks about twice smaller. We have pretty good calc times so far with these settings, but I just hate the idea of having blocks bigger than 400k for no good reason.

We already tried to change the setting in EAS but it was overwritten as soon as we refreshed. I kinda hoped there would be UDA like HSP_UDF or HSP_NOLINK that would prevent Planning from overwriting the storage properties but couldn't find anything.

So I'm considering re-creating the app as a single currency and then create user defined custom Currency and HSP_Rates dimensions to keep the same dimensionality I currently have. I'm just concerned with migrating all the objects / forms / rules and I wonder if LCM would work or if I'll have to recreate everything from scratch...

Anyway, I'll keep you posted....

Why does that have to be dense?

Really, why does Currency have to be dense? It's been so long since I've used Planning's cc that I forget if that is a requirement or not. Yes, you will in theory double the blocks if it's sparse but so what?

If you go down the custom cc route, DON'T add an pseudo-HSP_Rates dimension. There Are Better Ways. Glenn recently posted a way to do this here:
http://glennschwartzbergs-essbase-blog. ... -some.html

I've used a technique with a separate CC Essbase app/db that just contains rates. The cc code then XREFs to that very, very, very small Essbase db and gets the rate. It works really, really quickly. *Much* more quickly than the Planning-derived cc.

I have to say I haven't tried Glenn's approach in the real world but I would give it a shot as it is a one database approach.

Regards,

Cameron Lackpour
Quote
Like
Share

PlanningAdmin
PlanningAdmin

December 12th, 2011, 9:51 am #7

Hey Cameron,

We setup currency as dense because our conversion & agg script runs much faster with a dense currency dimension and 400kb block than a 160kb block with currency sparse. Basically 20 seconds vs more than 5 minutes.

I haven't tried View as sparse yet. We will give it a shot and see whether this makes any difference.

And thanks for the separate rates database idea. I have never really tried it before and never knew if this was any faster. @XREF are usually pretty slow though so I'm not sure there would be much benefit.

Quote
Share

Cameron Lackpour
Cameron Lackpour

December 12th, 2011, 11:49 am #8

>>@XREF are usually pretty slow though so I'm not sure there would be much benefit.
^^^It is *lightning* fast. You'd be more than pleasantly surprised. You do have to ARRAY out the dimension cc has -- it is then ultra quick.

Or look at Glenn's single database approach. I have to benchmark the two in a db with some size. When I have a free moment. Which is never.

Regards,

Cameron Lackpour
Quote
Share

Samish
Samish

December 12th, 2011, 3:44 pm #9

Hey Jake,

Sorry for the confusion. Version is not dense. I just made a mistake in my post.

I have four dense dimensions in the app: Accounts / Periods / View / Currency. The conversion & agg calc runs MUCH faster when Currency is set as dense since I only have the Local and EUR currency for a given entity can do all the calcs within a block.

But the blocks are pretty big and could be considerably smaller if I could override this default Planning setting that doesn't make any sense to me. I just don't understand the purpose of it.....

Thanks for the help!
You can always make the parents of the dense dimension dynamic. It'll reduce the block size.
Quote
Share

Aleck
Aleck

December 12th, 2011, 4:54 pm #10

Why does that have to be dense?

Really, why does Currency have to be dense? It's been so long since I've used Planning's cc that I forget if that is a requirement or not. Yes, you will in theory double the blocks if it's sparse but so what?

If you go down the custom cc route, DON'T add an pseudo-HSP_Rates dimension. There Are Better Ways. Glenn recently posted a way to do this here:
http://glennschwartzbergs-essbase-blog. ... -some.html

I've used a technique with a separate CC Essbase app/db that just contains rates. The cc code then XREFs to that very, very, very small Essbase db and gets the rate. It works really, really quickly. *Much* more quickly than the Planning-derived cc.

I have to say I haven't tried Glenn's approach in the real world but I would give it a shot as it is a one database approach.

Regards,

Cameron Lackpour
Cameron, can you please provide more detail in your statement "I've used a technique with a separate CC Essbase app/db that just contains rates. The cc code then XREFs to that very, very, very small Essbase db and gets the rate. It works really, really quickly. *Much* more quickly than the Planning-derived cc."

It is Monday, a little slow today, but not understanding what’s your extra db doing…

Thanks!
Quote
Share


Confirmation of reply: