TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Data export TI process doesn't work in TM1 10.2.2

https://www.tm1forum.com/viewtopic.php?t=11066

Page 1 of 1

Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 10:36 pm
by 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 ;

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 10:38 pm
by TM1New
when I check in the random view, The subsets for ABCD and XYZ are not created.

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:00 pm
by declanr
When you say it doesn't work... what error message do you get?

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:03 pm
by declanr
And at a quick glance the MDX on this line is syntactically incorrect:

Code: Select all

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

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:12 pm
by 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 ;

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:12 pm
by 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

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:20 pm
by 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 ;

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:29 pm
by TM1New
Looks like it's looping and creating subsets in the dim's but not loading values in the two subsets using MDX.

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Wed Nov 05, 2014 11:30 pm
by declanr
TM1New wrote:

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.

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 12:08 am
by 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 ;

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 12:13 am
by declanr
If you open the created subset in the subset editor is the MDX expression in there?

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 12:17 am
by TM1New
Hi Declanr,

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

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 12:18 am
by 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

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 2:13 am
by 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.

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Thu Nov 06, 2014 2:30 am
by TM1New
Blackhawk,

You Spot on..

Thanks for your time and efforts.

Cheers

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Fri Nov 07, 2014 6:49 am
by 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

Re: Data export TI process doesn't work in TM1 10.2.2

Posted: Fri Nov 07, 2014 10:18 am
by declanr
tm1india wrote: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.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited