In this article I will show you how to load SAP BW data to TM1 using IBM Cognos Package Connector.
Installation of the required software
So you need to install and configure the next things:
- Install the SAP libraries that are specific for the platform and operating system on which you are running SAP
- Ensure that the Saprfc.ini file is available on the computer where the TM1 server is running.
- Install IBM Cognos Package Connector
- Configure Congos Configuration for IBM Cognos Package Connector (point to the required Cognos environment)
Define SAP BW datasource
In Cognos Administration you need to add a new datasource for SAP BW. Create a connection. If you create multiple connections and they all are active and available to a user, that user will receive “ambiguous connection” error while access the datasource.
While creating a datasource first time you will be asked to create connection, sign-on and a package. If you want to be able to create new packages for that datasource, go to datasource properties, then “Connection” tab and check “Allow personal packages”.
Creating a process
You need to run Architect (I suppose Modeler as well) as Administrator (on the machine with installed and configured IBM Cognos Package Connector).
Now while creating a new package, on “Data Source” tab you select “IBM Cognos Package” radio button and if you are going to load the data you should leave “Package” selected under it. You select “Dimension” when you need only to update a dimension.
After you need to configure another few tabs:
Connection: user account to access Cognos BI
Package: select Cognos package, TM1 cube and actions
Dimension: actions on dimensions, map measures, provide prompts values for variables used in SAP BW queries
Dimension Settings: specify Top Consolidation element if you selected Create/Update/Recreate action for any dimension.
To move a TI process, which is using TM1 Package Connector, from one TM1 environment to another one in addition to the PRO-file you must also copy the BPB-file, the object file having the same file name as the PRO-file but a file extension of “.blb” instead of “.pro”
The BLB-file includes details about the package and the queries the TM1 Package Connector is going to access when the TI process is executed.
Possible issues & fixes
Testing connection to SAP data source returns an error:
XQE-GEN-0002 An unexpected exception occured: SAP_RFC_JNI Library not found
This error occurs in DQM (dynamic query mode) if SAP library librfc32.dll is missing in <%Cognos dir%>/bin64 folder.
How to fix
If not installed, download classic RFC SDK (see SAP Note 27517- Installing RFCSDK) for MS-Windows (32-bit, for Framework Manager) as well as for your 64-bit OS. Install the RFC libraries as required by SAP. Copy the librfc32.dll (32-bit) into the \bin and the librfc32.dll (64-bit) into the \bin64 directory. Restart the Cognos services and retest SAP Namespace (if you are using one) and retest SAP data source connections.