Currency conversion using TM1 as datasource for Cognos BI

[Sebastian Paulussen]

Hi everyone,

i am relatively new to TM1 and having an issue with Currency Conversion.

We are planning to use a TM1 9.5.1 Cube as a Datasource for Cognos BI 10.1.1 (mainly Business Insight Advanced). As my first Approximations of total Cube Size is somewhere near 40-60 GBs of Data I am trying to reduce the stored amount of Data.

Unfortunately I have up to 3 currencies per data Object, a reporting Currency, a standard Currency and a local Currency. There is a total of 36 Currencies in the business Model. In a TM1 only world I could fix my Problem fairly easy in providing the cube with the standard currency and a lookup cube for exchange ratios to other Currencies.

I tried a little mock up Model with two cubes, published them into one package and tried to multiply my Measure from cube1 with a tuple from cube 2 resembling my currency conversion rate. Unfortunately I ran into an Error indicating that this operation is not possible. My web search didn’t come up with a Solution to it.

Basically I have three Questions:
1) Did anybody ever tried to use Measures from two cubes without confirmed dimensions in Business Insight Advanced to calculate a Measure and succeed?
2) If I use a rule calculation, there is no Data stored as long as I do net request the Data from calculated cells. So if my stored Data which is normally retrieved is 100 MByte and I add a Rule calculated Measure to my Reporting tm1 would have to hold roughly 200 MByte of Data?
3) What would be your way of designing the cube? I would favour a short draft to a complete Solution for didactical purposes.

Thanks for your time

Sebastian

[jim wood]

1) I don't think that is possible.
2) Rule data isn't stored but the associated feeders are. With the data set of teh size you are talkign about they would still take a fair amount of memory.
3) Have you considered building a summary cube? Have the detail data at the base currency and the summary data converted. You could do this then copy the data to teh summary using TI doing the currency coversion as the data loads.

[Sebastian Paulussen]

Thanks for your quick reply Jim.

A Summary Cube build by a TI Process shouldn't help in this Case. Basically i have all the data i need in a Star Layer Data Mart, the Use of TM1 is purely for Performance Increase and read-only for Reporting. I cannot alter the Granularity since one of the few requirements i do have at the moment is "We want to see the data down to the lowest levels."

[lotsaram]

If all the data is in a mart why not load all 3 currencies? the data volume is not outside of Tm1's range to handle.

Also what delineates the currency conversion? Is it by transaction or is there an entity or country dimension that determines the local currency and the lookup rate to the reporting currency? And what is the time granularity at lowest level versus likely reporting level. E.g. does the rate vary by month and reporting is by month & YTD or does the rate vary by day with reporting at month & YTD?

If you can be specific and answer those questions then you would be able to get more specific design input as it might be possible to set the reporting currency as a consolidation of local currency and do away with feeders but still have the correct calculated result, but if you have a very large entity dimension or currency rate varies by day then this is unlikely to be workable for performance reasons.

Also it might defeat the purpose of using TM1 over a DW model in the first place but if speed is important why not have a tiered design approach with smaller summary cubes from which more detail can be drilled down into. Makes the interaction on the BI side that much harder with multiple TM1 cubes in the FM model but its likely to perform better.

[ykud]

As my first Approximations of total Cube Size is somewhere near 40-60 GBs of Data I am trying to reduce the stored amount of Data.

How did you calculate that? What's your current data volume in an underlying database?

And AFAIK, it's quite popular to do currency conversion without feeders, just with using a bit more comples hierarchies.

[David Usherwood]

@Sebastian:
Just to check - you didn't mention feeders in your post. Have you tried to write the rule without a feeder? If so it will be very slow.
Can you tell us how many base (n level) data points you have eg by exporting them to a flat file? I wonder if your volumes are really so very high - but they may be.

[lotsaram]

@Sebastian:
Just to check - you didn't mention feeders in your post. Have you tried to write the rule without a feeder? If so it will be very slow.
Can you tell us how many base (n level) data points you have eg by exporting them to a flat file? I wonder if your volumes are really so very high - but they may be.

Don't you mean calculation will be very slow without Skipcheck? That's not the same as without a Feeder. Using leaf level data as a pseudo feeder can be just as effective (and just as fast) to calculate but without the detriment to rule save time or server startup time.

[Sebastian Paulussen]

Hello together,

i am sorry for not coming back earlier. I got some more answers regarding the requirements, but some are till missing.

Time granularity is on Month Basis and does not differ between fact an conversion ratio. I still have to clarify whether the conversion ratio is fixed for all the data loaded in the month or if is time dependent.

Report currency is no longer an issue, so i just have to deal with the local currency and conversion to the standard currency.

If I understood lotsaram correctly i could use my standard currency as C element and all currencies as n Element. My standard currency is also a local currency so there should be also a n Element for that currency. As weightng i would simply use the conversion ratio, so no rule would be necessary.

Changing the weight can be achieved through a TI process with a Combination of DimensionElementComponentAdd and DimensionElementComponentDelete.

The above Method is only possible as long the conversion ratio is independent from another dimension. If the conversion ratio is time dependent I will have to use rules. In this case I could still use my standard currency as a C Element.


For the Calculation of the Cube size I used the only information I do have so far. I know there shall be about 134 Million rows of Data from different fact Tables with an estimate Data Volume of 4.5 GByte in the relational Database. Then I counted the id and fact columns and multiplied this number by 14 Byte as described in the pdf “TM1 Server Administration” pp.7. This estimation is without columns for calculating averages and such. I don’t have and will not have access to the data or a complete and consistent logical Data Model or since this will be developed on the way by another part of the Project after requirement phase.

I know that this Development Method is not good especially in a rather large project, or any project at all, but I am not in the position to change it in this project.

[David Usherwood]

@lotsa:
Stand corrected (on the use of dummy rollups to avoid feeders). It's a nice idea that ought to work in this case and also with 'KPI' type calcs. I recall looking at it for a much more complex model and concluding that it can only be taken so far.
@Sebastian:
You don't need to use TI to change the weights - just use the dimension editor.
You will need to write a rule (not a feeder) to do the translation.
On volume, 134 million rows sounds large but not monstrous. How many numbers will you loading in? TM1 does implode data quite well if your design is right. Make sure you resequence the cube when it has been populated.

[Sebastian Paulussen]

@David
I am to lazy to change the weight by hand, especially since i will load them in a Currency Converter Cube anyway.

The Numbers can be as high as 30, but the Data Modelers are not done with that part because of lacking requirements. Maybe I will have to build different Cubes. We are still at a planning/requirement gathering Phase of the project. I was asked for a quantity structure (in german ist a "Mengengerüst", I do not know if i got the correct translation), to check if the technical requirements can be met. Since the business requirements are not fixed it is a lot of fortune telling.

Are you referring to reordering the dimension with the resequencing the cube?

[Sebastian Paulussen]

After i got more Information about the currency issue and the calculation basis for the fields i decide to use simple leaf elements an pre calculate the currency conversion.

Since most of the fields can be calculated without a currency information i will use 2 leaf elements, local currency and standard currency. With that design i do not have to duplicate the total number of rows.

thank you all for your help and improving my insight to TM1.