Disable Preview of ODBC data source in TI?

Post Reply
stex2727
Posts: 65
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Disable Preview of ODBC data source in TI?

Post by stex2727 » Mon Oct 19, 2009 11:04 pm

Hi all, long time listener, first time caller.

I have a TI process that is running an Oracle Query (takes about 12 mins to run). It would appear to me that even when I only want to edit the script it runs the query before opening TI. Effectively for a small change I need to run the SQL script twice. Once to open TI for editing and secondly to actually run the corrected script.

What I'm wondering is it possible to disable the preview of datasource in TI?

Steve

User avatar
Alan Kirk
Site Admin
Posts: 5878
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Disable Preview of ODBC data source in TI?

Post by Alan Kirk » Mon Oct 19, 2009 11:25 pm

stex2727 wrote:Hi all, long time listener, first time caller.

I have a TI process that is running an Oracle Query (takes about 12 mins to run). It would appear to me that even when I only want to edit the script it runs the query before opening TI. Effectively for a small change I need to run the SQL script twice. Once to open TI for editing and secondly to actually run the corrected script.

What I'm wondering is it possible to disable the preview of datasource in TI?
Welcome!

To the best of my knowledge there's no setting that you can make to regulate this, but if you don't need to monkey around with the variables there's a "lateral thinking" way around it. (Other than deleting the ODBC connection on your desktop, which is another option since when the process runs it'll use the one on the server. When you edit it it won't connect, because the connection won't exist. This is somewhat more flexible, however.)

You can open the process, delete the UserName that's been defined, then re-save the process.

Yes, you have to wear it previewing the thing this time, but it'll be the last time because thereafter when you open the process for editing you'll get a "Cannot connect to database" error, which you can ignore.

The last step in the exercise is to put a

Code: Select all

DatasourceUserName='YourUserName';
function in the Prolog.

That means that in future, the connection to the database will only be made at runtime.

If you really do need to alter the variables you can re-enter the user name on the Data Source tab and have it refresh. Yes, you'll again have to wear the wait if you do that, so best to make sure that you do whatever you need to with the variables once and once only, but you can edit the code to your heart's content.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Gregor Koch
MVP
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: Disable Preview of ODBC data source in TI?

Post by Gregor Koch » Mon Oct 19, 2009 11:40 pm

Hi Alan

I like the solution with the username but I don't think that the ODBC connection on your desktop (not server) is not used by TI ever, preview or load.

Cheers

User avatar
Alan Kirk
Site Admin
Posts: 5878
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Disable Preview of ODBC data source in TI?

Post by Alan Kirk » Mon Oct 19, 2009 11:53 pm

Gregor Koch wrote:Hi Alan
I like the solution with the username but I don't think that the ODBC connection on your desktop (not server) is not used by TI ever, preview or load.
Why I do stand corrected.

I just created a new ODBC source on the server to connect to the (SQL Server-based) GL system, created and saved a process on the server using that DSN, and then opened that process on my desktop (where I had NOT created the DSN). Yet as you predicted, it opened and previewed correctly.

For some reason I thought that it was always seen from the client's point of view much as text file paths are, but clearly it's not with an ODBC type.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
LoadzaGrunt
Posts: 72
Joined: Tue May 26, 2009 2:23 am
Version: LoadzaVersions
Excel Version: LoadzaVersions

Re: Disable Preview of ODBC data source in TI?

Post by LoadzaGrunt » Tue Oct 20, 2009 12:43 am

Hi Steve,

Yes, this is incredibly annoying. My workaround is to have two TIs - one with a full query and one with a additional clause in the WHERE statement that means only one row is returned. E.g.

Code: Select all

SELECT
  whatever_columns
FROM
  set_of_tables
WHERE
  your_criteria
AND
  something_here_to_return_one_row_only
This doesn't guarantee that the query will return faster but in many cases it does. You can then work on the TI script and have a single modification to make to the TI containing your full query. So, the idea is you know you are only going to incur the pain once !

There are techniques to limit the number of rows returned by your query but I have a fair idea that it is specific to MS SQL Server:
http://www.4guysfromrolla.com/webtech/070605-1.shtml

HTH

David Usherwood
Site Admin
Posts: 1351
Joined: Wed May 28, 2008 9:09 am

Re: Disable Preview of ODBC data source in TI?

Post by David Usherwood » Tue Oct 20, 2009 7:08 am

For Oracle, using
where row_number < 10
does something similar.

User avatar
paulsimon
MVP
Posts: 676
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Disable Preview of ODBC data source in TI?

Post by paulsimon » Tue Oct 20, 2009 11:14 pm

The other approach is to use the TI substitution variables. Eg we always had to subsitute the database name as we needed to go to 30 different databases, anyway, and this had the side-effect of getting past this problem.

Run the TI once to get the variables. Map them, etc, and build your TI. Test it. Save it. Re-open it.

change the SQL so eg

Select CustomerName
From
MyDB.dbo.Customer

becomes

Select CustomerName
From
?vDB?.dbo.Customer

Then save your TI clicking keep existing variables.

Go to prolog

vDB = 'MyDB' ;

As your SQL is now invalid, it won't run in preview mode, but at run time TI will do the substitution that is needed in the Prolog to make it run correctly, so ?vDB? will become MyDB and the SQL will run.

Just make sure that you never say keep derived variables only and it will work OK. If you add a column to your SQL selection, you will need to fix the SQL to run once properly to get the variables, and then you can revert to the substitution variable.

Regards


Paul Simon

stex2727
Posts: 65
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Disable Preview of ODBC data source in TI?

Post by stex2727 » Wed Oct 21, 2009 11:38 pm

Thanks all, I knew there would be a way. Just couldnt spot it while hunched over the screen and a project manager with a large cane behind me.

Steve

olapuser
Posts: 40
Joined: Fri Jan 29, 2010 1:55 am
OLAP Product: Cognos TM1
Version: 9.5
Excel Version: 2007
Contact:

Re: Disable Preview of ODBC data source in TI?

Post by olapuser » Wed Feb 03, 2010 3:04 am

Sorry OOT, just another idea

create your own TI function using .Net and create libraries then code the TI by the .Net way consol app or win form or web app depend on your needs.

Code: Select all

public double CellGetN(string cube, params string[] elements )
public void CellPutN(double value, string cube, params string[] elements )
public void ExecuteProcess(string processName, params object[] parameters)
...
...

Post Reply