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

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

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.

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.

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

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

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');

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.

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

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;

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

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;

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);

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:

Set up in SOLVE_ORDER in increments of tens or twenties for clarity and consistency.
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...

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)
);

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;

Open Excel and enter the YEARS dimension's data starting with field A1, as follows:
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.
Click on cell F2, then click on the box to the right and bottom of the cell, and drag it down to cell F12.
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 & "');"
```

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:

We added the YEARS table to the TBC database.
We added the SORT_ORDER column to the YEARS table.
We added an integer used to sort the members.
We also entered the YEARS dimension into an Excel sheet and sorted our YEARS hierarchy.
After placing the SORT_ORDER into column F1, we pasted the correct SORT_ORDER and concatenate Insert statements together with the values in Excel.
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.

Filter reviews by

All

Amazon verified reviews

Essbase Jan 30, 2012

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

Gokul Patel Mar 23, 2012

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.

jgomez16 Mar 09, 2012

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!

Jason Jones Mar 17, 2012

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.

David Feb 22, 2015

Its an okay book to find things, but certainly not something you would read cover to cover.

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

What do you get with eBook?

Contact Details

Billing Address

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with eBook?

Contact Details

Billing Address

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

Filter reviews by

People who bought this also bought

About the author

FAQs