Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Oracle Essbase 11 Development Cookbook
Oracle Essbase 11 Development Cookbook

Oracle Essbase 11 Development Cookbook: Over 90 advanced development recipes to build and take your Oracle Essbase Applications further with this book and ebook

Arrow left icon
Profile Icon Jose R Ruiz
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.2 (6 Ratings)
Paperback Jan 2012 400 pages 1st Edition
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial
Arrow left icon
Profile Icon Jose R Ruiz
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.2 (6 Ratings)
Paperback Jan 2012 400 pages 1st Edition
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Oracle Essbase 11 Development Cookbook

Chapter 2. Using Essbase Studio

In this chapter, we will cover the following topics:

  • Creating a TBC database and connecting to the data source

  • Adding user-defined tables

  • Building your minischema

  • Setting up joins in a minischema

  • Adding tables to a minischema

  • Using a text file as the data source

  • Working with Common Platform Language (CPL)

  • Using Sort Order on data elements

Introduction


In this chapter, we will go over some basic, yet necessary Essbase Studio recipes. The first question that we should discuss, is why begin developing with Essbase Studio as opposed to Essbase Administration Services (EAS) or Essbase Integration Services (EIS)? The most obvious answer is that Oracle has announced that their long-term vision is to replace EAS and EIS with Essbase Studio. Please visit the following URL for more information on the Oracle Essbase Road Map: http://communities.ioug.org/Portals/2/Oracle_Essbase_Roadmap_Sep_09.pdf.

That said, version 11.1.2.1, the version this book is written for, requires that you use Essbase Studio in conjunction with EAS for a set of tasks. Please see the following advantages and disadvantages of Essbase Studio for more details.

Advantages of Essbase Studio

  • Essbase Studio is integrated with all new features of 11.x

  • Ease of migrating Integration Service catalog to Essbase Studio catalog via migration utility

  • As opposed to EIS, Essbase Studio...

Creating TBC sample database and connecting to the data source


The first step in using Essbase Studio is to connect to the data source. In this recipe, we will be creating The Beverage Company (TBC) sample database and we will also be creating a SQL Server data source connection.

Getting ready

To get started, open SQL Server Management Studio, and add a database called TBC. We are using the SQL Server scripts in this example, but Oracle has provided the Oracle, DB2, Teradata, and MySQL scripts.

How to do it...

  1. Open SQL Server Management Studio, select the TBC database, click on the File menu, select Open, and click on File…. Open the following script: …\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseStudio\Server\sqlscripts\tbc_create_sqlsrv.sql.

  2. Make sure TBC is selected in your Available Database window and click on Execute.

  3. Click on the File menu, select Open, and click on File…. Open the following script:…\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseStudio\Server\sqlscripts...

Adding user-defined tables


The user-defined table is used to create a view within Essbase Studio. User-defined tables should give you some flexibility without having to change the data source. This functionality is going to be important when defining your metadata. Furthermore, this flexibility is valuable in environments where the Essbase developers cannot modify the data source at the risk of impacting other Essbase applications or relational reporting. In this recipe, we will add a user-defined table to our data source and then add that table to the TBC minischema.

Getting ready

To get started, click on the Start menu and navigate to Oracle EPM System | Essbase | Essbase Studio | Essbase Studio Console. On the login menu, enter the server, username, and password.

How to do it...

  1. Right-click on TBC Data Source under the Data Sources node. Then click on New and select User-Defined Table...:

  2. Enter a Table name in the available textbox and the following script to create the user-defined table...

Building your minischema


The minischema is a structure that defines the relationships between your tables, or in the case of parent-child recursive relationships, within the same table. In the preceding recipe, Creating TBC database and connecting to the data source, we had an opportunity to continue with the process and build the minischema and data elements. We separated the steps to focus on each component individually, but the Connection Wizard would have been a seamless approach to building your data source connection, minischema, and data elements. In this recipe, we will create a minischema using the Minischema Wizard in Essbase Studio.

Getting ready

To get started, click on the Start menu and navigate to Programs | Oracle EPM System | Essbase | Essbase Studio. The login menu will pop up. Enter your server, username, password, and click the Login button.

How to do it…

  1. On the right-hand side of your Essbase Studio screen, you are going to see two tabs. Click on the Minischemas tab, right...

Setting up joins in a minischema


When you create a minischema, if there are any joins in the data source they will be visible in the minischema diagram. In the TBC minischema, in the recipe Building your minischema, there are no visible recursive (parent-child) relationships. Any user-defined tables that you define in the data source will also not have any relationships assigned to them. These relationships will impact the queries that Essbase Studio generates. If you do not understand the relationships in your data source, then you will most likely not build a cube that truly defines how the business functions. Defining relationships between tables can be done manually by adding joins or by using inspection. In this recipe, we will practice both methods.

Getting ready

To get started with Essbase Studio, click on the Start menu and navigate to Programs | Oracle EPM System | Essbase | Essbase Studio. The login menu will pop up. Enter your server, username, and password, and click on the Login...

Adding tables to a minischema


In this recipe, we will add two new tables to your data source and minischema. We will begin this recipe by adding tables to your relational data source. Then, you will use incremental updates to add the two tables to your data source in Essbase Studio. Finally, we are going to add the tables to the minischema. This task would be easier if the tables already existed in your data source in Essbase Studio, but in real-world applications this is normally the order in which these changes take place.

Getting ready

Open your SQL Server Management Studio and open the TBC database. If you have not created the database, then see the recipe Creating TBC database and connecting to the data source in this chapter. The example is done using T-SQL, but the PL-SQL equivalent for the examples is as follows.

How to do it…

  1. In SQL Server Management Studio, click on File | Open File… to open the tsql_script_3265_02_01.sql file included within this chapter for T-SQL, and click on...

Using a text file data source


The ability to load data from many different data sources is one of the reasons why Essbase is such a flexible tool. In this recipe, we will see how to use text files as the data source and how to add a text file to a new minischema using the Connection Wizard.

Getting ready

To get started with Essbase Studio, click on the Start menu and navigate to Programs | Oracle EPM System | Essbase | Essbase Studio. The login menu will pop up. Enter your server, username, and password, and click on the Log in button.

How to do it…

  1. On the right-hand side of your Essbase Studio screen, you are going to see two tabs. Click on the Data Sources tab and right-click on the Data Source node. Then click on New and Data Source….

  2. When the data source menu pops up, enter the connection name tbc_sample_text and select Text as the Data Source Type. Click the Browse button to the right of the Location textbox. The following Text File Location dialog should be displayed:

  3. Double-click on tbc_samples...

Working with Common Platform Language (CPL)


CPL is the syntax that you will need to use on your data elements or dimension elements to apply functions, filters, and operators. In this recipe, we will use the MonthShortName function and concatenate data elements using CPL.

Getting ready

To get started with Essbase Studio, click on the Start menu and Programs | Oracle EPM System | Essbase | Essbase Studio. The login menu will pop up. Enter your server, username, password, and click on the Log in button.

How to do it…

  1. In Essbase Studio, on the right side of your screen, you will see two tabs. Click on the Minischema tab, click and hold the MEASURES folder, and drag it under the MetaData Navigator root directory. Repeat the same step for the SALES folder.

  2. Right-click on the SALES folder, click on New, and then Dimension Element:

  3. Change the name to MonthShortName, select the Function tab, drill down on All functions, and click on the monthShortName function. Click on the Source tab and highlight $...

Using Sort Order on data elements


Businesses will often ask for certain members to be in a specific order in the outline, and for this reason, it is important to have the Sort Order columns in each of your tables. In this recipe, we will use the Sort Order or Sort Key column of a table to specify the order of the members in a hierarchy.

Getting ready

To get started with Essbase Studio, click on the Start menu and navigate to Programs | Oracle EPM System | Essbase | Essbase Studio. The login menu will pop up. Enter your server, username, password, and click on the Log in button.

How to do it…

  1. On the right side of your Essbase Studio screen, you are going to see two tabs. Click on the Minischema tab and drag the MEASURES folder under the MetaData Navigator root directory.

  2. Drill down on the MEASURES folder and double-click on the CHILD member.

  3. Select the SORTKEY data element and use the single right arrow to add to the Sort Order selection. Make sure the order attribute is Ascending, by clicking...

Left arrow icon Right arrow icon

Key benefits

  • This book and e-book will provide you with the tools needed to successfully build and deploy your Essbase application.
  • Includes the major components that need to be considered when designing an Essbase application.
  • This book can be used to build calculations, design process automation, add security, integrate data, and report off an Essbase cube.

Description

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.

Who is this book 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.

What you will learn

  • Design components of a Data Mart to use as data source, maintain meta-data, and support drill-through reporting.
  • Setup a Data Source and Minischema in Essbase Studio so that metadata elements can be manipulated and hierarchies built.
  • Create an Essbase Model, Cube Schema, drill-through report, and deploy both a Block Storage (BSO) and Aggregate Storage (ASO) application.
  • Use Essbase Administration Services (EAS) to create applications, create and use substitution variables, and add complex outline formulas in both the Block Storage (BSO) and Aggregate Storage (ASO) models.
  • Create dimension build rules, flat file load rules, and SQL load rules.
  • Create complex calculations using the Calculation Script Editor, optimize a cube for calculations, and run allocation calculations off an Aggregate Storage database.
  • Automate processes via MaxL scripts and integrate data between Essbase databases and to relational databases
  • Design effective security and build dynamic reports

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jan 24, 2012
Length: 400 pages
Edition : 1st
Language : English
ISBN-13 : 9781849683265
Vendor :
Oracle
Category :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Jan 24, 2012
Length: 400 pages
Edition : 1st
Language : English
ISBN-13 : 9781849683265
Vendor :
Oracle
Category :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just S$6 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just S$6 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total S$ 177.98
Oracle Essbase 11 Development Cookbook
S$88.99
Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial
S$88.99
Total S$ 177.98 Stars icon
Banner background image

Table of Contents

10 Chapters
Understanding and Modifying Data Sources Chevron down icon Chevron up icon
Using Essbase Studio Chevron down icon Chevron up icon
Building the BSO Cube Chevron down icon Chevron up icon
Building the ASO Cube Chevron down icon Chevron up icon
Using EAS for Development Chevron down icon Chevron up icon
Creating Calculation Scripts Chevron down icon Chevron up icon
Using MaxL to Automate Process Chevron down icon Chevron up icon
Data Integration Chevron down icon Chevron up icon
Provisioning Security Using MaxL Editor or Shared Services Chevron down icon Chevron up icon
Developing Dynamic Reports Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.2
(6 Ratings)
5 star 50%
4 star 16.7%
3 star 33.3%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by




Essbase Jan 30, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
The content and recipes within this book was very helpful in explaining how to execute task related to Essbase processes. It offered both theory and practical content which was very useful and the instructions were easy to follow. I now keep my copy at the office as a guide. I would recommend this book.
Amazon Verified review Amazon
Gokul Patel Mar 23, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Excellent Handbook on Essbase. The book works as a catalyst as well as a reference guide in solving the common problems in handling day-to-day Essbase issues and tasks.
Amazon Verified review Amazon
jgomez16 Mar 09, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I found the Oracle Essbase 11 Development Cookbook to be very well written in an easy to follow, friendly style. Comprehensive step-by-step instructions guide you through each recipe and detailed explanations ensure you understand what you are doing. On top of a wealth of Essbase techniques and insider tips this book covers sourcing your cube from a relational database, building a relational star schema, creating the meta-outline and deploying your Essbase cube. It doesn't stop there. Once your cube is built there are more recipes for writing calculation scripts, report scripts and more. Essbase professionals at all levels will gain valuable knowledge from this book.All great chefs have at least one cookbook on their shelf. If you are cooking with Essbase, the Oracle Essbase 11 Development Cookbook is the book!
Amazon Verified review Amazon
Jason Jones Mar 17, 2012
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
I was recently given the opportunity to review another Essbase book from Packt: Oracle Essbase 11 Development Cookbook by Jose Ruiz. Overall I would say I am pleased with the book. It covers a lot of ground and a lot of disparate tools, many of which are scantily documented elsewhere.Before I really get into the review, I must say that I have never been a big fan of the approach that technology cookbooks take. I'm also not a huge fan of having a book for a specific version of software. Of course, in order for the cookbook approach to work you don't have a choice but to tie to a version of software. This is because the recipes are sequential and very explicit -- as with cooking a recipe in real life -- and rely on the exact version of the software in order for the detailed steps of the recipe to work. I've grown up with software, and am a cross between a visual and a kinesthetic learner, so my preference is to have concepts and goals explained to me, then to go exploring on my own. To this end, I find technology/recipe books to be tedious as they laboriously lay out the steps: click this, then click that, enter this text in, and 15 steps later you have a result.So, my personal preference for book styles aside, this book largely succeeds for what it is: specific, methodical ways to perform a certain task. You won't get a lot of explanation on why you might do something a certain way. In this regard, the book is useful as a complement to your Essbase literature rather that the place you would go to understand why you might want to accomplish some task.Okay, now that I have beat up on that horse enough.As I said, I enjoyed the breadth of content in the book. There are detailed recipes for setting up your relational data store to load a cube with EIS and Essbase Studio, building load rules and loading data to BSO/ASO cubes, writing calc scripts, working with Star Analytics, using EAS, HFR, writing MaxL scripts, and provisioning security. It even covers working with the revered Outline Extractor tool.All of this content was really nice to see in book form. One of the upsides to the recipe format book is that it won't spend a lot of time laboring over what a cube is and your first steps retrieving data with Excel. In fact, the book even says it's not for beginners. It just jumps right in. I think this book can be a very handy reference for someone that needs something a little more guided than the technical reference (and less heavy).On my arbitrary rating system, I would give this book a four out of five star rating. And again, that's me trying to be fair to the book even though I'm not in love with this format, but it largely accomplishes what it sets out to do. I'd say it's a great addition to the pragmatic Essbase developer's library, but certainly not the only book in it.
Amazon Verified review Amazon
David Feb 22, 2015
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3
Its an okay book to find things, but certainly not something you would read cover to cover.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.