How to iterate over odbc input columns?

Post Reply
LGEIS
Posts: 10
Joined: Tue Nov 16, 2021 8:16 am
OLAP Product: IBM TM1 Perspectives and PAfE
Version: several
Excel Version: several

How to iterate over odbc input columns?

Post by LGEIS »

As I want to fill a cube with as less code as possible, I was considering looping over the sql server input. In my example a value gets written, if several conditions are met. This works fine, unless I want to put it all in a loop. I may use a flat dimension to represent the input variables, but this works only in the CellPutN statement. I also want to check the if conditions for every input column. How do I achieve this? I could simply write a statement representing every input column. Where I put the question marks, there would be the input variable name and a loop wouldn't be necessary. But This would mean some hundreds of lines of code. Any suggestions?

Code: Select all

# define loop  input, where "DIM_flat_iter" represents a dimension containing the relevant input variables as dimension elements
 
sCube = 'xyCube';
sDIM = 'DIM_flat_iter';
DimCount = DIMSIZ('sDIM');
i = 0;
 
# start iteration
WHILE ( i < DimCount);
 
     i = i + 1;
    elemDIM = DIMNM(sDIM, i );
    varDIM   =  ?? ;                                 # ---->  how do I capture this, as string values from a dimension cannot represent input variables, I guess
 
    if (Q_Code @<>' ');
            if (varDIM  @= 'X');
                 if (QAB @= 'X');
                     CellPutN(1, sCube,  'QAB', elemDIM, Q_Code, 'Marker');
             endif;
         endif;
     endif;
 
END;
MarenC
Regular Participant
Posts: 390
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: How to iterate over odbc input columns?

Post by MarenC »

Hi,

the go to code to reduce lines of code is usually the Expand function.

I would try looking into that. You will have to label your variables in order that you can loop over them. So e.g. v1, v2, v3 and expand the number.


Maren
LGEIS
Posts: 10
Joined: Tue Nov 16, 2021 8:16 am
OLAP Product: IBM TM1 Perspectives and PAfE
Version: several
Excel Version: several

Re: How to iterate over odbc input columns?

Post by LGEIS »

Hi Maren,

thanks a lot for the quick help! The EXPAND function does the trick! In my example varDIM would equal EXPAND( '%v' | elemDIM | '%' );

Regards
David Usherwood
Site Admin
Posts: 1458
Joined: Wed May 28, 2008 9:09 am

Re: How to iterate over odbc input columns?

Post by David Usherwood »

The other approach which I have found useful is to use UNPIVOT in the SQL.
https://docs.microsoft.com/en-us/sql/t- ... rver-ver15
(Other RDBMS's are available - and a number of those also support UNPIVOT :) )
This turns columns into rows, allowing you to reduce the number of Cellputn statements.
LGEIS
Posts: 10
Joined: Tue Nov 16, 2021 8:16 am
OLAP Product: IBM TM1 Perspectives and PAfE
Version: several
Excel Version: several

Re: How to iterate over odbc input columns?

Post by LGEIS »

Thanks! This might come in handy, when the actual model gets integrated into the production environment
Post Reply