TM1 Forum

Hi Everyone,
I’ve got a ti that updates a dimension with sql table as data source. So I got a parameter used to filter the dimension name in sql. If user has a typo in parameter (that means spelling doesn’t match the one in sql), then I wanna generate an error in the log & quit the ti because sql query wouldn't return any row/data. Is there a way to do this in Prolog?

elee123 wrote:Hi Everyone,
I’ve got a ti that updates a dimension with sql table as data source. So I got a parameter used to filter the dimension name in sql. If user has a typo in parameter (that means spelling doesn’t match the one in sql), then I wanna generate an error in the log & quit the ti because sql query wouldn't return any row/data. Is there a way to do this in Prolog?

Not in a single-TI solution. This is because Prolog is executed before the data source is accessed, so you have no knowledge of what the data source will have returned.

Having said that, you can achieve that with a little bit of creativity, so not sure why you want to limit your options to Prolog code only. Why not e.g. have a counter incremented on the Data tab. Then in Epilog you can check if the counter has been incremented and if not (meaning that the data source was empty, so presumably the parameter incorrect), raise an error then.

Another approach would be to have a subprocess executed from the Prolog whose only purpose would be to run a separate query and reurn the status of the parameter. That way you'd run two queries - one to validate the parameter and one to get the data when the validating query is successful.

Or run a distinct query into SQL first to create a dimension in TM1 that will form a picklist where the user is validated on their selection of parameter before it ever goes out to the process.