One of proCube’s unique features is that it enables users to dynamically connect Excel worksheets to Cubes. This link is considered dynamic because the worksheet data values reflect data updates and changes made in thecube. proCube creates the worksheet with a single click, establishing a bi-directional link with proCube. There are two ways to create a worksheet:
Directly from within Excel by using the Slice Wizard. For more information about creating an Excel worksheet directly from within Excel, go to Using the Slice Wizard.
proCube’s installation process automatically installs the necessary add-in components into Excel to enable you to take advantage of this feature. During the installation process, a proCube Toolbar is added to Excel. proCube integration is supported in Excel 2003 and above.
proCube’s Excel Add-in Toolbar or Ribbon
The proCube Add-in Toolbar , shown below, is located within the Excel toolbar area. This toolbar (Excel 2003) or ribbon (Excel 2007) contains commands that provide point-and-click access to proCube’s add-in functions. For details, go to proCube’s Excel Ribbon/Toolbar.
Figure 1. Excel 2003 Add-In Toolbar
Figure 2. Excel 2007 Add-In Ribbon
Creating an Excel worksheet from a slice
To create an Excel worksheet from a proCube slice:
Open the Sales Accounts by Months slice from the Sales cube.
From the Ribbon, click Worksheet.
proCube launches Excel and displays the newly created worksheet. If Excel is already running, proCube brings it to the foreground, displaying the newly created worksheet:
Figure 3. Excel Worksheet Created from a proCube Slice
This bi-directional connection between Excel and proCube is established automatically when you launch an Excel worksheet connected to a proCube database. The connection works through database reference formulas that exist within the cells of the worksheet (and that reference dimension intersection points within the cube).
Click cell A7 (OLAPTable) to display the reference formula in the formula bar.
The connection between the two applications is bi-directional in that you may update and add data either from the Excel worksheet or from within a proCube slice.
Manipulating Data in Excel with Total Flexibility
An Excel worksheet created from a slice can be manipulated just like any other worksheet. The database references built by proCube can be moved and copied throughout the worksheet, just as you can copy and move Excel formulas within a normal Excel worksheet. Since the database references are bi-directional, the worksheet will always provide you with consistent and up-to-date data from the proCube database cube.
If you use the Xchange™ component of proCube, you can set this feature to update Excel as transactions are recorded in an underlying relational, transaction-processing database system. Doing so guarantees that you are always working with the most updated information at your desktop (i.e., in Excel).
proCube offers you total flexibility in working with Excel. You can build a bi-directional database reference in any cell in a worksheet. Thus, you can compose complex views of your business in a single worksheet. Furthermore, if you have used Excel graphing tools to generate a graph of your worksheet, that graph will update automatically as well. Using the Microsoft Web Publishing Wizard, you can also publish Excel worksheet updates to your company Intranet.
As an added advantage of Excel’s connection to proCube, a single Excel worksheet can be used as a template to enter and receive data across a number of Cubes contained within a database. With proCube, all of your Excel formats remain in place when you change to different cubes or page members, thus saving you time and effort when producing reports.