Performance 2009 Template - BrightStar Partners, Inc
Download
Report
Transcript Performance 2009 Template - BrightStar Partners, Inc
Top Ten Tips and Techniques
From the Field
Mark Karas
Senior Consultant
BrightStar Partners, Inc.
© 2009 IBM Corporation
Tenured
Professional
Services
ImplementationBased Products
Worldwide CPM
Support
Communities
Depth and Breadth / Thought Leadership
Proven Track Record
BrightStar Partners has a proven track record of success
across numerous industries and customers of all shapes
and sizes. Several of our marquis clients include:
Professional Services
Tenured staff of consultants
Best Practices / Trademarked Methodology
Focused on delivering solutions; not just reports
BSP Software arm provides unmatched technical
muscle
Technical assessments
Full life cycle implementation projects
Custom SDK and web app development
BSP Software
Implementation-based Software™ focused on delivering greater efficiencies and
tighter controls for your IBM Cognos environments.
MetaManager – Allows administrators and developers to streamline and
automate the administration and maintenance of IBM Cognos environments.
Integrated Version Control – IVC is an integrated, real-time, user-driven
method for capturing, maintaining, and managing previous versions of IBM
Cognos content.
CPM Explorer – Provides access to IBM Cognos content for end-users,
developers, and administrators through a file tree metaphor in either Windows
File Explorer or directly in Microsoft Outlook.
Many additional product offerings can be found on our website at
www.bspsoftware.com.
COGNOiSe.com
Worldwide Cognos support community
9,500 members and growing
25,000+ posts
Forums for everything Cognos
Cognos 8 Platform
Legacy BI
Planning & Consolidation
Scorecarding and Dashboards
TM1
CPM
Top Ten Tips and Techniques from the Field
This technical presentation highlights numerous
challenges that BSP consultants and software
developers have overcome during the course of
delivering superior solutions to our clients
1. Security-driven columnar data display
2.
3.
4.
5.
6.
7.
8.
9.
10.
Dynamically nesting data in a report
Express vs. Professional authoring
Running Cognos reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
1. Security-driven columnar data display
This technique is a simple yet functional way
to restrict sensitive data so only a certain
group of users will “See” the sensitive data’s
values.
Requires:
– Creating a group in the Cognos namespace
– Adding the appropriate members to the new
group
– Creating a derived query item to use in place
of the standard query item
1. Security-driven columnar data display, cont’d
Create a Security Group in Cognos namespace
(See_Cost in this example)
– This security group should have all members that are
allowed to see the secured data
1. Security-driven columnar data display, cont’d
Modify the definition of the column to be
secured. The definition should refer to the
security group.
Publish the package
1. Security-driven columnar data display, cont’d
Create a report using the modified column in
the layout (Unit cost in this example)
1. Security-driven columnar data display, cont’d
Run the report as a member of security group
Run the report as a non-member of security group
1. Security-driven columnar data display
2. Dynamically nesting data in a report
3.
4.
5.
6.
7.
8.
9.
10.
Express vs. Professional authoring
Running Cognos reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
2. Dynamically nesting data in a report
Advanced Report Studio
Nice usage of some relatively simple JavaScript
that can transform a grouped list into a decent drilldown-like, nested style replacement for true OLAP
analysis, when you are working with relationally
modeled data.
Some simple JavaScript, a couple of new image
files and a little creativity is all that is required!!
2. Dynamically nesting data in a report, cont’d
In Report Studio, create a new list report with
hierarchical columns (Product Type > Product Line >
Product Name)
Create List header and footer, then group the all
columns but bottom level of hierarchy
Cut list details for all hierarchy levels but the bottom
level
Add HTML items in list page header/ footer, and
before and after each level of the hierarchy
16
2. Dynamically nesting data in a report, cont’d
JavaScript needed for HTML items can be provided… find
someone wearing our logo after this presentation if you
are interested, and we will forward the code to you
This code points to image files at ... <install location>
\webcontent\pat\images. Make sure that the proper
images are in this location, or repoint the JavaScript code
Run the report… When you click on the “+” or “-” images,
they will expand and collapse the next level of detail with
each click
Essentially, you have now created drill-like capabilities that
will work with non-dimensional data.
17
1. Security-driven columnar data display
2. Dynamically nesting data in a report
3. Express vs. Professional authoring
4.
5.
6.
7.
8.
9.
10.
Running Cognos reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
3. Express vs. Professional authoring
Facts about, and prerequisites for the Express Authoring mode:
Main purpose: Provide non-Cognos report authors the ability to
create Financial reports
Works best with a package that contains a multi-dimensional data
source (DMR, Cube)
Reports created in either authoring mode can be edited using the
other mode
The mode that is set in Report Studio at the time of authoring a
report is not tied to the report specification or vice-versa
Like Query and Analysis Studios, the default Express authoring
environment works with live data
3. Express vs. Professional authoring, cont’d
Express Authoring facts, cont’d
The Express Authoring mode works only with Crosstabs. Lists and
Charts are not available in the tool set, nor are there any prompting
capabilities.
By default, the reports authored in Express mode are not Drill-Down
enabled, but drilling up and down is available
Levels are not part of the toolset, strictly entire hierarchies and the
members, a la Analysis Studio
To get an understanding of the differences in the two modes, I used the
GO Finance cube as a data source to create a Balance Sheet report in
both authoring modes. My findings are as follows:
3. Express vs. Professional authoring, cont’d
Positives/Neutrals
Negatives
Formatting is similar from the toolbar
aspect. There is no properties
window like in Professional mode, so
the toolbar is the place to change font,
justification, add borders, indenting
(padding), etc.
Changes in the underlying
hierarchies will probably break these
member-only reports.
Creating a Balance Sheet report in
Express mode was indeed faster than
using the Professional mode
From the Financial Analyst’s
viewpoint, he or she will be working
with the accounts and account rollups… things they are already very
familiar with, as opposed to levels of a
hierarchy which is often confusing to
them.
Additions to the Chart of Accounts
will not be picked up automatically
as they would if levels were used.
Lack of flexibility in object usage and
setting.
No way to turn off the data preview
3. Express vs. Professional authoring, cont’d
In Summary:
I found Express Mode to be fairly easy to use for
creating a quick financial report. It is probably not,
however, going to replace a truly professionally
authored and formatted report when it comes to the
total end user/consumer experience, given its “Ad
Hoc” feel.
1. Security-driven columnar data display
2. Dynamically nesting data in a report
3. Express vs. Professional authoring
4. Running Cognos reports from URLs
5.
6.
7.
8.
9.
10.
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
4. Running Cognos Reports using URLs
Cognos offers the ability to run reports through a URL
You can pass along information such as:
• Format
• Runtime Parameters
•Items per page
•Locale
•Many more…
•User Interface Options
•Hide the toolbars or buttons
•‘Return’ button behavior
•Action
•Run report or view saved output
•Prompt values
4. Running Cognos Reports using URLs, cont’d
Why would I want to run a report using a URL?
•Integration with other web applications
•Embed the reports in an IFrame to make it part of the page
•Replace data driven web pages with Cognos reports
•Cut down on development time
•No need for a developer
•Create interactive reports
•Create expand/collapse sections within a report to display details
using a second report
•Only run the detail data as needed, cut down on query time!
•Use more advanced AJAX techniques to create Fly-outs
•Special output format ‘HTMLFragment’
•Impress your friends
4. Running Cognos Reports using URLs, cont’d
Some Simple HTML
in any web page…
…embeds a report within it.
4. Running Cognos Reports using URLs, cont’d
A little JavaScript and
a few HTML items…
…and you get a custom
master / detail report
4. Running Cognos Reports using URLs, cont’d
URL Format
Starts with the path to your Cognos installation
http://servername/cognos8/cgi-bin/cognos.cgi ...or...
http://server:9300/p2pd/servlet/dispatch
Required values
•b_action=cognosViewer
•ui.action=run ( or view)
•ui.object=<search path goes here>
Optional values
•p_<prompt name>=<value>
•ui.outputFormat=<format>
•cv.header=false
•cv.toolbar=false
4. Running Cognos Reports using URL, cont’d
Where can I get more information?
Chapter 17 of the Cognos SDK Developer Guide is
dedicated entirely to performing tasks with URLs.
Cognoise.com
1.
2.
3.
4.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
5. Preserving Top-N functionality in drill-downs
6.
7.
8.
9.
10.
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
5. Preserving Top-N functionality in Drill-Downs
Goal: Have a drill-down enabled report showing only the Top 2
values rather than showing all values, at any level of the hierarchy
Problem: While the simple usage of an MDX function, TopCount()
will provide the desired effect on the report’s initial output, when the
user drills-down on one of the Top 2 values, however, the TopCount
functionality is lost.
Solution:
– Utilize the Children() MDX function
– Incorporate Member Sets
– Set Advanced Drill Behavior
5. Preserving Top-N functionality in Drill-Downs, cont’d
Example:
– Uses DMR package, GO Sales
– Query:
• Series:
[Sales].[Time].[Time].[Year]
• Measure:
[Sales].[Sales].[Revenue]
• X-Axis:
TopCount([Sales].[Product].[Prod
uct].[Product line],2,[Measure])
5. Preserving Top-N functionality in Drill-Downs, cont’d
We want to see the
breakdown of Revenue by
Product Types under
Camping Equipment so
we drill-down on Camping
Equipment.
Note how the Top 2 is
lost…we see 5 product
types. Our desire is to still
only see the top 2, Tents
and Packs in this case
5. Preserving Top-N functionality in Drill-Downs, cont’d
Changes:
– Query updated:
• All Products (new)
[Sales].[Product].[Product].[Product(All)]->[all]… is a MUN (member
unique name) where [Sales].[Product].[Product].[Product(All)]
represents the Top or “All” level of the Product Hierarchy and ->[all]
represents the member of this level
• Product Lines (new):
children([All Products])
• X-Axis (changed):
TopCount([Product Lines],2,[Measure])
• Query Property Define Member Sets set to Yes.
5. Preserving Top-N functionality in Drill-Downs, cont’d
Changes cont’d:
– Define Member sets:
•
From the Insertable Objects window in the upper left, drag the
Product Lines query item into the member sets window (on the
right, currently empty). Rename this item Product Lines MS
•
From the Insertable Objects window in the upper left, drag the XAxis query item into the member sets window and onto the
existing Product Lines MS member set. Rename this to X-Axis
MS
5. Preserving Top-N functionality in Drill-Downs, cont’d
Changes cont’d:
• Modify the Report’s Drill
Behavior
– From the Data menu, select
Drill Behavior…
– Click the Advanced tab
– Click the Product Lines data
item
– Set both the Drill-Up and
Down behavior to “Replace
Expression”
– Click the X-Axis data item
– Set both the Drill-Up and
Down behaviors to “Preserve”
1.
2.
3.
4.
5.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
Preserving Top-N functionality in drill-downs
6. Effective dashboard navigation
7.
8.
9.
10.
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
Framework Model Namespaces and Folders
6. Effective Dashboard Navigation
Allows easy intuitive navigation between summary and detail
views of data using Table of Contents panel
Table of Content panel contains links to detail reports which is
displayed on each report
Simple and quick setup in Report Studio
Easy replicable, flexible, and efficient design
Setup uses the following major components:
–
–
–
–
–
Cognos Connection Portlet Tab
Layout component reference
Drill-through
Summary report
Detail reports
1.
2.
3.
4.
5.
6.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
7. Performance Tuning – Native vs. Cognos SQL
8. Automated Promotion of PowerCubes
9. Dynamically showing/hiding in-report prompts
10. Framework Model Namespaces and Folders
7. Performance Tuning – Native vs. Cognos SQL
You can optimize your report performance by analyzing the Native
and Cognos SQL produced by a slow performing query.
Cognos SQL is SQL which is required to be processed in order for
Cognos to produce the report.
The Native SQL is the query which is passed to database for
execution. Native SQL is subset of Cognos SQL.
7. Performance Tuning – Native vs. Cognos SQL, cont’d
Native SQL
select "RETURNED_ITEM"."RETURN_REASON_CODE"
from "GOSL"."dbo"."RETURNED_ITEM" "RETURNED_ITEM"
order by 1 asc
select "RETAILER"."RETAILER_CODE"
from "GORT"."dbo"."RETAILER" "RETAILER"
order by 1 asc
Cognos SQL
select
1 as C_____CubeDetailsItem,
XSUM(RETAILER.RETAILER_CODE ) as RETAILER_CODE,
XSUM(RETURNED_ITEM.RETURN_REASON_CODE ) as RETURN_REASON_CODE
from
GOSL.GOSL.dbo.RETURNED_ITEM RETURNED_ITEM
left outer join
GORT.GORT.dbo.RETAILER RETAILER
on (RETAILER.RETAILER_CODE = RETURNED_ITEM.RETURN_REASON_CODE)
group by 1
7. Performance Tuning – Native vs. Cognos SQL, cont’d
The Native SQL consists of two separate select
statements. You may notice that the
qualifications are different. This indicates that
each of the two columns come from a table in a
different schema.
The Cognos SQL shows that the query requires
a left outer join relationship between the two
tables. However, since this relationship is not
included in the Native SQL, it is being
processed locally. Depending on the size of the
tables involved, this may impact performance
Actual source of the problem is the tables were
imported as two different data sources.
7. Performance Tuning – Native vs. Cognos SQL, cont’d
The model will need to be updated to include
both tables under the same data source
This will change the Native SQL to
select distinct "RETAILER"."RETAILER_CODE" "RETAILER_CODE",
"RETURNED_ITEM"."RETURN_CODE" "RETURN_CODE"
from "GORT"."RETAILER" "RETAILER" LEFT OUTER JOIN
"GOSL"."RETURNED_ITEM" "RETURNED_ITEM" on
"RETAILER"."RETAILER_CODE"="RETURNED_ITEM"."RETURN_REASON_CODE"
1.
2.
3.
4.
5.
6.
7.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
8. Automated Promotion of PowerCubes
9. Dynamically showing/hiding in-report prompts
10. Framework Model Namespaces and Folders
8. Automated Promotion of PowerCubes
IBM Cognos 8 creates a file lock when a user accesses the
cube through any studio of IBM Cognos. Even after user
closes the cube, IBM Cognos keeps those locks for a specific
time.
You will get an error if you try to build cube while the file is
locked.
Steps involved in building cubes in Cognos 8 - disable cube,
build the cube, and enable the cube.
PCConn – PCConn is Cognos utility which accepts Cognos
commands in batch mode. It accepts file as input. It is under
c8\webapps\utilities directory. This will be used to disable and
enable the Cube
CogTr – Cognos Transformer command line utility. It is under
c8\bin directory. This is used to build the Cube
8. Automated Promotion of PowerCubes, cont’d
Create one file as DisableCube.txt under
D:\Script directory
connect
<Domain Name> <CognosLogon> <Cognos Password>
Disable <Data Source Name>
Exit
Create another as EnableCube.txt under
D:\Script directory
connect
<Domain Name> <CognosLogon> <Cognos Password>
Enable <Data Source Name>
Exit
8. Automated Promotion of PowerCubes, cont’d
Create batch file CubeBuild.bat under the
D:\Script directory
cd “C:\Program Files\cognos\c8\webapps\utilities\PCConn"
cmd /c PCConn -f "D:\Script\DisableCube.txt"
ping localhost -n 300 > nul
cd "D:\Program Files\cognos\c8\bin"
cmd /c cogtr -n -p"D:\Cognos OLAP Models\PNL Reporting.pyj"
cd "D:\Program Files\cognos\c8\webapps\utilities\PCConn"
cmd /c PCConn -f "D:\Script\EnableCube.txt"
1.
2.
3.
4.
5.
6.
7.
8.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
9. Dynamically showing/hiding in-report prompts
10. Framework Model Namespaces and Folders
9. Dynamically showing/hiding in-report prompts
Create a
Conditional Block
Insert prompts into
conditional block
49
Insert Value Prompt
outside of
conditional block
with Static Choices
(Show / Hide)
9. Dynamically showing/hiding in-report prompts, cont’d
Set Default Choice
(as Hide or Show)
Use render variable
to hide/ show the
block with prompts
based on
parameter
50
Run the report and
test… you now
have a collapsible
prompt area for
your report.
1.
2.
3.
4.
5.
6.
7.
8.
9.
Security-driven columnar data display
Dynamically nesting data in a report
Express vs. Professional authoring
Running reports from URLs
Preserving Top-N functionality in drill-downs
Effective dashboard navigation
Performance Tuning – Native vs. Cognos SQL
Automated Promotion of PowerCubes
Dynamically showing/hiding in-report prompts
10. Framework Model Namespaces and Folders
10. Framework Model Namespaces and Folders
Namespaces ( ) are part of the Object ID.
[Data Source View].[Sales Fact].[Revenue]
Namespaces can be used to create required unique Object
IDs for Query Items.
[Data Source View].[Sales Fact].[Revenue]
[Data Source View].[Invoices].[Revenue]
Namespaces must be unique across the model.
Any changes made to published namespaces will break
reports. ***Shameless Plug Alert!!! ***
Folders can be used to organize content without affecting
Object IDs or authored reports. Changes to folder names
will not affect reports.
Questions?
Thank you and enjoy the rest of the Event!!!
Mark Karas
[email protected]
www.brightstarpartners.com
BSP Toll free: (888) 915-6200
BSP Direct: (847) 439-0308