Error Handling within a TI process?

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Error Handling within a TI process?

Post 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
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Error Handling within a TI process?

Post 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 ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Error Handling within a TI process?

Post 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.
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Error Handling within a TI process?

Post 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;
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Error Handling within a TI process?

Post 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.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Error Handling within a TI process?

Post 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);
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Error Handling within a TI process?

Post 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') 
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Error Handling within a TI process?

Post by Wim Gielis »

In case elements in TM1 themselves contain strange characters, you might be having additional issues.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Error Handling within a TI process?

Post 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!
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Error Handling within a TI process?

Post 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 ...
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Error Handling within a TI process?

Post by michaelc99 »

Thank you everyone for your help! I was able to get the output that I was looking to achieve!
Post Reply