OLAP Exchange® Quick Start Guide
This feature is enabled through Licensing. Check Edit, Options, Licensing to see if OLAP Exchange® functionality is enabled on your system.
These succeeding discussions provide an overview of the deployment of OLAP Exchange® component tool to create a Cube directly by sourcing data from, in the example shown, a SQL Server example database. (Please see the OLAP Exchange® section for a detailed explanation of the entire range of features and benefits of OLAP Exchange® tool.) Keep these three important points in mind as you proceed: First, OLAP Exchange® component, apart from being able to source data from SQL Server tables, can reach other ODBC-compliant relational data sources. OLAP Exchange® component provides limitless customization options in the way it allows users to create Cubes. And, after creating Cubes via OLAP Exchange, you still enjoy all the features available in PowerOLAP® software for sophisticated business-specific modeling.
Additional Systems Requirements for OLAP Exchange® topic
In order to follow the steps, we recommend that user have access to the Northwind database running on SQL Server.
The OLAP Exchange® Process
The following is an overview of the steps to follow when creating a Cube via OLAP Exchange®.
To build a Cube from a relational source, you can begin by creating a new PowerOLAP database or you can create a Cube in an existing PowerOLAP database; proceed, next, by selecting OLAP Exchange as follows:
1. Launch PowerOLAP and in the PowerOLAP ribbon, go to Data tab and in the OLAPExchange control group select the Create Cube command.
To establish your ODBC connection with a relational database:
2. In the Create Cube From Relational Source dialog box, click Configure. This will bring up the OLAP Exchange Data Source dialog box.
3. For your ODBC Driver, select SQL Server using the drop down selection then click Add button (as encircled below).
4. Type in the <data source name> (e.g., OLAPExchDemo), <Description> (optional) and select Server (this should be the SQL server that contains the Northwind database) from the drop down selection. Click Next.
5. In the succeeding dialog box, leave the default settings with the With Windows NT authentication using the network login ID radio button enabled. Click Next.
6. In the next dialog box, enable the Change the default database to: checkbox then select Northwind from the drop down selection. Leave other default settings as is then click Next.
7. Click Finish in the next dialog box.
8. In the ensuing message box that appears click on the Test Data Source button to test if connection is valid and active.
9. Another message box will appear that will tell if test was completed successfully. Click OK.
10. Back in the first message box, click OK. This will return the OLAP Exchange Data Source dialog box where you will see the Data Source Name you have created, OLAPExchDemo, is now added.
11. In the Data Source Name list box, select OLAPExchDemo then click OK.
12. Back in the Create Cube From Relational Source dialog box, under the Cubes section, type in Sales Analysis in the Cube name textbox. (Sales Analysis will be the name of the Cube that we will create in PowerOLAP via OLAP Exchange)
13. Click OK. You will next see the Dimensions dialog box—blank, since you have just started building the Cube.
14. Click Add in the Dimensions dialog box.
15. Click Next in the Measures Dimension dialog box.
The Define Measures Table dialog box appears.
16. In the Dimension Name text box, replace the text with Measures.
17. Click Next after determining the Measures table.
The Define Members dialog box appears.
18. Click Next.
A Relational Dimension Summary dialog box appears (as in the detail figure below). This dialog box sums up the “logic” of the Dimension you just created.
19. Click Finish in the Relational Dimension Summary dialog box.
The Dimensions dialog box appears with the first—i.e., Measures—Dimension listed.
20. Click Add to proceed to the creation of the next Dimension.
The Select Dimension dialog box appears again. This time New Dimension is indicated (radio button enabled). Click Next.
[If you were working on a second or further Cube, you could select Shared Dimension, i.e., one that was created for use in a Cube created previously.]
The Define Links dialog box appears on screen.
21. Next, to define links between the tables, hold down the top left of your mouse and “draw links” between common fields—akin to creating an ad hoc “star schema”.
Note: You can link one field between tables at a time.
22. After the proper links have been created, replace the text in the Dimension Name textbox with Customers then click Next.
The Define Members dialog box appears.
23. After selecting the field to supply Member names, click Next. The Define Aliases dialog box appears. In this dialog box you can select a field from which to create coordinating unique Alias names for the Member Names—very useful if you wish to report on, for example, Customer ID numbers along with Customer names
24. Click Next. The Define Properties dialog box appears. Here, too, you can “leverage” the logic of underlying tables, to report on Properties (which need not be unique) characteristics of Members.
25. Click Next. The Define Hierarchy dialog box appears.
26. In the Define Hierarchy dialog box, drag Country under Customer in the right-hand pane to create this Hierarchy in the PowerOLAP dimension.
Note, too, that there is a provision (a checkbox on the bottom right boxed in the following image) to create an Aggregate Member for the Dimension—here, it will be Total Customers. Enable this checkbox.
27. Click Next. The Relational Dimension Summary for the newly created Dimension appears.
◘ Define Links
◘ Define Members
◘ Conditional Retrieval (if checked)
◘ Order Preference (if checked)
◘ Partial Fields (if checked)
◘ Drill-Through Values (if checked)
◘ Define Aliases
◘ Define Properties
◘ Define Hierarchy
◘ Define Rollups (if checked)
◘ Relational Summary
28. In this example, assume that you have completed the creation of a Products dimension. After seeing the Relational Summary dialog box, you would click Finish, and be returned to the Dimensions dialog box:
OLAP Exchange® also provides for sending OLAP data back to relational tables—in this way, OLAP Exchange® is truly bi-directional.
Please consult the OLAP Exchange® Manual for full details.