How to Edit TI Process without first Executing it?
-
- 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?
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.
Appreciate any help/suggestions.Thanks.
-
- 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?
I'm not sure there is a way to open a TI without it automatically initiating the datasource query.
However, for ongoing issues:
If it's one specific TI you have already written that is the issue:
However, for ongoing issues:
- Leave the datasource query box empty.
- Set the query in the prolog tab.
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: How to Edit TI Process without first Executing it?
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: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.
Code: Select all
SELECT Col1, Col2, Col3
FROM Your_Table
WHERE Col1=1
AND Col2=etc
AND 1=0
Code: Select all
SELECT Col1, Col2, Col3
FROM Your_Table
WHERE Col1=1
AND Col2=etc
AND 1=?pEditFlag?
Last edited by rmackenzie on Mon Sep 02, 2013 2:58 am, edited 1 time in total.
Robin Mackenzie
-
- 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?
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 :.
Solved my issues instantly.
Do you know why it behaves like this with numeric parameters?
Thank you,
ET
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: How to Edit TI Process without first Executing it?
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: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:
Code: Select all
SELECT A.Account, A.Year, A.Month, A.Balance
FROM SomeTable A
WHERE
A.Year = ?pYear?
Code: Select all
SELECT A.Account, A.Year, A.Month, A.Balance
FROM SomeTable A
WHERE
A.Year = '?pYear?'
Code: Select all
AND 1 = ?EditpFlag?
Robin Mackenzie
-
- 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?
Thanks mate,
Counter - intuitive indeed.
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
Counter - intuitive indeed.
did work for me, thank you.A.Year = ?pYear?
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
-
- 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?
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!
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!
-
- 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?
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'));
pYear = NumberToString(CellGetN('Control Cube', 'Year', 'Current'));
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: How to Edit TI Process without first Executing it?
But it still matters if you're doing this in the SQL: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'));
Code: Select all
A.Year = ?pYear?
Code: Select all
A.Year = '?pYear?'
Robin Mackenzie
-
- 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?
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
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
- PavoGa
- MVP
- Posts: 617
- 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?
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.
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
Cleveland, TN
- 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?
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
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
-
- MVP
- Posts: 2832
- 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?
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.
-
- 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?
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
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
- 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?
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- 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?
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- Steve Rowe
- Site Admin
- Posts: 2417
- 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?
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...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
Technical Director
www.infocat.co.uk
www.infocat.co.uk