Using the DeepSee Analyzer
Creating Pivot Tables
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter presents basic information on creating pivot tables. It discusses the following topics:

Introduction to the Autosave Feature
DeepSee provides an optional autosave feature, which can automatically save the state of the Analyzer for each user, for each pivot table. If the autosave feature is enabled for the Analyzer:
For information on this option, see Specifying Basic Settings in the DeepSee Implementation Guide. Note that there is a separate autosave feature for the User Portal.
Selecting a Subject Area
A subject area provides the elements that you use as the basis of any pivot table. To select the subject area to use:
  1. Click the Change button or click the Select Subject Area link.
  2. Click the name of the subject area.
  3. Click OK.
Getting Familiar with a Subject Area
The Model Contents area lists the contents of the subject area that you are currently viewing. For example, this area might look like this initially:
If you have not worked with the current subject area before, it is useful to examine its contents. First, use the drop-down list to select the kind of item to display in this area. Select one of the following:
The main purpose of the Model Content area is to enable you to create pivot tables. To do this, you generally drag and drop items into the Pivot Builder area on the right, as described in the next section.
Tip:
For most items, if you hover the cursor over the name of an item in the Model Content area, the system displays a tooltip that shows the description of the item, if any.
Measures
To display a list of measures, select Dimensions from the drop-down list in the Model Contents area. Then expand the Measures folder. This folder lists all measures in the subject area. For example:
You can have two types of measures, indicated by icons as follows:
Standard measures
Calculated measures, which are defined in terms of other measures
Dimensions
To display a list of dimensions, select Dimensions from the drop-down list in the Model Contents area. Then expand the Dimensions folder. This folder lists the dimensions and the levels, members, and properties that they contain; it might also list related cubes, with their dimensions, levels, and so on.
Click the triangle next to any dimension name to expand it. A dimension contains at least one level and may also include a special member known as the All member. In the following example, the AgeD dimension includes an All member named All Patients, as well as the levels Age Group, Age Bucket, and Age.
Hierarchies
Each dimension also includes at least one hierarchy, which is the actual container for levels. The following shows an example (a modified version of the Patients cube). Here H1 is a hierarchy name.
`
By default, the hierarchy names are not shown unless a dimension contains multiple hierarchies. Alternatively, a dimension can be defined so that its hierarchy names are always shown or never shown.
In the Patients cube, each dimension contains only one hierarchy, so the hierarchy names are not shown. Hierarchies are purely informational; you cannot drag and drop them as you can with the other items in the Model Contents pane.
Level Members and Properties
If you expand a level, the list displays the members of that level. For example:
If a level also includes properties, this area shows the property names with a different icon. For example, the City level includes the Population and Principal Export properties:
Variations for Data Levels with Many Members
If a data level contains more than 500 members but fewer than 10000 members, this area displays any property names, followed by subfolders that contain the members in groups of 100. For example (not as contained in SAMPLES):
In this case, DeepSee does not access the member names until you expand one of these subfolders. Thus it is possible to see and use the properties without waiting for DeepSee to access all the member names.
If a level contains 10000 members or more, this area displays only the property names, if any, of the level.
The preceding discussion applies only to data levels. In contrast, time levels are always displayed in the same way.
Related Cubes
The Dimensions folder might also list related cubes, which it shows in bold italics. If you expand a related cube, you can access the dimensions, levels (and possibly hierarchies) of the other cube, as follows:
Pivot Variables
To display a list of pivot variables, select Dimensions from the drop-down list in the Model Contents area. Then expand the Pivot Variables folder. This folder (if included) lists any pivot variables in the subject area. For example:
Unlike most other elements in the Dimensions section, pivot variables are defined in the Analyzer; see Defining and Using Pivot Variables,” later in this book.
Plug-ins
To display a list of plugins, select Dimensions from the drop-down list in the Model Contents area. Then expand the Plug-ins folder. This folder (if included) lists any plugins in the subject area. For example:
A plugin contains one or more properties, each of which computes a value. This plugin contains two properties. Plugin properties can be used in the same way as measures.
Named Sets
To display a list of named sets, select Dimensions from the drop-down list in the Model Contents area. Then expand the Named Sets folder. This folder (if included) lists any named sets in the subject area. For example:
A set contains one or more items; typically each item is a member of a level defined elsewhere.
Named Filters
To display a list of named filters, select Dimensions from the drop-down list in the Model Contents area. Then expand the Named Filters folder. This folder (if included) lists any named filters in the subject area. For example:
Unlike most other elements in the Dimensions section, named filters are defined in the Analyzer; see Defining Named Filters,” later in this book.
Calculated Members
At the bottom of Dimensions section, the Model Contents pane shows any special dimensions that contain calculated members. These special dimensions just include members, not hierarchies or levels. For example:
Calculated members are typically combinations of other members. For example, the Primary Colors member is a combination of the Red, Yellow, and Blue members of the Favorite Color level.
Other dimensions in your model might also include calculated members. If so, the member is displayed in that dimension with a different icon than other members. For example, if we redefined the two calculated members to move them to the ColorD dimension, we would see the following for that dimension:
Unlike most other elements in the Dimensions section, calculated members can be defined in the Analyzer; see Defining Calculated Elements,” later in this book.
Pivot Tables
To display a list of pivot tables, select Pivots from the drop-down list in the Model Contents area. Then expand the Pivots folder. This folder (if included) lists any pivot tables defined in the subject area. For example:
To use this area, expand folders and needed and click a pivot table name. The Pivot Preview area then displays that pivot table.
Quality Measures
To display a list of quality measures, select Quality Measures from the drop-down list in the Model Contents area. Then expand the Quality Measures folder. This folder (if included) lists any quality measures in the subject area. For example:
Quality measures can be used in the same way as measures.
Detail Listings
To display a list of detail listings, select Detail Listings from the drop-down list in the Model Contents area. This area then lists any detail listings defined in the subject area. For example:
For information on using this area, see Displaying a Detail Listing,” later in this book.
Creating a Pivot Table
To create a pivot table:
  1. Click New.
  2. In the Model Contents pane, select either Dimensions or Quality Measures.
  3. Drag and drop items from the Model Contents pane to the Pivot Builder area, as described in the rest of this chapter.
    For example:
    1. Expand DiagD in the Model Contents pane.
    2. Drag and drop Diagnoses to Rows.
      Or double-click Diagnoses.
      Or click Diagnoses and then click the plus sign in the header of the Rows box.
    3. Drag and drop Patient Count to Measures.
      Or double-click this measure.
      Or click Patient Count and then click the plus sign in the header of the Measures box.
    4. Drag and drop Avg Allergy Count to Measures.
    5. Expand AgeD in the Model Contents pane.
    6. Drag and drop All Patients to Rows, below Diagnoses.
  4. Repeat steps 2 and 3 as necessary.
    Each time you make a change, the Analyzer reruns the pivot table and shows the results in the Pivot Preview area. For the preceding steps, the final result might be as follows (depending on the values in your data):
  5. Click Save.
    The system displays a dialog box where you specify the pivot table name.
  6. For Pivot Name, specify the name of the pivot table.
    Note that this complete string is used (along with Folder) as the logical name of the pivot table; DeepSee displays this logical name when you use the Save As option, for example.
  7. Optionally specify other values as follows:
  8. Click OK.
Specifying the Rows in a Pivot Table
To specify the rows of the pivot table, you drag and drop items of items of various types from the Model Contents pane into different areas of the Rows box. This section discusses the items that can be used as rows (and shows samples) and then discusses the details for how to specify the rows.
Items That Can Be Used in the Rows Box
You can use any combination of the following items in the Rows box:
You can also double-click a level, dimension, member, or named set in the Model Contents pane. If you do, the system removes any items currently in Rows and replaces them with what you double-clicked.
If hierarchies are displayed in the Model Contents pane, they are for informational purposes only; you cannot drag and drop them.
Specifying the Rows
To specify the rows in the pivot table, drag and drop items from the Model Contents pane to the Drop row here label in the Rows box. When you do so, the Rows box looks like this:
Now there are three places here into which you can drag and drop additional items:
To remove an item from the rows, click the X button to the right of its label in the Rows box.
For information on the Advanced Options button , see Customizing a Pivot Table Items,” later in this book.
Note:
If you use calculated members for rows, they are always shown after the other members.
Specifying the Columns in a Pivot Table
The columns of a pivot table are controlled by Measures and by Columns:
Measures are always used as the innermost grouping. For example, consider the following pivot table configuration:
This pivot table looks as follows:
Note:
DeepSee does not display more than 100 columns.
Displaying Measures in a Pivot Table
By default, the Analyzer displays the count of lowest-level records. For example:
Within the body of the pivot table, you can instead display one or more measures, quality measures, or plugin properties, in any combination. Note that you can use measures, quality measures, and plugin properties (usually just referred to as plugins) interchangeably. This section uses the generic term measures to refer to all of these items.
To display a measure as a column, drag and drop it into the Measures box or the Columns box. For example:
Measures Pivot Table
Avg Age measure
Or double-click the measure in the Model Contents pane.
Or drag and drop a measure to the Rows box, as discussed earlier. Note that if you display a measure as a row and another measure as a column, DeepSee displays only the value of the measure that is used as a column.
The following subsections discuss additional topics:
Pivot Tables with One Measure
To display measures as columns, you can drag and drop them to the Measures box or to the Columns box. If the pivot table has multiple measures, the resulting pivot table (and underlying MDX query) is identical with either technique. If the pivot table has only a single measure, however, the two techniques generate slightly different MDX queries:
Technique Comments
Drag and drop the measure to the Measures box The underlying MDX query uses the measure in its filter clause. This means that this pivot table cannot be displayed in a scorecard (on a dashboard).
Drag and drop the measure to the Columns box The underlying MDX query uses the measure as a column. This pivot table can be displayed in a scorecard.
Pivot Tables with Multiple Measures
If you drag and drop multiple measures to the Measures box, you can specify whether the measures are used as columns (as in the default case) or as rows. To do so, click the Options button in the Measures box. DeepSee displays a dialog box; for this discussion, use the options in the section Place measures on. Choose one of the following:
Then click OK.
This option makes it easier to redefine complex pivot tables, because you can rearrange the Rows box or Columns box and leave the measures alone.
For example, consider the following pivot table definition:
By default, the measures are used as columns (nested within the elements listed in the Columns box), as in the following example:
If you instead use the option to display the measures as rows, the pivot table looks like this:
This pivot table is equivalent to one defined as follows:
Controlling the Display of the Measure Headings
By default, when a pivot table displays a level on columns and displays measures, the pivot table may or may not include a heading for the measures, as follows:
You can control this behavior. To do so, click the Options button in the Measures box. DeepSee displays a dialog box; for this discussion, use the options in the section Display measure headers. Choose one of the following options:
These options apply only when the pivot table also displays another item on columns, as in the examples shown here.
Displaying Properties in a Pivot Table
You can display the properties of a level if you are also using that level as rows or columns. For example, if you display City as rows, you can display Principal Export as a column (by dragging the property to the Columns box) :
If you display City as columns, you can display Principal Export as a row:
Displaying Both Properties and Measures
If you display a property as a column and you want to display a measure as another column, you must drag and drop the measure to Columns rather than to Measures. For example:
Transposing a Pivot Table
You can transpose a pivot table; that is, switch its rows and columns. To do so, click the Transpose button .
Changing the Order of Items
To change the order of items listed within the Rows, Columns, Measures, or Filters box:
  1. Click an item within that box. When you do so, the system enables the applicable buttons in the header of the box.
  2. Click the up or down arrows as appropriate.
Copying and Pasting Items
To copy and paste items within the Rows, Columns, Measures, or Filters box:
  1. Click an item within that box. Now it is shown with a highlight.
  2. Click the Copy button in the top area.
  3. Click the destination for this item. Either click another box or click an item in a box.
  4. Click one of the paste buttons:
Adding a Summary Row or Column
You can add a summary row, summary column, or both. This section describes the following approaches:
Adding a Summary Row or Column as a Pivot Option
To add a summary row, summary column, or both:
  1. Click the Pivot Options button .
    The Analyzer displays the Pivot Options page, which is discussed in detail later in this book.
    This procedure discusses only the Summary option.
  2. Click the Summary check box.
  3. Select a summary option from the drop-down list. The options are as follows:
    For information on overriding how the summary is computed for a given measure, see Specifying Alternative Aggregation Methods for a Measure,” later in this book.
  4. (For summary rows only) Optionally, to cause the summary row to summarize all rows (including rows on later pages), select All Rows from the second drop-down list.
For example, the following pivot table includes a total row. Notice that it displays the total only for numeric values:
The following example shows % of Total, which is useful primarily when you use a level in Columns:
In contrast to the other forms of summaries, note the following:
Adding the All Member as a Summary Row or Column
Your cube may include one or more All members. Each dimension can contain a All member, which is listed within the dimension, in the Model Contents pane, before any levels. For example:
An All member represents all records and is typically named something like All Ages or All Categories. Or, as in the example shown here, the All member might have a truly generic name.
You can drag and drop this member to Rows or Columns to add it as a summary row or column. For example:
In contrast to using the pivot options, this approach allows each measure to be aggregated in a different manner. The Patient Count measure is summed, and the Avg Age and Avg Allergy Count measures are averaged.
If you do not have any All members, you can always add a custom aggregation; this is also quite simple to do.
Adding a Summary Row or Column as a Custom Aggregation
For clarity, this section describes how to add a summary row as a custom aggregation. The technique is easily adapted for a summary column.
  1. For the level used as the rows, add the same level again to the Rows box.
  2. Click the Advanced Options button next to the second level in Rows.
    The Analyzer displays the Advanced Options page, which is discussed in detail later in this book.
    This procedure discusses only a couple of the options.
  3. Select Compute Aggregate and then select an aggregation function. You can use any of the following functions:
  4. Optionally type a new caption into Caption. The default caption is the name of the function.
When you use this option, the system first uses any other settings you specified on this dialog box.
For example, consider the following pivot table:
This pivot table is defined with the following items in Rows:
The second Gender item uses the following options:
Applying 80/20 Suppression
The Analyzer provides an easy way to display the top 80% of a set (considering the count measure by default), and to group the bottom 20% into a single unit, as in the following example.
This example demonstrates the 80/20 suppression option used for rows. The option is also available for columns. To use this option:
  1. Click the Advanced Options button in the Rows or Columns box.
  2. Optionally click Sort Members.
    Then select a measure and select Ascending or Descending.
    By default, the 80/20 suppression option first sorts the members in descending order by count. Use Sort Members to specify a different sort order.
  3. Click OK.
To modify the query to use different percentages, see the section Modifying Details of the 80/20 Suppression Option,” later in this book.
Clearing the Autosave State of a Pivot Table
If the autosave feature for the Analyzer is enabled, then when you display a pivot table, the Analyzer displays that pivot table with any unsaved changes that you might have made in the Analyzer. To remove these unsaved changes, click Restore. Then the Analyzer displays the pivot table as defined in its saved definition.
Or save the pivot table, thus saving the changes and changing the definition. See the next topic.
Saving a Pivot Table
To save a pivot table, do one of the following:
Or click Save As to make a copy of the pivot table. The Analyzer prompts you for a new name.