Database Reference
In-Depth Information
10.1 introDuCtion
There is no question about it, finance 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 flexibility, power, and potential, Excel has made its way into almost every
modern office. In the early days of Essbase, Arbor Software recognized the importance
of Excel and used it as the standard interface to their new oLAP (online analytical
processing) database. When Arbor Software 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. After the oracle acquisition of hyperion Solutions, Smart view has continued
to improve, which validates Excel as the favorite interface for business users.
This chapter discusses the programmatic and reporting side of this capability, start-
ing with considerations for efficient 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 Over view
Why “programmatic and reporting” and not “reporting and programmatic”? The whole
point of retrieving Essbase data in Excel is reporting, right? given the advanced nature of
this topic, 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.
This 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 efficient, high performance solutions.
10.1.1.1 Retrieving Data Efficiently
to create efficient 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 efficiently using a few easy to implement rules. This
chapter will review and apply these rules and discuss creating efficient, dynamic Essbase
spreadsheets.
10.1.1.2 Code Theory 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. The vBA functions provide you with a means of extending the Smart
view functionality. With these functions, you can automate and customize Smart view
