Essbase is a multidimensional database management system. The name Essbase stands for Extended Spread Sheet dataBASE. Using the custom add-in provides the end-user with near seamless compatibility in the Microsoft Excel spreadsheet program.

Essbase as we know it today evolved from software components developed by Arbor Software Corporation and through the acquisition of additional components or tools from other OLAP and Business Intelligence product development companies. In some cases Arbor Software Corporation purchased the entire company to acquire the needed components as was the case with App Source in late 1997. It is generally agreed that the release of Essbase version 3.2 in 1995 set Essbase as the standard for OLAP and Business Intelligence and Analytics enterprise software.

Rapid growth and popularity of the Essbase product led to the merger of Arbor Software Corporation and Hyperion Software becoming Hyperion Solutions Corporation in 1998. This new company achieved near global leadership in the OLAP and Business Intelligence (BI) software arena and ultimately attracted the attention of Oracle Corporation. Oracle completed the purchase of Hyperion Solutions in 2007 for $3.3 Billion. Hyperion Solutions is now a subsidiary of Oracle Corporation and offers a complete line of integrated Business Intelligence and Business Performance Management products.

In addition to being the leading global provider of OLAP and Business Intelligence software, Essbase also offers incomparable value as a RAD (Rapid Application Development) tool. As will be demonstrated in the following pages the complete cycle from concept to design to build to implement can be only a fraction of what a traditional system may require. Further, enhancements to reporting or other functionality are fast, accurate and easy to code.

Chapter 1 guides you through a typical Essbase installation which includes the Essbase Agent on an analytic server, the Essbase API on the server, the Essbase Administration Services, and the Essbase Add-in for Microsoft Excel.

Chapter 2 covers Essbase database design considerations and how to apply them to a multidimensional database as opposed to the traditional row and column relational database.

Chapter 3 we begin to build in Essbase (hooray!). Using the information learned in the previous chapter we build the Essbase outline which is the foundation of the Essbase database. Instead of rows and columns an Essbase database contains dimensions and members in a hierarchical parent-child structure.

Chapter 4 dives right into loading data into your Essbase database. From user inputted data to flat file data manipulation and loading to direct database access all forms of data loading are explained and demonstrated.

Chapter 5 explains the varied and simple ways to calculate your data. Once data is loaded it is time to demonstrate one of the largest benefits of Essbase. Unlike relational databases, Essbase data can be calculated in many different ways. Instead of writing complex programs to calculate and derive data elements from existing elements or loading excessive amounts of data to derive the needed elements Essbase can calculate and derive data from a minimal amount of loaded data. Essbase has powerful yet simple to use tools that calculate the data

Chapter 6 goes over the use of the data for reporting, presentation, or data extracts to feed other systems. Simple steps explain how to create dynamic reporting abilities or user interfaces with a minimum of effort.

Chapter 7 jumps feet first into the Essbase Add-in for Microsoft Excel. As Microsoft Excel seems to have become the dominant spreadsheet program used by business today the Essbase Add-in for Microsoft Excel has evolved into a very powerful tool indeed. This is one of the main reasons Essbase is so popular today. Even the most novice end user can quickly create professional and dynamic reports with relative ease. We explain these features as well as how the addition of very little code can make the spreadsheet very powerful.

Chapter 8 we cover automating your Essbase cube. Depending on the requirements of your user community it is possible to design, build, and automate an Essbase application to where there is virtually no need for IT intervention.

Chapter 9 explains advanced techniques that can be used to keep your Essbase application running at peak performance. You know, those little things not usually covered in the user guide but learned with experience. Cache settings, server configuration, memory management are just a few topics covered.

Chapter 10 explains the Block Storage Option (BSO) and the Aggregate Storage Option (ASO) for storing data in its database cubes. For the most part this book deals with the BSO. Since the release of version 7.x Essbase has also offered the ASO. As this method of storing the data has substantial differences we felt it needed its own chapter to explain it.

Chapter 11 gives a high level view of the optional Essbase System 9 components that are available in the System 9 Suite with the Essbase database the common foundation for all the other components to launch from. With components like Essbase Planning or Hyperion Smart Office there's enough to make even the stodgiest accountant's head spin.

Appendix explains the significance of Oracle's new product Smart View.

This book is aimed at the IT professional who has an understanding of typical client-server applications but is new to Essbase and the concept of multidimensional database management systems.

Occasionally explaining the concept of a multidimensional database to someone who only has experience with traditional row/column relational databases can make their head explode! This book will show you the common sense approach to designing, building, and most importantly understanding Essbase and the cube concept.

As of this writing, Essbase System 9.x is the latest offering from Oracle Corporation. Essbase System 9.x itself is an integrated suite of Business Intelligence software. The Essbase module in System 9.x is substantially similar to Hyperion Essbase 7.x. Hyperion Essbase 7.x is still widely used and supported.

Since this book primarily covers the Essbase component where screen captures are used, they will be version 9.x however most all examples in this book will work in versions 7.x and 9.x unless otherwise noted.

Before we drill down into Essbase let us quickly take a minute to refresh some accepted data warehousing concepts.

Data warehousing is not a new concept. In fact, it has been around for many years now. Traditionally a data warehouse has been constructed with some sort of relational database structure. What is relatively new is the addition of the multidimensional database architecture to data warehousing family.

The following information is designed to give you a high level understanding of data warehousing and how it can be used in your business. Once you understand the basic principles and concepts of data warehousing it will be easier to understand where Essbase fits into the picture.

We guess we shouldn't tell you about the data warehousing concept without first telling you who is widely recognized as the creator or father of the modern data warehouse.

Bill Inmon is a world-renowned expert on data warehousing and is also widely recognized as the Father of Data Warehousing. With 35+ years of experience in the Information Technology field and more specifically database technology management and data warehouse design, Bill has been a highly sought after speaker for many major computing associations and industry conferences, seminars, and tradeshows.

Another widely recognized name in the data warehousing arena is Ralph Kimball. Ralph Kimball is an author on the subject of data warehousing and business intelligence and received a Ph.D. in 1972 from Stanford University in Electrical Engineering specializing in man-machine systems. He is widely regarded as the Guru of Data Warehousing and is known for long-term convictions that data warehouses must be designed to be understandable and fast. Ralph's methodology is also known as dimensional modeling or the Kimball methodology.

The similarities between Mr. Inmon and Mr. Kimball are many and so are the differences. The following paradigm statements illustrate just how Mr. Inmon and Mr. Kimball are perceived in the world of Data Warehousing.

Bill Inmon's paradigm: The enterprise data warehouse is one part of the overall business intelligence system. An enterprise should have just one data warehouse and one to many data marts. The data marts then source their information from the data warehouse. In the data warehouse, information is stored in third normal form.

Ralph Kimball's paradigm: The enterprise data warehouse is the conglomerate of all data marts within the enterprise. Information is always stored in the dimensional model.

There is no right way or wrong way between either of these two ideas. They each represent different data warehousing philosophies. In reality, the data warehouse philosophy used in most enterprises is closer to Ralph Kimball's idea. This is because most data warehouses started out as department level efforts, and as such they originated as an activity specific data mart. Only when more data marts are built later do they evolve into a data warehouse.

In addition to the relational database, an enterprise data warehouse environment often consists of an Extract Transform and Load (ETL) solution, an OLAP engine (hooray Essbase), client analysis tools, and other web or desktop applications that manage the gathering of data and delivering it to business users.

There are three types of data warehouses:

In case you're wondering, here are a few words about an OLAP solution and an OLTP solution. An OLAP solution stands for On-Line Analytical Processing, which in a nutshell means that the data you are using for your analysis is mainly considered reporting or presentation data and any updates or write-backs are solely for analytical purposes. The source data is rarely updated in this method.

The OLTP solution stands for On-Line Transactional Processing which means that the base or source data is directly updated with factual and historical data as an output of the analysis or data entry processes. Conventional straight line reporting can be performed and there is very little, if any, slice-and-dice analysis or what-if scenarios.

Data warehouses and data marts are usually built on dimensional data modeling where fact tables are connected with dimension tables. This is most useful for users to access data since a database can be visualized as a cube containing many dimensions. A data warehouse and its smaller, more specific data mart provide an opportunity for slicing and dicing that visualize cube along any one of its dimensions.

As mentioned above, even the so-called masters of the data warehouse have differing ideas as to the data modeling methodology that should be used in a data warehouse. There is general agreement that seem to have the choices narrowed down to just two popular architectures. There is the Third Normal Form and the Dimensional Data Model.

Of the two main types of data modeling most popularly used in data warehousing the more common of the two is the Dimensional Data Model. Read on as we briefly explain the differences between the two.

The Third Normal Form or 3NF method of database modeling in a nutshell is all about the primary key. What this means is there is no data element in the database that cannot be referenced by the primary key. To achieve 3NF a database must also pass the first levels on normalization.

In the First Normal Form or 1NF the theory is that all of the data in all of the columns must be atomic. This means there can be no sets of data in one column. For instance, a name column that contains both first and last names has sets of data. It is better to have one column for the first name and a separate column for the last name.

To pass the Second Normal Form or 2NF the data must be 1NF compliant and now must also be more key dependent. Where the 1NF model focuses on the atomic nature of the data the 2NF model is more key dependent. What this means is that data in non-key columns cannot depend on the composite or primary key.

Finally there is the Third Normal Form or 3NF which now, on top of organizing the data at the atomic level as well as identifying the data in conjunction with other supporting data, must now be completely primary key dependent. To be 3NF all data in non-key columns must be dependent on the primary key. No more can the data in one column or table be dependent on data in another column or table that is dependent on the primary key.

As we said earlier, there is no right or wrong reason to use either data modeling methodology. Both have their merits and their demerits.

Being the least popular of the data warehousing data models, the 3NF model is actually the most popular data modeling methodology used in active online transactional processing systems.

Ironically, when data is exported from an Essbase cube to a flat file for load to a relational database, it more closely resembles a 3NF data model than a Dimensional Data Model.

The Dimensional Data Model is the data modeling methodology most commonly used in data warehousing systems. The Dimensional Data Model differs substantially from the Third Normal Form, more commonly used for transactional systems. As you can imagine, the same data would then be stored much differently in a dimensional model than in a 3NF model.

The Dimensional Data Model consists of Fact and Dimension tables. The Fact tables store the numerical values of the business unit and contain numerical or additive measures of the business like Gross Sales, Gross Units. The Fact table also contains columns which link to the Dimension table. The Dimension table stores the descriptive information about the dimension and some times these are joined to other dimension tables to define the hierarchy of a dimension like Market (Geographical information) or Time information.

To understand Dimensional Data Modeling, we'll define some of the terms commonly used. Pay attention here as you may notice a definite similarity here with the terms used to describe data in an Essbase database

When the data in the data warehouse is modelled using the Dimension Data Model method instead of being organized like the 3NF method, which is in neat rows and columns with primary keys to identify everything, it usually follows the line of the dimensions that are included as necessary components of your data. The resultant structure of the dimensional data method resembles more of a multidimensional cube than two dimensional rows and columns.

Okay, now for the big question. Where does Essbase fit in with all this data warehouse mumbo jumbo?

Well if you were paying attention a few paragraphs back you would notice that we mentioned that a necessary tool in your enterprise data warehouse toolbox included an OLAP solution. Well, Essbase is it!

Essbase is the perfect multidimensional OLAP database tool to use as your function specific reporting and analysis data mart tool. Consider this, if your data is stored in your relational database data warehouse under the Dimensional Data Model methodology what better tool is there that has the power and capability to perform in the multidimensional arena. Essbase is a natural.

Consider this, with the proper hardware, Essbase is designed to support even the largest cubes with vast numbers of users so scalability is not an issue. Essbase is also the superior real time analysis and reporting tool that performs complex calculations. It can also be updated from the source database, in this case the data warehouse, quickly and effortlessly and depending on the technology you use for your data warehouse, Essbase can also connect directly to the data warehouse database to draw its data.

Knowing all this what other choice is there besides Essbase?

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "Note that the Time dimension contains the calendar periods used in the EssCar system"

A block of code will be set as follows:

[default]
IF (@ISMBR ("PRICE"))
"TOTAL NET REVENUE" ="TOTAL REVENUE" - "TOTAL DISCOUNTS";
ELSEIF (@ISMBR ("UNIT"))
"TOTAL NET REVENUE" = 0;
ENDIF

When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:

[default]
[Thu Sep 11 00:40:45 2008]Local/ESSBASE0///Info(1051061)
Application Demo loaded - connection established
[Thu Sep 11 00:40:45 2008]Local/ESSBASE0///Info(1054027)
Application [Demo] started with process id [4744]

[Thu Sep 11 00:40:45 2008]Local/ESSBASE0///Info(1056090)

Any command-line input or output is written as follows:

ESSCMD C:\Batch.SCR

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: "clicking the Next button moves you to the next screen".

If you're still holding onto this book then you are ready to embark on your journey towards Essbase Nirvana.

We begin by covering the installation of Essbase on both the client and the server and end with you having created a fully functional Essbase cube. This is where you usually read some form of good luck statement. With this book you don't need it! Let's GO!

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply drop an email to <[email protected]>, and mention the book title in the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[email protected]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.