Issue with creating subset via TI

[kugors]

Hi All,

I want to create Drill process to another cube. One of destinations cube elements have MDX subset, that's pointing another subset, to walkaround problem with deleteing subsets when they are useb by view (found on this forum).

Code: Select all

{[Dim].[Subset_for_recreation]}

Now i can delete Subset_for_recreation and use SubsetCreatebyMDX.

Code: Select all

# Deleting Subset if exists

If(SubsetExists('Acc', 'Subset_for_recreation')=1);
SubsetDestroy('Acc', 'Subset_for_recreation');
EndIf;

#Variable mapping

Position = '"*""' | DIMNM('PL',DIMIX('PL',PL)) | '""*"';
DummyElement =  'xyz"' | DIMNM('PL',DIMIX('PL',PL));
MDX = '{TM1FILTERBYPATTERN( {TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Acc] )}, 0)}, ASC)}, '|Position|')}';

#Adding dummy element to walkaround that values for filtering are on alias
DimensionElementInsert('PL', '',DummyElement,'N');


SubsetCreatebyMDX('PLDrill', MDX);
SubsetAliasSet('PL', 'PLDrill', 'PLPosition' );

DimensionElementDelete('PL',DummyElement);

My problem is that ElementInsert doesn't work like i want. I comment Creating subset and process works (it is adding element). Then I rerun drill (uncomented), and I'ts worked. I'm thinking that i have to save dimension before creating subset, but i don't known how to do it.

I'm working on Cognos Express 9.5

[Steve Rowe]

Dimensions are not "refreshed" until the tab the command is on is complete.

So if you are doing the dimension element insert on the prolog tab, and then you should do your create by mdx on the beginning of the epilog before the start of the generated statements and the return view handle statement

Edit so it makes sense!

[kugors]

Thank you Steve. I suspected that I'm missing something simple...

--Edited--

I have checked this, and it's working but it's not enough for me. I want to make few steps, and Prolog and Epilog gives me only two. I tried to create nested processes (executing it from for example Prolog) hoping that data will be "refreshed" by the nested process, but this doesn't work. Any other way to do it?

[qml]

You would need to provide more detail on what you're trying to do to get a better reply, but here are my two eurocents.

Nested TIs should commit metadata when they finish. This does not apply to data when Parallel Interaction is switched on - all changes will be commited at the end of the parent process.

If you need more "steps" you can always try adding a dummy data source to the drill TI (e.g. with one record only) so that Data and Metadata tabs become active.

Another idea is to decouple the parent process from the children processes by using the command line executable RunTI.exe - I believe that this should force the children processes to commit all types of changes at the end of their execution.

[kugors]

Thanks you all for replies. I have read more about MDX and manage to do what I want in two MDX based querys.