Oracle Essbase 11 Development Cookbook will help you learn the tools necessary for the development of Essbase databases in Oracle Essbase version 11.1.2.1. Here you will find over 90 recipes that explain everything from how to use a relational data model to building and loading an Essbase database in Essbase Studio. The book also goes over how to build the Block Storage (BSO) databases and explains some of the options are exclusive to building an Aggregate Storage (ASO) database. In this book, we will be using Essbase Studio, Essbase Integration Services (EIS), and Essbase Administration Service (EAS) to build databases, and we will discuss the strengths of each tool. Moreover, we discuss how to create Calculation Scripts, use MaxL to automate your processes, and integrate data. Finally, we step through how to effectively implement security, and how to build dynamic reports. The reader is encouraged to use these recipes as the foundation for their own customized databases and scripts.
Chapter 1, Understanding and Modifying Data Sources. This chapter explains how to prepare your data source to build hierarchies and load data in Essbase databases. Because you should not have to rebuild the wheel, we cover some tools that will assist us in extracting hierarchies from existing Essbase databases for the purpose of setting up your star schema in a relational environment. The goal of this chapter is to show the reader the components needed to maintain metadata in a relational environment and set up that environment to support drill-through reporting. This being said, most of the techniques used in this chapter can be implemented using flat files as well.
Chapter 2, Using Essbase Studio. We will begin this chapter by discussing advantages of and disadvantages of Essbase Studio when compared to development tools like Essbase Integration Services (EIS) and Essbase Administration Services (EAS). This chapter also has some of the more basic yet necessary steps needed to build your database using Essbase Studio. We will review how to create a data source, minischema, and manipulate data elements with Common Platform Language (CPL).
Chapter 3, Building the BSO Cube. In this chapter, we build and deploy the TBC Block Storage (BSO) database using Essbase Studio. We also explore the building of TBC databases using Essbase Integration Services (EIS).
Chapter 4, Building the ASO Cube. This chapter explains some of the options exclusive to building the Aggregate Storage (ASO) model. In addition, we learn how to build a Measure dimension from the fact table, and how to build a drill-through report in Essbase Studio.
Chapter 5, Using EAS for Development. This chapter explains how to build the Sample Basic database using the Essbase Administration Services (EAS) outline editor, build rules, load rules, and flat files. We also explore the use of Text and Date measures, outline formulas in the BSO model, and MDX in an aggregate storage database.
Chapter 6, Creating Calculation Scripts. In this chapter, we learn how to use calculation scripts to run complex formulas that require multiple passes through the Essbase database, data allocations, copying data, clearing data, aggregating data, and some best practices for optimizing your calculations' performance.
Chapter 7, Using MaxL to Automate Process. This chapter teaches you how to automate the updating, building, and loading of an Essbase database. This chapter more specifically shows MaxL script techniques designed to make scripts reusable and portable. These techniques will allow us to move our automation from development to staging or production without having to re-write our MaxL script before migration.
Chapter 8, Data Integration. This chapter explains how to integrate data in between Essbase and relational databases. In addition, we discuss how to move data between Essbase databases.
Chapter 9, Provisioning Security using MaxL Editor or Shared Services. This chapter shows how to use Shared Services and MaxL to set up security. Essbase has very flexible and powerful security features. This functionality, if planned carefully, can make your database more intuitive and customized to the needs of each end user.
Chapter 10, Developing Dynamic Reports. In this chapter, you will learn how to build a more dynamic Financial Report. Moreover, we discuss how to build a simple Web Analysis Report for an even more dynamic user experience.
You will need the following software to complete the recipes in this book:
Oracle EPM Essbase 11.1.2.1
Essbase Studio 11.1.2.1
Essbase Integration Services (EIS)
Financial Report & Web Analysis
SQL Server 2008/ Oracle 11g
Essbase Outline Extractor
Star Integration Server – Express Edition
If you are an experienced Essbase developer, Essbase Database Designer or Database Administrator, then this book is for you. This book assumes that you have good knowledge of Oracle 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: "enter connection: \'TBC'::'TBC.dbo.MEASURES'.'CHILD'||" - "||connection : \'TBC'::'TBC.dbo.MEASURES'.'MEASURES_ALIAS' in the textbox."
A block of code is set as follows:
Create Table PRODUCTS( PRODUCTID int NOT NULL, SKU varchar(15) NULL, SKU_ALIAS varchar(25) NULL, Constraint PK_PRODUCTS_PRODUCTID Primary Key (PRODUCTID) );
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 the text like this: "Click on cell F2, then click on the box to the right and bottom of the cell, and drag it down to cell F12."
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 send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
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.
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the 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 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 errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. 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.