Data export TI process doesn't work in TM1 10.2.2

[TM1New]

Hi Guys,

I have created a TI process to export the data from cube to csv file in Cognos express.

After we deploy the TI from cognos express to TM1 10.2.2, my code doesn't work. Can you please let me know your thoughts on this.
The below mentioned code is not working.

Code: Select all

cCubeName = 'Sales' ;
cViewName =  ' View ' | NumberToString( ROUND( RAND() * 100000 ) ) ;
cSubName = 'Subset ' | NumberToString( ROUND( RAND() * 100000 ) ) ;


iContinue = 1 ;
iDimCount = 1 ;
WHILE ( iContinue = 1 ) ;
  
  sDimName = TABDIM ( cCubeName , iDimCount ) ;

  IF ( sDimName @<> '' ) ;

  IF ( sDimName @= 'ABCD' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
            SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
   ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

 IF ( sDimName @= 'XYZ' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
         SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ )}, 0)}' ) ;
      ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements( sDimName , cSubName ) ;
    ELSE ;
        SubsetCreate( sDimName , cSubName ) ;
   ENDIF;
        ViewSubsetAssign( cCubeName , cViewName , sDimName , cSubName ) ;
  ELSE ;
    # must have gone through all the dims
    iContinue = 0 ;
  ENDIF ;
  iDimCount = iDimCount + 1 ;
END ;

[TM1New]

when I check in the random view, The subsets for ABCD and XYZ are not created.

[declanr]

When you say it doesn't work... what error message do you get?

[declanr]

And at a quick glance the MDX on this line is syntactically incorrect:

Code: Select all

         SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ )}, 0)}' ) ;

[TM1New]

I am not getting any error message, Process successfully executed but it's not doing data export.
When I check the view it has generated, the two subsets for Dim's ( ABCD , XYZ) I am creating using MDX doesn't have any values in it. whereas I can see other subsets of dim's are populating with values.

I suspect that view I am populating in prolog tab is not getting created properly. I am passing parameter's through an excel template.

my full code in Prolog;

Code: Select all

cCubeName = 'Sales' ;
cViewName =  ' View ' | NumberToString( ROUND( RAND() * 100000 ) ) ;
cSubName = 'Subset ' | NumberToString( ROUND( RAND() * 100000 ) ) ;


iContinue = 1 ;
iDimCount = 1 ;
WHILE ( iContinue = 1 ) ;
  
  sDimName = TABDIM ( cCubeName , iDimCount ) ;

  IF ( sDimName @<> '' ) ;

  IF ( sDimName @= 'ABCD' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
            SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
   ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

 IF ( sDimName @= 'XYZ' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
         SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ )}, 0)}' ) ;
      ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements( sDimName , cSubName ) ;
    ELSE ;
        SubsetCreate( sDimName , cSubName ) ;
   ENDIF;
        ViewSubsetAssign( cCubeName , cViewName , sDimName , cSubName ) ;
  ELSE ;
    # must have gone through all the dims
    iContinue = 0 ;
  ENDIF ;
  iDimCount = iDimCount + 1 ;
END ;

# Loading Subsets

Subsetelementinsert ( 'Date1' , cSubName, pDate1 , 1 );
Subsetelementinsert ( 'Date2' , cSubName, pDate2 , 1 );
Subsetelementinsert ( 'Date3' , cSubName, pDate3 , 1 );
Subsetelementinsert ( 'Currency' , cSubName, pCurrency , 1 );
Subsetelementinsert ( 'Org' , cSubName, pOrg , 1 );

Subsetelementinsert ( 'Measure' , cSubName, 'Value' , 1 );


### Assign Datasource ###

DataSourceType = 'VIEW';
DatasourceNameForServer = cCubeName ;
DatasourceNameForClient = cCubeName ;
DatasourceCubeView = cViewName ;

[EvgenyT]

as Rodger pointed out..

SubsetcreatebyMDX ( cSubName , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ )}, 0)}' ) ;

Should be:

SubsetcreatebyMDX ( cSubName , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ] )}, 0)}' ) ;

You are missing a ']' after XYZ

Thanks

[TM1New]

Sorry guys my fault, it's typo error. Below is the code.

Code: Select all

cCubeName = 'Sales' ;
cViewName =  ' View ' | NumberToString( ROUND( RAND() * 100000 ) ) ;
cSubName = 'Subset ' | NumberToString( ROUND( RAND() * 100000 ) ) ;


iContinue = 1 ;
iDimCount = 1 ;
WHILE ( iContinue = 1 ) ;
  
  sDimName = TABDIM ( cCubeName , iDimCount ) ;

  IF ( sDimName @<> '' ) ;

  IF ( sDimName @= 'ABCD' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
            SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
   ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

 IF ( sDimName @= 'XYZ' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
         SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ] )}, 0)}' ) ;
      ENDIF;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements( sDimName , cSubName ) ;
    ELSE ;
        SubsetCreate( sDimName , cSubName ) ;
   ENDIF;
        ViewSubsetAssign( cCubeName , cViewName , sDimName , cSubName ) ;
  ELSE ;
    # must have gone through all the dims
    iContinue = 0 ;
  ENDIF ;
  iDimCount = iDimCount + 1 ;
END ;

# Loading Subsets

Subsetelementinsert ( 'Date1' , cSubName, pDate1 , 1 );
Subsetelementinsert ( 'Date2' , cSubName, pDate2 , 1 );
Subsetelementinsert ( 'Date3' , cSubName, pDate3 , 1 );
Subsetelementinsert ( 'Currency' , cSubName, pCurrency , 1 );
Subsetelementinsert ( 'Org' , cSubName, pOrg , 1 );

Subsetelementinsert ( 'Measure' , cSubName, 'Value' , 1 );


### Assign Datasource ###

DataSourceType = 'VIEW';
DatasourceNameForServer = cCubeName ;
DatasourceNameForClient = cCubeName ;
DatasourceCubeView = cViewName ;

[TM1New]

Looks like it's looping and creating subsets in the dim's but not loading values in the two subsets using MDX.

[declanr]

Code: Select all

  IF ( sDimName @= 'ABCD' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements ( sDimName , cSubName ) ;
    ELSE ;
            SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
   ENDIF;

Right your problem is here. If the subset doesn't exist you create it via MDX and stick an element in it as part of that... that's fine. But if it already does exist (aka if you have ever run the process before) then you just delete all of the elements in it and don't put any elements back in.

try:

Code: Select all

  IF ( sDimName @= 'ABCD' ) ;
    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDestroy ( sDimName , cSubName ) ;
    EndIf;
    SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}', sDimName ) ;

And do the same for your other dimension.

[TM1New]

Hi Declanr,

thanks for your suggestion,

I have tried that, but I can see the empty subset.

I have modified my code as below, but still the same result.

Code: Select all

cCubeName = 'Sales' ;
cViewName =  ' View ' | NumberToString( ROUND( RAND() * 100000 ) ) ;
cSubName = 'Subset ' | NumberToString( ROUND( RAND() * 100000 ) ) ;


iContinue = 1 ;
iDimCount = 1 ;
WHILE ( iContinue = 1 ) ;
  
  sDimName = TABDIM ( cCubeName , iDimCount ) ;

  IF ( sDimName @<> '' ) ;

  IF ( sDimName @= 'ABCD' ) ;
            SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

 IF ( sDimName @= 'XYZ' ) ;
         SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ] )}, 0)}' ) ;
           ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
     ENDIF ;

    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements( sDimName , cSubName ) ;
    ELSE ;
        SubsetCreate( sDimName , cSubName ) ;
   ENDIF;
        ViewSubsetAssign( cCubeName , cViewName , sDimName , cSubName ) ;
  ELSE ;
    # must have gone through all the dims
    iContinue = 0 ;
  ENDIF ;
  iDimCount = iDimCount + 1 ;
END ;

# Loading Subsets

Subsetelementinsert ( 'Date1' , cSubName, pDate1 , 1 );
Subsetelementinsert ( 'Date2' , cSubName, pDate2 , 1 );
Subsetelementinsert ( 'Date3' , cSubName, pDate3 , 1 );
Subsetelementinsert ( 'Currency' , cSubName, pCurrency , 1 );
Subsetelementinsert ( 'Org' , cSubName, pOrg , 1 );

Subsetelementinsert ( 'Measure' , cSubName, 'Value' , 1 );


### Assign Datasource ###

DataSourceType = 'VIEW';
DatasourceNameForServer = cCubeName ;
DatasourceNameForClient = cCubeName ;
DatasourceCubeView = cViewName ;

[declanr]

If you open the created subset in the subset editor is the MDX expression in there?

[TM1New]

Hi Declanr,

I can't see the MDX expression in subset window.

[TM1New]

But there is a work around, Instead of running a loop, when I try to create each and every dimension and subset manually, it works fine.
I am wondering it's the same code working fine in cognos express but not in TM1 10.2.2.

Cheers

[blackhawk]

TM1New,

I think you might be getting tripped up in your indenting. If you clean it up, you can see right away that you delete all elements right after you create the subsets.

Code: Select all

    IF ( sDimName @= 'ABCD' ) ;
        ...
    ENDIF ;

    IF ( sDimName @= 'XYZ' ) ;
        ...
    ENDIF ;

    IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
      SubsetDeleteAllElements( sDimName , cSubName ) ;
    ELSE ;
      SubsetCreate( sDimName , cSubName ) ;
    ENDIF;

So the above statements will first create the MDX subset, then destroy it right away. I am not sure exactly what you are trying to accomplish, but perhaps restructure the code like this:

Code: Select all

iContinue = 1 ;
iDimCount = 1 ;
WHILE ( iContinue = 1 ) ;

  sDimName = TABDIM ( cCubeName , iDimCount ) ;

  IF ( sDimName @<> '' ) ;
    IF ( sDimName @= 'ABCD' ) ;
      SubsetcreatebyMDX  (cSubName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ABCD].['| pABCD |']}, ALL, RECURSIVE )}, 0)}' ) ;
      ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
    ELSEIF( sDimName @= 'XYZ' ) ;
      SubsetcreatebyMDX ( cSubName  , '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [XYZ] )}, 0)}' ) ;
      ViewSubsetAssign ( cCubeName , cViewName , sDimName , cSubName ) ;
    ELSE;
      IF ( SubsetExists ( sDimName, cSubName ) = 1  ) ;
        SubsetDeleteAllElements( sDimName , cSubName ) ;
      ELSE ;
        SubsetCreate( sDimName , cSubName ) ;
      ENDIF;
    ENDIF;
    
    ViewSubsetAssign( cCubeName , cViewName , sDimName , cSubName ) ;
  ELSE ;
  # must have gone through all the dims
  iContinue = 0 ;
  ENDIF ;

  iDimCount = iDimCount + 1 ;
END ;

See if that helps.

[TM1New]

Blackhawk,

You Spot on..

Thanks for your time and efforts.

Cheers

[tm1india]

I have a problem with alternate hierarchy subset ..
i have a dimension which has
I have a sales dimension which has 2 hierarcy product and type and they have consolidated and leaf elements..Now my requirement is to create a subset for type with leaf elements of color2..
Sales Dimension name
Product Hierarchy 1
Color1
16 Leaf
15
Color2
13
18
color3
14
12
Type Hierarchy 2
Brand1
12 Leaf elements
13
Brand2
14
15
Brand3
16
18

Expected subset - only elemts thats beloong to color2

Type
Brand1

13

Brand3
16

[declanr]

I have a problem with alternate hierarchy subset ..
i have a dimension which has
I have a sales dimension which has 2 hierarcy product and type and they have consolidated and leaf elements..Now my requirement is to create a subset for type with leaf elements of color2..

You can use the MDX Intersect function to find the intersections of 2 descendants functions, or just do a while loop and 2 If ElisAnc/ElIsPar functions if you want a static subset.
Not sure what this question has to do with the OP though.