Defining an ODBC Data Source Between proCube and an SQL Server Database

Defining an ODBC Data Source Between proCube and an SQL Server Database

We will use a new proCube database from which to define an ODBC data source. proCube makes defining an ODBC data source easy to accomplish by incorporating the standard Windows ODBC-32 or Windows ODBC-64 configuration utility within the proCube application. The ODBC data source is given a Data Source Name (DSN) and a path to the location of the database, which requires that the user have necessary access rights to that database.

Rather than create a DSN through the proCube interface, you can do the same through the Control Panel, described in Configuring the Server for Xchange.

The first steps, in this example, concern creating a new proCube database:

  1. From proCube Management Studio, highlight your server, right click, and from the menu select New Database.


Figure 1.       Server View New Database

  1. The New Database dialog opens. Name the new proCube database Northwind after the source relational database.

  2. Place this database in your selected directory, e.g.,  in the C:\Program Files\proCube\Examples subdirectory.


Figure 2.    New Database Dialog - Northwind

  1. Select Allow Reserved Characters.

Selecting Allow Reserved Characters allows a Dimension to be created and populated with Members whose names contain proCube Reserved Characters (go to Allowing reserved characters for more information). Our example underlying database, Northwind, contains some of these characters, and we want to be allowed to use them.

  1. If you are working with the example database, deselect Secure Database. If you elect to enable this feature, go to Applying Database Security.

  2. Click OK to save the database in the specified directory. You return to proCube Management Studio.

  3. Load and connect the Northwind database.

  4. From the Data ribbon's Xchange group, click Create Cube to open the Create Cube from Relational Source dialog.


Figure 3.    Create Cube From Relational Source Dialog

You must have the necessary license for this area of the Xchange menu to be active.

  1. You will now create a data source configuration, via ODBC, to the source relational database. This involves either adding a new Data Source, which will be shown in the following steps, or selecting a Data Source that already exists.

  2. Click Configure to display the Xchange Data Source dialog.


Figure 4.    Xchange Data Source Dialog

  1. The Xchange Data Source dialog allows you to select an ODBC driver (installed on your system using the Windows – Control Panel ODBC-32 (or ODBC-64 configuration utility) and then either select an existing data source or create a new System DSN. Note that for any selection you make in the drop-down ODBC Driver menu, the existing Data Source Names appear in the list box below it. Once you define a new data source, it will be listed together with other data sources in the dialog.

  1. Select SQL Server (if using 32-bit) or SQL Native Client (if using 64-bit) from the ODBC Driver drop-down. After making this selection (for 32-bit), the dialog appears as follows:


Figure 5.    Xchange Data Source Dialog - SQL Server

  1. Where no DSN (Data Source Name) exists, click Add to create one. [If a DSN already exists you can pick it from the list box.] The SQL Server DSN dialog is displayed as in the following figure:


Figure 6.    Create a New Data Source to SQL Server Dialog

  1. Enter a Data Source Name (DSN), e.g., Northwind, as shown.

  2. [Optional] Enter a Description (e.g., My Xchange Database).

  3. Select a SQL Server from the Server drop-down. This is the named SQL Server where the relational data is located—(local) in our example.

  4. Click Next. This and the following several dialogs give you choices concerning how to configure your SQL Server DSN.


Figure 7.    Create a New Data Source to SQL Server Dialog

  1. In this box, click the top radio button (as shown) to enable  With Windows NT authentication using the network login ID, the default setting.

DO NOT select the second radio button. Selecting the first button guarantees the use of Windows NT authentication when specifying the ODBC data source in order to establish a trusted connection to SQL Server for the NT User (currently logged in User) account.

  1. Click Next.


Figure 8.    Create a New Data Source to SQL Server Dialog - Northwind (default database)

  1. Select Change the default database to:and from the drop-down menu, select Northwind.

  2. Click Next. You will see another options dialog box for the data source.


Figure 9.    Create New Data Source to SQL Server Dialog - Finish

  1. Click Finish. The following message box appears:


Figure 10.           ODBC Microsoft SQL Server Setup Dialog

  1. Click the Test Data Source button to be certain the connection is valid and active, confirmed in the following message box:


Figure 11.           SQL Server ODBC Data Source Test Dialog

  1. Click OK. You are returned to the Xchange Data Source dialog where you began, and the new DSN now appears in the list box.


Figure 12.           Xchange Data Source Dialog - Northwind

  1. Select the DSN you just created.

  2. Click OK to proceed. The Create Cube from Relational Source dialog opens.


Figure 13.           Create Cube From Relational Source

  1. Enter a username and password. These fields are used when the ODBC driver that you have selected does not support Windows Authentication to access the Relation source. The ODBC driver for Microsoft SQL Server supports the use of Windows Authentication.

  2. In the Cube/Name field,enter SalesOrder as the name of your cube.

  1. Once you have named the Cube and made selections in this dialog, click OK. The Dimensions dialog opens as described in Creating the Measures Dimension. Here you will begin creating the first dimension for your SalesOrder cube.



Please sign in to leave a comment.
Powered by Zendesk