How to iterate over odbc input columns?

[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]

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]

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]

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]

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