Integrating Oracle Data Integrator and Analysis Services
In medium and large environments, companies tend to use tools from one vendor only. This approach have its benefits as built-in integration – mostly of the time – between tools and a single point of support. Moreover, find skilled workers that can handle with tools from same vendor stack is easier.
Although the scenario described above is probably the best case, sometimes companies choose to use solutions from others vendors to deal with specific issues. Therefore, in these situations some questions like lack of integration and tools that overlap some of its features may arise.
Though some configurations in ODI and SSAS must be done in order to get the integration, it basically consists in a ODI procedure coded in Jython that does a http request to SSAS passing a xmla script.
Note that when configuring authentication type for the IIS application, it must be set to anonymous and a user account should be used to impersonate the connection. This account will be used later to set up processing-cubes permissions in SSAS databases.
For security reasons, the account used to configure IIS application must have only process permissions, so we need to create a role in SSAS database and allow it to process all cubes and dimensions. Leave the
Local Cube / Drillthrough both set to
None and check
Process for all cubes in database. After that, just add the user account used earlier as a member of this role.
If you plan to use more the one environment you will need to configure one web application for each of them.
Second step is to configure ODI. This is not mandatory, but if you have more than one environment, like QA and Production, it will help you coping deployment questions when migrating from one environment to another.
The ODI configuration lies just in creating a flexfield for contexts where we should place the url where the SSAS responds to http requests. Therefore, in an ODI QA environment you can set the url to call a SSAS instance used only for test or QA purpouses. When the package is deployed at production level, the flexfield context in this environment should be configured with the url pointing to the production instance of SSAS.
After create the flexfield you can configure it with the SSAS url:
The procedure code is simple. It just wraps a xmla script and completes it with the database and cube names and the processing option.
Now just drop the procedure into a ODI scenario and configure the Options with your own information:
That’s it! Now you can use ODI to process SSAS objects. Below you can download the procedures to process both Cubes and Dimensions. Download ODI Procedures
I would like to thanks my colleague Guthierry Marques who helped us develop these procedures. Thanks buddy!