TM1 Forum

Posted: **Sun Sep 01, 2013 7:50 am**

While editing a TI process, it first executes the SQL Query before opening it. As the Query is incorrect, it goes into infinite loop, and the TI process hangs. Is there a way to Edit a TI process, without first executing it?

Appreciate any help/suggestions.Thanks.

Posted: **Sun Sep 01, 2013 9:12 am**

I'm not sure there is a way to open a TI without it automatically initiating the datasource query.

However, for ongoing issues:

Leave the datasource query box empty.
Set the query in the prolog tab.

This will allow you to open the TI to your heart's content all day long without it running the query until you press execute.

If it's one specific TI you have already written that is the issue:

Just create a new TI with the correct query (you can save the query in your prolog if you want to do the above)
If you need to get your code from the prolog, data, metadata & epilog tab - you could try opening the old TI within the data directory as a text file (but do this at your own risk.)

Posted: **Sun Sep 01, 2013 9:52 am**

declanr wrote:I'm not sure there is a way to open a TI without it automatically initiating the datasource query.

However, for ongoing issues:
Leave the datasource query box empty.
Set the query in the prolog tab.

The only problem there is that there is the risk that when the TI editor asks to update the variables that someone selects the option where the previous variables aren't retained and this will mean the variables get wiped. To prevent this you can have a query like this:

Code: Select all

SELECT Col1, Col2, Col3
FROM Your_Table
WHERE Col1=1
AND Col2=etc
AND 1=0

It is the last '1=0' bit which is important here as it will force the query to return zero rows. But this will prevent any data being processed at run-time, so the query really needs to be like this:

Code: Select all

SELECT Col1, Col2, Col3
FROM Your_Table
WHERE Col1=1
AND Col2=etc
AND 1=?pEditFlag?

You need a parameter in your TI called 'pEditFlag' which is defaulted to zero. When the preview builds it will use the default and prevent any rows returning. At run-time, set the flag to 1 and you will get the full row-set. It's a neat trick - quite a few people use it.

Posted: **Mon Sep 02, 2013 12:10 am**

Quite a neat solution rmackenzie, thanks for posting... I was literally thinking of how to stop query being executed each time you open your TI another day.

I came across a small issue though... Maybe you can shine some light on it please?

When I set up pEditFlag as a numeric parameter, 1 = pEditFlag was returning "SQL failed...", so I scratched my head for a second and found a work-around solution:

set pEditFlag to a String and then in my sql convert it into a number :

Code: Select all

AND 1 = CAST('?EditpFlag?' as INT)

.

Solved my issues instantly.

Do you know why it behaves like this with numeric parameters?

Thank you,

ET

Posted: **Mon Sep 02, 2013 2:57 am**

EvgenyT wrote:When I set up pEditFlag as a numeric parameter, 1 = pEditFlag was returning "SQL failed...", so I scratched my head for a second and found a work-around solution:

It's a bit counter-intuitive but when you are going to use your parameter inside the data source query then the parameter should always be set as a string even if it represents numeric data. This is because the query itself is a string and you are basically doing an insert of one string value (your parameter) inside of another string value (the query). So, if your parameter is numeric data then set the parameter as a string and use it like this, e.g. where A.Year is a INT field:

Code: Select all

SELECT A.Account, A.Year, A.Month, A.Balance
FROM SomeTable A
WHERE
A.Year = ?pYear?

Suppose in that example, A.Year is a CHAR field then just wrap the parameter in single quotes:

Code: Select all

SELECT A.Account, A.Year, A.Month, A.Balance
FROM SomeTable A
WHERE
A.Year = '?pYear?'

So, in your fix that you presented, you probably should find that this works as long as the data type of the parameters is String and the actual value is a valid number (i.e. zero or one):

Code: Select all

AND 1 = ?EditpFlag?

HTH

Posted: **Mon Sep 02, 2013 4:06 am**

Thanks mate,

Counter - intuitive indeed.

A.Year = ?pYear?

did work for me, thank you.

Another odd feature of TM1 SQl interpreter is that using A.Year = ?pYear? kind of scenario blanks out SQL preview even though there are records coming through.

that's my 2 cents worth

Thanks

ET

Posted: **Tue Apr 08, 2014 8:14 pm**

Wondering if we could take this just a little bit further

So, if the parameter, 'pYear,' is changed to "String" on the Parameters tab in the Advanced section of the TI process...

What happens if 'pYear' is actually coming from let's say a "Control" cube so it's like this:

pYear = CellGetN('Control Cube', 'Year', 'Current');

The point of the above is that in the Control cube, the year is a number and thus, CellGetN is used. If you change the parameter to String from Numeric, then the CellGetN doesn't work

Thus, wondering how to accommodate that?

I am currently facing this scenario and ran along this thread. Thanks a lot!

Posted: **Tue Apr 08, 2014 8:40 pm**

To add, I tried the following and while it didn't error out or anything, it also didn't produce anything:

pYear = NumberToString(CellGetN('Control Cube', 'Year', 'Current'));

Posted: **Tue Apr 08, 2014 11:27 pm**

jimicron wrote:To add, I tried the following and while it didn't error out or anything, it also didn't produce anything:

pYear = NumberToString(CellGetN('Control Cube', 'Year', 'Current'));

But it still matters if you're doing this in the SQL:

Code: Select all

A.Year = ?pYear?

or

Code: Select all

A.Year = '?pYear?'

If your Year column in the table is a string-type then you need the second example regardless of whether pYear is a string in TM1.

Posted: **Tue Feb 19, 2019 12:06 pm**

Hello,

I am not able to open/edit the TI since this TI process source as SQL query, When i try to open the TI it will loading for long time after then it went not responding mode.

Any suggestion here

Could you Please help me .

Thanks

Posted: **Tue Feb 19, 2019 12:19 pm**

Assuming you want to save the coding on the Advance tabs:

1) save the TI off to another file.
2) delete the TI from within Architect.
3) open the copy with a text editor such as Notepad++
4) rebuild your desired TI using the suggestions from above to set the data source, copy and paste the code sections from the saved TI into the new one.

Posted: **Tue Feb 19, 2019 11:23 pm**

Hi Venkat

The other option if you have access to the SQL side is to start up SQL Server Management Studio right click on the server and start Activity Monitor and then you can kill the SQL query which will then allow the TI process to open. Possibly a bit less fiddly then copying the process out. Just make sure that you do kill the right SQL query.

I normally parameterise SQL queries anyway so I don't get the problem that you are having, but we do sometimes have to do this as we have a mixed TM1 and BI-SQL Server setup and on occasions locking in SQL Server can stop things from working. The only solution is to kill of the offending SQL query.

Regards

Paul Simon

Posted: **Wed Feb 20, 2019 2:38 pm**

venkat wrote: ↑Tue Feb 19, 2019 12:06 pm Hello,

I am not able to open/edit the TI since this TI process source as SQL query, When i try to open the TI it will loading for long time after then it went not responding mode.

Any suggestion here

Could you Please help me .

Thanks

Did you even bother to read the posts in this topic or did you just hijack the post to ask a question? The reason I ask is that the earlier posts actually give you several examples of how to do what you are asking.

Posted: **Wed Feb 20, 2019 3:28 pm**

Hi ,

Thank you for reply, unfortunate i dont have access to SQL SERVER. I have given wrong password in my system attribute cube and trying to run the TI process, Assuming TI process fails without execution since credentials are incorrect and open the TI but no luck still loading since 3 hours.

Any suggestions please

Thanks
Venkata

Posted: **Wed Feb 20, 2019 4:27 pm**

Why not just point to an ODBC that doesn't exist. That way when the process opens the query fails. The using parameters or a lookup simply overwrite the ODBC data source. That doesn't involve any code as you should be including a lookup for the ODBC anyway to make deployment easier.

With the above, the only time you need the query to run is the first time without a parameter to generate variables etc. Much easier than inserting flags etc...

Jim.

Posted: **Wed Feb 20, 2019 4:29 pm**

Or even have the source as a flat file with correct number of fields in it. Then override. That way you can amend your file to match without executing a query at all. You can specify the query in the process.

Posted: **Wed Feb 20, 2019 5:04 pm**

Hi ,

Thank you for reply, unfortunate i dont have access to SQL SERVER. I have given wrong password in my system attribute cube and trying to run the TI process, Assuming TI process fails without execution since credentials are incorrect and open the TI but no luck still loading since 3 hours.

Any suggestions please

Thanks
Venkata

This is a very bad idea, if you use failing credentials to stop a query from executing you will usually find that the account gets locked. Then it will never work...

TM1 Forum

How to Edit TI Process without first Executing it?

How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?

Re: How to Edit TI Process without first Executing it?