Create
a new Query
Using the Investigator Olap queries are
visually built by navigating the cubes and data structures. So long as you are
familiar with the structure of the business data, for example organization and
product hierarchies, you should not need to understand the underlying query
that is created. If you are not familiar with the term Olap the following Olap
Primer will provide an introduction to the subject.
The creation of a basic query involves the
following steps:
Olap Primer
OLAP is an acronym for online analytical
processing. It is an approach to quickly provide the answer to complex database
queries. It is used in business reporting for sales, marketing, management
reporting, data mining and similar areas. Some people have suggested that an
alternative and perhaps more descriptive term to describe the concept of OLAP
is Fast Analysis of Shared Multidimensional Information, or FASMI.
The reason for using OLAP to answer queries
is speed. Relational databases store entities in discrete tables if they have
been properly normalized. This structure is good for operational databases but
for complex multi-table queries it is relatively slow. A better model for
querying, but worse for operational use, is a dimensional database.
Functionality
OLAP takes a snapshot of a relational
database and restructures it into dimensional data. The queries can then be run
against this. It has been claimed that for complex queries OLAP can produce an
answer in around 0.1% of the time for the same query on relational data.
An OLAP structure created from the
operational data is called an OLAP cube. The cube is created from a star schema
of tables. At the centre is the fact table which lists the core facts which
make up the query. Numerous dimension tables are linked to the fact tables.
These tables indicate how the aggregations of relational data can be analyzed.
The number of possible aggregations is determined by every possible manner in
which the original data can be hierarchically linked.
For example a set of customers can be
grouped by city, by district or by country; so with 50 cities, 8 districts and
two countries there are three hierarchical levels with 60 members. These
customers can be considered in relation to products; if there are 250 products
with 20 categories, three families and three departments then there are 276
product members. With just these two dimensions there are 16,560 possible
aggregations. As the data considered increases the number of aggregations can
quickly total tens of millions or more.
The calculation of the aggregations and the
base data combined make up an OLAP cube, which can potentially contain all the
answers to every query which can be answered from the data. Due to the
potential number of aggregations to be calculated, often only a predetermined
number are fully calculated while the remainder are solved when demanded.
Types of OLAP
Beyond the basic concept there are three
types of OLAP - Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and
Hybrid OLAP (HOLAP). MOLAP is the 'classic' form of OLAP and is sometimes
referred to as just OLAP. It uses a summary database, has a specific dimensional
database engine and creates the required schema as a dimensional set of both
base data and aggregations. ROLAP works directly with relational databases, the
base data and the dimension tables are stored as relational tables and new
tables are created to hold the aggregation information. Hybrid OLAP uses
relational tables to hold base data and multi-dimensional tables to hold the
speculative aggregations.
Each type has certain benefits, although
there is disagreement about the specifics of the benefits between providers.
MOLAP is better on smaller sets of data, it is faster to calculate the
aggregations and return answers but does create enormous amounts of data. ROLAP
is considered more scalable and uses the least space but is the slowest at
pre-processing and query performance. HOLAP is between the two in all areas,
but it can pre-process quickly and scale well. The difficulty in implementing
OLAP comes in forming the queries, choosing the base data and developing the
schema, as a result of which most modern OLAP products come with huge libraries
of pre-configured queries. Another problem is in the base data - it must be
complete and consistent.
After the Investigator window has opened
you will need to select a cube. You do this by selecting the Source in the Home
bar of the ribbon menu.
"Select Cube" window. This
window displays a list of the cubes and perspectives that exist within the
database. Select the item that contains the data you wish to query and then
select OK to close the window.
Data within a cube is organized into 3 main
categories:
- Measures
- KPIs
- Hierarchies
You select this data from the Field List
task pane which is, by default displayed on the left hand side of the main
window. The Field List is organized as a hierarchy of folders which can be
explored by expanding the content. The organization of these folders is set by
the application administrator.
Adding Fields to the Query
There are 3 ways by which fields can be
added to the query.
Select the field by clicking the check box in the Field
List.
When you do this the field will be added to
the query in its default location. The location depends on the type of field
selected. For example user hierarchies will be added to the rows of your report
by default where KPIs will be added to the columns.
Context Menu
If you open the context menu for an item in
the field list a context menu will be displayed allowing you to select where
you would like the field to be displayed.
Drag and Drop
You can also drag an item from the Field
List onto the report and drop it in the region where you would like to see it
displayed. Before any data has been added to a report the display panel will
contain three regions stating:
"Drop column fields here"
"Drop row fields here"
"Drop measures here"
These regions are targets for dropping
fields as you build your report.
Once fields have been added to the query
the display will change to show the default selection. Each hierarchy added to
the query will be displayed along with a "grip control" that can be
used to re-organize the query by dragging hierarchies both within the report
and removing them by dragging back to the field list.
The following screen shot shows a simple
default report created by dragging hierarchies from the "Account",
"Organization" and "Department" dimension onto the query.
Filtering Fields by Measure Group
It is possible to filter the list of fields
in the Field List by selecting a Measure Group from the combo labeled
"Show fields relating to:". Your application administrator can create
measure groups that relate groups of measures, KPIs and hierarchies by subject
matter. Filtering by measure group can make it easier to find the combinations
of data that should be displayed in your query.
Choosing additional rows and columns to be
displayed in the query requires selecting the members of each hierarchy that
has been added to the display. There are 2 ways to do this:
Selecting members by navigating within the grid
As you move the mouse pointer over each
label cell in the grid, if the member has children in the hierarchy, a small
+/- symbol will be displayed. If you click on this symbol the selection will be
expanded to include the member’s children. Clicking on this member again will
remove the child members thus "collapsing" the query.
Before Expanding...
After Expanding...
Alternately if click on the drop down arrow
for the member a context menu will be displayed with the following options:
Drill Down
Will replace the current selection with the
children of the selected member.
Drill up
Will replace the current selection with the
members in the same level as the selected member’s parent
Isolate
Will replace the current selection with
just the selected member
Select Fields...
Launches the member selection window for
the hierarchy as explained below.
Member Selection Windows
The member selection window is used to make
multiple, non-contiguous selections from a hierarchy. The window can be
launched from the following locations:
- The context menu displayed when you click
on the dropdown for an item in the Field List
- The context menu displayed when you click
on the dropdown for a member in the grid display
- Left mouse clicking the arrow in the
hierarchy grip control in the top left region of the grid
Member Selection Window
The member selection window for standard
hierarchies is shown below.
Selections can be made by clicking on the
dropdown arrow for a member and selecting the desired selection type. This
allows you a wide range of choice as to which members will be returned by the
query.
Note: Careful choice of selection type will
improve the behavior of your query if the database undergoes structural
changes. For example by selecting "This item + children" any new
child added to a member will automatically be included when the query is
re-run.
The Selection Cone
Once a selection has been made the pyramid
icon for the member will be changed to indicate the type of selection made.
This helps you to visualize how your query was built particularly when you
return to the query after a significant period of time.
Include and Exclude Selections
Because a selection on a member can affect
many other members a small green tick will be displayed next to the selection
cone on all the items that have been selected as a consequence of a parent
selection.
If you make the same selection on an item
twice the selection will become an "exclude" selection which will
remove the selections from that point in the hierarchy. Exclude selections are
highlighted by a small red cross next to the selection cone. The following
example shows how include and exclude selections can be combined to create a
composite query.
Named Sets
Named Sets are pre-defined lists of members
that can be included in reports without the need to specify the individual
members. If you open the Named Sets folder in the hierarchy tree you will see a
list of named sets that has been created by the database administrator.
Sorting and filtering can occur on both
data values and member attributes. Details of how to set sort and filter
options are shown in the following sections.
Sorting and filtering on data values is
accessed from the home tab in the ribbon menu.
The first task in sorting and filtering is
to select the row/column you wish to work with.
Sorting Data
The quick sort options will sort data in
ascending or descending order within the selected column whilst preserving the
hierarchy sequence. In other words the children of each parent node are sorted
within the parent.
Selecting the sort button launches the Sort
Data window and allows you to specify more detailed sort options.
The Sort Data window allows you to change
the selected column and specify the "Break Hierarchy" option. When
this option is selected data will be sorted in ascending or descending order
irrespective of hierarchy. Use this option with care as the results can be
confusing. Normally this option is only relevant where selected members are all
from the same hierarchy level.
Filtering Data
Selecting the Filter menu option launches
the Filter Values window.
Range Filtering
The Filter Values window allows you to
select the top/bottom group of values based on the following criteria.
|
Count
|
After sorting the set, the Count function
then returns the number of values, specified by Count, with the highest
value.
|
|
Percent
|
The Percent function calculates the sum
of the members in the column evaluated over the set specified by the column,
and then sorts the set in ascending/descending order. The function then
returns the elements with the lowest/highest values whose cumulative
percentage of the total of Value is equal to or less than the percentage
specified in Percentage.
|
|
Sum
|
The Sum function sorts on the value
specified by Value and picks up the top n (the smallest number possible)
elements such that their sum is at least the value specified by Value.
|
Value Filtering
When you check the Value filter check box
you can set up multiple criteria to filter data by the values in the cells.
Sorting and filtering member attributes is
carried out in the Select Members window via the sort and filter toolbar or by
selecting the drop down window for any attribute displayed in the tree view.
Sort Options
You can sort the selections made by the
captions in ascending or descending order by clicking on the “sort ascending”
or “sort descending” buttons in the toolbar.
The “More sort options…”window provides extended
sort options which allow you to control how sorting relates to data groups
within the hierarchy.
By default data is sorted in hierarchy
order as determined by the application administrator with totals and sub-totals
at the bottom of each group and is filtered so that no duplicate member
selections are shown. Selecting "More Sort Options..." launches the
"Sort Options" window (shown with the default options).
Note: If an Alpha sort is selected it is not
possible to specify that totals should be displayed at the bottom of the group.
Filter Options
Selecting "Filter Options..."
from the "Select Members" window launches the "Filter
Options" window.
This window allows you to apply Top “N” and
Bottom “N” filter criteria to the set of selected fields.
The "Remove duplicate values"
check-box indicates whether any duplicate member selections should be suppressed.
The “Include calculated members” check box
determines whether calculated members in the cube will be automatically
included in the query. This is checked by default.
You can also filter the set by selecting
values for the attributes of each field. To do this you select the drop down
arrow on the header for the attribute in the tree view. This displays the
attribute filter window.
By default you can select the attribute
values to display from a list of all available values. Alternately you can set up
alternate filter criteria by selecting the filter behavior from the drop down
list and selecting or typing the required filter criteria in the text box.
Note: If you select attribute values for
more than one attribute they are combined using “And” behavior.
Adding calculations to queries is similar
to working with a spreadsheet program such as Microsoft Excel the main
difference being that you need to specify which rows, columns and cells are
allowed to contain formulae. The following topics describe how to work with
calculations in a query:
To insert a calculated row or column in the
query select a member within the level that you want the calculation to appear
and press the right mouse button.
From the context menu select the option
"Insert Calculated Row" or "Insert Calculated Column". A
new member will be added at the end of the displayed group of values within the
level selected.
Calculation Bar
As soon as a calculated row/column is
selected by left mouse click the Calculations tab in the ribbon menu will be
selected and the "calculation bar" will be displayed. It is in this
bar that you will enter the formula for the calculated member.
Calculation formulae are entered into the
calculation bar using cell references in much the same way as you would define
formulae for spreadsheet cells. Some slight differences occur in that you can
describe the contents of an entire row or column using the $ notation and the
values of label cells have a specific meaning with relation to the members and
hierarchies in the cube. These differences are described in detail later in
this topic. First we will show some examples of how to define formulae for
typical business calculations.
Example 1 - Percentage of Total
In the screen below a calculated column
named "Percentage of Total" has been created in order to display
expenses as a percentage of total expenses. The formula for this column is
entered as
$B$/$B$12
Where the expression $B$
translates to "the current value in column B" and $B$12
refers to the cell "Operating Expenses for department Corporate".
This single formula computes the values for all cells in column C without the
need to specify individual cell formulae.
Note: It happens that the formula B1/B12 would
give the same result in this case as there is only one hierarchy on the column
axis. This would not be true for cases where 2 or more hierarchies are
included.
Example 2 - Actual vs.
Budget Variance
The next example shows a typical Actual vs.
Budget variance report with a calculated column for "Variance" and
another for "Variance %".
The formulae for these columns are as
follows:
Variance
B2-/C2
Note: By using the cell references B2 and C2
rather than the column references B$ and C$ the formula is able to work for any
year. The reference B$ in this example refers to the specific combination of
"CY 2001" with "Actual"
Variance %
Iif(C2=0, null, D2/C2)
In this formula we have used the Mdx Iif
function to check for division by zero which caters for the 2 null values in
cells C16 and C17. Use of functions is described in the topic Inserting
Functions. It can be seen from this formula that there is no
problem referencing another calculated column.
Investigator vs Spreadsheet
As you will have seen from the examples
above there are some subtle differences between the formulae used in the
Investigator and those used in a spreadsheet. These differences are as follows:
1: Investigator formulae can apply to multiple cells
When a calculated row or column is added to
the query the formula entered for that member applies to all the cells
associated with that row or column. This is very powerful as it significantly
decreases the number of formulae that need to be entered in order to create a
report.
2: $ Notation refers to a specific column
When the $ sign is used in conjunction with
a row or column reference the formula refers to the "current value"
within that row or column.
3: No = sign is used to precede the calculation
It is not necessary to specify an = at the
start of a formula. A side effect of this is that string values must be quoted
or generated by string functions. If you enter the value ABC the system will
try to interpret this as a function or cell reference and will show an error.
The correct way to enter a string is "ABC"
Technical Note
Before running the query the Investigator
expands the formula entered to an Mdx query language expression. In Example 2
above the expression:
Iif(C2=0, null, D2/C2)
Is expanded to the Mdx expression:
Iif([Scenario].[Scenario].&[2]=0, null,
[Scenario].[Scenario].[Variance]/[Scenario].[Scenario].&[2])
It is easy to see that the spreadsheet
formula is easier to read and maintain.
A full library of Mdx and VB functions is
provided via the Ribbon Menu. To insert a function into a formula you can
select the function from the category drop down menus e.g. Date/Time.
