BI Prompts to TM1 data?

[ram123]

Hi,

I have a TM1 cube from which the data is populated to a BI report. There are few prompts which are to be run in the BI report to get the respective data. For example Year, Month, Quarter, Region etc..
The data in TM1 cube is populated from TI Processes (without any parameters/prompts).
My question is:
There are few calculations which are to be calculated based only on the prompts selected in the BI report.
For example, if a user selects 'Jan', '2014' in the Month,Year prompts in the BI report, calculations must happen w.r.t that particular month and year (Calculations are - Multiplying the measure with Month no ('1') and getting the suffix of the year('14'). These calculations are specific to the business reqts)

Now then, when the data is being pulled directly from TM1 cube (using a pkg in FM) to the BI report, how do we set up calculations based on the prompts entered by the user in BI report? Where and how do I filter the TM1 data based on prompts running in the report?

[declanr]

Sounds like a fairly simple requirement, why don't you just pre-calculate all possible calculations in the cube on the TM1 side and then have it show that relevant intersection in BI after they have selected the prompts?

Or if for some reason it absolutely has to be done BI side then just create calculation fields based on the prompt data item, you will likely find this significantly slower performance wise though. I tend to stick to the idea of keeping the amount of "work" that BI has to do to a minimum when there is something that can instead be done in the source system.

[ram123]

Thanks Declan for the reply.
But I feel it is really tedious to calculate all possible combinations at TM1.
I have got 5 years of data- Months, Quarters, Years and there are 3 different prompts (A,B,C) which would be selected in BI. (A - 146 elements, B- 300~, C- 950~)
All possible combinations of this scenario could go upto somewhere like - 5*4*12*146*300*1000 (approx) - To create so many elements in BI in measure column.. !! Not feasible

[David Usherwood]

Why do you think adding new measures is the right way to precalculate the numbers you want?

[PlanningDev]

I'm not following exactly but what you want to do sounds simple to do in report studio. You should be able to create a calculated measure and then create whatever calculation you want. You can access prompt values, measures, dimensional attributes, etc. for example if you needed to multiply the measure by the promoted month no you could just use promptValue(para name) x Measure. Assuming prompt value is numeric. If it's not then either add a numeric attributing TM1 or use a case statement in report studio.