351
10
advanCed smart vIew
Robb Salzman
CONTENTS
10.1 Introduction 352
10.1.1 Overview 352
10.2 A Word or Two about the Classic Add-In 353
10.3 Tune Your Reports, Create Ecient Smart View Spreadsheets 353
10.3.1 Hardware: It’s Your Laptop 353
10.3.2 How Smart View Is Architected; What Are All ose URLs? 354
10.3.3 Spreadsheet Eciency Meets Essbase 354
10.3.4 How Many Cells? 354
10.3.5 Dimension Arrangement for Ecient Spreadsheets 354
10.3.6 Attributes, DTS, and Dynamically Calculated Members 355
10.3.7 Use “Navigate Without Data” to Improve Performance during
Development 355
10.3.8 Compression 355
10.4 Excel Automation 356
10.4.1 VBA: Traditional Automation 356
10.4.2 Smart View VBA Functions Refresher and Newer Features 356
10.4.3 Setting and Managing Options 357
10.4.4 Connecting, Logging In, and Refreshing Forms 358
10.4.5 Cascading Reports 362
10.4.6 Linking Smart View Functionality to Common Form Elements 362
10.4.7 Managing Combo Edits 363
10.4.8 Creating a Combo Edits Lookup Table for Valid Sparse Combinations 363
10.4.9 Adding the Combo Boxes 366
10.4.10 Adding the Initialization Code 367
10.4.11 Adding Events to Drive the Combo Boxes 367
10.4.12 Next Steps 369
10.4.13 Error Handling 370
10.5 Aerthoughts 371
352 • Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals
10.1 INTRODUCTION
ere is no question about it, nance and accounting people love Excel
®
. Given Excel’s
usefulness and pervasiveness in the enterprise, there are few who do not use Excel. With
its evolved exibility, power, and potential, Excel has made its way into almost every
modern oce. In the early days of Essbase, Arbor Soware recognized the importance
of Excel and used it as the standard interface to their new OLAP (online analytical
processing) database. When Arbor Soware subsequently merged with Hyperion to
form Hyperion Solutions, the technological direction was toward the Web interfaces
driven by Java on the server. However, many users found the spreadsheet interface
much better for their needs and the response from Hyperion Solutions was the Smart
View add-in for Excel. Smart View added new capabilities so it could talk to other data
sources. Aer the Oracle acquisition of Hyperion Solutions, Smart View has continued
to improve, which validates Excel as the favorite interface for business users.
is chapter discusses the programmatic and reporting side of this capability, start-
ing with considerations for ecient Essbase reporting to get that extra margin of
performance from interactive use and then diving deep into the programming power of
Excel. Follow along and explore how to take your reporting beyond the out-of-the-box
capabilities and make the marriage of Essbase and Excel complete.
10.1.1 Overview
Why “programmatic and reporting” and not “reporting and programmatic”? e whole
point of retrieving Essbase data in Excel is reporting, right? Given the advanced nature of
this book, the assumption is you already have a background in creating reports in Excel
and now want a better understanding of how those spreadsheet queries really work. Once
those underpinnings are dealt with, the bulk of this chapter is designed to introduce you
to the Smart View VBA (Visual Basic for Applications) functions and their capabilities.
is chapter is written for Excel users who want to leverage that tool to extend and
automate Essbase reports. Whether you have a minimal amount of VBA experience,
are capable of creating and editing Excel macros, or are a seasoned programmer, this
chapter is for you. In addition to automating and expanding the reporting and anal-
ysis of Essbase in Excel, we also will take a look at how Excel and Essbase interact.
Understanding this interaction is key to building ecient, high performance solutions.
10.1.1.1 Retrieving Data Eciently To create ecient Essbase reports within Excel, it is
important to construct your spreadsheets correctly. Reports and data input sheets can
be tuned and optimized to run very eciently using a few easy to implement rules. is
chapter will review and apply these rules and discuss creating ecient, dynamic Essbase
spreadsheets.
10.1.1.2 Code eory and Examples Finally, we dive into automation. My hope is the
chapter that follows will greatly smooth out the learning and implementation curve
allowing you to quickly impress your boss and win the favor of your co-workers with
the cool solutions you build with Excel Smart View automation. Automation is the
path to making a user’s life much easier. Smart View and Excel have well-evolved user
interfaces. However, they also provide API (application programming interface)-based
VBA functions. e VBA functions provide you with a means of extending the Smart
View functionality. With these functions, you can automate and customize Smart View
Get Developing Essbase Applications now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
