TM1 Forum

hello everyone,
I have a problem at the time of counting income for 10 years. I have a cube,
in it there is a calculation of four elements, I need to count the sum of these elements by 10 years.
I consider through attributes as a summation ( total for 2011 + total for 2012 + total for 2013 + total for 2014 and so on ).
I have Measurement to 2036 year, if now 2014, the sum in 10 years considers by 2023 (the current year considers too)
Everything considers correctly, but very long, calculation goes about a minute - one and a half,
Perhaps someone can prompt as to accelerate calculation
I will be very grateful
thanks

Do you have a year dimension?

yes, in other cube where I draw out total values, and then I summarize them

Do you only need this calculated for the currrent year or all years? Have you considered simply creating a consolidation in your year dimension?

Anothe roption is if you use PM you should be able to create a time based rule in your year dimension, as long as the year dimension is set as your time dimension,

Jim.

Wirt wrote:yes, in other cube where I draw out total values, and then I summarize them

So why don't you do the '10 year profit calc' in the source cube and pull through the final value to the summary cube (i.e. your reporting cube). If you need to add stuff up in a cube and you've decided not to use a bog-standard dimension aggregation then you've simply got to question that decision.

Everyone gets that some summary cubes may not have certain dimensions doing consolidations because, for some reason, you need a 'report in a cube' ... but simply adding up 10 years of something is probably easiest done through custom consolidations in a Year dimension. E.g.
10 Years to 2014
-2014
-2013
-2012
-2011
-2010
etc

Wirt wrote:I consider through attributes as a summation ( total for 2011 + total for 2012 + total for 2013 + total for 2014 and so on ).
I have Measurement to 2036 year, if now 2014, the sum in 10 years considers by 2023 (the current year considers too)
Everything considers correctly, but very long, calculation goes about a minute - one and a half,
Perhaps someone can prompt as to accelerate calculation

If you absolutely have to use a rule based on attributes - why don't you post the rule? You will likely get some constructive feedback.

I have a measurement - Period ( 2013,2014,2015,2016 ... 2035,2036 ) (only years)
I have four affiliated elements
'Profit 1'
'Profit 2'
'Profit 3'
'Profit 4'

and a consolidating element - 'Comprehensive Profit'

+'Comprehensive Profit'
- 'Profit 1'
- 'Profit 2'
- 'Profit 3'
- 'Profit 4'


and a simple element which considers the profit in 10 years - 'Profit for 10 years'

So,

['Profit 1'] = N: DB ( 'Calculation of the Profit 1', ......, ATTRS ( 'Period', !Period, 'year'), 'Profit 1' );
['Profit 2'] = N: DB ( 'Calculation of the Profit 2', ......, ATTRS ( 'Period', !Period, 'year'), 'Profit 2' );
['Profit 3'] = N: DB ( 'Calculation of the Profit 3', ......, ATTRS ( 'Period', !Period, 'year'), 'Profit 3' );
['Profit 4'] = N: DB ( 'Calculation of the Profit 4', ......, ATTRS ( 'Period', !Period, 'year'), 'Profit 4' );

In cubes 'Calculation of the Profit (1,2,3,4)' profit is calculated in month,
data 'total in a year' get to a cube Profit

and

['Profit for 10 years']=N:
['Comprehensive Profit']+
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 1year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 2year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 3year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 4year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 5year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 6year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 7year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 8year'),'Comprehensive Profit') +
DB('Profit'.....,ATTRS ( 'Period', !Period, 'Next 9year'),'Comprehensive Profit') ;

everything considers but very slowly,

may be, because a long period?

Have you looked at using a continuous time dimesions (i.e. period and months in the same dimension) and having decade-to-date consolidations in there. This would give you the sort of cumulative profit calculation you are after without using rules, I suspect.

rmackenzie wrote:having decade-to-date consolidations in there. This would give you the sort of cumulative profit calculation you are after without using rules, I suspect.

jim wood wrote:Have you considered simply creating a consolidation in your year dimension?

That's pretty much where I was heading, hence asking if for current period only or not. The only catch to it is how this being reported I guess. There may be a requirement for a specific measure (or account) in the reports. You can work around this easily if the reporting solution is Excel but it will be a little harder in say BI,

Jim.

jim wood wrote:hat's pretty much where I was heading, hence asking if for current period only or not. The only catch to it is how this being reported I guess. There may be a requirement for a specific measure (or account) in the reports. You can work around this easily if the reporting solution is Excel but it will be a little harder in say BI,

Yes, I agree. The OP can get the best of both worlds if one cube does the calculations with decade-to-date consolidations and has the 'current period' result in a separate reporting cube that is more amenable for BI reporting.

Hi Wirt,look at Wim's blog,there is quite good article with title 'Relative Time Dimensions'.It Could help you.Thanks
http://www.wimgielis.be/

thanks for the help, I'll take into account your advice