TM1 Forum

Hi All,

I have an issue when creating a subset via TI. The mdx string that I have created to work out my top 10 emp with amex spend is around 420char in length. When i attempt to create the subset using TI it does not work. Is this a limitation of the version i am on?

I have not tried this yet, but would it be possible to create a string within a rule attached to a cube and get the process to point to a cell within that cube that has the calulcated mdx string within it? Would this get around the 255 char limit or because its a "calulated value" and not a hard coded value, would this work??

I have tested the mdx statement in the subset editor and it pulls back the desired result however running this in TI seems to be a no go.I have also throught about putting the whole mdx string in a txt file and TI off that to see whether it would work. Has anyone had an issue with above and have a work around? I would love to test this now, but dont have access to my work pc remotely atm.

Any help would be much appreciated.

Cheers

Shock

Pre 9.4 I'm pretty sure the only way to create a dynamic subset with more than 255 characters in the expression was manually. (Unless you go to the trouble of using a script based approach to build a .sub file and then you would probably need to bounce the server to properly register the subset.)

I don't think any of the options you listed will work as the limitation within TI is that a string variable cannot exceed 255 characters. Whether you build the string wholly within your script, read it in from a text file or combine some string cell values you still bump up against the same problem.

Thanks mate. Looks like an upgrade is due. Does this also apply to calulated string values? I mean does tm1 treat a calulated cell different to a hard coded cell value and this would then ignore the 255 char limit? And then if this holds true I could then TI it??????????? Or am I still screwed?

Cheers

Shock

I dont know if it applies to your case but could you break up the statement into different subsets? You can utilize TM1 Subsets within an MDX statement. I was able to break a similar size statement into 4 different queries that are saved as temp subsets. Then all you have to do is reference the subset in your final mdx. Page 22 of the MDX Primer available on this site goes into more detail.

I have since moved away from this after going to 9.4 just because I did not like the extra subsets, but it was a decent work around for the time.

Hi Shock

I think George's work around is the best option available to you.

In terms of whether a calculated string in 9.1 can exceed 255 characters I can't say I have ever tried but I doubt it. Most of the time calculated strings would only be for security, simple lookup or meta data validation purposes so are short by nature and only manually input comments might be longer. So this is could well be something no one has ever bothered to test, you might as well go for it just to satisfy intellectual curiosity! But even if a calculated string can exceed 255 characters if you subsequently need to read the string into a TI string variable via CellGetS then it would either get truncated or cause an error at that point.

Thanks for you comments guys. Lotsaram I tried producing the string within a rule and it crapped out at 255, so scratch that one. And I reckon your right, even if I was able to get in the rule and then pull it across the limitation would still kick in when tryiing to create the subset via TI process. Not sure whether this statement can be split up. The TOPCOUNT needs to ref all the other dimensions to sort hc emp name by highest $. But perhaps I am wrong here and this statment could be split up / written another way? / doesnt need to ref the other dims? Any ideas????


{ TOPCOUNT( {{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[hc_emp name].[All Employees]}, ALL, RECURSIVE )}, 0)}},
10.000000,
[amex expenditure 2].([amex bill date].[Aug 2009],[amex charge date].[All Charge Dates],[amex genesis level 5].[All Genesis Level 5],[amex genesis major industry].[All Genesis Major Industry],[amex supplier].[All AMEX Suppliers],[coo_ausext].[GENI_AU],[amex emp status].[All_Emp_Status],[variable].[Net Expense])) }

Cheers

There are a couple of possibilities open to you that could get the character count below 255.

Firstly following George's suggestion the set that you are doing the topcount on:
{{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[hc_emp name].[All Employees]}, ALL, RECURSIVE )}, 0)}}
could be a named public subset itself. TM1 will let you refer to a subset in MDX in much the same way as a dimension member so the statement above could be reduced to
{[hc_emp name].[AllEmpDrillDown]}
or something similar in the larger expression nested with topcount (just make sure that the subset name is not the same as any element name)

But your real problem is in the cube reference for the TOPCOUNT itself as this is where the character count blows up due to the cube having 9 dimensions. One possibility here would be to create a "short name" alias for each dimension to cut down on the characters used for each member definition. This might do the trick for you but would be rather a lot of work.

Some other ideas
1/ How "dynamic" does the dynamic subset have to be? As another approach could be to do the ranking analysis via a TI batch process and write a numeric attribute against the hc_emp name or write the rank to a simple lookup cube. Then in the subset MDX just refer to the attribute or lookup cube
2/ For the analysis that you are wanting to do why not use the ODBO connection from Excel, build the MDX in VBA and pull the set directly into an Excel report?
3/ Following from above the upgrade option might be worth considering as this kind of report and analysis is now very easy to do with active forms (absolutely no programming required)

Thats Nuts! Didn't relize you could:

{[hc_emp name].[AllEmpDrillDown]}
thats pretty cool. Yeah that solves the first bit but the cube ref is ridulously long. You mention this:
"One possibility here would be to create a "short name" alias for each dimension " > How do I do that??? I mean what would be the statement that I would us
[amex charge date].[All Charge Dates], = [a].
do I just need to create a alias for the consold nodes and then refer to em like RHS above?

The other ideas are definately worth a go:
1) this ones looks interesting give that one a go, i have time to burn.
2) created this b4 and works well, then it takes away from tm1 being the source and gloabal reporting tool. basically want everyone to see the same thing in one place
3) We are planning on doing and upgrade in december when things are a little quieter, but ppl want it now! as always.

Thanks again lotsaram. You help has been much appreciated.

Cheers

Shock