Notes
In order to follow the steps covered in this manual, we recommend that
user have access to the Northwind database running on the SQL Server 2008
R2.
We will use a new PowerOLAP database from which to define an ODBC data source. PowerOLAP makes defining an ODBC data source easy to accomplish by incorporating the standard Windows ODBC-32 configuration utility within the PowerOLAP® application. The ODBC Data Source must be 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.
Tip
Rather than create a DSN through the PowerOLAP interface, you can do the
same through the Control Panel: this is demonstrated in Working with
OLAP Exchange on the MDB Server section of this manual.
The first steps, in this example, concern creating a new PowerOLAP database:
1. Create a new PowerOLAP database file (.olp) where the Cube you intend to build will be located.
2. Name the new PowerOLAP database; for example, in this demonstration, Northwind, after the source relational database.
3. Place this database in your selected
directory, e.g., in the
C:\Program Files (x86)\PowerOLAP\Examples\Northwind.olp.
After the required steps, you will return to the New Database dialog box, which appears as follows:
.png)
Note the check box to Allow Reserved Characters, boxed in the preceding figure. By maintaining the default setting (checked) you will allow a Dimension to be created and populated with Members whose names contain PowerOLAP Reserved Characters. [You may familiarize yourself with what those reserved characters are by consulting the PowerOLAP® User Manual.] Our example underlying database, Northwind, contains some of these characters, and we want to be allowed to use them, therefore:
4. Check the box Allow Reserved Characters, as shown above.
If you are working with the example database, you can leave the Secure Database feature unchecked. [Here, too, you can consult the PowerOLAP® User Manual concerning database security, if you decide to enable this feature.]
Notes
Take note of the three radio buttons at bottom, concerning Synchronization
Server,
another PowerOLAP® component tool. For the purposes of demonstrating
OLAP Exchange®, make sure the radio button for None
is selected. For more information about Synchronization Server, see the
Synchronization Server section.]
5. Click OK to save the database in the specified directory.
You are returned to the PowerOLAP main application window.
6. From the PowerOLAP ribbon, go to the Data tab and in the OLAP Exchange control group select the Create Cube command.
Notes
You must have the necessary license for
this area of the OLAP Exchange menu to be active.
The following dialog box appears:
.png)
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.
7. Click the Configure button.
The OLAP Exchange Data Source dialog box is displayed.
The OLAP Exchange Data Source dialog box allows you to select an ODBC driver (installed on your system using the Windows – Control Panel ODBC-32 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 box.
Since you will be working with SQL Server in this example, proceed as follows:
8. Select SQL Server from the ODBC Driver drop-down, as shown in the following figure:
.png)
After making this selection, the dialog box will appear as follows:
.png)
9. 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 box is displayed as in the following figure:
Notes
The sequence of steps and screens/dialog boxes applies only to SQL Server.
Adding or configuring Data Source Name to databases other than SQL Server
will require different steps using a totally different set of screens
and dialog boxes.
.png)
10. Type in a Data Source Name (DSN), e.g., Northwind, as shown.
11. Type in a Description (optional).
12. 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.
13. Click Next.
This and the following several dialog boxes give you choices concerning how to configure your SQL Server DSN.
.png)
14. 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.]
Important
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.
Note: It is assumed that the currently logged on user has been given
rights to access the Northwind database in the local SQL Server.
15. Click Next.
.png)
16. Click the radio button Change the default database to. The databases that will be available in the drop-down selection are those in which the currently logged in user has access rights to. You want to show the relational database you want to connect to. Thus, in this example, select Northwind.
17. Click Next.
You will see another options dialog box for the data source.
.png)
18. Click Finish.
A message box similar to this will appear:
.png)
19. Click the Test Data Source button to be certain the connection is valid and active, confirmed in the following message box:
.png)
20. Click OK.
You are returned to the OLAP Exchange Data Source dialog box where you began, and the new DSN now appears in the list box.
.png)
21. You must now select the DSN you just created (as shown above).
22. Click OK to proceed with Cube creation.