Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Oracle Essbase 11 Development Cookbook
  • Table Of Contents Toc
Oracle Essbase 11 Development Cookbook

Oracle Essbase 11 Development Cookbook

By : Jose R Ruiz
4.2 (6)
Buy this Book
close
close
Oracle Essbase 11 Development Cookbook

Oracle Essbase 11 Development Cookbook

4.2 (6)
By: Jose R Ruiz
Buy this Book

Overview of this book

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.
Table of Contents (17 chapters)
close
close
close
Oracle Essbase 11 Development Cookbook
Icon
Oracle Essbase 11 Development Cookbook
Credits
Icon
Credits
About the Author
Icon
About the Author
About the Reviewer
Icon
About the Reviewer
www.PacktPub.com
Icon
www.PacktPub.com
Preface
Icon
Preface
Lock Free Chapter
1
Understanding and Modifying Data Sources
chevron up
Icon
Understanding and Modifying Data Sources
Icon
Introduction
Icon
Setting up an Account or Measures dimension with a parent-child reference
Icon
Setting up dimensions with a generation reference
Icon
Adding columns for outline formulas
Icon
Adding the solve order column to tables that have ASO formulas
Icon
Adding and populating the Sort Order Column
Icon
Adding tables for varying attributes
Icon
Determining hierarchies in relational tables
Icon
Using the Essbase Outline Extractor to extract dimensions
Icon
Using Star Analytics to build your star schema from existing Essbase cubes
2
Using Essbase Studio
Icon
Using Essbase Studio
Icon
Introduction
Icon
Creating TBC sample database and connecting to the data source
Icon
Adding user-defined tables
Icon
Building your minischema
Icon
Setting up joins in a minischema
Icon
Adding tables to a minischema
Icon
Using a text file data source
Icon
Working with Common Platform Language (CPL)
Icon
Using Sort Order on data elements
3
Building the BSO Cube
Icon
Building the BSO Cube
Icon
Introduction
Icon
Creating hierarchies using a parent-child reference table
Icon
Creating hierarchies using a generation reference table
Icon
Adding attribute dimensions to hierarchies
Icon
Building a Calendar dimension
Icon
Creating date elements
Icon
Creating Alias tables
Icon
Developing cube schema and an Essbase model
Icon
Setting Essbase properties
Icon
Deploying a cube
Icon
Creating an OLAP Model in EIS
Icon
Creating an OLAP metaoutline in EIS
4
Building the ASO Cube
Icon
Building the ASO Cube
Icon
Introduction
Icon
Using the Connection Wizard to set up an ASO cube
Icon
Building a Measures dimension from the fact table
Icon
Creating an ASO Cube Schema and an Essbase Model
Icon
Understanding Essbase Model properties for the ASO cube
Icon
Designing a drill-through report
Icon
Using the View dimension for Dynamic Time Series reporting
5
Using EAS for Development
Icon
Using EAS for Development
Icon
Introduction
Icon
Adding an application and database on an Essbase Server
Icon
Using the outline editor to add dimensions
Icon
Using dimension build rules to add the parent-child dimension
Icon
Creating dimension build rules to add a base and attribute dimensions
Icon
Using dimension build rules to add user-defined attributes and associate dimensions
Icon
Creating load rules for flat file data loads
Icon
Creating substitution variables
Icon
Using If/Else logic and substitution variables in outline formulas
Icon
Using Text measures on a BSO cube
Icon
Using Date measures on a BSO cube
Icon
Using different outline formula logic at parent level
Icon
Creating a load rule for SQL data load using substitution variables
Icon
Using MDX in aggregate storage applications
6
Creating Calculation Scripts
Icon
Creating Calculation Scripts
Icon
Introduction
Icon
Using Essbase Set function commands and Calc All to calculate cubes
Icon
Using control flow commands, conditional, and logical operators
Icon
Using substitution variables in calculations script
Icon
Using UDAs and Calc Two Pass in calculation scripts
Icon
Using Attributes in calculation scripts
Icon
Clearing data and using the cross- dimensional operators in a calculation script
Icon
Using allocation functions in calculation scripts
Icon
Modifying Essbase settings to improve calculation performance
Icon
Using MDX to calculate Aggregate Storage database
7
Using MaxL to Automate Process
Icon
Using MaxL to Automate Process
Icon
Introduction
Icon
Setting up folder structure and other files needed for MaxL automation
Icon
Executing dimension build rules using MaxL
Icon
Executing load rules using MaxL
Icon
Executing calculations using MaxL
Icon
Executing partitions using MaxL
Icon
Executing report scripts using MaxL
Icon
Adding or changing substitution variables with MaxL
Icon
Using ASO incremental data loads
Icon
Using encryption in MaxL scripts
Icon
Deploy dimension created in Essbase Studio
8
Data Integration
Icon
Data Integration
Icon
Introduction
Icon
Using report script to extract data to a text file
Icon
Using the DATAEXPORT function to extract data into a text file
Icon
Using the DATAEXPORT function to extract data into a relational source
Icon
Exporting data using column format
Icon
Using MaxL to extract the outline in XML format
Icon
Using @XREF functions to move data between BSO cubes
Icon
Partitioning data from BSO to ASO cubes
Icon
Using MDX for extracting data using API
9
Provisioning Security Using MaxL Editor or Shared Services
Icon
Provisioning Security Using MaxL Editor or Shared Services
Icon
Introduction
Icon
Using MaxL editor to add and externalize a user
Icon
Using Shared Services to add and provision a user
Icon
Using MaxL Editor to set up a filter for MetaRead and Write access
Icon
Using Shared Services to provision filters to a group
Icon
Using Shared Services to provision calculation scripts to a group
Icon
Using MaxL to export security file
10
Developing Dynamic Reports
Icon
Developing Dynamic Reports
Icon
Introduction
Icon
Creating a connection and using substitution variables in financial reports
Icon
Using the column templates and formatting reports
Icon
Retrieving data using UDAs and Attributes
Icon
Retrieving data using children and descendants member set functions
Icon
Using User Prompts and the POV to select members
Icon
Using conditional formatting and suppression in financial reports
Icon
Adding related content to financial reports
Icon
Creating a web analysis report
1
Index
Icon
Index

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 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...

  1. Execute the following script to add the FORMULA_MDX and SOLVE_ORDER columns to the MEASURES table, if it does not exist:

    --This is the script in T-SQL
Alter Table MEASURES Add FORMULA_MDX VARCHAR(4000) NULL;
Alter Table MEASURES Add SOLVE_ORDER INT NULL;
--This is the script in PL-SQL
Alter Table MEASURES ADD FORMULA_MDX VARCHAR2(4000) NULL;
Alter Table MEASURES Add SOLVE_ORDER INTEGER NULL;

  2. Execute the following scripts to add the formula and the solve order values to the MEASURES table:

    INSERT INTO MEASURES
  (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOP ASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE,FORMULA_MDX, SOLVE_ORDER) Values(0, 14, 'Ratios', 'Margin %', '', '+', 'T','X', '', '', '', '', 'Margin % Sales;', '', '[Measures].[Sales] / [Measures].[Margin];', 20);

INSERT INTO MEASURES(SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX, SOLVE_ORDER) Values(0, 15, 'Ratios', 'Profit %', '', '~', 'T', 'X', '', '', '', '', 'Profit % Sales;', '', '[Measures].[Sales] / [Measures].[Profit];', 20);

  3. Select from the table to see the values that you added:

    Select * From MEASURES;

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:

  1. Set up in SOLVE_ORDER in increments of tens or twenties for clarity and consistency.

  2. When the default is not specified, SOLVE_ORDER is zero, but it is good practice to always specify the SOLVE_ORDER to remove ambiguity and define the calculation's priority.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Oracle Essbase 11 Development Cookbook
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon