Enabling Integration with Analysis Services

To enable IBM® SPSS® Modeler integration with Analysis Services, you will need to configure SQL Server and Analysis Services, create an ODBC source, enable the integration in the IBM SPSS Modeler Helper Applications dialog box, and enable SQL generation and optimization.

Note: Microsoft SQL Server and Microsoft Analysis Services must be available. See the topic Requirements for Integration with Microsoft Analysis Services for more information.

Configuring SQL Server

Configure SQL Server to allow scoring to take place within the database.

Create the following registry key on the SQL Server host machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSOLAP

Add the following DWORD value to this key:
```
AllowInProcess 1
```
Restart SQL Server after making this change.

Configuring Analysis Services

Before IBM SPSS Modeler can communicate with Analysis Services, you must first manually configure two settings in the Analysis Server Properties dialog box:

Log in to the Analysis Server through the MS SQL Server Management Studio.
Access the Properties dialog box by right-clicking the server name and choosing Properties.
Select the Show Advanced (All) Properties check box.
Change the following properties:

Change the value for DataMining\AllowAdHocOpenRowsetQueries to True (the default value is False).
Change the value for DataMining\AllowProvidersInOpenRowset to [all] (there is no default value).

Creating an ODBC DSN for SQL Server

To read or write to a database, you must have an ODBC data source installed and configured for the relevant database, with read or write permissions as needed. The Microsoft SQL Native Client ODBC driver is required and automatically installed with SQL Server. The driver provided with the IBM SPSS Data Access Pack (and typically recommended for other uses with IBM SPSS Modeler) is not recommended for this purpose. If IBM SPSS Modeler and SQL Server reside on different hosts, you can download the Microsoft SQL Native Client ODBC driver. See the topic Requirements for Integration with Microsoft Analysis Services for more information.

If you have questions about creating or setting permissions for ODBC data sources, contact your database administrator.

Using the Microsoft SQL Native Client ODBC driver, create an ODBC DSN that points to the SQL Server database used in the data mining process. The remaining default driver settings should be used.
For this DSN, ensure that With Integrated Windows Authentication is selected.

If IBM SPSS Modeler and IBM SPSS Modeler Server are running on different hosts, create the same ODBC DSN on each of the hosts. Ensure that the same DSN name is used on each host.

Enabling the Analysis Services Integration in IBM SPSS Modeler

To enable IBM SPSS Modeler to use Analysis Services, you must first provide server specifications in the Helper Applications dialog box.

From the IBM SPSS Modeler menus choose:
Tools > Options > Helper Applications
Click the Microsoft tab.

Enable Microsoft Analysis Services Integration. Enables the Database Modeling palette (if not already displayed) at the bottom of the IBM SPSS Modeler window and adds the nodes for Analysis Services algorithms.
Analysis Server Host. Specify the name of the machine on which Analysis Services is running.
Analysis Server Database. Select the desired database by clicking the ellipsis (...) button to open a subdialog box in which you can choose from available databases. The list is populated with databases available to the specified Analysis server. Since Microsoft Analysis Services stores data mining models within named databases, you should select the appropriate database in which Microsoft models built by IBM SPSS Modeler are stored.
SQL Server Connection. Specify the DSN information used by the SQL Server database to store the data that are passed into the Analysis server. Choose the ODBC data source that will be used to provide the data for building Analysis Services data mining models. If you are building Analysis Services models from data supplied in flat files or ODBC data sources, the data will be automatically uploaded to a temporary table created in the SQL Server database to which this ODBC data source points.
Warn when about to overwrite a data mining model. Select to ensure that models stored in the database are not overwritten by IBM SPSS Modeler without warning.

Note: Settings made in the Helper Applications dialog box can be overridden inside the various Analysis Services nodes.

Enabling SQL Generation and Optimization

From the IBM SPSS Modeler menus choose:
Tools > Stream Properties > Options
Click the Optimization option in the navigation pane.
Confirm that the Generate SQL option is enabled. This setting is required for database modeling to function.
Select Optimize SQL Generation and Optimize other execution (not strictly required but strongly recommended for optimized performance).