TM1 Forum

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

Error Handling within a TI process?

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

Page 1 of 1

Error Handling within a TI process?

Posted: Mon Apr 04, 2022 9:03 pm
by michaelc99
Good Afternoon,

I created a Drill-Through process that returns records from an MS-SQL database. In the WHERE clause, I parameterized the ENTITY, PERIOD and ACCOUNT dimensions then used ELLEV and ELISANC in the drill rules against the ACCOUNT dimension to prevent users from executing the query against C-level and Comp and Ben accounts. As long as the user goes down to the zero-level for both Account and Entity dimensions then the query returns results. I may need to write ELLEV level drill rules for ENTITY, but may tackle that later on.

I am going to add the following code (below) to the top of Prolog to ensure that the connection to the database is available. However, is it possible to display "The database containing the GL Transactions is currently unavailable. Please try again later." within PAW and PAfE should the 'ConnectionTest' fail?

Code: Select all

ExecuteProcess( 'ConnectionTest');
NumericGlobalVariable( 'ProcessReturnCode');
If( ProcessReturnCode <> ProcessExitNormal());
   ProcessQuit;
EndIf;
Additionally, if the business decides against creating an ELLEV drill rule for ENTITY, and a user fires the query against a C-Level member, the result of the query will fail and return an error message with SQL code embedded. How would I write error-handling to return something like, "Please select a valid Entity then re-run your query" instead of the default error message?


Thank you in advance,
Michael

Re: Error Handling within a TI process?

Posted: Mon Apr 04, 2022 10:32 pm
by Wim Gielis
When the user selects a consolidated element, you will typically create a loop over the descendent elements. This will in turn creates an IN( …, …, … ) statement all in the prolog tab. Will this query work or error out ?

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 12:07 am
by burnstripe
An alternative is create sql tables storing the consolidation to leaf mapping.

Col 1 - consolidation
Col 2 - leaf

These mapping tables would only need updating when the structure is changed. Which you could automate by exporting the structure to csv and then executing a stored sql procedure to load.

Once the tables are in place you can then write a sql with an inner join linking the data and mapping tables, thus allowing you to pass any element as a parameter.

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 12:13 pm
by michaelc99
Wim Gielis wrote: ↑Mon Apr 04, 2022 10:32 pm When the user selects a consolidated element, you will typically create a loop over the descendent elements. This will in turn creates an IN( …, …, … ) statement all in the prolog tab. Will this query work or error out ?
In the current form, the user will not be able to run the drill through process on a consolidated account level. But, if they run it on a consolidated Entity level then the query will error out. I have the code (below) to loop through the C-level and grab zero level account numbers, but I don't think I have successfully figured out the formatting of sAcct and pAccount, so the SQL query errors out.

Code: Select all

IF( ELLEV('ACCOUNT MAIN',pAccount) > 0 );
  DimName = 'ACCOUNT MAIN';
  SubName = DimName | ' Drill Set';

  IF( SUBSETEXISTS(DimName,SubName) = 1 );
    SUBSETDESTROY(DimName,SubName);
  ENDIF;

  SUBSETCREATEBYMDX(SubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[ACCOUNT MAIN].[' | pAccount | ']}, ALL, RECURSIVE )}, 0)}');
  iIndex = 1;
  sAcct = ''' | SubsetGetElementName(DimName,SubName,iIndex) | ''';

  
  WHILE(iIndex < SubsetGetSize(DimName,SubName) );
    iIndex = iIndex+1;
    sAcct = sAcct | ', ''' | SubsetGetElementName(DimName,SubName,iIndex) | '''';
  END;

  sAcct = sAcct | ',';
  pAccount = sAcct ;
ELSE;
  pAccount  = '' | pAccount | '';
ENDIF;

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 12:43 pm
by Wim Gielis
Hi

Indeed formatting issues can be a pain sometimes.

You need to use TextOutput for instance and simply copy paste the resulting query in an ODBC client to see it that one is correct or not.

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 12:56 pm
by michaelc99
Wim Gielis wrote: ↑Tue Apr 05, 2022 12:43 pm Hi

Indeed formatting issues can be a pain sometimes.

You need to use TextOutput for instance and simply copy paste the resulting query in an ODBC client to see it that one is correct or not.
I will add the following and see where my syntax is failing on C-Level data pulls.

ASCIIOutput('DW_DrillOutput.xls', sAcct, pAccount, pCompanyKey, sQuery);

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 1:02 pm
by burnstripe
Try something like this. Whenever I have to insert a ' I always use char(39) to stop my eyes hurting :D

Code: Select all

pAccount = '10000';
DimName = 'Account';
SubName = DimName | ' Drill Set';

sAcctSQL = '[Account] IN (';

  IF( SUBSETEXISTS(DimName,SubName) = 1 );
    SUBSETDESTROY(DimName,SubName);
  ENDIF;

  SUBSETCREATEBYMDX(SubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Account].[' | pAccount | ']}, ALL, RECURSIVE )}, 0)}');
  iIndex = 1;

  SubsetGetElementName( DimName, SubName, iIndex 
  sAcct = SubsetGetElementName(DimName,SubName,iIndex);
  sAcctSQL = sAcctSQL | char(39) | sAcct | char(39);

  iIndex = 2;
  WHILE(iIndex <= SubsetGetSize(DimName,SubName) );
	sAcct = SubsetGetElementName(DimName,SubName,iIndex);
	sAcctSQL = sAcctSQL | char(44) | char(39) | sAcct | char(39);
	iIndex = iIndex + 1;
  End;

  sAcctSQL = sAcctSQL ' | ')';


# This should give something like this
# [Account] IN ( '1','23','48')

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 1:44 pm
by Wim Gielis
In case elements in TM1 themselves contain strange characters, you might be having additional issues.

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 2:58 pm
by michaelc99
burnstripe wrote: ↑Tue Apr 05, 2022 1:02 pm Try something like this. Whenever I have to insert a ' I always use char(39) to stop my eyes hurting :D

Code: Select all

pAccount = '10000';
DimName = 'Account';
SubName = DimName | ' Drill Set';

sAcctSQL = '[Account] IN (';

  IF( SUBSETEXISTS(DimName,SubName) = 1 );
    SUBSETDESTROY(DimName,SubName);
  ENDIF;

  SUBSETCREATEBYMDX(SubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Account].[' | pAccount | ']}, ALL, RECURSIVE )}, 0)}');
  iIndex = 1;

  SubsetGetElementName( DimName, SubName, iIndex 
  sAcct = SubsetGetElementName(DimName,SubName,iIndex);
  sAcctSQL = sAcctSQL | char(39) | sAcct | char(39);

  iIndex = 2;
  WHILE(iIndex <= SubsetGetSize(DimName,SubName) );
	sAcct = SubsetGetElementName(DimName,SubName,iIndex);
	sAcctSQL = sAcctSQL | char(44) | char(39) | sAcct | char(39);
	iIndex = iIndex + 1;
  End;

  sAcctSQL = sAcctSQL ' | ')';


# This should give something like this
# [Account] IN ( '1','23','48')

I made a few tweaks to the above as PAW didn't like sAcctSQL = sAcctSQL ' | ')'; . It errored out with the following message, "Error at Prolog, line 37 + Syntax error on or before: " ' | ')'; missing semicolon "". After several attempts to correct, I ended up moving the end-parenthesis to the query (as shown below) and it worked. I got the query to fire correctly against C-Level accounts numbers. I will now replicate the code for Entity.

Code: Select all

 WHERE 
                ( FACTGENERA.CompanyKey IN (' | pCompanyKey |')
                AND FACTGENERA.TransactionDateKey BETWEEN ' | BOP | ' AND ' | EOP | ')
                ' | sAcctSQL |' )
Though, if the database is down, is there a way to display an error message in PAW or PAfE?

Thank you!

Re: Error Handling within a TI process?

Posted: Tue Apr 05, 2022 5:22 pm
by burnstripe
Whoops sorry was writing freehand,
sAcctSQL = sAcctSQL ' | ')';
should have been
sAcctSQL = sAcctSQL | ')';

Glad you got it in the end. Displaying messages is a bit awkward, itemreject can be used to display messages to admin users but normal users won't be able to see this. Custom messages might be in the pipeline but who knows when it will come in.

If you want some sort of messages you could store them in a logging cube and link to that in your report/dashboard. However if you want a message when the database is down as the message would likely need generating in a parent process, which runs the drill and determines if the process has run successfully or not, and then populates the cube with the status.

You could fire off emails in such events instead with the message ...

Re: Error Handling within a TI process?

Posted: Mon Apr 18, 2022 6:47 pm
by michaelc99
Thank you everyone for your help! I was able to get the output that I was looking to achieve!
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited