Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
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

eBook
$35.98 $39.99
Paperback
$65.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with Print?

Product feature icon Instant access to your digital copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Redeem a companion digital copy on all Print orders
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Table of content icon View table of contents Preview book icon Preview Book

Oracle Essbase 11 Development Cookbook

Chapter 1. Understanding and Modifying Data Sources

In this chapter, we will cover the following topics:

  • Setting up an Account or Measures dimension with a parent-child reference

  • Setting up dimensions with a generation reference

  • Adding columns for outline formulas

  • Adding the Solve Order column to tables that have ASO formulas

  • Adding and populating the Sort Order Column

  • Adding tables for varying attributes

  • Determining hierarchies in relational tables

  • Using the Essbase Outline Extractor to extract dimensions

  • Using Star Analytics to build your star schema from existing Essbase cubes

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

  1. Run the following scripts to create the Measures table. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2(). A screenshot of the table follows the script:

     --This is the syntax in T-SQL  
    create table MEASURES
    (
        SORTKEY            int                   not null,
        MEASURESID         int                   not null,
        PARENT             varchar(85)           null    ,
        CHILD              varchar(85)           not null,
        MEASURES_ALIAS     varchar(85)           null    ,
        CONSOLIDATION      varchar(85)           null    ,
        TWOPASSCALC        varchar(85)           null    ,
        STORAGE            varchar(85)           null    ,
        VARIANCEREPORTING  varchar(85)           null    ,
        TIMEBALANCE        varchar(85)           null    ,
        SKIP               varchar(85)           null    ,
        UDA                varchar(85)           null    ,
        FORMULA            varchar(255)          null    ,
        COMMENT_ESSBASE    varchar(85)           null    ,
        constraint PK_MEASURES primary key (MEASURESID)
    )
    Go

    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.

  2. Execute the following scripts to add the data to your table:

    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
      TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE)
      VALUES (100,1,'Measures','Profit','','+','','X','','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
      TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (200,2,'Profit','Margin','','+','','X','','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (300,3,'Margin','Sales','','+','',
      '','','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES (400,4,'Margin','COGS','Cost of Goods Sold','-','','','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (500,5,'Profit','Total Expenses','','-','','X','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (600,6,'Total Expenses','Marketing','','+',
      '','','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (700,7,'Total Expenses','Payroll','','+','','','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
      TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (800,8,'Total Expenses','Misc','Miscellaneous','+',
      '','','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (900,9,'Measures','Inventory','','~','','O','','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
      TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
      (1000,10,'Inventory','Opening Inventory','','+','','','E','F','','',
      'IF(NOT @ISMBR(Jan)) "Opening Inventory"=@PRIOR("Ending Inventory");ENDIF;"Ending Inventory"="Opening Inventory"+Additions-Sales;','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,
    COMMENT_ESSBASE) VALUES (1100,11,'Inventory','Additions','','~','','','E','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
      TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
      (1200,12,'Inventory','Ending Inventory','','~','','','E','L','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,
    COMMENT_ESSBASE) VALUES (1300,13,'Measures','Ratios','','~','','O','','','','','','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
      (1400,14,'Ratios','Margin %','','+','T','X','','','','','Margin % Sales;','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
      (1500,15,'Ratios','Profit %','','~','T','X','','','','','Profit % Sales;','');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
      CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
      SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
      (1600,16,'Ratios','Profit per Ounce','','~','T','X','','','','',
        'Profit/@ATTRIBUTEVAL(Ounces);',''); 
    

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 Two Pass 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 Variance Reporting value:

TYPE

TYPE DESC

TYPE LONG DESCRIPTION

E

Expense

Treats as an expense item (applies to accounts dimensions only)

These are the valid Time Balance 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...

  1. Run the following scripts to create the SUPPLIER table. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2(). Following the scripts is a screenshot of the table:

    --This is the syntax in T-SQL
    create table SUPPLIER
    (
      SUPPLIERID         int                   not null,
      SUPPLIER_ALIAS     varchar(50)           null    ,
      ADDRESS            varchar(25)           null    ,
      CITY               varchar(25)           null    ,
      STATE              varchar(25)           null    ,
      ZIP                varchar(20)           null    ,
      COUNTRY            varchar(25)           null    ,
      constraint PK_SUPPLIER primary key (SUPPLIERID)
    )
    go
  2. Execute the following scripts to add data to the SUPPLIER table:

    INSERT INTO SUPPLIER(SUPPLIERID,SUPPLIER_ALIAS,ADDRESS,CITY,STATE,ZIP,COUNTRY)VALUES (1,'High Tech Drinks','1344 CrossmanAve','Sunnyvale','California','94675','USA');
    
    INSERT INTO SUPPLIER(SUPPLIERID,SUPPLIER_ALIAS,ADDRESS,CITY,STATE,ZIP,COUNTRY)VALUES (2,'East Coast Beverage','900 Long RidgeRd','Stamford','Connecticut','92001','USA');
    
    INSERT INTO SUPPLIER(SUPPLIERID,SUPPLIER_ALIAS,ADDRESS,CITY,STATE,ZIP,COUNTRY)VALUES (3,'Cool Canadian','1250 Boul ReneLevesque','Montreal','New York','H3B-W4B','Canada');
  3. Select from the SUPPLIER table to see the results:

    Select * From SUPPLIER;

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

  1. Execute the following script to add a column to the MEASURES table. Following the script is the screenshot of the table after the modification:

    --This is the syntax in T-SQL
    Alter Table MEASURES Add FORMULA_MDX VARCHAR(4000) NULL;
    --This is the syntax in PL\SQL
    Alter Table MEASURES ADD FORMULA_MDX VARCHAR2(4000) NULL;
    --Delete content of the table to avoid issues with executing this execise
    Delete From MEASURES;
  2. Execute the following script to add a row with the new formula:

    --This is the syntax for both T-SQL and PL\SQL
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX) Values(0, 14, 'Ratios', 'Margin %', '', '+', 'T', 'X', '', '', '', '', 'Margin % Sales;', '', '[Measures].[Sales] / [Measures].[Margin];');
    
    INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE, FORMULA_MDX) Values(0, 15, 'Ratios', 'Profit %', '', '~', 'T', 'X', '', '', '', '', 'Profit % Sales;', '', '[Measures].[Sales] / [Measures].[Profit];');

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

Adding and populating the Sort Order Column


In previous releases of Essbase, a developer had the option of building a hierarchy in ascending or descending alphabetical order via a build rule. If you wanted to sort the hierarchies in a different order, then you would go into Essbase Administrative Services (EAS). Then, open the outline, and drag and drop the members in the order that the business wanted or extract the dimension using an Outline Extract utility, sort the hierarchy, and use a build rule to rebuild the dimension. In contrast, when we are using Essbase Studio, in version 11.1.2.1, we are going to have to define the Sort Order in the relational environment. If you have the Oracle's data-governance software Data Relationship Management (DRM), this task will be handled there, but this recipe shows you how to load the Sort Order field with some SQL knowledge and Excel.

Getting ready

To get started, open SQL Server Management Studio, and add a database called TBC, if you have not already done it. In this recipe, we are using T-SQL and providing the PL\SQL equivalent where the syntax is different. You need to add a SCHEMA instead and use a tool like TOAD or Golden, if you are using Oracle. You should also open an Excel workbook.

How to do it...

  1. Execute the following query to create the YEARS table. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2():

    --For T-SQL user
    Create Table YEARS(
      YEARID       int         NOT NULL,
      YEAR         int         NULL,
      QUARTER      varchar(80)     NULL,
      MONTH        varchar(80)     NULL,
      MONTH_ALIAS  varchar(80)     NULL,
      Constraint PK_YEAR_YEARID Primary Key(YEARID Asc)
    ); 
  2. Execute the following script to add the SORT_ORDER column:

    --This is the syntax in T-SQL
    Alter Table YEARS Add SORT_ORDER INT NULL;
    --This is the syntax in PL/SQL
    Alter Table YEARS Add SORT_ORDER INTEGER NULL;
  3. Open Excel and enter the YEARS dimension's data starting with field A1, as follows:

  4. Sort the hierarchy manually, if it does not look right in the order specified in the preceding screenshot. Enter the number 1 in cell F1 and formula =F1+1 in cell F2.

  5. Click on cell F2, then click on the box to the right and bottom of the cell, and drag it down to cell F12.

  6. Enter the following concatenation string in cell G1, select G1, and press CTRL+C. Select range G2:G12, and press CTRL+V to paste the concatenation string:

    ="Insert Into TIME Values (" & A1 & ", " & B1 & ", '"& C1 & "', '" & D1 & "', '" & E1 & "', '" & F1 & "');"
  7. Copy range G1:G12, open up SQL Management Studio, connect to the TBC database, paste the range in the query window, and execute the following queries:

    Insert Into YEARS Values(1, 2011, 'QTR1 11', 'Jan 2011', 'January 2011', '1');
    Insert Into YEARS Values(2, 2011, 'QTR1 11', 'Feb 2011', 'February 2011', '2');
    Insert Into YEARS Values(3, 2011, 'QTR1 11', 'Mar 2011', 'March 2011', '3');
    Insert Into YEARS Values(4, 2011, 'QTR2 11', 'Apr 2011', 'April 2011', '4');
    Insert Into YEARS Values(5, 2011, 'QTR2 11', 'May 2011', 'May 2011', '5');
    Insert Into YEARS Values(6, 2011, 'QTR2 11', 'Jun 2011', 'June 2011', '6');Insert Into YEARS Values(7, 2011, 'QTR3 11', 'Jul 2011', 'July 2011', '7');
    Insert Into YEARS Values(8, 2011, 'QTR3 11', 'Aug 2011', 'August 2011', '8');
    Insert Into YEARS Values(9, 2011, 'QTR3 11', 'Sep 2011', 'September 2011', '9');
    Insert Into YEARS Values(10, 2011, 'QTR4 11', 'Oct 2011', 'October 2011', '10');
    Insert Into YEARS Values(11, 2011, 'QTR4 11', 'Nov 2011', 'November 2011', '11');
    Insert Into YEARS Values(12, 2011, 'QTR4 11', 'Dec 2011', 'December 2011', '12'); 
    

How it works...

The following are the steps in this recipe:

  1. We added the YEARS table to the TBC database.

  2. We added the SORT_ORDER column to the YEARS table.

  3. We added an integer used to sort the members.

  4. We also entered the YEARS dimension into an Excel sheet and sorted our YEARS hierarchy.

  5. After placing the SORT_ORDER into column F1, we pasted the correct SORT_ORDER and concatenate Insert statements together with the values in Excel.

  6. Finally, we used the Insert statements in the Excel workbook to update the YEARS table using the SQL Management Studio.

The following is what your YEARS hierarchy should look like without the SORT_ORDER column:

2011

  
 

QTR1 11

 
  

Feb 2011

  

Jan 2011

  

Mar 2011

 

QTR2 11

 
  

Apr 2011

  

Jun 2011

  

May 2011

 

QTR3 11

 
  

Aug 2011

  

Jul 2011

  

Sep 2011

 

QTR4 11

 
  

Dec 2011

  

Nov 2011

  

Oct 2011

Essbase Studio will enter February into the outline before January, May will be after June, August will be before July, and the fourth quarter will be completely out of order. For this reason, it is suggested that you add a SORT_ORDER column to all of your dimension tables.

See also

Refer to the Using Sort Order on data elements recipe in Chapter 2 to learn how to set the sort order for your metadata elements.

Adding tables for varying attributes


The varying attributes is an attribute dimension that maps to multiple dimensions. The concept of varying attributes in a relational environment is depicted by creating a mapping table. In this recipe, we will build a mapping table that joins the SALESMAN table to the Product and Market tables. We will also see how this format works for a varying attribute.

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.

How to do it...

  1. Create and populate a SALESMAN table with following script. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2():

    --This is the syntax in T-SQL
    Create Table SALESMAN(
      SALESMANID       int            NOT NULL,
      SALESMANNAME     varchar (80)   NULL,
      Constraint PK_SALESMAN_SALESMANID Primary Key (SALESMANID)
    );
    go
    --This scripts enter the data into the SALEMAN table
      Insert Into SALESMAN Values(1, 'John Smith');
      Insert Into SALESMAN Values(2, 'Jose Garcia');
      Insert Into SALESMAN Values(3, 'Johnny Blaze');
  2. Create and populate a PRODUCTS table:

    --This is the syntax in T-SQL
    Create Table PRODUCTS(
      PRODUCTID   int         NOT NULL,
      SKU         varchar(15) NULL,
      SKU_ALIAS   varchar(25) NULL,
      Constraint PK_PRODUCTS_PRODUCTID Primary Key (PRODUCTID)
    );
    --Insert data into PRODUCTS table
    Insert Into PRODUCTS Values(1, '100-10', 'Cola');
    Insert Into PRODUCTS Values(2, '100-20', 'Diet Cola');
    Insert Into PRODUCTS Values(3, '100-30', 'Caffeine Free Cola'); 
  3. Create and populate a MARKETS table:

    --This is the syntax in T-SQL
    Create Table MARKETS(
      STATEID       int           NOT NULL,
      STATE         varchar(25)   NULL,
      Constraint PK_MARKETS_STATEID Primary Key (STATEID)  
    );
    --Insert data into MARKETS table
    Insert Into MARKETS Values(1, 'New York');
    Insert Into MARKETS Values(2, 'Massachusetts');
    Insert Into MARKETS Values(3, 'Florida');
  4. Create and populate a SALESMANMAP table:

    --This is the syntax in T-SQL
    Create Table SALESMANMAP(
      SALESMANID   int NOT NULL,
      STATEID      int NOT NULL,
      PRODUCTID    int NOT NULL,
      Constraint PK_SALESMANMAP Primary Key (STATEID, PRODUCTID)  
    );
    
    --Insert data into the SALESMANMAP table
    Insert Into SALESMANMAP Values(1,1,2);
    Insert Into SALESMANMAP Values(1,2,3);
    Insert Into SALESMANMAP Values(2,1,1);
    Insert Into SALESMANMAP Values(2,2,2);
    Insert Into SALESMANMAP Values(3,3,1);
    Insert Into SALESMANMAP Values(3,3,3);
  5. Execute the following scripts to join the tables:

    Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_PRODUCTID Foreign Key(PRODUCTID)
    References PRODUCTS (PRODUCTID);
    
    Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_SALESMANID Foreign Key(SALESMANID)
    References SALESMAN (SALESMANID);
    Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_STATEID Foreign Key(STATEID)
    References MARKETS (STATEID);
  6. Execute the following query to see the relationship between the Market, Product, and Salesman:

    Select
      T4.SALESMANID, T2.SALESMANNAME, T4.STATEID, 
      T3.STATE, T4.PRODUCTID, T1.SKU_ALIAS
      From PRODUCTS T1, SALESMAN T2, MARKETS T3, SALESMANMAP T4
      Where T1.PRODUCTID = T4.PRODUCTID and
      T2.SALESMANID = T4.SALESMANID and
      T3.STATEID = T4.STATEID and
      T2.SALESMANNAME = 'John Smith'

How it works...

The relationships between the tables created and populated in steps 1 through 4 are shown in the following diagram:

The SALESMANMAP table joins the other three tables of which SALESMAN is the varying attribute. Salesman varies depending on the state and product you want to report on. We can retrieve the relationship between tables using the query shown in step 5. The results for this query are displayed in the following screenshot:

The important thing to note about this varying attribute example is that it is possible for two Salesmen to conduct business in the same Market, but it is not possible for the same two Salesmen to sell the same Product in the same Market. This logic is maintained by the constraints placed on the SALESMANMAP mapping table.

See also

Refer to the Adding tables to a Minischema recipe in Chapter 2 to add the varying attribute example to the TBC database. Refer to the Setting Essbase Properties recipe in Chapter 3 to learn how to set the varying attributes' properties.

Determining hierarchies in relational tables


In this recipe, we will determine hierarchies in relational models. This recipe will also go over some of the main attribute dimension types. Attribute dimensions are dynamic dimensions that allow users to report on their data without increasing the foot print of the database. Attributes work in a similar way to an alternate hierarchy, but unlike an alternate hierarchy you can use an attribute dimension to conduct cross tab reporting on a different axis than your base dimension.

Getting ready

To get started, open SQL Server Management Studio, and add a database TBC, or if you are using Oracle you can add schema TBC and use TOAD or Golden to complete the recipe.

How to do it...

  1. Execute the following script to add the Product Table. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2():

    --Create Product Table T-SQL
    create table PRODUCT
    (
      PRODUCTID          int                   not null,
      FAMILYID           int                   null    ,
      SKU                varchar(15)           null    ,
      SKU_ALIAS          varchar(25)           null    ,
      CAFFEINATED        varchar(5)            null    ,
      OUNCES             int                   null    ,
      PKGTYPE            varchar(15)           null    ,
      INTRODATE          datetime              null    ,
      constraint PK_PRODUCT primary key (PRODUCTID)
    )
    Go
  2. Execute the following queries to load data into the table you have just created. The SKUs do not repeat in this table, but as part of your discovery phase you should make sure that this is the case as duplicates will throw off your findings. The scripts that follow this step will help you do just that.

    --Insert values into product table. This syntax should work with either Pl/SQL or T-SQL
    Insert into PRODUCT Values(1 , 1 , '100-10', 'Cola', 'TRUE', 12, 'Can', 'Mar 25 1996 12:00AM');
    Insert into PRODUCT Values(2 , 1 , '100-20', 'Diet Cola', 'TRUE', 12, 'Can', 'Apr  1 1996 12:00AM');
    Insert into PRODUCT Values(3 , 1 , '100-30', 'Caffeine Free Cola', 'FALSE', 16, 'Bottle', 'Apr  1 1996 12:00AM');
    Insert into PRODUCT Values(4 , 2 , '200-10', 'Old Fashioned', 'TRUE', 12, 'Bottle', 'Sep 27 1995 12:00AM');
    Insert into PRODUCT Values(5 , 2 , '200-20', 'Diet Root Beer', 'TRUE', 16, 'Bottle', 'Jul 26 1996 12:00AM');
    Insert into PRODUCT Values(6 , 2 , '200-30', 'Sasparilla', 'FALSE', 12, 'Bottle', 'Dec 10 1996 12:00AM');
    Insert into PRODUCT Values(7 , 2 , '200-40', 'Birch Beer', 'FALSE', 16, 'Bottle', 'Dec 10 1996 12:00AM');
    Insert into PRODUCT Values(8 , 3 , '300-10', 'Dark Cream', 'TRUE', 20, 'Bottle', 'Jun 26 1996 12:00AM');
    Insert into PRODUCT Values(9 , 3 , '300-20', 'Vanilla Cream', 'TRUE', 20, 'Bottle', 'Jun 26 1996 12:00AM');
    Insert into PRODUCT Values(10, 3 , '300-30', 'Diet Cream', 'TRUE', 12, 'Can', 'Jun 26 1996 12:00AM');
    Insert into PRODUCT Values(11, 4 , '400-10', 'Grape', 'FALSE', 32, 'Bottle', 'Oct  1 1996 12:00AM');
    Insert into PRODUCT Values(12, 4 , '400-20', 'Orange', 'FALSE', 32, 'Bottle', 'Oct  1 1996 12:00AM');
    Insert into PRODUCT Values(13, 4 , '400-30', 'Strawberry', 'FALSE', 32, 'Bottle', 'Oct  1 1996 12:00AM');
  3. Execute the following script to determine the SKU count in the PRODUCT table:

    --Retrieve SKU count for both PL\SQL and T-SQL
    Select Count(SKU) From PRODUCT;
  4. Execute the following scripts to determine the cardinality between the SKU and the CAFFEINATED columns:

    --Determine if there is one-to-many relationship between SKU and Caffeinated for both PL\SQL and T-SQL
    Select SKU, Count(CAFFEINATED) As Cnt 
      From PRODUCT 
      Group By SKU 
      Having Count(CAFFEINATED) = 1;
    
    Select CAFFEINATED, Count(SKU) As Cnt 
      From PRODUCT T1
      Group By CAFFEINATED;
  5. Execute the following scripts to determine the cardinality between the SKU and the OUNCES columns:

    --Determine if there is one-to-many relationship between SKU and Ounces for both PL\SQL and T-SQL
    Select SKU, Count(OUNCES) As Cnt
      From PRODUCT 
      Group By SKU 
      Having Count(OUNCES) = 1;
    
    Select OUNCES, Count(SKU) As Cnt
      From PRODUCT 
      Group By OUNCES;
  6. Execute the following scripts to determine the cardinality between the SKU and PKGTYPE columns:

    --Determine if there is one-to-many relationship between SKU and PKGTYPE for both PL\SQL and T-SQL
    Select SKU, Count(PKGTYPE) As Cnt
      From PRODUCT 
      Group By SKU 
      Having Count(PKGTYPE) = 1;
    
    Select PKGTYPE, Count(SKU) As Cnt
      From PRODUCT
      Group By PKGTYPE;
  7. Execute the following script to determine the cardinality between the SKU and INTRODATE columns:

    --Determine if there is one-to-many relationship between SKU and IntroDate for both PL\SQL and T-SQL
    Select SKU, Count(INTRODATE) As Cnt
      From PRODUCT 
      Group By SKU 
      Having Count(INTRODATE) = 1;
    
    Select INTRODATE, Count(SKU) As Cnt
      From PRODUCT 
      Group By INTRODATE;
  8. Execute the following scripts to determine the cardinality between the SKU and PRODUCTID columns:

    --Determine if there is a relationship between SKU and PRODUCTID for both PL\SQL and T-SQL
    Select SKU, Count(PRODUCTID) As Cnt
      From PRODUCT 
      Group By SKU 
      Having Count(PRODUCTID) = 1;
    
    Select PRODUCTID, Count(SKU) As Cnt
      From PRODUCT 
      Group By PRODUCTID; 
    
  9. Execute the following scripts to determine the cardinality between the PRODUCTID and FAMILYID columns:

    --Determine if there is one-to-many relationship between PRODUCTID and FAMILYID for both PL\SQL and T-SQL
    Select PRODUCTID, Count(FAMILYID) As Cnt
      From PRODUCT 
      Group By PRODUCTID 
      Having Count(PRODUCTID) = 1;
    
    Select FAMILYID, Count(PRODUCTID) As Cnt
      From PRODUCT 
      Group By FAMILYID;

How it works...

The first and second steps setup a Product Table and populate it with data. The third step retrieves a count of the SKUs in your Product table. You will need this count to determine if the attribute column has a one-to-many relationship with the SKU. This query returned exactly 13 valid SKUs.

The first script in step 4 returns a row for every time the SKU rolls up to one parent. You can see in the following screenshot, that there are exactly 13 rows signifying that each SKU rolls up to one CAFFEINATED value.

This query returns the value, as shown in the following screenshot:

Select SKU, Count(CAFFEINATED) As Cnt 
  From PRODUCT 
  Group By SKU 
  Having Count(CAFFEINATED) = 1;1;

The second script in step 4 shows you that the CAFFEINATED column has a true and false value, which makes this column a good candidate for a Boolean attribute dimension. The reason this is a Boolean attribute is because it has exactly either of the two values TRUE or FALSE. The counts from this query and the previous one shows you that there are one-to-many relationships as SKUs rollup to only one CAFFEINATED value, but a single CAFFEINATED value can have many SKU children.

This script returns TRUE and FALSE values as depicted in the following screenshot:

Select CAFFEINATED, Count(SKU) As Cnt 
  From PRODUCT T1
  Group By CAFFEINATED;

The scripts in step 5 show a one-to-many relationship between SKU and OUNCES. Script 2 of step 5 shows that OUNCES will make a good candidate for a NUMERIC attribute dimension. A NUMERIC attribute type is as the word implies a number. We can use a NUMERIC attribute's value in calculating scripts or outline formulas to create formulas based on the attribute assigned.

This query returns a list of numeric values as depicted in the following screenshot:

Select OUNCES, Count(SKU) As Cnt
  From PRODUCT 
  Group By OUNCES;

The scripts in step 6 show a one-to-many relationship between SKU and PKGTYPE as it returns exactly 13 unique records. Script 2 of step 6 shows the PKGTYPE that would make a good TEXT attribute or an alternate dimension. A TEXT attribute allows for a comparison in calculations and a selection of a member based on the attribute assigned. Text attributes are the default attribute in Essbase.

This query returns a list of text values as depicted in the following screenshot:

Select PKGTYPE, Count(SKU) As Cnt
  From PRODUCT 
  Group By PKGTYPE;

The scripts in step 7 also show a one-to-many relationship between SKU and INTRODATE. Script 2 of step 7 shows that INTRODATE is, as the name implies, a date and as such will make a good DATE attribute dimension. You can use DATE attributes in calculation as well. We can compare different Products, for example, based on the DATE attribute for specific Market. Date attributes from January 1, 1970 through January 1, 2038 are supported.

This query returns a list of date values as depicted in the following screenshot:

Select INTRODATE, Count(SKU) As Cnt
  From PRODUCT 
  Group By INTRODATE;

Moreover, in step 8 you will see a one-to-one relationship between SKU and PRODUCTID, which means there is exactly one PRODUCTID for each SKU, and one SKU will rollup to exactly one parent. Script 2 of step 8 shows that this will more than likely be the main rollup in your PRODUCT dimension. This is also intuitive as the SKU in this case is the column representing the product:

Finally, the relationship between FAMILYID and PRODUCTID is a one-to-many relationship, shown in step 9. A possible outcome of these findings is to create a dimension and hiearchy with three generations as depicted in the following table:

DIMENSION

GENERATION

LEVEL

PRODUCT

1

2

FAMILYID

2

1

SKU

3

0

There's more...

In real world situations, you may come across scenarios where your relationships are not always one-to-one or one-to-many. Keep a note of these exceptions as you may need to concatenate columns, create alternate rollups with shared members, add a prefix or suffix, create a new dimension, or turn on duplicate members in your outline in order to deal with these issues. We will go over some of these in other chapters. That said, the preceding scripts will help you see these discrepancies before you begin building your cube, which will save you time later in your development. In addition, note that when building a BSO cube you can only assign attributes to a sparse dimension.

See also

Refer to the recipes Adding attribute dimensions to hierarchies and Setting Essbase Properties in Chapter 3 to learn how to set up attribute dimensions and their properties in Essbase Studio.

Using the Essbase Outline Extractor to extract dimensions


In this recipe, we are going to use the Essbase Outline Extractor to extract the meta-data from the Sample Basic database in parent-child format. The Essbase Outline Extractor is a free product and can be downloaded from AppliedOLAP's website: http://www.appliedolap.com/free-tools/outline-extractor. The version you should consider for Essbase 11.1.2.1 for now is essbase_outline_extractor_11.1.2. This tool can assist you in extracting your metadata from an existing Essbase cube. We can then use its output to load Data Relationship Management (DRM) or a relational environment.

Getting ready

To get started, click on the Start menu to open the Essbase Outline Extractor.

How to do it...

  1. Click on Programs and select olapunderground | Essbase Outline Extractor | Essbase Outline Extractor.

  2. Click on the Do not show this message again checkbox and click on Proceed if you get the following prompt:

  3. You should now see the extract utility, as follows:

  4. Click on the Login button on the top left and enter the server you want to log in to. Enter your username and password.

  5. Click on the Sample.Basic application and database, and click on the OK button.

  6. You should now have the option to select a dimension from the Select Dimension combo box. Click on the Product dimension.

  7. Click on the Load File Format radio button and the Field Options command button. Select all the checkboxes except Currency Name/Category in this menu as it is not applicable in this case.

  8. Click on the Browser button circled in the following screenshot and select a file location and filename.

  9. You have the option to change the file's delimiter as well, but the exclamation mark (!) character should work fine.

  10. Click on the Export button.

  11. A file should be produced in the location you specified, delimited by the bang or exclamation character, and in a parent-child format. The column headers in this file should be as follows:

    COLUMN HEADERS

    PARENT0,Product

    CHILD0,Product

    ALIAS0,Product

    PROPERTY0,Product

    FORMULA0,Product

    OUNCES0,Product

    CAFFEINATED0,Product

    PKGTYPE0,Product

How it works...

In this recipe, we used the Essbase Outline Extractor to extract the metadata for the Product dimension into a text file. This tool works with both the Block Storage and Aggregate storage model, but may have issues when the dimension member count is very large.

There's more...

You can also use the tool to extract dimensions in Generation, Level, and Documentation Format. It is encouraged that you try the different options to see what format best fits your needs.

Using Star Analytics to build your star schema from existing Essbase cubes


In this recipe, we will be using a third party tool called Star Integration Server Manager to build a star schema from an existing cube. The purpose of using this tool in this recipe is to quickly move the meta-data of your Essbase, Planning, or HFM applications to a relational environment. The tool we will be using is the express version and can be downloaded from the Star Analytics website: http://www.staranalytics.com/products/index.htm.

Getting ready

To get started, open Microsoft SQL Server and add a database called BASIC. Microsoft SQL Server is being used for this example, but you can use Oracle, Sybase, Teradata, DB2, MySQL, or a text file to extract your outline.

How to do it...

  1. Click on Programs and select Star Analytics | Star Integration Server Manager.

  2. Double-click on Connection Manager on the left-hand side, and click on the New Connection button. The New HyperionEssbase Connection menu will appear on the screen, as follows:

  3. Enter a Sample for your Connection Name and enter your Essbase Server.

  4. Enter a Sample for the Application parameter value, Basic for the Database parameter value, Basic for your Outline parameter value, and your User Name and Password. Click on the Test button to test your connection, click the OK button on the pop up menu, and click on the Save and Close button.

  5. Double-click on Connection Manager, select SQL Server using SQL Server login in the Connection Type, and enter Basic for the Name.

  6. Enter your SQL Server name into the parameter value, Basic for the Database parameter, and enter your Login Name and Password.

  7. Click on the Test button to test your connection, click the OK button on the popup menu, and click the Save and Close button.

  8. Click on the New Essbase Selection button and select the Selection Information tab. In the Source Connection drop-down select Sample: Hyperion Essbase, and in the Target Connection drop-down select Basic: SQLServer-SQLLogin.

  9. Check all the Export checkboxes in the Column Selections grid, and check on the Spin checkbox for Measures. Your screen will look like the following screenshot:

  10. Click on the Run button in the Run Selection prompt and select Yes to run the Current Selection. Click on the OK button in the Run Completion on the pop up.

  11. Verify that all your tables were created in the Running Essbase Selection in the local dialog box, and click on the Close button. Your screen should look as follows:

How it works...

In this recipe, we created a connection to our relational database and a connection to Essbase. We then used the tool to define the dimensions we wanted to include in our star schema. Finally, we created a star schema using the Sample Basic database with our selections. You should see six tables in your Basic relational database. The results of this exercise are available in script 3265_01_08_tsql.sql.

See also

The Sample Basic database comes with the Essbase installation, but if you would like to build it, refer to the Adding an Application and Database on an Essbase server recipe in Chapter 5.

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
Estimated delivery fee Deliver to United States

Economy delivery 10 - 13 business days

Free $6.95

Premium delivery 6 - 9 business days

$21.95
(Includes tracking information)

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 Print?

Product feature icon Instant access to your digital copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Redeem a companion digital copy on all Print orders
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Estimated delivery fee Deliver to United States

Economy delivery 10 - 13 business days

Free $6.95

Premium delivery 6 - 9 business days

$21.95
(Includes tracking information)

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 $5 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 $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 131.98
Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial
$65.99
Oracle Essbase 11 Development Cookbook
$65.99
Total $ 131.98 Stars icon

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 the digital copy I get with my Print order? Chevron down icon Chevron up icon

When you buy any Print edition of our Books, you can redeem (for free) the eBook edition of the Print Book you’ve purchased. This gives you instant access to your book when you make an order via PDF, EPUB or our online Reader experience.

What is the delivery time and cost of print book? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela
What is custom duty/charge? Chevron down icon Chevron up icon

Customs duty are charges levied on goods when they cross international borders. It is a tax that is imposed on imported goods. These duties are charged by special authorities and bodies created by local governments and are meant to protect local industries, economies, and businesses.

Do I have to pay customs charges for the print book order? Chevron down icon Chevron up icon

The orders shipped to the countries that are listed under EU27 will not bear custom charges. They are paid by Packt as part of the order.

List of EU27 countries: www.gov.uk/eu-eea:

A custom duty or localized taxes may be applicable on the shipment and would be charged by the recipient country outside of the EU27 which should be paid by the customer and these duties are not included in the shipping charges been charged on the order.

How do I know my custom duty charges? Chevron down icon Chevron up icon

The amount of duty payable varies greatly depending on the imported goods, the country of origin and several other factors like the total invoice amount or dimensions like weight, and other such criteria applicable in your country.

For example:

  • If you live in Mexico, and the declared value of your ordered items is over $ 50, for you to receive a package, you will have to pay additional import tax of 19% which will be $ 9.50 to the courier service.
  • Whereas if you live in Turkey, and the declared value of your ordered items is over € 22, for you to receive a package, you will have to pay additional import tax of 18% which will be € 3.96 to the courier service.
How can I cancel my order? Chevron down icon Chevron up icon

Cancellation Policy for Published Printed Books:

You can cancel any order within 1 hour of placing the order. Simply contact customercare@packt.com with your order details or payment transaction id. If your order has already started the shipment process, we will do our best to stop it. However, if it is already on the way to you then when you receive it, you can contact us at customercare@packt.com using the returns and refund process.

Please understand that Packt Publishing cannot provide refunds or cancel any order except for the cases described in our Return Policy (i.e. Packt Publishing agrees to replace your printed book because it arrives damaged or material defect in book), Packt Publishing will not accept returns.

What is your returns and refunds policy? Chevron down icon Chevron up icon

Return Policy:

We want you to be happy with your purchase from Packtpub.com. We will not hassle you with returning print books to us. If the print book you receive from us is incorrect, damaged, doesn't work or is unacceptably late, please contact Customer Relations Team on customercare@packt.com with the order number and issue details as explained below:

  1. If you ordered (eBook, Video or Print Book) incorrectly or accidentally, please contact Customer Relations Team on customercare@packt.com within one hour of placing the order and we will replace/refund you the item cost.
  2. Sadly, if your eBook or Video file is faulty or a fault occurs during the eBook or Video being made available to you, i.e. during download then you should contact Customer Relations Team within 14 days of purchase on customercare@packt.com who will be able to resolve this issue for you.
  3. You will have a choice of replacement or refund of the problem items.(damaged, defective or incorrect)
  4. Once Customer Care Team confirms that you will be refunded, you should receive the refund within 10 to 12 working days.
  5. If you are only requesting a refund of one book from a multiple order, then we will refund you the appropriate single item.
  6. Where the items were shipped under a free shipping offer, there will be no shipping costs to refund.

On the off chance your printed book arrives damaged, with book material defect, contact our Customer Relation Team on customercare@packt.com within 14 days of receipt of the book with appropriate evidence of damage and we will work with you to secure a replacement copy, if necessary. Please note that each printed book you order from us is individually made by Packt's professional book-printing partner which is on a print-on-demand basis.

What tax is charged? Chevron down icon Chevron up icon

Currently, no tax is charged on the purchase of any print book (subject to change based on the laws and regulations). A localized VAT fee is charged only to our European and UK customers on eBooks, Video and subscriptions that they buy. GST is charged to Indian customers for eBooks and video purchases.

What payment methods can I use? Chevron down icon Chevron up icon

You can pay with the following card types:

  1. Visa Debit
  2. Visa Credit
  3. MasterCard
  4. PayPal
What is the delivery time and cost of print books? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela