Changing Data Views from within Excel

Changing Data Views from within Excel

proCube’s ability to provide instant access to all database information is a powerful feature. You now know how to access different Cubes, Dimensions, and Page members from one Excel spreadsheet. You may never need to create a new Excel spreadsheet to access data from the same database—because you can access that data instantly from the one you just created!

You have already seen how quickly you can navigate a proCube slice to access data for different Page members. In this Section, you will learn how to use a linked Excel worksheet to access different Page members in a proCube database, as well as alternate Cubes and Dimensions or arrays of Members.

Selecting alternate page members

For this exercise, you will use the Sales Accounts by Months Excel worksheet created in Entering Data into proCube Through an Excel Worksheet.

  1. Double-click C4 to change from Aunt Re’s Chicken to a different Product Page member. The Select Member for Product dialog opens.

selectmemberforproductdialog.png

Figure 1.    Select A Member dialog, with Hierarchy expanded

  1. Click the Hierarchy tab.

  2. Expand the Total Product and highlight Southtown Pies.

  3. Click OK to change the product member and return to the Excel worksheet. Southtown Pies replaces Aunt Re's Chicken in cell C4. is entered into the cell. If Excel’s recalculation mode is set to Automatic, the worksheet will automatically be updated, reflecting the data of the new Member selected.

  4. Press F9 to recalculate the new worksheet.

southtownpiesproductexcelsheet.png

Figure 2.    Southtown Pies - New Product Member in Excel

Rather than work through the Select A Member dialog, you can change the Member number in the argument (from 8 in the above formula) to another Member number in the list. You can also type a Member name into the appropriate worksheet cell to view that Member’s data contained in a linked cube. Note, however, that a typed member name will now be ‘fixed’ as a name, rather than as an argument—as above, the cell contains the function and argument, =OLAPMember($B$1,”Products”,8) [In the example worksheet, you could type Aunt Re’s Chicken into C4 to set this sheet to always show data for Aunt Re’s Chicken. Make sure to type the name correctly, and then press F9 to update the worksheet.]

Database reference formulas

As we demonstrated previously by looking at the OLAPTable and OLAPReadWrite functions, an Excel worksheet maintains its bi-directional connection to proCube through the use of database reference formulas . Database references look like formulas, but are different in that they provide arithmetic calculations—the references that refer to individual cells (or Dimension-member intersection points) within proCube database cubes. The database references are similar to Excel formulas that link two or more worksheets within a workbook.

But as you have already seen, proCube database references provide far more power than Excel formulas because they have following features:

  • Database references can be bi-directional. Thus, data can be retrieved from a proCube database cube, and data can be entered through a worksheet into a proCube cube.

  • Database reference formulas remain intact in worksheet cells even when data is typed over them. Typed values do not overwrite the references. Only accidental deletion of cell contents overwrites the references. [Note: database reference formulas can be restored easily by copying and pasting the formula from an adjacent cell back into a cleared cell.]

  • Database reference formulas can be built using Member names, Alias names (see Section 3) or Excel cell references. The database reference may contain one of these formats, or a combination of all three.

The following sections discuss key aspects of database reference formulas, including:

  • The structural nature of the references.

  • How to build a reference formula using the Edit Formula Wizard.

  • How to modify Edit Formula text.

The structure of database references

To illustrate the structure of database references, the Sales Accounts by Months slice from the Sales cube was created as an Excel worksheet using OLAPReadWrite (previously, this slice was generated as an OLAPTable) and reproduced below.

olapreadwriteworksheetdatastructures.png

Figure 3.    OLAPReadWrite Data Structures

fx (Excel Formula bar) =OLAP ReadWrite($B$1,$B$2,$C$3,$C$4,B$8,$A9)

OLAPReadWrite is the proCube database reference formula Function that enables values to be read from and written to a cube.  Each of the cell references within the parentheses is called a database reference Argument.

The following is a breakdown of the example database reference arguments:

Excel Formula Value

Corresponding proCube Database Description

$B$1

proCube Server and Database Name

$B$2

proCube Cube Name

$C$3

Page Dimension Region Member

$C$4

Page Dimension Product Member

B$8

Column Dimension Sales Account Member Name

$A9

Row Dimension Month Member Name

These database reference arguments indicate that at this moment, they are accessing the example file Acme Trading Company, Sales cube, for the Region dimension, member USA, for the Product dimension, member Aunt Re’s Chicken, for the Sales Account dimension, for the Month dimension.

By using cell references, you have the flexibility to change arguments to access different locations within a proCube database. You could access, for example, different Page members; groups or lists of row and column Dimensions; or even different Cubes within the database—all by simply changing the reference arguments.

You can quickly identify database references by clicking within the formula bar. All references to that cell are highlighted in the worksheet as shown below.

highlightedcellsolapreadwriteworksheet.png

Figure 4.    Highlighting Cells for OLAPReadWrite Worksheet

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk