OLAP Exchange® Quick Start Guide



Notes


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

Important


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.

The Create Cube From Relational Source dialog box appears. In addition to naming the Cube and specifying security to access the Cube, in this dialog box you will configure the OLAP Exchange Data Source (click on Configure) using Data Source naming conventions corresponding to the Driver to the source relational database. In other words, you first need to define an ODBC data source before you can actually proceed with creating your Cube using OLAP Exchange.

 

 

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.

In the succeeding dialog box you will be required to select an ODBC driver and either pick an existing data source name from the driver specified or create a new data source name. In this example we will create a new data source name.

3.   For your ODBC Driver, select SQL Server using the drop-down selection then click Add button (as encircled below).

The Create a New Data Source to SQL Server dialog box appears.

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)

The following image shows a completed dialog box, with SQL Server indicated as the data source.

Note some of the other selections in this dialog box, including the bottom-right area concerning Incremental Updating. This feature enables OLAP Exchange to integrate only the incremental data that is recorded in an underlying relational database, rather than needing to rebuild a Cube for each update. Note that Incremental Updating can be done periodically, live or on demand. This portion will be grayed out if this feature is not available in your license.

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.

In the Select Dimension dialog box, the Measures Dimension radio button will be selected—the Measures dimension is the first Dimension you will build. This Dimension will be based on the table in the underlying database containing, most typically, the Fact Data values that you want to analyze and report on, and which you may use as the basis for further modeling, e.g. for budgeting/forecasting.

15. Click Next in the Measures Dimension dialog box.
The Define Measures Table dialog box appears.

Here you see for the first time PowerOLAP reaching through from the multidimensional world (OLAP) to the relational word—all the underlying tables are shown in the left-hand pane.
Note: The tables shown are dependent on the data source selected.

By selecting table on the left, you will see the fields from that table in the right-hand pane—you can even click on the Preview button to see the first few rows of the table and its fields. This will enable you to determine if it contains the Fact Data in question.

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.

The Define Members dialog box is where you will make the selection of fields that supply the Fact Data points for the Cube—for example, in the figure above, Quantity, and Discount.

Note also the options at the bottom right: these enable you to create an "on the fly" Conditional Retrieval statement for bringing over the Fact Data and to indicate what fields you wish to see in a "drill-through" from an eventual Slice or even an Excel spreadsheet. If you enable Drill Through, the next dialog box will allow you to specify those fields (Refer to the OLAP Exchange Manual for a more in-depth discussion on the Conditional Retrieval and Drill-Through options).

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.

This completes the creation of the Measures dimension in this example. Since this is a Sales Analysis cube (built from the example Northwind SQL Server database). Next you will create Customer and Product dimensions—in order, from the Cube, to analyze and report on the Quantity and Discount of the products your customers purchased.

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.

Let us proceed under the assumption that you next wish to create a Customers dimension. The objective—which will be accomplished in the Define Links dialog box—is to "work back" from the eventual table that will supply the data for a Customers dimension to the Measures table. This is a good example of the need to link via other tables back to Measures: in this case, knowing something about the logic of the underlying tables, you could reach Measures by creating links via the Order table.

In other words, first, with Order Details table already showing in the Define Links dialog box (this crucial Measures table always shows as the first table in the link schema), you would double-click on the Orders table, to make it show in the dialog box; then, do the same for the Customers table.

 

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.

In the preceding figure, you see that the Order ID field in the Order Details (i.e., Measures) table has been linked to the Order ID field in the Orders table; and the Customer ID field in the Orders table has been linked to the Customer ID field in the Customers table. In this manner, you can now proceed to use the last table (Customers) to supply the "logic" for the Customers dimension.

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.

In this dialog box (like the Define Members dialog box for Measures), you will want to specify the field that will supply Members for the Customers dimension. A logical choice is the CompanyName field—thus, this field has been moved to the right-hand pane in the following figure.

Note in the dialog box the additional options available at the bottom right of the dialog. These options are explained in detail in the OLAP Exchange Manual. It is worth noting here, though, the customization options that OLAP Exchange affords for Dimension-creation: for example, the ability to create your own rollups (consolidated Members that you name); or, creating Member names in a certain Order or composed of a fixed number of characters.

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.

In this dialog box, also, you can construct the Dimension in such a way to ultimately analyze/report on data according to the hierarchical logic of the elements in a table—for example, as shown below, your Customers may exist within Countries (or Regions of Cities), which is recorded in the underlying table.

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.

When you click Add again in the Dimensions you start the process of creating another Dimension via OLAP Exchange. Assume, for example, that you wish to create a Products dimension. You will go through the same steps as discussed above.

 

Summarized below is a list of the procedure for creating a Dimension via OLAP Exchange:

 

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:

 

At this point, if it is logical that a Cube with three Dimensions will yield worthwhile results for your planning, analysis and reporting requirements (and for this example, it certainly will!), you can click OK to create the Cube. [Note that, upon clicking OK, an option appears to "save the template" of the Dimensions you created—this will be useful for additional Cubes you might create with those Dimensions.]

The progress bar will appear on-screen until the Cube creation process is complete. When finished, a message box appears indicating the number of data points in the Cube.

Having created the Cube, you will have successfully "bridged the gap" from relational to multidimensional worlds—and you will have all the modeling capabilities described in the initial exercises in this manual. Assuming you create spreadsheet reports or templates from a Slice of the Cube, you will enjoy the capability of an online, dynamic data flow from underlying relational database tables through to your desktop. With a few clicks, you can Update Cube for Incremental Data that has been recorded in relational tables (if the Cube supports Incremental Updating) or Rebuild the Cube completely. These and other, more advanced functions are accessible in the OLAP Exchange control group via the Data tab of the PowerOLAP application window.

 

 

Notes


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.