[Skip Global Navigation]

SPSS Tech Tips

SPSS Direct Home

Using Data from Microsoft® SQL Server™ or Oracle® in ShowCase® Essbase® 7.1 cubes

This is a complimentary tip available only to SPSS Direct subscribers.

Question

I am using ShowCase Essbase 7.1 and would like to take advantage of its new ability to load data directly from Oracle or SQL Server databases. How can I do this?

Answer

In addition to using iSeries™ data sources, you can now use ShowCase Essbase 7.1 to load data into your cube from SQL data sources located on other platforms. Supported platforms include Microsoft SQL Server and Oracle. In order to use this feature, a JDBC connection to the SQL database must be configured. The corresponding DataDirect JDBC drivers are installed with the Essbase server. To enable loading from non-iSeries SQL data sources, follow the steps detailed below.

From an iSeries command line, open the ESSBASE.CFG file using the iSeries EDTF command:

To enable support for non-iSeries data you will need to alter the CFG file. Type “I” for insert and press “Enter.” Insert the following line:

Next, navigate to the Essbase server directory in the IFS, and open the bin folder. This can be done using Operations Navigator or a mapped drive in Windows® to the AS/400. Use a text editor (such as Notepad), to open the ExternalDataSource.cfg file for editing. Using the examples shown below and the defaults in the ExternalDataSource.cfg file as a guide, create a “definition” for each non-iSeries data source. The definition must contain the data source name, the JDBC driver name, and the URL of the relational database to be used as data—all separated by spaces. Once the definitions have been added, save the file with your new definitions. The ExternalDataSource.cfg can be altered when needed as long as the file name does not change.

When you create a rule file or load data in the EAS Console, the non-iSeries data sources are listed along with the iSeries data sources. The name listed will match the data source name in your ExternalDataSource.cfg definition. For example, in the SQL example above, DS1 is the data source name. DS1 is what displays in the EAS Console.

Here is exactly what was added to the ExternalDataSources.cfg file:

The only pieces changed were:

Note: You will need to start your application in EAS (right-click, start, on application). You do not have to add this data source to the RDBDIRE.

For more tips and techniques, check out the ShowCase Virtual User Group microsite for a list of recorded and upcoming webcasts that can help you get greater value from your ShowCase products.