...

Improving Prompt Performance in IBM Cognos 8 Proven Practice

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Improving Prompt Performance in IBM Cognos 8 Proven Practice
Proven Practice
Improving Prompt Performance in
IBM Cognos 8
Product(s): IBM Cognos 8 - Report Studio
Area of Interest: Report Design
Improving Prompt Performance in IBM Cognos 8
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE ............................................................................................................4
REQUIRED INFORMATION.........................................................................................4
2
REPORT CREATION ....................................................................................... 5
2.1
2.1.1
2.1.2
2.1.3
2.1.4
2.2
2.2.1
2.2.2
2.2.3
2.2.4
2.2.5
2.2.6
2.2.7
2.2.8
CREATING THE REPORT USING THE MEMBER NAME ..........................................................6
Step 1 Member Name. .......................................................................................6
Step 2 Member Name. .......................................................................................6
Step 3 Member Name. .......................................................................................8
Step 4 Member Name. .......................................................................................9
CREATING THE REPORT USING MEMBER UNIQUE NAME (MUN) ...........................................9
Step 1 MUN. .....................................................................................................9
Step 2 MUN. ................................................................................................... 10
Step 3 MUN. ................................................................................................... 11
Step 4 MUN. ................................................................................................... 12
Step 5 MUN. ................................................................................................... 14
Step 6 MUN. ................................................................................................... 15
Step 7 MUN. ................................................................................................... 16
Step 8 MUN .................................................................................................... 16
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
4
1 Introduction
1.1
Purpose
Depending on the datasource it’s possible that the time to display prompts
values may be quite significant. The amount of data in the underlying
datasource used for the prompt and how the data is retrieved will impact the
length of time the prompt takes to be displayed to the user. It’s possible to
use a separate table/column or even datasource to populate and optimise the
prompt information. For example assume you have a Cube datasource with
1000’s of members at a particular level, but your users only need to choose
from a group of 50 members for prompting. You can use a relational
datasource and build a table with only those 50 members and then use a
query to that table to provide the values to filter your cube query. In the
steps that follow you’ll use a relational query to provide values which will be
used to filter the cube datasource.
1.2
Required Information
The OLAP source for this report is a modified version of the “Great Outdoors
Company” sample cube to create unique category codes for each level in a
dimension. The relational datasource is a single Oracle table which contains
the category code and the physical member name (figure 1).
Figure 1.
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
5
In order to accomplish the technique described in this document both the
new relational datasource for the prompt information and the original query
datasource must be published in a single package to IBM Cognos 8 or IBM
Cognos ReportNet (CRN) (figure 2).
Figure 2.
2 Report Creation
There are two techniques which can be used to pass a parameter from a
relational datasource to a cube to filter the report. Both techniques rely on
the ability within Report Studio to create a query which references a
parameter which is driven by another query in the same report. The first
technique uses the actual member name (ie. Camping Equipment) for the
filter where the second technique uses the full Member Unique Name (MUN)
for the the filter. The MUN is the preferred method as it is the only way to
guarantee that the member being filtered is the unique member from the
Hirearchy that is desired. For example if you are filtering on employees you
wouldn’t use the Employee Name as you may have two John Smith’s working
for your company. Instead you would use the employee number to filter the
query and identify the single unique John Smith required.
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
2.1
2.1.1
6
Creating the report using the Member Name
While this method is easier to construct and understand it could result in
invalid data results if, as above, the member names are not unique. If the
member names are not unique the parameterised filter could not return more
data than expected. Below are the steps for creating the report using the
Member Name.
Step 1 Member Name.
Open Report Studio and create a new List report. Drag Product Line – Long
Name from the Cube datasource into the List (figure 3).
Figure 3.
2.1.2
Step 2 Member Name.
Highlight <Product Line – Long Name> in the list report and click the filter
button to create a new detail filter where the data item [Product Line Long
Name] = ?p1? (figure 4). If you run this report you will be prompted for a
product line to filter on and this information will by defeault be retrieved
from the cube datasource (figures 5 & 6.)
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
Figure 4.
Figure 5.
IBM Cognos Proprietary Information
7
Improving Prompt Performance in IBM Cognos 8
8
Figure 6.
2.1.3
Step 3 Member Name.
Create a new Prompt Page and drag a Value Prompt object into the body of
the Prompt page. In the “Prompt Wizard” dialog box that appears choose the
“Use existing parameter” radio button and select the parameter p1 created in
Step 2 (figure 7).
Figure 7.
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
2.1.4
9
Step 4 Member Name.
In the “Populate control” section of the “Prompt wizard” dialog box choose
the “Create new query” check box. “Query2” should be the name given by
default to the new query. For both the “Values to use:” and the “Values to
display:” the PRODUCTLINEDESC column from the relational datasource
should be used (figure 8). Click the Finish button to complete the new
prompt control.
Figure 8
Execute the report. The values in the prompt control and the report output
should be the same as when the report was executed in step 2. The
difference here is that the prompt control is now populated by the new
relational query “Query2” rather than retrieveing the prompt values from the
cube datasource as is the default prompt behaviour.
2.2
2.2.1
Creating the report using Member Unique Name (MUN)
Step 1 MUN.
Open Report Studio and create a new List report. Drag Product Line from the
Cube datasource into the List (figure 9).
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
10
Figure 9.
2.2.2
Step 2 MUN.
Now create a new Prompt Page and drag a Value Prompt object into the body
of the Prompt page. In the “Prompt Wizard” dialog box that appears
choose the “Create a new parameter” radio button and use p1 as the
parameter name (figure 10).
Figure 10.
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
11
Click Next and for the Create Filter dialog window uncheck the “Create a
parameterized filter” checkbox (figure 11) and hit Next.
Figure 11.
2.2.3
Step 3 MUN.
In the “Populate control” section of the “Prompt wizard” dialog box choose
the “Create new query” check box. “Query2” should be the name given by
default to the new query. For the “Values to use:” select the PRODUCTLINE
column, and for “Values to display:” select the PRODUCTLINEDESC column
(figure 12). Click the Finish button to complete the new prompt control.
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
12
Figure 12.
2.2.4
Step 4 MUN.
The next step is to generate the MUN to be used in the query filter. In order
to do this we must create a string which will make up the MUN. In Report
Studio ensure that under Tools-> Options that the “Show members” folder is
enabled. Next expand the cube and the Products Dimension and then
expand the Products level and finally the Product Line level. You should see
a members folder (figure 13). Expanding this folder will show you the
members that exist at this level. Right click on one of the members and
select properties. You will see several properties of this member; we are only
concerned with the Member Unique Name (figure 14).
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
Figure 13.
Figure 14.
IBM Cognos Proprietary Information
13
Improving Prompt Performance in IBM Cognos 8
2.2.5
14
Step 5 MUN.
Highlight the MUN and then use CTRL-C to copy it. Now expand the Query
Explorer window and open Query 1 (or whatever you called the query which
contains the cube level). Highlight the data item [Product Line], and select
the filter icon. Create a new detail filter and paste the MUN in front of the
existing query item definition. Modify the query item to quote the MUN and
remove everything after the 2nd last [ character in the MUN. It should
resemble figure 15 prior to the deletion of characters and figure 16 after.
Figure 15
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
15
Figure 16
2.2.6
Step 6 MUN.
Now concatenate the prompt expression to the string you just inserted and
then concatenate a closing ] character to the expression. The calculation that
represents the MUN should now be complete (figure 17).
Figure 17
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
2.2.7
16
Step 7 MUN.
Now it’s time to complete the filter calculation. To do this we need to convert
the level object [Product Line] into a string representation of it’s MUN. This
can be done by first converting the level into a MUN using the rolevalue
function, roleValue('_memberUniqueName',[Product Line]). Then cast this as
a string using the cast function. The finished product should look like figure
18
Figure 18
2.2.8
Step 8 MUN
To verify that the value used in the prompt is from the relational set up a
report expression calculation in the header to display the ParamValue of the
p1 paramenter we created (figure 19). Now run the report and select a
product line to filter on (figure 20), and notice that the value in the report
header is the PRODUCTLINE value from the relational data source (figure 21).
IBM Cognos Proprietary Information
Improving Prompt Performance in IBM Cognos 8
Figure 19
Figure 20
IBM Cognos Proprietary Information
17
Improving Prompt Performance in IBM Cognos 8
Figure 21
IBM Cognos Proprietary Information
18
Fly UP