Palo Community Forum

Hi All,

When I first set up our cube, I didn't set up a currency dimension. All month end balances were recorded in their native currency and then I used Excel to convert results at a year-to-date average rate. Sort of like the push rule but I didn't send anything back to Palo. I did this because using a YTD rate meant that previous period results would need to be adjusted every month based on the new rate. Easier to let Excel work with the raw dollars.

Anyway, now we've decided to set a month end rate for each period and convert the transactions at that rate. That means I can now use the push rules and store historical results at the month end rate.

I'm trying to figure out how to add a new dimension so I can choose the currency on my queries. I want to add the following elements to the currency dimension: Base, $Can, $Comp (where comp is the results converted at the budget rate).

Any ideas how I can add this dimension without having to rebuild the cube?

Thx

Thanks Holger. I was afraid of that.

Hopefully it's easier exporting the cube and re-importing. I originally built up the cube using flat file imports which are time consuming.

Well, it was not as painful as I thought.

Once I figured out that the cube import loops through every combination in the cube (like a flatfile) it was easy. For anyone else, here are the steps:

Create the new dimension on your server
Create a new cube and add all the dimensions including the new one
In your new cube add "one"! element to your new dimension
Start the cube import wizard and point it to the original cube
Hit Next one time so that the first row is populated with data
Use the insert formula function to guess the arguments for a setdata expression
Edit the formula by substituting "value" with the cell containing the data intersect (for me it was the amount)
Rerun the import only this time hit "Finish"
Watch as all the data loads automatically

Good job Jedox!

I have two currencies set up in my cube, "Base" which is all of my historical data in their native currencies. The other element is called "Local" which is all of the "Base" data converted at a set rate each month. Using a push method would be very time consuming as I'd have to drill down to the bottom level of every dimension and set up a formula to push the converted value back. I was hoping the "like" function would help but I crashed the server so I think I did it wrong

My other thought was a cube import but hardcode the currency and then set the rate by using a vlookup.

Any better suggestions?

Thx

Time for a really dumb question.

The copy and like examples in the guide (pg 86) show a ; separating the elements. I assumed I should use a , since I'm on a North American set up but I received an error (something about an adjunct cell). So I tried using the ; which seems to lock up excel (maybe it takes a long time to process?)

Should it be a "," or ";"?

thx

hehe. It's a ";". Just takes a long time to process!