Xchange automatically writes the SQL code necessary to propagate data between your company’s internal transaction-processing systems and the proCube analytical engine. Through the use of simple interfaces, users select what relational data to extract to create multidimensional Cubes in proCube. Because Xchange is bi-directional, it can also be used to send data from the multidimensional to the relational database. And because Xchange is dynamic, changes originating in a relational system can be reported instantaneously to proCube.
Xchange completes the integration of the relational database, the optimized proCube multidimensional engine and the spreadsheet or Web browser front end into a faster, more powerful, and seamless business solution.
The integration of various relational databases with proCube through the use of Xchange occurs as follows:
Figure 1. Xchange, proCube, and applications
Employing the Excel spreadsheet as the User interface, as well as the proCube Web browser and User-developed front ends, proCube emerged as the optimal end-user OLAP application available to the market.
With the release of proCube™, 9Dots established a milestone in the evolution of OLAP (online analytical processing) technology. Through the use of the component tool Xchange, proCube bridges the gap between relational and multidimensional worlds. The rapid deployment of Xchange has enabled organizations to propagate data dynamicallybetween transactional and analytical databases—for advanced, online, real-time planning, budgeting, forecasting, analysis, and reporting needs.
Summary of benefits
- Xchange directly addresses the need to reach up-to-the-instant transactional data located in records databases, and to use that data to budget and forecast, analyze and report, online, in real time. proCube empowers end-users by giving them fast, accurate, current information from all organizational sources—direct to their desktops.
- Xchange means an end to batching, importing or duplicating data between separate database applications; no longer does staff waste time—and cause errors—by manually inputting or re-keying data into various applications, including spreadsheets.
- Through the use of Xchange’s Drill Through feature, users can see the individual records in the source relational database that make up a point in a multidimensional cube.
- MIS groups use a single set of advanced tools, deploying Xchange with great speed and ease, to get data to end users, rather than utilizing various unsatisfactory methods to update disparate systems or to create and re-create business models.
- proCube supports an open-architecture environment, since it will integrate with ODBC-compliant relational data sources.
Creating a cube with Xchange
You will first define an Open Database Connectivity (ODBC) connection between proCube and the relational database whose data you want to convert to a multidimensional Cube(s). You can use one of several relational databases in an actual production environment. For more information, go to Configuring the Server for Xchange.
Figure 2. Creating a cube with Xchange
Any cube-building process involves selecting tables to use as the basis of Dimensions; selecting fields whose entries will be Members of those Dimensions; and proceeding to the actual cube creation. In these topics we will also demonstrate the Drill Through feature, which allows you to see the relational transactions that make up a multidimensional point in proCube.
Hardware and software requirements
Using Xchange requires that you are familiar with using proCube and the basic operations of proCube Server.
Often references are made to proCube and SQL Server because this online help demonstrates Xchange by using proCube with Microsoft SQL Server 2008. To follow the demonstration steps:
- You should have a basic understanding of working with SQL Server.
- For the machine running SQL Server, you should follow the configuration guidelines set up by Microsoft for SQL Server.
Xchange uses ODBC (Open Database Connectivity) to connect to relational databases in order to create, populate, and update cubes. It is strongly recommended that you use the most updated ODBC and vendor-specific drivers when you set up in your own environment.
proCube supports the following databases and their associated vendor-specific ODBC drivers:
- Microsoft SQL Server 2000 or higher
- IBM DB2
- Oracle 7.x or greater
- Microsoft Access 2000 and greater
In addition, proCube provides generic support for other ODBC drivers not listed above.
proCube Server 64-bit edition requires the use of 64-bit ODBC drivers. These drivers must be present on the server in order to use proCube Xchange.
Downloading the Northwind database
For the demonstrations in these topics, we will use Microsoft SQL Server as the underlying relational database, and the Microsoft Northwind demonstration database, as the basis of our example. These topics will show how to create a Sales Order Cube from information in Northwind tables. By downloading the Northwind database and working through this example, you will learn how to create a cube using Xchange.
To download the Northwind database:
- Go to http://code.msdn.microsoft.com/northwind and download the database.
- Follow the instructions provided for configuring.
Make sure you follow the instructions carefully. The ensuing topics assume that you have correctly downloaded and configured the Northwind database according to those instructions.