This lesson teaches you how to use the Query Builder to
modify the layout of presentations, and selections for the data that is displayed
in crosstabs and graphs.
Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
Overview
The Query Builder provides a simple user
interface to define sophisticated queries. A key strength of the Query Builder
is that the end-user does not need to know a query language to define the query.
In
this lesson, you will learn how to create powerful analytic queries that are
made simple by presenting the query definition in business terms. End users
can easliy modify these queries to meet their needs using the same simple interface.
You will use the Query Builder to modify
the layout and selections for the data that appears in a crosstab and a graph.
Specifically, you will learn how to:
Select
Query Items
The Query Builder presents the
available measures for query in a format that is identical to the Items
step of the Presentation Wizard. You can use this interface to add and
remove query items for a presentation at any time.
Modify the Query Layout
The Query Builder provides a graphical layout tool that lets you modify
the layout of your query for a Crosstab or a Graph.
Specify Dimension Selections
The Query Builder provides three ways to specify selections for the dimensions
in a query:
Members
The Members tab provides
an ad hoc method of adding and removing specified dimension members
to affect the current selection.
Conditions
The Query
Builder supports all of the conditional functions that business
intelligence users are accustomed to, such as ranking and exception
tools. Other selection tools include level selections, selection
by attribute, hierarchical selections, and time-based selections.
For example, a default query definition
can be “Show the Top 5 Products based on Sales”. An
end-user can then modify the word “Top” to “Bottom”
in the definition to show the bottom 5 products based on sales.
Saved Selections
The Query
Builder also makes it easy to reuse selections by allowing users
to save and retrieve defined selections. Users often want to use
a popular selection or query (such as “Top 5 Products”
or "Last 3 Months") in multiple reports or presentations.
The Query Builder also has two tabs that reveal the contents of the current
dimension selection before you execute the query:
Steps
The Steps tab organizes
the current selection based on the criteria that comprises the query.
The criteria can be a combination of any of the following: members,
conditions, and saved selection steps.
Members
The Members tab shows the result
of the current set of selection steps.
For example, the Members tab shown here displays a preview of
the dimension members that would be returned from the "Top
5 Products" condition shown previously.
Data Model
For information on the data model used in the BI Beans OBE
lessons, please see the Data Model
page.
High Level Objectives
The general business requirements of the BI Beans OBE lessons
are described in the Business Requirements
page.
Perform the following steps to change the layout of the dimensions
in the Sales Analysis crosstab:
1.
If the crosstab that you defined in the previous lesson is not already
open, in the Applications-Navigator pane, right-click Sales Analysis
Crosstab and click Open.
2.
On
the toolbar in the upper left corner of the crosstab, click the Query
Editor tool.
The Query Editor tool launches the re-entrant Query Wizard.
3.
In the Query Wizard, select the Layout tab.
Notes:
The Layout tab that is displayed in the Query Wizard is also represented
as a step in the Presentation Wizard. In the previous lesson, you created
a crosstab and graph without performing this step.
Therefore, you can specify the layout of a presentation when you first
create the object, and modify it later using the Query Wizard.
4.
To rearrange dimensions, select a dimension,
and use a drag-and-drop operation to move the dimension to the desired
position.
Rearrange the dimensions so that the layout appears as follows:
Page Items:
Promotion, Channel,
Time
Row dimension (on the left):
Geography
Column dimensions:
Measure below
Product
Click OK to to apply the changes and exit the wizard.
5.
Observe that the layout of the crosstab has
changed.
From the File menu, select Save All to save your work.
Select Dimension Members
As stated in the Overview section, you can modify
dimension selections in an ad-hoc fashion by selecting from a list of available
dimension members.
Here, you will use this method to modify the
selections for the data that appears in your crosstab in order to show the Sales
Revenue for the sub regions and countries of Americas, at the
Product Division level, for All Channels of
distribution.
To change the Geography selection to the sub-regions and
countries within Americas, perform the following steps:
1.
Click
the Query Editor tool on the toolbar of the
Sales Analysis Crosstab.
The Query Wizard is opened.
2.
In the Query Wizard, select the Dimensions
tab.
Notes
The Dimensions tab that is displayed in the Query Wizard is also represented
as steps in the Presentation Wizard -- one step for each dimension in
the query. In the previous lesson, you created a crosstab and graph without
performing these steps. Rather, a default set of members for each dimension
was included in the query.
Therefore, you can specify dimension selections for a presentation when
you first create that object, or later using the Query Editor.
3.
In the Choose box, select Geography.
Click the Remove All Items shuttle
button ('<<') to remove the current selection.
4.
In the Available list, select the
Members tab and click the drill symbol ('+'),
which is located to the left of World total, then again
click the drill symbol ('+') to expand Americas.
The list of sub-regions appears.
Click the drill symbol ('+') located to the left of
Northern America and Southern America
to display the countries associated with each sub-region.
Using the mouse and the 'Shift' key, multi-select the following Geography
dimension members: 'Americas', 'Northern America',
'Canada', 'United States of America',
'Southern America', 'Argentina' and
'Brazil'.
5.
Click the Add Selected Items shuttle button
('>') to move Americas group to the Selected List.
Click Apply to apply the changes. Do not exit the Query
Wizard.
To modify the Channel selection to include all channels of
distribution, perform the following steps:
1.
Ensure that the Dimensions
tab is selected in the Query Wizard. In the Choose box,
select Channel..
Click the Remove All Items shuttle button ('<<') to remove
the current selection.
2.
In the Available list, click the drill symbol
('+'), which is located to the left of Channel
total. Then, click on the Add all Items shuttle
button ('>>') to move all of the channels to the
Selected list.
Click Apply to apply the changes. Do not exit the Query
Wizard.
In this topic, you will create two saved selections
for the Geography dimension that will be used later in these lessons.
To create the saved selections for the Geography dimension,
follow these steps:
1.
Click
the Query Editor tool on the toolbar of
the Sales Analysis Crosstab.
2.
In the Query Wizard, click the Dimensions
tab and select Geography in the Choose box. The current
selection for Geography dimension is displayed in the Selected region.
3.
Click the Save button at the bottom of
the Selected region. The Save Selection As dialog box is displayed.
4.
In the Save Selection As dialog box, enter America
Areas as the name.
Accept the default Save steps option and click OK
to return to the Query Wizard.
Notes:
Saved steps: The "Save steps" option creates
an XML object that records the steps that result in the dimension selection.
This object can be thought of as a dynamic saved selection. For example,
if you create a saved selection that contains a condition, you should
choose this option. Each time the query is run, this kind of saved selection
is re-evaluated using the current state of the dimension.
Saved members: The "Save members" option creates
an XML object that records a static list of values that are specified
for the dimension when the saved selection is created. Therefore, this
object contains a list of dimension members that never change.
5.
Now, still in the Query Wizard's Dimension tab, create
a second regional selection for European Regional members by following
these steps:
A.
Click Remove All
Items ("<<") to clear the
Selected list.
B.
In the Available list, select the
Members tab and click the drill symbol ('+'),
which is located to the left of World total, then
again click the drill symbol ('+') to expand Europe.
The list of sub-regions appears.
C.
Multi-select the following Geography
dimension members: 'Europe', 'Eastern Europe',
and 'Western Europe'. Then, click Add Selectedl
Items (">").
D.
In the Steps tab of the Selected
region, drill on 'Eastern Europe', and 'Western
Europe' to display their countries:
E.
Click the Save button
at the bottom of the Selected region.
F.
In the Save Selection As dialog box,
enter European Areas as the name and click OK
to return to the Query Wizard.
The new saved selections are ready for reuse.
6.
Use America Areas as the default selection for the crosstab.
To do this:
A.
Click Remove All
Items ("<<") to clear the
Selected list.
B.
In the Available region, choose the
Saved Selections tab to view the new saved selections.
C.
Choose Ameria Areas
and click Add Selectedl Items (">").
To preview the members that will be returned by a saved selection, click
the Members tab in the Selected region.
7.
Click OK to close the Query Wizard.
8.
From the File menu, select Save All to
save your work. Then, close the crosstab.
Add a Calculation to the Graph
A calculation can be added to a graph or a crosstab
using the Query Editor. Existing calculations may also be included in a crosstab
or a graph in the Presentation Wizard
In the following steps, you will add the Sales Revenue
Growth % calculation (which you created in the previous lesson) to
the graph. In addition, you will change the layout of the dimensions that are
displayed in the Sales Analysis graph so that the bars of the graph represent
Products, and they are grouped by Channel.
1.
If the graph that you defined in the previous
lesson is not already open, right-click Sales Analysis Graph
and click Open.
Then, in the toolbar of the Sales Analysis Graph, click
the Query Editor tool.
The Query Wizard is displayed.
2.
In the Items tab of the Query Wizard, select
the Sales Revenue Growth % calculation in the Available
list. Then, add it to the Selected list, as shown here.
3.
To change the dimensional orientation of the graph, select
the Layout tab.
4.
To rearrange dimensions, select a dimension, and use a
drag-and-drop operation to move the dimension to the desired position.
Rearrange the dimensions so that the layout appears as follows:
Page Items:
Geography, Promotion,
Time, Measure
Bars:
Product
Groups:
Channel
Click OK to to apply the changes and exit the wizard.
The modified layout of the graph is shown here:
4.
You can now select the calculated measure from the Measure
tile.
Experiment with the graph layout and data selections. When finished,
re-establish the layout shown in step 3, and click Save All
to save your work.
Use Saved Selections
and Dimension Conditions
As stated in the Overview section, you can modify
the data selections in a presentation by using Saved Selections and creating
Dimension Conditions.
For the Sales Analysis graph, you will modify
the Geography dimension by using a Saved Selection that you created previously.
In addition, you will create a ranking condition that returns the top 5 products
in the Photo division, based on Sales Revenue for each Geography, Channel, and
Time dimension member in the query. Finally, you will select Channel dimension
members using the Members tab, as you did with the crosstab.
To create the Product ranking condition, perform the following
steps:
1.
Ensure that the Dimensions
tab is selected in the Query Wizard. In the Choose box, select Product.
Click the Remove All Items shuttle button ('<<')
to remove the current selection.
2.
In the Members tab of the Available list,
expand Product total by clicking on the drill symbol
('+'). Select Photo and click the Add
Selected Items shuttle button ('>') to move Photo
to the Selected list.
3.
In the Available list, select the Conditions
tab.
4.
In the Conditions list, there are templates that can be
customized to match the condition that you want to specify.
Expand the Top/Bottom folder and select the template
Top 10 based on Sales Revenue. Click the Add
Selected Items shuttle button ('>') to move the condition
to the Selected list.
5.
In the Selected list, select the Top 10 condition. Click
the first hypertext link: ‘Add’. Select ‘Then
Keep’ from the list.
6.
In the condition, click the '10' hypertext
link and change it to '5' so that the condition reads
as follows:
Keep Product: Top 5 based on Sales Revenue
This condition will Keep the top 5 products in the Photo division.
This means the Photo division member itself will not be part of the member
selection that is returned by this condition.
7.
Now, you will qualify this ranking condition so that it
will be re-evaluated when any new Geography or Time member is selected
from the Page dimension tiles in the graph.
First, click the Edit Step tool (pencil icon) to display
the Edit Step box.
8.
An OLAP ranking condition returns the appropriate dimension
members for the condition by using a specific combination of values for
the remaining,or qualifying, dimensions in the query. The values
for these dimensions are displayed in the For box.
For example, as shown in the Edit Step dialog above, our ranking condition
would return the top 5 products for Channel total, Americas,
Promotion total and 1998.
In an OLAP ranking condition, you can use the Qualify button
to select a different dimension member for each of the qualifying dimensions
in the query.
Even more, you can specify that the ranking condition should be re-executed
each time you select a new member from a qualifying dimension that is
in the Page Items region of the crosstab. To do this, choose
the Selected <Dimension> page item option in the
dimension value drop-down list.
Click on the Qualify button.
The Qualify Measure-Sales Revenue dialog box opens.
9.
In the column ‘Include Sales Revenue In’,
select Each Geography for the Geography
dimension.
10.
In the column ‘Include Sales Revenue In’,
select Each Time for the Time dimension.
Click OK.
The Edit Step dialog box now shows that the "Top 5 Products"
condition will be re-evaluated for all Geography and Time dimension members,
but will only be evaluated for the Channel total and Promotion total dimension
members.
Click OK to complete the condition and close the Edit
Step dialog box.
11.
Click the Sort button below the Selected
list in the Query wizard to display the Sort Members
dialog box.
Choose the Sort members by option and then click Add.
The Edit Sort Criterion dialog box opens.
12.
In the Edit Sort Criterion dialog, select Product
name from the Sort By options list, then click
OK.
The Sort Members list box will show the condition as
follows:
Click OK to close the Sort Members
dialog box.
13.
In the Query Wizard, click Apply to apply
the changes. Do not exit the Query Wizard.
To test the ranking condition perform the following
steps:
1.
Click the Time dimension and select 1999.
The graph updates with a new set of top 5 products.
2.
Click the Geography dimension and select
Canada. The graph updates with a new set of top 5 products.
Click the Geography dimension and select Argentina.
Once again, the graph updates with a new set of top 5 products. Notice
that there is only one channel of distribution in Agentina - Direct.
Once again, click the Geography dimension and select Americas.
The graph updates with a new set of top 5 products.
From the Measure tile, select Sales Revenue Growth %.
Notice that the data changes, but the product ranking does not, since
the ranking condition is based on Sales Revenue. Re-select Sales
Revenue from the Measure tile.
From the File menu, select Save All to save your work.
Choose File > Close to close the
graph.
Summary
In this lesson you learned how to use the Query Builder to:
Select measures for a query.
Modify the layout of presentations
Specify dimension selections that define
the data that is displayed in crosstabs and graphs.
In the next lesson, you will learn how to create highly-formatted
crosstabs and graphs using the Presentation Editor.
Bookmark
