Turbo Integrator - Data tab dont run?

Post Reply
HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Turbo Integrator - Data tab dont run?

Post by HighKeys » Fri Jul 03, 2020 1:30 pm

Hello Community,

i have a issue with a TI that i created yet, first i create a View and some subsets and assign them to the view, works pretty well.

The View looks like what i need, now i used this view as Datasource and want to create a ASCIIOutput log if the Value is not 0 or 1. But the Datatab never runs, i removed the IF to log every Value, but it dont log anything... Also my code on the prolog dont execude.

Anyidea why just the prolog runs fine?

No errors in the Log.

Version 2.0.9

Thanks!

Datatab looks like:

Code: Select all

#IF(Value <> 0 & Value <> 1);

    ASCIIOutput( 'D:\Planning_Analytics\Custom_Logs\Logger\SchluesselCheck_'  | sRunID | '.txt', V1, V2, V3, V4, V5, V6, V7, NumberToString( Value ));

#ENDIF;
Edit:


In the Prolog i also changed the temp view to a permanent view and selected them as Datasouce.. so its a normal Process with a View, but i dont get the logs i want to see :(

Sidequestion:

Is this still right to define a temp view a datasource?

Code: Select all

#DataSourceType = 'VIEW';
#DatasourceNameForServer = Cubename;
#DatasourceCubeview = 'Viewname';

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

Re: Turbo Integrator - Data tab dont run?

Post by David Usherwood » Fri Jul 03, 2020 1:39 pm

Most likely you have selected to skip consolidated elements but the selections in one or more of your dimensions have no N level elements. Easiest way to check this is to export the view to a file. It will have a cma extension but is in fact a csv file.

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Turbo Integrator - Data tab dont run?

Post by HighKeys » Fri Jul 03, 2020 1:41 pm

Hi!

thanks for the fast replay, sounds genuis! How can i deactivate "SkipConsolidatedElements" in a TI Process when i create the view there?

Edit:

Found it.. Sorry!

Code: Select all

 ViewExtractSkipRuleValuesSet (Cube, ViewName, Flag);


Edit 2:

nvm its for rule values, didnt read right...

Edit 3:

ok..ok... here is it.

Code: Select all

ViewExtractSkipCalcsSet(Cube, View, 1);
and now everything is working fine. Thank you soo much!


Edit4:

ok one thing isnt working and i cannot find out why

Code: Select all

IF(Value <> 1);

    ASCIIOutput( 'D:\Planning_Analytics\Custom_Logs\Logger\SchluesselCheck_'  | sRunID | '.txt', V1, V2, V3, V4, V5, V6, V7, NumberToString( Value ));

ENDIF;

just logs every cell and the log says it have a 1, but thats not true.

Thanks!

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Turbo Integrator - Data tab dont run?

Post by orlando » Tue Jul 07, 2020 7:08 am

HighKeys wrote:
Fri Jul 03, 2020 1:41 pm

Edit4:

ok one thing isnt working and i cannot find out why

Code: Select all

IF(Value <> 1);

    ASCIIOutput( 'D:\Planning_Analytics\Custom_Logs\Logger\SchluesselCheck_'  | sRunID | '.txt', V1, V2, V3, V4, V5, V6, V7, NumberToString( Value ));

ENDIF;

just logs every cell and the log says it have a 1, but thats not true.

Thanks!
Your check is "unequal 1" - then write the file. you say the value is not 1 - so it has to be in the file. looks ok for me - or i didn't get your problem

best regards,
orlando

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Turbo Integrator - Data tab dont run?

Post by HighKeys » Tue Jul 07, 2020 9:36 am

Hey,

i got some more details, i made the check manually and compared it to the csv output from the TI Process. Seems to be ok, but the output Value thats get logged is '1'.

I think this is very weird but it worked for me

Code: Select all

sValue = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');
nValue = StringToNumber(sWert);

IF(nWert <> 1);

    ASCIIOutput(logPath , V1, V2, V3, V4, V5, V6, sValue);
    nDataCount = nDataCount +1;

ENDIF;
now i can see the 'real' value and could add a column with "Diff to 100".


Is it normal that 'NUMBERTOSTRING' dont convert all decimals to the string? Is it based on the formatting?

Thanks for your help!

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Turbo Integrator - Data tab dont run?

Post by orlando » Tue Jul 07, 2020 10:36 am

HighKeys wrote:
Tue Jul 07, 2020 9:36 am
Hey,

i got some more details, i made the check manually and compared it to the csv output from the TI Process. Seems to be ok, but the output Value thats get logged is '1'.

I think this is very weird but it worked for me

Code: Select all

sValue = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');
nValue = StringToNumber(sWert);

IF(nWert <> 1);

    ASCIIOutput(logPath , V1, V2, V3, V4, V5, V6, sValue);
    nDataCount = nDataCount +1;

ENDIF;
now i can see the 'real' value and could add a column with "Diff to 100".


Is it normal that 'NUMBERTOSTRING' dont convert all decimals to the string? Is it based on the formatting?

Thanks for your help!
mmhhh. first of all i would not use nValue and sValue - https://www.ibm.com/support/knowledgece ... 80019.html
I'm not absolut sure if they are reserved, but you never know.

either way. you convert a numric to a string, then another string to a number and the unequal 1 check is made with a third variable.
and where do u user nDataCount
could you post the real process?

best regards orlando

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Turbo Integrator - Data tab dont run?

Post by HighKeys » Tue Jul 07, 2020 11:16 am

Hi Orlando,

sure i can post the original process, the DataCount is for counting the not 100% Values, but i think i should use a boolen or just 1 or 0, cause its just for the E-mail Output (Errors found or not)

Prolog:

Code: Select all


#Output Setup
sRunID = STR( NOW(), 8, 5 );
DataSourceAsciiQuoteCharacter = '"';
DataSourceAsciiDelimiter = ';';
logPath = 'D:\Planning_Analytics\Custom_Logs\Logger\MandantenSchluesselAuwertung_'  | sRunID | '.csv';


#Const Cube String
cCube = 'Mengen-Planung Monat Mandant ab 2019';


#Counters
nDataCount = 0;

#Create View
ViewCreate(cCube, 'tmpSchlüsselCheck_' | sRunID, 1);


#Create Subset for Mandant dimension with Sum of Mandant
SubsetCreate('Mandant', 'tmpSummeMandant_'  | sRunID, 1);

#Add element to  tmp Mandant Subset
SubsetElementInsert('Mandant', 'tmpSummeMandant_ ' | sRunID, 'Summe Mandanten', 0);

#Create tmp Subset for Kennzahlen 
SubsetCreate('Kennzahlen Mengen-Planung Monat Mandant ab 2019', 'tmpSchlüsselCheck_' |  sRunID, 1 );

#Insert Schlüsssel elements to Subset
SubsetElementInsert('Kennzahlen Mengen-Planung Monat Mandant ab 2019', 'tmpSchlüsselCheck_'  | sRunID, 'Schlüssel %', 0);
SubsetElementInsert('Kennzahlen Mengen-Planung Monat Mandant ab 2019', 'tmpSchlüsselCheck_'  | sRunID, 'bearbeitete Kontakte %', 1);

#Create tmp Subset for Jahre
SubsetCreate('Jahre', 'tmpJahreSchlüsselCheck_'  | sRunID, 1);
SubsetElementInsert('Jahre', 'tmpJahreSchlüsselCheck_'  | sRunID, pJahr, 0);

#Create tmp Subset for Version
SubsetCreate('Version', 'tmpVersionSchlüsselCheck_'  | sRunID, 1);
SubsetElementInsert('Version', 'tmpVersionSchlüsselCheck_'  | sRunID, pVersion, 0);


#Assign Subsets to View
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Planungseinheit', 'Alle n Level' );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Kennzahlen Mengen-Planung Monat Mandant ab 2019', 'tmpSchlüsselCheck_' | sRunID );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Kontaktart', 'n_Kontaktarten Gesamt' );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Jahre', 'tmpJahreSchlüsselCheck_'  | sRunID );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Monat', 'Monate_N_Level' );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Mandant', 'tmpSummeMandant_'  | sRunID );
ViewSubsetAssign( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Version', 'tmpVersionSchlüsselCheck_'  | sRunID );

#Sort Rows and Columns
ViewColumnDimensionSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Monat', 1 );
ViewColumnDimensionSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Kennzahlen Mengen-Planung Monat Mandant ab 2019', 2 );
ViewColumnDimensionSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Kontaktart', 3 );
ViewRowDimensionSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Planungseinheit', 1 );

#Setup Title Dims
ViewTitleDimensionSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 'Mandant' );

#SuppressZeros and dont skip consolidated objects
ViewExtractSkipCalcsSet(cCube, 'tmpSchlüsselCheck_'  | sRunID, 0);
ViewSuppressZeroesSet( cCube, 'tmpSchlüsselCheck_'  | sRunID, 1 );


#Switch datasource
DataSourceType = 'VIEW';
DatasourceNameForServer = cCube;
DatasourceCubeview = 'tmpSchlüsselCheck_'  | sRunID;

Data:

Code: Select all

sWert = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');
nWert = StringToNumber(sWert);

IF(nWert <> 1);

    ASCIIOutput(logPath , Jahre, Version, Monat, Planungseinheit, Kontaktart, V6, sWert);
    nDataCount = nDataCount +1;

ENDIF;

Epilog:

Code: Select all


IF(nDataCount > 0);

ExecuteProcess('System_SendHTMLmail', 'pRecipient', pEmail, 'pSubject', 'Automatische Mandanten Schlüssel Auswertung', 'pBody', 'Im Anhang befindet sich die automatische Mandanten Schlüssel Auswertung mit der ID  ' |  sRunID | '.','pAttachFile', logPath,  'pTitle', 'Automatische Mandanten Schlüssel Auswertung - ID  ' |  sRunID | '.');

nTime = NOW();
nStartNow = nTime;
nWaitTime = nStartNow + ( StringToNumber( '3' ) / 86400 );

WHILE( nTime <= nWaitTime );
  nTime = NOW();
END;

ASCIIDelete(logPath);

ELSE;

ExecuteProcess('System_SendHTMLmail', 'pRecipient', pEmail, 'pSubject', 'Automatische Mandanten Schlüssel Auswertung', 'pBody', 'Alle Schlüssel sind auf 100% geplant!.','pAttachFile', '',  'pTitle', 'Automatische Mandanten Schlüssel Auswertung - ID  ' |  sRunID | '.');

ENDIF;


I'm sorry if the code is a lil bit weird, i try to get more practice in TI Scripting.


Edit: Wert = Value in German, should i also avoid this? Didnt know it yet, then i have to rename the variables. thanks for the hint! :)

Thanks for your help!
BR

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Turbo Integrator - Data tab dont run?

Post by orlando » Tue Jul 07, 2020 1:00 pm

HighKeys wrote:
Tue Jul 07, 2020 11:16 am

Prolog:

ViewSuppressZeroesSet( cCube, 'tmpSchlüsselCheck_' | sRunID, 1 );
zeroSuppression is turned on - so the Wert<>1 gives you only the values that are eg 0.1 or 2 ...... you will not see the zero values

sWert = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');
nWert = StringToNumber(sWert);

IF(nWert <> 1);

ASCIIOutput(logPath , Jahre, Version, Monat, Planungseinheit, Kontaktart, V6, sWert);
nDataCount = nDataCount +1;

ENDIF;
Why don't you ask the original value "Wert"?

Edit: Wert = Value in German, should i also avoid this? Didnt know it yet, then i have to rename the variables. thanks for the hint! :)
nö, mach ich auch so.

so, if you like to filter the zero values - you have to turn off the zeroSuppression

best regards,
orlando

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Turbo Integrator - Data tab dont run?

Post by HighKeys » Tue Jul 07, 2020 1:11 pm

zeroSuppression is turned on - so the Wert<>1 gives you only the values that are eg 0.1 or 2 ...... you will not see the zero values
Thats correct! I just want to see Cells thats not 100 % and not 0% so something between (Its a matchtable and in Sum it should reach the 100%)

For sure i think i can ask for the original "Wert" Value but if i write the "Wert" to the log with 'NumberToString(Wert)' the Log shows a 1 and not 0,969999999999 as an example.

I got the output right if i convert it with StringEx, i thought stringex is just for formatting and the normal convertion will keep all float values (may i'm wrong?)

So your approch would be like this?

Code: Select all

sWert = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');

IF(Wert <> 1);

ASCIIOutput(logPath , Jahre, Version, Monat, Planungseinheit, Kontaktart, V6, sWert);
nDataCount = nDataCount +1;

ENDIF;


Thanks!


ps
nö, mach ich auch so.
:D Paar Deutsche gibts hier wohl :P

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Turbo Integrator - Data tab dont run?

Post by orlando » Tue Jul 07, 2020 1:39 pm

I got the output right if i convert it with StringEx, i thought stringex is just for formatting and the normal convertion will keep all float values (may i'm wrong?)

So your approch would be like this?


sWert = NUMBERTOSTRINGEX(Wert,'#.##############', ',','.');

IF(Wert <> 1);

ASCIIOutput(logPath , Jahre, Version, Monat, Planungseinheit, Kontaktart, V6, sWert);
nDataCount = nDataCount +1;

ENDIF;
NUMBERTOSTRINGEX converts a number to a formatted string. i'm pretty sure IBM messes this up if you convert it back to a number ;-)

for any calculation, if-statement or whatever i only use the original value

nö, mach ich auch so.
:D Paar Deutsche gibts hier wohl :P
[/quote]

jupp - more than you think

best regards,
orlando

Post Reply