Disable Preview of ODBC data source in TI?

[stex2727]

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

[Alan Kirk]

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.

[Gregor Koch]

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

[Alan Kirk]

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.

[LoadzaGrunt]

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]

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

[paulsimon]

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]

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]

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)
...
...