TM1 Forum

Hi ,

We have an important issue thats happening in cognos transformer where it cannot handle more than 65000 categories under a member .

We have no problem in tm1 in loading how many ever members . Sowe daily do a extract to cognos seperatley a flat file for products dimensions and seperate flat file for other dimensions and measures.

The issue happened was for 1 level of product it has a children which is more than 65000 categories. Eventhough all these catgories doesnot have sales its still sent and trying to load in cognos .

In tm1 while I do a asciooutput can i do somrthing like if a product level is say 10-1-150 -100 then take its material and check if it has sales for last 4 years and quantity for last four years and if its doesnot have then i will not extract to cognos .

Can this MDX be easily written and will not casue any performance issues on the extract? should i write a mdx subset and get all elements that has sales or qty greate than zero ?

Any thoughts or suggestions is appreciated.

I can write something like this

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test].([Product].CurrentMember, [Posting Measures].[Amount]) > 18 )}

I can check something similar like Amount and qty is not equal to zero . This will ensure i get all the products that had a sale or not .

But say if there was 4 transactions with -10.-5 ,10 ,5 and this will net to zero and will this casue this material not to be in the result . Is there an mdx that can give the results for the above option as well

Thanks

Incorporate a count with a cellincrement of 1. Then it’s part of you cube, ready for use in the MDX.

Can you please give an example , where should this count be done with cell increment.

Thanks,

I see that you have a measures dimension called "Posting measures", with Amount as element.
Add another element "Count" and populate it in the same way. For example, do CellIncrementN( 1, cube, ..., ..., 'Count' ) to each time add 1 to the Count.

Hi Wim ,

Thanks for the suggestions, One question here so i wanted to check for manly three measures like qty,sales and budget if that material has any instance .

so i put my source view with all n for all dimensions with 3 measures on measure dimension and a currency dimension where it has input or local currency with two values in source.

But i will always write the count to count measure .

Question here is I know dont have a consolidation for InPut Currency and Local currecny in currency dimension and All months in month dimension ,

When doing MDX I had to create one consolidation for this dimensions right?

Is there a code I can grab both input currency and local currency total .

Maybe you need more than 1 Count measure. Or use an OR statement in the MDX: if Count_1 > 0 OR Count_2 > 0. Or simpy an addition: if Count_1 + Count_2 > 0