TM1 Forum

Posted: **Fri Mar 17, 2017 3:46 pm**

Hello everyone!
IBM Cognos TM1 Build Number: 10.2.20603.106
OS: Windows Server 2012 R2 Standart

We have several servers. On each server (OS - the same) there are several different Cognos models (TM1 Version - the same).

There is a need to transfer data from one server to another and from another model.
The customer insists on using ODBO-connection.
When loading with ODBO, we need to use the MDX-Query.

For example.
#----------------#
Source1:
ODBO Provider - IBM Cognos TM1 OLE DB MD Provider
ODBO Datasource - ServerName1
ODBO Catalog - ModelName1
ODBO UserID - User1
ODBO Password - Password1
MDXQuery1

Source2:
ODBO Provider - IBM Cognos TM1 OLE DB MD Provider
ODBO Datasource - ServerName2
ODBO Catalog - ModelName1
ODBO UserID - User1
ODBO Password - Password1
MDXQuery1

Target:
ServerName3
ModelName3

There is a server ServerName3 and model ModelName3.
We need to transfer data from ServerName1 and ModelName1.
We write the TI process and use the ODBO-connection as the source.
We select MDX-Query and write a query.

At some point, the customer changes the server (the model in this example is not necessary).
Now, as a source, we need to use ServerName2.

We have many TI-processes, like this.
Therefore, we would like to change the name of the server in one place so that the name of the server changes to the new one in all TI-processes at once.
#----------------#

Based on the situation above, the question is:
Is it possible to set the ODBO Datasource in the Prolog of the TI-Process?

After a short investigation, it became clear that:
- I did not find a special local variable for the possibility to set ODBO Datasource;
- You can set a subset (DataSourceODBOHierarchyName) and a cube (DataSourceODBOCubeName), but there is no separate local variable for MDX-Query (for example, it could be called DataSourceODBOQuery). This is another additional question;
# Local variable DatasourceQuery, as it seems to me, should not work here.
- You can set ODBO Catalog using the local variable DataSourceODBOCatalog.

This idea arose because for other sources for TI-processes, it is possible to set the types and source names in the Prolog tab.
For example, when we specify, as a source, the view of a cube, then we can use local variables in Prolog of TI-process.

For example.
DataSourceType = 'VIEW';
DatasourceNameForServer = sSrcCube;
DatasourceNameForClient = sSrcCube;
DatasourceCubeView = sView_Sub;

For information.

Link to local variables:
https://www.ibm.com/support/knowledgece ... les_N80019

P.S.
There was an attempt to write Prolog, but it ended, as expected, unsuccessfully:

DataSourceType = 'OLEDBOLAP';
DataSourceODBOProvider = 'IBM Cognos TM1 OLE DB MD Provider';
DataSourceODBOLocation = '';
DatasourceNameForServer = 'ServerName2';
DatasourceNameForClient = 'ServerName2';
DataSourceODBOCatalog = 'ModelName1';
DatasourceUsername = 'User1';
DatasourcePassword = 'Password1';
DatasourceQuery = 'SELECT <...> ';

In the end, there was an error:
Error: Prolog procedure line (0): Unable to open data source 'ServerName2'.

In this case, if you use the standard functionality, everything works (that is, ServerName2 and ModelName1 are available).

The alternative - to do replace in files * .pro and restart the model.

Thank you!

--
Best regards,
Dmitry

Posted: **Fri Mar 17, 2017 7:46 pm**

Hello,

Welcome on board with your first post here.

Not sure I understand what is going on, but this works fine for me:

Code: Select all

DataSourceNameForServer = 'THE_NAME_OF_MY_PC';
DataSourceODBOCatalog = 'THE_TM1_MODEL_NAME';
DataSourceQuery = '"WITH    SET [General_Years Set] AS           
''{ FILTER( { [General_Years].MEMBERS }, ISLEAF( [General_Years].CURRENTMEMBER ) ) }''    
SET [General_Months Set] AS           ''
{ FILTER( { [General_Months].MEMBERS }, ISLEAF( [General_Months].CURRENTMEMBER ) ) }''    
SET [CPI_Geldwaarde Set] AS           
''{ FILTER( { [CPI_Geldwaarde].MEMBERS }, ISLEAF( [CPI_Geldwaarde].CURRENTMEMBER ) ) }''    
SET [CPI_Measures Set] AS           
''{ FILTER( { [CPI_Measures].MEMBERS }, ISLEAF( [CPI_Measures].CURRENTMEMBER ) ) }''    
SELECT NON EMPTY {     [General_Years Set]  *  [General_Months Set]  *  [CPI_Geldwaarde Set]  *  [CPI_Measures Set]    } ON COLUMNS FROM [CPI] "';



#****Begin: Generated Statements***
IF ( CUBEEXISTS('test') <> 0 );
  CUBEDESTROY('test');
ENDIF;
ret_code=EXECUTEPROCESS('_test ODBO 2_General_Years');
if (ret_code = ProcessExitSeriousError() );
ProcessError();
endif;
ret_code=EXECUTEPROCESS('_test ODBO 2_General_Months');
if (ret_code = ProcessExitSeriousError() );
ProcessError();
endif;
ret_code=EXECUTEPROCESS('_test ODBO 2_CPI_Geldwaarde');
if (ret_code = ProcessExitSeriousError() );
ProcessError();
endif;
ret_code=EXECUTEPROCESS('_test ODBO 2_CPI_Measures');
if (ret_code = ProcessExitSeriousError() );
ProcessError();
endif;
CUBECREATE('test','General_Years','General_Months','CPI_Geldwaarde','CPI_Measures');
OldCubeLogChanges = CUBEGETLOGCHANGES('test');
CUBESETLOGCHANGES('test', 0);
#****End: Generated Statements****


asciioutput( 'test.txt', DataSourceNameForServer );
asciioutput( 'test.txt', DataSourceODBOCatalog );
asciioutput( 'test.txt', DataSourceQuery );

In fact, I started generating the process, and then outside the generated statements I added my own coding.
If I change the TM1 model name to something non-existing, the process stops which is correct).

Wim

Posted: **Mon Mar 20, 2017 1:38 pm**

Thank you, Wim, for the warm welcome!

Yes, you really did not quite understand what I asked about initially.
But you helped me A LOT in solving my problem.
Thank you!

When I put the values of local variables in a file (TextOutPut) during the standard functional is working (without using any overrides in the Prolog) I was able to understand, what it is necessary to write in the Prolog for the correct override of the ODBO-connection.

TI-Processes will not work correctly if the source server has been changed. We would have to update each process manually... This is extremely inconvenient and long. So we created one more parameter in our cube with system parameters, in which we store the value of the source server (it is not some new approach, just for information

).
Now in our updated process, we refer to this parameter (CellGetS) and redefine the source in the Prolog using the same scheme as for the other types of loading (for example, DataSourceType = 'VIEW';).

As a result, we got the working code of the following kind, which solved our problem:

Code: Select all

DataSourceType = 'ODBOMDXQUERY';
DataSourceODBOProvider = 'TM1OLAP';
DataSourceODBOLocation = '';
DatasourceNameForServer = CellGetS ('System_Parameters', 'SP_01', 'String');
DataSourceODBOCatalog = CellGetS ('System_Parameters', 'SP_02', 'String');
DatasourceUsername = 'UserName1';
DatasourcePassword = 'Password1';
DatasourceQuery = 'SELECT <...query...>';

This approach allows you to make the use of ODBO-connection more flexible and independent of changes to the original connection parameters.
We can change the source server for all processes at once, which reference the cube with system parameters.
Perhaps someone will come in handy

P.S.
As we can see, the problem was that the required values of local variables are not specified in the IBM knowledge center, for example:
DataSourceType = 'ODBOMDXQUERY'
and
DataSourceODBOProvider = 'TM1OLAP';

And, unfortunately, the case of using these local variables together is not described anywhere, in order to correctly redefine the Prolog.
Or I did not find.
Anyway, now the information is here

--
Best regards,
Dmitry

Posted: **Tue Mar 21, 2017 4:07 pm**

One more thing

For some reason, security specialists can prohibit storing the login and password openly and side by side in the TI-process (most likely it's a technical user with administrator rights).
In this case, you can do the following:
In Prolog you do not need to use local variables, which will override the user's login and password, with which we want to connect to another server.
When the TI-process starts, it will use those servers and models, that we specify in Prolog, and the user's login and password will be used based on the information from the first tab, where we first set up the ODBO-connection, when creating the TI-process.

It looks like this:

Code: Select all

#---------First Tab: Start---------#
<...Settings of ODBO-connection..>
ODBO UserID - UserName1
ODBO Password - ********
#---------First Tab: End-----------#


#---------Prolog: Start---------#
DataSourceType = 'ODBOMDXQUERY';
DataSourceODBOProvider = 'TM1OLAP';
DataSourceODBOLocation = '';
DatasourceNameForServer = CellGetS ('System_Parameters', 'SP_01', 'String');
DataSourceODBOCatalog = CellGetS ('System_Parameters', 'SP_02', 'String');
     #---You can delete it-------#
     # DatasourceUsername = 'UserName1';
     # DatasourcePassword = 'Password1';
     #----------------------------#
DatasourceQuery = 'SELECT <...query...>';
#---------Prolog: End-----------#

--
Best regards,
Dmitry

Posted: **Tue Mar 21, 2017 6:28 pm**

That's true, and it's the reason why I only mentioned the local variables you would overwrite.
If you start from a working process with the correct data source type and credentials, things are easier.

Posted: **Wed Jan 24, 2018 8:46 pm**

Hey Wim,

I'm having a play around with ODBO and I've tried passing a parameter to the code as follows:

Code: Select all

DataSourceQuery=
'"WITH    
  SET [Capital - Version Set] AS           
    '' { [OnBoarding - Version].[Forecast] } ''    
  SET Capital - Projects] AS           
    '' { [OnBoarding - Projects].['|pProject|'] } ''    
  SET [Capital - Attribute Measures] AS           
    '' { FILTER( { [OnBoarding - Attribute Measures].MEMBERS }, ISLEAF( [OnBoarding - Attribute Measures].CURRENTMEMBER ) ) }''    
  SELECT NON EMPTY { [Capital - Version Set]  *  [Capital - Projects Set]  *  [Capital - Attribute Measures Set] } ON COLUMNS FROM [OnBoarding Attributes] "';

The process saved but when I ran it to do a test export it ran for all projects rather than the one specified as the parameter. Here is my prolog section in total:

Code: Select all

DataSourceType='OLEDBOLAP';
DataSourceNameForServer='*********.PACORP.PANYNJ.GOV';
DataSourceODBOCatalog = 'DEV_Project_OnBoarding_Financials';
DataSourceODBOConnectionString='CAMNamespace=Active Directory';
DataSourceUserName='******';
DataSourcePassword='*********';
DataSourceQuery=
'"WITH    
  SET [Capital - Version Set] AS           
    '' { [OnBoarding - Version].[Forecast] } ''    
  SET Capital - Projects] AS           
    '' { [OnBoarding - Projects].['|pProject|'] } ''    
  SET [Capital - Attribute Measures] AS           
    '' { FILTER( { [OnBoarding - Attribute Measures].MEMBERS }, ISLEAF( [OnBoarding - Attribute Measures].CURRENTMEMBER ) ) }''    
  SELECT NON EMPTY { [Capital - Version Set]  *  [Capital - Projects Set]  *  [Capital - Attribute Measures Set] } ON COLUMNS FROM [OnBoarding Attributes] "';

Thanks,

Jim.

Posted: **Thu Jan 25, 2018 2:10 pm**

Hello, Jim!

Perhaps, I can help in solving your problem.
To specify the values that are passed from the parameters, you have to use the following syntax: [Dimension].[?ParameterName?]

For example:

Code: Select all

DatasourceQuery = '
    SELECT    {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}   ON COLUMNS,               
Non empty
{[Measure_Test].[MSR_001],  [Measure_Test].[MSR_002]}   
*  {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Entity].[?pEntity_Hierarchy?]}, ALL, RECURSIVE )}, 1)}   ON ROWS

 FROM [Cube_Test]               
WHERE ( [Scentype].[?pScentype?], [Year].[?pYear?], [Version].[?pVersion?], [Scenario].[?pScenario?] ,  [Value].[NValue])
';

--
Best regards,
Dmitry

Posted: **Thu Jan 25, 2018 5:59 pm**

Ah ok, so it behaves in the same way as passing a parameter to a query in the data source tab. Nice and thanks,

Jim.

Posted: **Thu Jan 25, 2018 6:34 pm**

So it looks like the query I've specified is not running in the Data Tab. It is still running the original query. I can only guess that I'm missing something from data source overrides:

Code: Select all

DataSourceType='ODBOMDXQUERY';
DataSourceODBOProvider='TM1OLAP';
DataSourceODBOLocation='';
DataSourceNameForServer='**************';
DataSourceODBOCatalog = '*************';
DataSourceUserName='*************';
DataSourcePassword='*************';
DataSourceODBOConnectionString='CAMNamespace=Active Directory';
DataSourceQuery=
'"WITH  
  SET [Capital - Version Set] AS  
     ''{ [OnBoarding - Version].[Forecast] }''
  SET [Capital - Projects Set] AS  
     ''{ [OnBoarding - Projects].[?pProject?] }''
  SET [Capital - Attribute Measures Set] AS  
     ''{ FILTER( { [OnBoarding - Attribute Measures].MEMBERS }, ISLEAF( [OnBoarding - Attribute Measures].CURRENTMEMBER ) ) }''
  SELECT NON EMPTY {  
     [Capital - Version Set]  *  [Capital - Projects Set]  *  [Capital - Attribute Measures Set]  
     } ON COLUMNS FROM [OnBoarding Attributes] "';

I have exported the parameters as Wim suggested above and they (including the query) are coming out as specified in the prolog. I'm stumped as to why this is happening. Any ideas guys?

Thanks,

Jim.

Posted: **Thu Jan 25, 2018 6:54 pm**

I tried to simplify the query:

Code: Select all

DatasourceQuery = 
'SELECT NON EMPTY
  { [OnBoarding - Projects].[?pProject?] } * { [OnBoarding - Version].[Forecast] } * { TM1SUBSETALL( [OnBoarding - Attribute Measures] ) }
ON COLUMNS
FROM [OnBoarding Attributes]';

But still not working

Posted: **Thu Jan 25, 2018 8:31 pm**

Taxi for me. Sussed it. I had my override code below the query generated code in the prolog. Thanks for all your help PowerDim,

Jim.

TM1 Forum

Set ODBO Datasource in Prolog

Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog

Re: Set ODBO Datasource in Prolog