The business analyst's guide to Oracle Hyperion Interactive Reporting 11 : quickly master this powerful business intelligence product

211 Pages·2010·5.29 MB·English

by  Edward J Cody

The Business Analyst's Guide
to Oracle Hyperion Interactive
Reporting 11
Quickly master this powerful business
intelligence product
Edward J. Cody
professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
Download from Wow! eBook <www.wowebook.com>
The Business Analyst's Guide to Oracle Hyperion
Interactive Reporting 11
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: September 2010
Production Reference: 1010910
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849680-36-3
www.packtpub.com
Cover Image by Tina Negus ([email protected])
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Preface 1
Chapter 1: Oracle Hyperion Interactive Reporting 7
The Interactive Reporting document: The BQY 8
The EPM Workspace 8
Navigating the EPM Workspace 8
Installing the Interactive Reporting Web Client 11
Opening documents in the Workspace 12
Opening documents from the local machine 13
The Web Client interface 14
The Section Catalogue 15
Menus 15
Toolbars 16
Section Title Bar 17
Status Bar 17
Saving documents 17
Saving documents to the Workspace 18
Saving documents to the local machine 18
Save options 19
Password protecting the document 20
Password protecting the design mode 20
Offline mode 21
Importing BQY documents into the EPM Workspace 21
Editing Interactive Reporting file properties 27
Summary 28
Chapter 2: The Query 29
The Query section layout 30
Sections window 30
Section Title Bar 30
Status Bar 31
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Working with pre-built data models 31
Building a query 32
Request items 32
Removing request items 35
Filters 35
Filter interface 35
Show Values 38
Custom Values 39
Manual entry 40
Transfer values 41
Loading filters from a file (importing) 42
Modifying the AND and OR operators 43
Variable filters 47
Filtering on request items 48
Removing filters 49
Sorting 49
Query processing 50
Query options 51
Identifying metadata (table and column remarks) 52
Summary 53
Chapter 3: The Data Model 55
Creating a data model 56
Duplicating a query 57
Removing a query 57
Building a data model 57
The Elements window 58
Adding tables to the data model 58
Joins 59
The Topic View 61
Working with meta topics 61
Creating meta topics 62
Promoting to a meta topic 65
Editing meta topics 65
Changing the data model topic view 66
Updating the data model 67
Changing the table definition 68
Combining multiple queries in one model 69
Local results 69
Limitations of local results 70
Derivable queries 70
Limitations of derivable queries 71
[ ii ]
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Importing external data 71
Importing a file 71
Refreshing external data 72
Importing tips 72
Fixing additional columns 72
Fixing datatype and join issues 72
Importing custom SQL files 73
Refreshing imported SQL 73
Summary 74
Chapter 4: Advanced Querying and Filtering 75
Structured Query Language (SQL) 75
How SQL is translated from Request, Filter, and Sort 76
Advanced request items 76
Modifying column definitions 77
Concatenation 78
String manipulation 79
Value replacement 79
Date/time functions 80
Nesting functions 80
Datatypes 81
Advanced filtering 81
Custom SQL filter option 82
SubQuery option 82
Custom SQL SubQuery 84
Custom SQL and custom join conditions 84
Custom join conditions 85
Combining queries 86
The query log and exporting SQL 90
Summary 90
Chapter 5: Results and Tables 91
Results section 91
The Tables sections 92
Creating a table section 93
Duplicating a table 94
Removing Table sections 94
Removing Results sections 94
Adding and modifying content 95
Data layout 95
Auto-populating table columns 96
Moving columns 96
Hiding/showing columns 96
[ iii ]
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Identifying computed items 97
Removing columns 97
Filters 99
Creating filters 99
Filters on multiple sections 100
Status Bar 101
Filter performance 101
Sorting 101
Sorting performance 102
Creating totals 102
Grand totals 103
Break totals 103
Impact when adding filters 104
Removing totals 104
Grouping columns 104
Computed items 105
Computed item examples 106
Concatenation 106
String manipulation 106
Conditional functions 106
Date/time functions 107
Numeric functions 107
Date Groups 107
Summary 109
Chapter 6: Pivots 111
Creating Pivot sections 112
Pivot this Chart 112
Pivots created using results or tables 112
Removing Pivot sections 113
Adding content 113
Row and column labels 114
Facts 114
Data functions 114
Removing content 115
Arranging label content 115
Managing label and data headings 116
Data labels 116
Corner labels 117
Sorting 117
Manual sorting 118
Analyzing content 118
Drill 119
[ iv ]
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Drillup 120
Focus and hide 120
Focus 120
Hide 120
Totals and computed items 122
Totals 122
Data functions for totals 124
Removing totals 125
Renaming items in row or column label 125
Grouping 126
Computed items 126
True totals and surface values 127
Formatting and conditional formatting 128
Conditional formatting 128
Exporting and printing 129
Summary 130
Chapter 7: Charts 131
Creating Chart sections 132
Charts created using results or tables 132
Chart this pivot 132
Removing Chart sections 132
Adding content 133
Removing content 134
Chart types 134
Vertical and horizontal bar charts 134
Vertical and horizontal stack bar charts 138
Vertical cluster bar chart 140
Pie charts 140
Area and stacked area charts 142
Line charts 143
Ribbon charts 144
Bar-Line charts 146
Scatter / Bubble charts 147
Legend 148
Data functions 149
Analyzing content 149
Drill 150
Drillup 150
Focus 150
Hide 150
Adding reference and trend lines 152
[ v ]
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Sorting Chart labels 152
Chart formatting and properties 152
Chart properties 153
Inserting text labels 153
Grouping and computed items 153
Grouping 154
Computed items 154
Exporting charts 154
Summary 155
Chapter 8: Reports 157
Creating Report sections 158
An overview of the Report layout 158
Headers and Footers 158
Report setup 159
Design guides 160
Adding content 160
Elements window 160
Query elements 161
Graphics 161
Fields 161
Resources 161
Adding pivots and charts 161
Adding a table 162
Adding graphics, fields, and resources 162
Removing content 163
Resource Manager 163
Building Report tables 164
Table window 164
Table dimensions and facts 164
Dimension and fact properties 165
Break totals 166
Sorting results 166
Removing elements 167
Report groups 167
Adding report group content 167
Sorting groups 170
Focusing and hiding report group content 170
Creating and modifying expressions 171
Table expressions 171
Predefined field expressions 172
Report groups 172
Custom fields 172
[ vi ]
Download from Wow! eBook <www.wowebook.com>
Table of Contents
Formatting expressions 173
Combining data from multiple queries 173
Adding computed items and modifying items 175
Exporting and printing 176
Summary 177
Chapter 9: Dashboards 179
Creating Dashboard sections 179
Understanding the Dashboard layout 180
Editing the dashboard 180
Dashboard objects 181
Query objects 181
Reports 181
Graphics 181
Controls 182
Gauges 182
Live Charts 182
Resources 182
Building a dashboard 182
Adding sections 184
Adding graphics and resources 184
Adding gauges, live charts, and controls 185
Removing content 185
Adding content example 185
Configuring gauges 190
Gauge properties and color ranges 191
Configuring live charts 192
Live chart properties 195
Working with controls 195
Control types 196
Script Editor 196
Building an interactive dashboard 198
Populating Drop Downs with a script 199
Setting a filter from a Drop Down control 200
Exporting and printing 202
Summary 202
Index 203
[ vii ]
Download from Wow! eBook <www.wowebook.com>
Oracle Hyperion Interactive
Reporting
Welcome to Oracle Hyperion Interactive Reporting! Interactive Reporting is an
extremely robust and powerful business intelligence tool providing ad-hoc querying,
data analysis, dashboards, and other reporting capabilities. Business analysts
access the software through the EPM Workspace, a central location for viewing and
managing content across all of the Hyperion products. The product is extremely
flexible and provides analysts with the ability to quickly analyze data and produce
deliverables. The software provides a consistent platform for managing content,
where documents can be customized, saved, and shared across an organization.
The focus of this chapter is to provide an overview of the Interactive Reporting Web
Client and EPM Workspace, providing the business analyst with the skills necessary
to work with the tool on a daily basis.
The content in this chapter covers:
• Navigating the EPM Workspace
• Installing the Web Client
• An Overview of the Web Client interface
• Methods for opening and saving documents
• Methods for importing and provisioning files
• Steps to edit file properties
Download from Wow! eBook <www.wowebook.com>