Creating a SSAS Connection

Connecting the Data Mining Add-In to an SSAS instance involves two tasks. First, the SSAS server must be configured to work with the Data Mining Add-In. The second required task is adding the configured SSAS server connection within the Data Mining Add-In. In between those two tasks are some things that you can do to avoid common frustrations in making the connection to SSAS.

Configuring the SSAS Server

Within the Connection group of the Analyze menu, notice the No Connection label illustrated in Figure 10-14. This is an indication we have no SSAS server available to execute data mining operations. Therefore, our next step is configuring the connection between Microsoft Excel and SSAS.

Figure 10-14. Data Mining Add-In connection

The installation process described earlier had you choose a feature labeled Server Configuration Utility as part of the installed items (see Figure 10-5). Assuming you have access to an SSAS server within your development environment, you may configure the SSAS instance to work with the Data Mining Add-In by following the process outlined in this section.

From the Windows, All Programs menu, execute the Server Configuration Utility. The program is also illustrated in Figure 10-9. The initial screen of this process will look similar to Figure 10-15.

Figure 10-15. Data Mining Add-In Configuration Wizard

Click the Next button to advance the wizard to the SSAS server selection, illustrated in Figure 10-16.

Figure 10-16. SSAS server selection

Because SSAS supports only Windows authentication, there is no mixed-mode authentication as it exists within the SQL Server relational database. Instead, the Authentication selection is already made for you.

Select an SSAS server to be configured to perform data mining operations for the Data Mining Add-In. If you have installed an instance of SSAS locally, then “localhost” is an acceptable value for this configuration item. If you wish to use a named instance, enter the name in the form of serverinstance. Clicking the Next button will advance the wizard to the temporary mining model configuration settings, illustrated in Figure 10-17.

Figure 10-17. Configuring session mining models

In order to use the selected server with the Data Mining Add-In, verify that the check box labeled “Allow creating temporary mining models” is checked, as illustrated in Figure 10-17. Click the Next button to advance the configuration to the next step, database creation, illustrated in Figure 10-18.

Figure 10-18. Create database for add-in users

The “Create database” dialog allows you to create a new database exclusively for data mining operations using Excel or to configure an existing database for use with the Data Mining Add-Ins. For our purposes, select the default database, DMAddInsDB. Your doing so will create a new SSAS database on the selected server, ensuring no conflicts with other SSAS database operations. Click the Next button to proceed to granting database administrator permissions, as illustrated in Figure 10-19.

Figure 10-19. Granting database administrator access

For our example purposes, using the wizard defaults will be sufficient. Your own dialog should look similar, with the Windows username you are currently using pre-selected. Clicking the Finish button will advance the wizard to the final step, the Configuration Wizard status report, illustrated in Figure 10-20. Clicking the Close button will exit the Server Configuration Utility.

Figure 10-20. Configuration Wizard status report

Avoiding Connection Frustration

You may be tempted to utilize the Excel ribbon Connections item to define an SSAS connection via the dialog pictured in Figure 10-21. Unless the steps under Configuring the SSAS Server have been completed, defining a connection in this manner will fail in most cases. Follow the process I've outlined, and you'll save yourself some frustration.

Figure 10-21. Analysis Services Connections dialog

Adding the SSAS Connection Within the Data Mining Add-In

The final task in establishing an SSAS connection is to add the connection within the Data Mining Add-In.

From within Excel, open the On Time Performance.xlsx file from the book's examples download file. From within a data table as created in the section “Locating the Table Analysis Tools,” select the <No Connection> item from the ribbon, as illustrated in Figure 10-22.

Figure 10-22. No Connection ribbon item

The “<No Connection>” ribbon item will generate the Analysis Services Connections dialog illustrated in Figure 10-23.

Figure 10-23. Analysis Services Connections dialog

Click the New button in order to define a connection for the SSAS server configured in the section of this chapter called “Configuring the SSAS Server.” The configuration dialog, illustrated in Figure 10-24, will be presented.

Figure 10-24. Connect to Analysis Services dialog

The “Server name” text box should contain the same name as the SSAS server established in the “Configuring the SSAS Server” section. In my specific case, this is the default instance on the server named Bluemountain. Logon credentials will be preset to “Use Windows Authentication”. The catalog name should be set to the database created specifically for the Data Mining Add-In, DMAddInsDB. The friendly name will be calculated by the Data Mining Add-In in the form “database name + (servername)”. While this can be edited to any name you would like to appear within the Data Mining Add-In connection manager, the default is sufficient for our example. Click the Test Connection button to verify the settings will establish communications with the SSAS instance. Upon a successful test, click the OK button to save the new connection. The new connection should now appear in the Analysis Services Connections dialog, as illustrated in Figure 10-25.

Figure 10-25. Analysis Services Connections dialog

Select the new connection, as illustrated in Figure 10-25, and click the Make Current button. This will establish the new SSAS server as the provider of data mining model services for the Data Mining Add-In. Click the Close button to see that the <No Connection> item in the ribbon has been replaced with the name of the new connection, as illustrated in Figure 10-26.

Figure 10-26. Connection established