Use SQL-Read in Prolog?

[schlemiel29]

I'm importing data from SQl-Server to a cube. To clear the slice before, I need the information for which year the data will be delivered.
How do I get the year element name?
I thought in manner like this:

sYear = Execute('SELECT Year From Settingstable');

But which function I have to use as >Execute< to get a return value from the select statement?
Do I first have to open a ODBC-Connection (one is already used for the data import itself)?
Best regards
Dirk

[Lukas Meyer]

I'm not aware of a possibility to query the ODBC-datasource within a process, but I have workarounds a had to cope with

In prolog:

Code: Select all

executeonce=1;

In data, very first statement to be executed:

Code: Select all

IF(executeonce=1);
  #clear data here, variables are accessible 
  #I called a zero-out process here, maybe it works directly...
  executeonce = 0;
ENDIF;

this will work but isn't really great.

You could call a subprocess via EXECUTEPROCESS that uses the same connection as yours and updates values in a settings-cube. (be careful to not obtain a w-lock for the settings cube in the parent process)
If you don't know what elements need to be cleared (multiple years/months/accounts), alter your sql-query so that it only returns one column of the needed elements (keyword distinct) and have one sub-process each, every subprocess altering it's own subset.
(if you alter existing subsets rather than recreating, your server should be longer accessible as the processes don't need to obtain a lock)
This should work, I used it in TM1 9.

alternatively you can create the subset(s) in the meta-data tab - it might be best to use mdx to distinct the subsets as they will have lots of duplicates. I'm not certain that this works, I don't recall if subsets may be registered/altered during data-tab

[rmackenzie]

I'm importing data from SQl-Server to a cube. To clear the slice before, I need the information for which year the data will be delivered.

Why don't you create a 'settings' cube and load that from 'SettingsTable' in a separate process before you run the clear-and-reload process? I realise Lukas suggests this, in part, but overall the presented workaround seems overly complex and a bit unclear.

[schlemiel29]

Yes, this is a possible solution. I would have prefered a direct access to SQL-Server instead of managing a copy of a sql table to a TM/1 cube.
If there is no change of querying SQL server within the prolog, I will use this workaround.
Thanks for your hints
Dirk

[rmackenzie]

Yes, this is a possible solution. I would have prefered a direct access to SQL-Server instead of managing a copy of a sql table to a TM/1 cube.
If there is no change of querying SQL server within the prolog, I will use this workaround.

There is a workaround, but it is more complex than managing a TM1 copy of the SQL table. You can make a ODBC-based process that looks at the particular field that you are interested in, making sure that the data source query selects one row and one column. Then, have your main process call that one using a global variable to store the result of the query. You can get fancy with the workaround and parameterise this process and then use DataSourceQuery or standard SQL parameterisation to have it look up different fields in different tables. You'd invoke it something like this:

Code: Select all

# prolog of main process
StringGlobalVariable('YOUR_SETTING_VARIABLE_NAME'); 
ExecuteProcess ( 'Sys_Lookup_SQL_Settings', 'pSQL', 'SELECT SETTING_1 FROM SETTINGS_TABLE WHERE MAKE_SURE_1_ROW_COMES_BACK = TRUE');
# YOUR_SETTING_VARIABLE_NAME should now contain the correct value
# ...

# prolog of helper process
DataSouceQuery = pSQL;
StringGlobalVariable('YOUR_SETTING_VARIABLE_NAME');

# data tab of helper process
YOUR_SETTING_VARIABLE_NAME = V1;

Hope that makes sense? I guess this approach is useful when you really need to look stuff up on the fly. But for basic settings stuff that changes maybe only at most on a daily basis, I would use the settings cube.