kbogies wrote:i'm reposting the below topic because the issue is believed to be a clitch with the datasource rather than the TI syntax.
The TI process's datasource is a SQL sub query via ODBC connection. See below for query. The query presents the expected results in the datasource tab's preview window. The sample value is also appearing as expected on the variables tab, where i've identified the variable as a string and identified the contents as other to write advanced syntax.
However, the variable does not register properly on the advanced prolog tab. Initial testing with IBM (applix) moved syntax to the epilog tab and the variable registered correctly, until the following day when the variable did not register on either advanced tab.
Has anyone experienced similar issues with odbc datasources?
http://forums.olapforums.com/viewtopic.php?f=3&t=1060
sql sub query:
select next_proc from dbo.EXEC_RUN_LOG
where ID = (select max(ID) from dbo.EXEC_RUN_LOG)
It is unsafe to expect ANY values to be available from a process' data source on either the Prolog or Epilog tabs, particularly the former.
The prolog executes once, before a single row of the data source is read. At that time, any variables will have a value of either 0 or an empty string, as the case may be. As I mentioned in a recent thread, my own view is that the compiler should throw a syntax error if the Variables tab variables are used in the Prolog since it's effectively the same as having an uninitialised code variable. It doesn't, but it should.
The Metadata tab will loop through each row of the data source once, allowing the process to add any new elements and the like.
The Data tab will loop through each row of the data source once, allowing the data to be written to the cubes or otherwise manipulated.
The Epilog tab will execute once, AFTER the last row of data has been processed.
Although I haven't tested it, I would expect any process (Variables tab) variables to retain the values that they had from the last row of data when the Epilog is executed... however if the data source HAD no data then again the variables would be either 0 or an empty string, as the case may be.
(Though even if this supposition is right, you're still using the variable values out of context and IMHO, in an unsafe fashion. The only places that Variables tab variable values have a valid context are in the Metadata or Data tabs.)