The Business Analyst’s Guide to Oracle Hyperion Interactive Reporting 11

232 Pages·2010·13.75 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
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])
Credits
Author Editorial Team Leader
Edward J. Cody Mithun Sehgal
Reviewer Project Team Leader
Robert J. Fronzaglia Lata Basantani
Acquisition Editor Project Coordinator
James Lumsden Srimoyee Ghoshal
Development Editor Proofreaders
Dhwani Devater Arani Roy
Conrad Sardinha
Technical Editor
Arun Nadar
Production Coordinator
Kruthika Bangera
Indexer
Monica Ajmera Mehta
Cover Work
Kruthika Bangera
About the Author
Edward J. Cody is accomplished Data Warehouse and Business Intelligence
consultant with over seven years of experience with Hyperion products. Mr. Cody
was a speaker at Oracle OpenWorld 2008 on Essbase, and he has spoken at local
user groups on ETL software. His experience with Interactive Reporting began with
Brio v6 and has continued through the most recent version. He also has extensive
experience with Essbase and Financial Reporting.
Mr. Cody has consulted both private and Government organizations throughout
his career. He has a Bachelor of Science in Systems Engineering from the George
Washington University, School of Engineering and Applied Science, and he has a
Master of Science in the Management of Information Technology from the University
of Virginia, McIntire School of Commerce. His experience includes leading large
Data Warehouse and Oracle Hyperion Business Intelligence projects, and he is
currently leading an assessment of a major data warehouse and business intelligence
initiative. He is well know for his efforts to aid clients in realizing their investment in
business intelligence and a data warehouse through building well designed systems,
leveraging automation, and reducing BI complexity.
Mr. Cody has designed training programs on the software and decided to author
a book on Oracle Hyperion Interactive Reporting after exposure to user feedback
on the complexity of learning the product. He identified that an introductory user
guidebook on using the essential features of the software would be beneficial to all
software users.
Acknowledgement
I would first like to start by thanking all of you who purchased this book. I hope
that you find it a good resource to aid you in your use with the product. I would
also like to thank my parents (Ed & Cathy), brother (David), and all of my family
and friends for working around my schedule as I wrote this book. I really appreciate
your patience and understanding.
I would like to thank all of my managers, peers, and employees that have supported
me throughout my career. I would especially like to thank Dan Miller for providing
me the opportunity to work in this field. His efforts and the efforts of many others
have helped me develop as a professional throughout my career.
I must thank Bob Griesemer, author of the Oracle Warehouse Builder 11g: Getting
Started, for setting me up with the opportunity to work with Packt Publishing. Bob
is a good friend, colleague, and technical expert. His book is a great resource for all
those interested in learning about Data Warehousing. I would also like to thank Bob
Fronzaglia, the book's reviewer, for his contributions to the material. Bob has been a
long time mentor and friend, and I thank him for his efforts.
I must also thank James Lumsden and all of those at Packt Publishing for their efforts
with the book. The team has worked tirelessly with me to produce a quality product
and I thank them for all of their time and efforts.
About the Reviewer
Robert J. Fronzaglia is an Operations Research Analysts in the Office of the
Secretary Director (OSD), Cost Analysis and Program Evaluation. He is responsible
for managing the Department of Defense (DoD) Resources Data Warehouse that
contains DoD strategic resource data for budgeting, programming, acquisition, force
structure, and manpower.
Before joining OSD, Mr. Fronzaglia was a consultant with MPRI and AT&T
Government Solutions where he provided analysis, data warehousing, and
Activity-Based Costing support for various DoD organizations. Prior to that,
he served in the U.S. Army for over 20 years as both a Field Artillery officer
and Comptroller. He served in staff, instructor, and command assignments in
Europe, Southwest Asia, and the U.S. His military decorations include the Legion
of Merit and Bronze Star.
Mr. Fronzaglia holds a Bachelor of Science degree in Industrial Management from
Gannon University, and a Masters in Business Administration from Syracuse
University. Academic honors include being selected for the Beta Gamma Sigma
National Honor Society for Scholastic Achievement and for Who's Who in American
Universities and Colleges.
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
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 ]
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 ]