Cube and Member Wizards

Cube and Member Wizards

Just as the Slice Wizard expedites slice creation, the Cube and Member Wizards help you to quickly insert your cubes and members into Excel worksheets. Use the Cube and Member Wizards to create custom reports within Excel showing data from multiple cubes with customized member lists.

Using the cube wizard

The Cube Wizard is invoked from proCube's Excel ribbon and  easily lets you add a cube to an Excel worksheet. It is especially useful for inserting multiple single point read and single point read/write cells taken from multiple cubes. Multiple cubes placed on an Excel worksheet lets you create custom reports that compare and contrast different sides of your business.

  1. Start Excel.

  2. From a blank workbook, display the proCube ribbon and click Cube Wizard to open the Select Server and Database dialog.

selectserver&database.png

Figure 1.    Select Server and Database

  1. Here you will connect to a working database previously defined within the proCube environment. To connect, use the Server drop-down box to select a server.

If there are no servers defined in the drop-down list, enter the name of a valid proCube server. If necessary, contact your Administrator for the name of a valid proCube Server.

  1. Once you select a server, one or more databases display in the Database field. Select a database from the list.

  2. Check the validity of the connection by clicking Test Connection. A popup box displays reading “Your connection is valid” if you are properly connected.

A specific cell within the Excel sheet is listed in the OLAPOPenCell field as shown in the Select Server and Database dialog. The default value of this field is cell A1 in the Excel worksheet. You can change the location of the OLAPOpen (proCube connection) information by clicking in a new cell in the Excel worksheet. However, if data exists in the newly designated cell, it will be erased.

  1. Click OK to accept your values. The OLAPCube Formula dialog opens.

  2. olapcubeformula.png

Figure 2.    OLAPCube Formula

  1. The next step is to select the cube. In the OLAPCube Formula dialog, available cubes have been sorted by Ascending. These same cubes could also be sorted by Descending or by As Entered. Change the sort method by selecting As Entered (or Descending) and clicking Apply.

  1. Highlight a cube.

  2. Select a specific cell on the Excel worksheet (or keep the default value) and click OK. The Specify Database and Cube dialog opens. In this dialog you will work with the current database and cube connections.

specifydatabase&cubewizard.png

Figure 3.    Specify Database and Cube for the Cube Wizard

From Specify Database and Cube for the Cube Wizard, you can add:

  • a new server and database

  • a new cube

  1. [Optional] Clicking Define New opens the Select Server and Database dialog. Clicking Define Cube opens the OLAPCube Formula dialog.

  2. Click Finish to accept the selected server, database, and cube.

Using the member wizard

The Member Wizard is used to add one or all members of a selected dimension into an Excel worksheet to create a custom report. Often, the Member Wizard is used in combination with the Cube Wizard to add members to the newly inserted cube. Like the Cube Wizard, the Member Wizard is invoked from proCube's Excel ribbon.

The procedure below describes how to use the Member Wizard in a typical manner. Following this procedure is a description of the wizard's available functionality.

To place members on an Excel worksheet using the Member Wizard:

  1. Open an Excel worksheet and connected it to a server, database, and cube.

  2. Click the cell where the array of Members will be entered. Members can be displayed downward or across cells.

  3. Click Member Wizard to open the wizard.

selectmemberswizard.png

Figure 4.    Select Members

  1. From the Dimension drop-down, select one of the dimensions tied to the current cube. The selected dimension's members display in the All Members list.

  2. From the All Members list, highlight members to include, and click > to transfer them to the Selected Members list as shown in Select Members.

  3. From the Display drop-down, select Down to display the selected members downward from the selected Excel worksheet cell.

  4. Click OK to close the Select Members dialog to display the selected members on the Excel worksheet. The members are entered into the selected cell and listed in a downward fashion.

  5. Press F9 (if necessary) to recalculate the worksheet.

Available member wizard functionality

Wizard Function

Description & Usage

Dimension drop-down

Lists the dimensions for the selected cube. By selecting a dimension, its members become available for selection from the All Members: list.

Available Members:

Lists members of the selected dimension which can be moved over to Selected Members: for further processing and placement on the Excel worksheet. The All Members tab contains functionality for working, sorting and viewing members. The Hierarchy tab displays members within their Aggregate/Detail structure.

Subset

Lists the available subsets that the members were saved under.

Filter Criteria

Helps you to sort the displayed members. Enter search criteria in list to the right.
Contains – select and enter part of a word or phrase. For example, enter 'x'to list all members containing the letter 'x'.
Begins – select and enter part of a word or phrase. For example, enter 'bus' to list all members starting with the word 'bus'.
Ends –  select and enter part of a word or phrase. For example, enter 'Expenses' to list all members ending with the word or phrase of 'Expenses'.

Sort Order

Sorts the displayed members by As Entered, Ascending, Descending.

Level

The levels listed correspond to the number of member levels within the selected dimension (to compare, click the Hierarchy tab and expand all sublevels). A level, if selected (checked) will display. If deselected, the level will not display. Click Apply to refresh the list after changing levels. By default, all levels are selected.

Apply

Affect changes made with the All Members tab

Display

Drop-down affecting how the selected members are listed within the Excel worksheet. Members can be listed Down (vertically) or Across (horizontally).

memwizardbuttons1.png   memwizardbuttons2.png     memwizardbuttons3.png

Buttons to move selected members back-and-forth between Available Members: and Selected Members: and move members up-and-down within Selected Members:.

memwizardbuttons4.png

Buttons to help reorder members in Selected Members.Reordering occurs after members are highlighted using the mouse pointer or Select All.

Display Members by Aliases

A switch to display members by their names or by their aliases.

Selected Members:

Members with their listed order are placed onto the Excel worksheet from this list after clicking OK.

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk