47,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Oracle Essbase is a Multi-Dimensional Online Analytical Processing (OLAP) server, providing a rich environment for effectively developing custom analytic and enterprise performance management applications. Oracle Essbase enables business users to quickly model complex business scenarios.
This practical cookbook shows you the advanced development techniques when building Essbase Applications and how to take these applications further.
Packed with over 90 task-based and immediately reusable recipes, this book starts by showing you how to use a relational data model to build and load an Essbase cube and how to create a data source, prepare the mini schema, and work with the data elements in Essbase Studio. The book then dives into topics such as building the BSO cube, building the ASO cube, using EAS for development, creating Calculation Scripts and using MaxL to automate processes.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 432
Veröffentlichungsjahr: 2012
Ähnliche
Table of Contents
Oracle Essbase 11Development Cookbook
Oracle Essbase 11 Development Cookbook
Copyright © 2012 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: January 2012
Production Reference: 1170112
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-326-5
www.packtpub.com
Cover Image by Sandeep Babu (<[email protected]>)
Credits
Author
Jose R. Ruiz
Reviewers
Alexia Rodriguez Alwine
Satyanarayana Bodhanapu
Acquisition Editor
Kerry George
Lead Technical Editor
Susmita Panda
Technical Editor
Llewellyn F. Rozario
Copy Editor
Neha Shetty
Project Coordinator
Vishal Bodwani
Proofreaders
Aaron Nash
Chris Smith
Indexer
Rekha Nair
Production Coordinator
Arvindkumar Gupta
Cover Work
Arvindkumar Gupta
About the Author
Jose R. Ruiz is an Oracle Essbase 11 Certified Implementation Specialist with over nine years experience in developing enterprise-level Essbase applications. He has maintained and conducted post-production development on 18 Essbase databases. In addition, Jose Ruiz has been charged with developing E-commerce, Fixed Assets, Balance Sheets, Point of Sales, and Inventory databases.
Jose Ruiz is currently working with Oracle consultants on designing, developing, and implementing an Inventory, Purchase Order, and Sales Data Mart and an Essbase database at his current employer.
I would like to thank my colleagues and friends Peter Beddoe and Alexia Alwine for their review and advice. In addition, I would like to thank my wife, Yaneth C. Ruiz, for her support and patience throughout this endeavor.
About the Reviewer
Alexia Rodriguez Alwine is a Project Manager with extensive experience in the pharmaceutical and consumer products industries. She has worked with Unilever, Inc. and several of its subsidiaries; Steifel Laboratories, a GlaxoSmithKline company; and BE Aerospace. In addition to serving as a Project Manager, she has served as Hyperion Administrator, Systems Analyst, and Finance Manager. Her experience with Oracle includes Web Analysis, Financial Reporting, FDM, HFM, Hyperion Planning, Oracle Upgrades, and Essbase Migration Projects. She also has experience with SAP and Data Mart implementation.
Alexia graduated with a bachelor's degree in economics, communications, and international relations from the University of Pennsylvania. She received her MBA from the University of Florida. In her spare time, she researches and conducts workshops concerning the impact of technology on the family.
www.PacktPub.com
Support files, eBooks, discount offers, and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and, as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read, and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
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.
What this book covers
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.
What you need for this book
You will need the following software to complete the recipes in this book:
Who this book is for
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.
Conventions
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:
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."
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
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.
Customer support
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.
Downloading the example code
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.
Errata
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 erratasubmissionform 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
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.
Questions
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.
Chapter 1. Understanding and Modifying Data Sources
In this chapter, we will cover the following topics:
Introduction
In this chapter, we will build components into our relational environment that will allow us to successfully build an Essbase database and facilitate drill-through reporting. Although we are discussing relational data sources, the properties, attributes, and concepts discussed in this chapter can be used to build hierarchies off data sources such as flat files for example. The techniques used here can be used in tools like Essbase Administrative Services, Essbase Integration Services, and Essbase Studio. This chapter also has recipes on the Essbase Outline Extractor and Star Analytics. These two tools allow us to extract hierarchies from existing Essbase cubes. We would use these tools to extract existing hierarchies or modify existing hierarchies to build all or parts of our star schema.
Setting up an Account or Measures dimension with a parent-child reference
In this recipe, we will set up a relational table in a parent-child reference format. We will also review the type of properties that can go in each column and their definitions. The Account or Measuredimension is normally the most dynamic dimension in a financial database and it is recommended that you use the parent-child structure to build the dimension in a relational environment. The parent-child reference also allows ragged hierarchies without having to add columns to your tables when an additional level or generation is needed. We will also review an alternative method, which requires us to use the measures field in our fact table to build our Measure dimension.
Getting ready
To get started, open your SQL Server Management Studio, and add a database called TBC. For this recipe, we are using T-SQL, but the PL\SQL equivalent will be provided where applicable. You should add a SCHEMA called TBC using tools such as TOAD, SQL Developer, or Golden, if you are using Oracle.
How to do it...
Tip
Downloading the example code
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.
How it works...
The MEASURES table has the following columns:
COLUMN
DESCRIPTION
SORTKEY
This column is the integer that helps you sort the MEASURES in the order that you want them to appear in the hierarchy
MEASURESID
This ID is used as the PRIMARY KEY in the MEASURES table and as a FOREIGN KEY in the fact table
PARENT
This column is the Parent in the hierarchy
CHILD
This column is the Child of the Parent column
MEASURES_ALIAS
This is a more intuitive description of Measures normally defined by the business
CONSOLIDATION
This field has the aggregation type for the Child column
TWOPASSCALC
This field has the value "T" if the aggregation requires a second pass through the outline for the results to be right
STORAGE
Storage can have many values and will determine how or if the data in the outline is stored or dynamically calculated
VARIANCEREPORTING
The Variance Reporting column is used to mark Expense accounts for reporting variances
TIMEBALANCE
The Time Balance column is used with your time dimension to determine whether to use LIFO, FIFO, or the Average method for a specific measure
SKIP
The Skip column works with Time Balance to determine how to treat #MISSING or Zero values
UDA
The User Defined Attribute is useful for many purposes including outline formulas, calculation formulas, and the retrieval of data by the criteria defined by the business
FORMULA
These are the outline formulas used in the BSO model
COMMENT_ESSBASE
These are simply comments on the meta-data stored in this table
In step 2, we load the data. The following are descriptions of what goes into some of these columns as per Oracle's documentation.
These are the valid Consolidations values:
TYPE
TYPE DESCRIPTION
TYPE LONG DESCRIPTION
%
Percent
Expresses as a percentage of the current total in a consolidation
*
Multiplication
Multiplies by the current total in a consolidation
+
Addition
Adds to the current total in a consolidation
-
Subtraction
Subtracts from the current total in a consolidation
/
Division
Divides by the current total in a consolidation
^
Never
Excludes from all consolidations in all dimensions
~
Ignore
Excludes from the consolidation
This is the valid TwoPass value:
TYPE
TYPE DESC
TYPE LONG DESCRIPTION
T
Two Pass Calculation
Requires a two-pass calculation (applies to accounts dimensions only)
These are the valid Storage values:
TYPE
TYPE DESC
TYPE LONG DESCRIPTION
N
Never Share
Never allows data sharing
O
Label Only
Tags as label only (store no data)
S
Store Data
Sets member as stored member (non-Dynamic Calc and not label only)
V
Dynamic Calc and Store
Creates as Dynamic Calc and Store
X
Dynamic Calc
Creates as Dynamic Calc
This is the valid VarianceReporting value:
TYPE
TYPE DESC
TYPE LONG DESCRIPTION
E
Expense
Treats as an expense item (applies to accounts dimensions only)
These are the valid TimeBalance values:
TYPE
TYPE DESC
TYPE LONG DESCRIPTION
A
Average
Treats as an average time balance item (applies to accounts dimensions only)
F
First
Treats as a first time balance item (applies to accounts dimensions only)
L
Last
Treats as a last time balance item (applies to accounts dimensions only)
These are the valid Skip options per Oracle's Documentation:
TYPE
TYPE DESC
TYPE LONG DESCRIPTION
B
Missing and Zeros
Skips #MISSING data and data that equals zero when calculating the parent value
M
Missing
Skips #MISSING data when calculating the parent value
Z
Zeros
Skips data that equals zero when calculating the parent value
There's more...
Using the parent-child reference table structure will depend on whether we know that our Measures and Accounts are going to change often. The structure of your fact table will have to change if you decide to use Measure tables. A fact table that has the Measures going down a table vertically, as rows, will allow us to use the Measures column in the fact table to join to the MEASURES table. The following screenshot illustrates how this design will look:
We can easily add accounts or change parent-child associations using this format without having to modify the fact table. On the other hand, if our fact table has Measures horizontally, in columns, then the Measures dimension will have to be built in Essbase Studio or Essbase Integration Services instead. The following screenshot is an example of what a fact table, with Measures as columns, would look like:
The Beverage Company (TBC) sample database's SALES and SALESFACT tables are examples of the two different formats.
See also
You can find an example of the MEASURES dimension being built in the recipe Creating hierarchies using a Parent-child reference table in Chapter 3. For an example on how to build the MEASURES dimension using Essbase Studio from the fact table, refer to the recipe Building a Measures dimension from the fact table in Chapter 4.
Setting up dimensions with a generation reference
In this recipe, we will build a table in a generation reference format. The SUPPLIER is a geographical dimension. Geographic dimensions are natural hierarchies, which means that the generations are related to each other naturally and there is normally a one-to-many relationship. A generation reference format is common in a relational environment as it can be used to conduct relational reporting as well. The same cannot be said about the parent-child structure.
Getting ready
To get started, open your SQL Server Management Studio, and add a TBC database. Add a SCHEMA using a tool such as TOAD, SQL Developer, or Golden, if you are using Oracle.
How to do it...
How it works...
In step 1, the SUPPLIER table was created and in step 2 the data was populated. A generation in Essbase begins with generation 1 at dimension because the name of the cube in the outline is generation 0. We can tell from the structure of the table that it is clearly set up in generationreference as depicted in the following grid:
COLUMN
DESCRIPTION
SUPPLIERID
The PRIMARY KEY and a FOREIGN KEY
COUNTRY
Generation 2
STATE
Generation 3
CITY
Generation 4
ZIPCODE
Generation 5
ADDRESS
Generation 6
Tip
The generation reference will allow us to create ragged hierarchies, but requires the handling of null values by your development tool.
See also
For more information on how to build the SUPPLIER dimension using Essbase Studio, refer to the recipe Creating hierarchies using a Generation reference table in Chapter 3.
Adding columns for outline formulas
In this recipe, we will add columns to our MEASURES table, so that we can later add a formula to the dimension's members. The importance of this is apparent when you consider that the Aggregate Storage (ASO) model does not use the same syntax as the Block Storage (BSO) model for their outline formulas. The ASO outline uses Multidimensional Expressions (MDX), which is the standard syntax convention for OLAP applications. We can use our table for both BSO and ASO applications by adding an additional column for the ASO model's formulas.
Getting ready
To get started, open SQL Server Management Studio, and add a database called TBC. In this recipe, we are using T-SQL, but the PL-SQL equivalent for the examples has been included in the following code snippet. The MEASURES dimension was created in the recipe Setting up an Account or Measure dimension with parent-child reference in Chapter 1. We need to complete step 1 of the aforementioned recipe before we continue.
How to do it...
How it works...
In step 1, the column FORMULA_MDX is added to the MEASURES table. The script in step 2 adds the new rows with the FORMULA_MDX column included. The objective of this recipe is to show you that the syntax is different every time you use a table for both an ASO and BSO set of applications, so you need to have two formula columns. You can see how different the syntax is in the following code snippet, but if you need a more detailed explanation on this, please visit: http://www.oracle.com/technetwork/middleware/bi-foundation/4395-calc-to-mdx-wp-133362.pdf. This is Oracle's white paper on Converting Calc Formulas to MDX in an Essbase Outline.
FORMULA
FORMULA_MDX
Margin % Sales;
Measures.Sales / Measures.Margin;
Profit % Sales;
Measures.Sales / Measures.Profit;
Adding the solve order column to tables that have ASO formulas
In this recipe, we will include an additional column to our MEASURES table to specify the solve order for the hierarchy. The ASO outline does not have the Two Pass Calc option in its Account dimension; as a result, you will have to specify the solve order by adding an additional column.
Getting ready
To get started, open SQL Server Management Studio, and add a database called TBC. In this recipe, we are using T-SQL, but the PL\SQL equivalent is provided in the examples. The MEASURESdimension was created in the recipe Setting up an Account or Measure dimension with parent-child reference in Chapter 1. We need to complete step 1 of the aforementioned recipe before we continue.
How to do it...
How it works...
We started this recipe by adding the SOLVE_ORDER column to the MEASURES table. We also added two new rows with the SOLVE_ORDER populated. The objective of this recipe is to show you that the SOLVE_ORDER value has to be higher than its respective components in order for the formula to return the correct values. We should consider the following steps when assigning SOLVE_ORDER:
