How to Edit TI Process without first Executing it?

Post Reply
lepakshi
Posts: 1
Joined: Sun Sep 01, 2013 6:29 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007

How to Edit TI Process without first Executing it?

Post by lepakshi »

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.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

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

Post by declanr »

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.)
Declan Rodger
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

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

Post by rmackenzie »

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.
Last edited by rmackenzie on Mon Sep 02, 2013 2:58 am, edited 1 time in total.
Robin Mackenzie
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

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

Post by EvgenyT »

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
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

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

Post by rmackenzie »

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
Robin Mackenzie
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

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

Post by EvgenyT »

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
jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

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

Post by jimicron »

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!
jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

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

Post by jimicron »

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'));
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

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

Post by rmackenzie »

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.
Robin Mackenzie
venkat
Posts: 3
Joined: Sat Sep 06, 2014 3:03 pm
OLAP Product: Cognos
Version: Cognos TM1 10.0.1
Excel Version: Excel 2007

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

Post by venkat »

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
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

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

Post by PavoGa »

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.
Ty
Cleveland, TN
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

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

Post by paulsimon »

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
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

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

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
venkat
Posts: 3
Joined: Sat Sep 06, 2014 3:03 pm
OLAP Product: Cognos
Version: Cognos TM1 10.0.1
Excel Version: Excel 2007

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

Post by venkat »

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
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

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

Post by jim wood »

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

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

Post by jim wood »

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

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

Post by Steve Rowe »

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...
Technical Director
www.infocat.co.uk
Post Reply