Oracle Essbase Studio Deep-Dive

By Christian Screen

Dive into a step-by-step tutorial that reflects real-world data warehousing complexities.

Published March 2010

As Oracle's acquisition of Hyperion solidified in 2007, integrating Essbase tools into Oracle BI has become part of Oracle's roadmap. Essbase Studio is the part of that roadmap which provides users with a graphical interface to develop, deploy, and maintain Essbase OLAP cubes based on one or more data sources.

Before Oracle Essbase Studio 11 was released, in order to accomplish this, a combination of Essbase Integration Services (EIS) and Essbase Administration Services (EAS) was required. Now, only Essbase Studio is needed to build a multidimensional model from data sources that range from relational database tables, views, and flat files to OBIEE and EPMA dimensions. In addition, Essbase Studio makes use of several wizard-driven interfaces, seeking to make operations easy for end users to increase tool adoption.

Anyone associated with the Business Intelligence and EPM realm should pay close attention to the emergence of Essbase Studio as the integration of Essbase cubes into Oracle BI tools such as OBIEE has already begun. Leveraging the power of this tool's "one stop shop" for multidimensional development adds value to analysis efforts from both a developer and analyst perspective. With the ability to present data quickly via the Essbase OLAP engine as well as to build detailed drill-through reporting, Essbase Studio is here to stay.

However, the default Essbase Studio examples that are included with the Essbase Studio application are limited and don't support true real world scenarios.  In addition, the examples are pre-built and no direction is available on how to build an Essbase Studio model, step-by-step.

  This tutorial leverages a completed data warehouse based on an e-commerce store, Adventure Works, that sells bicycles and bicycle accessories.  For those of you familiar with data warehousing this data is easily recognizable as the data warehouse created and used for years in other analytical reporting and modeling applications.  We have leveraged this data as it is complete, well-defined, familiar to most, and it contains real-world data warehousing complexities. As part of the tutorial you will be required to download the Oracle DDL and DAL scripts that create a user/scheme and load the sample data warehouse data on to your Oracle RDBMS instance.

For the best comprehension of the tool, this tutorial should be completed in one sitting. During the tutorial it is recommended to complete the steps outlined first, then review each section to gain further understanding of any area for which you may have more curiosity. 

 

Requirements


In order to use this tutorial the following services must be started and functioning properly.
  • Shared Services
  • Essbase Services
  • Essbase Studio Service
  • Essbase Administration Services (EAS)  (needed for running calc, and viewing outline)
  • Oracle RDBMS

 

Projected Completion Time: Approx. 1.5 Hours

  • Download/Set-up Example Database: 10 minutes
  • Building a MiniSchema: 15 minutes
  • Building Metadata Dimension Elements: 15 minutes
  • Building Metadata Hierarchies: 30 minutes
  • Setting Essbase Model Properties: 10 minutes
  • Deploying Cube Wizard: 5 minutes


Setting-Up the Sample Data Source

In this section we will install and implement an Oracle data warehouse (and RDBMS if not already installed on your machine or accessible).  Oracle SQL*Plus scripts will be executed to create a new user scheme called AdvWorks and populate database table objects with data.  These scripts will prepare the data source for use in Essbase Studio.

If you currently have an Oracle RDBMS instance ready for use then you may use it.  Otherwise, you may download the Oracle Database 10g Express Edition from here.

Installing the database is beyond this tutorial but plenty of documentation is found on Oracle’s website.

You may also wish to have a graphical interface to view the AdvWorks scheme tables and data after tutorial data load scripts have been run.  You may download Oracle SQLDeveloper from here if you do not currently have a GUI for managing the database.


Pre-Requisites & Tutorial File Download

For this tutorial you should have an accessible instance of an Oracle RDBMS available.  The tutorial scripts also assume that you have the ability to login with the sysdba role privilege.  Finally, because the Essbase Studio console will be accessed, you will need a Shared Services user account that is provisioned to access Essbase Studio with a minimum of cpAdmin.  These high-level permissions are not examples of a production environment; they merely allow the tutorial to proceed smoothly without worrying for lack of accessibility issues.

Download the tutorial files necessary for this tutorial from here (URL TBD). Be sure to read the _Read_Me.txt file when this download is unzipped.


Install Tutorial Files/Data Procedure

  1. Unzip the tutorial files into a directory of your choosing on your local machine:
    1. Ex: C:\Tutorial\
    2. The zip file will then extract and create the following directory:
      1. C:\Tutorial\Oracle_Essbase_Studio_Tutorial\
  3. Read the full installation instructions in the _Read_Me.txt file.

Quick Install Instructions
  1. Open Command-Line Prompt
  2. Change the directory to the directory under which you have unzipped the download (ex: cd c:\downloads\oracle_essbase_studio_tutorial\).
  3. Enter SQLPLUS in the command-line to start SQL*PLUS
  4. Connect using a SysDBA account
  5. Enter @AdvWorks_Create_User_Script.sql;
  6. Enter @AdvWorks_Build_Data.sql;
    1. This should take approximately 5-8 minutes to run on most machines.

 

At this point you have installed all necessary schemes and scheme table objects required for the Oracle RDBMS to serve as the relational database for the tutorial. 

If you wish, you may familiarize yourself with the data warehouse objects now available in the AdvWorks scheme.  If you haven’t installed Oracle SQL Developer, now is a good time to do so. Of course, you can always use SQL*Plus to list the objects, etc.

Essbase Studio – Create a Data Source


At the core of Essbase Studio (EStudio) are its data sources.  A data source can be a text file (w/caveats), a CSV file (not MS Excel), OBIEE, DRM, or a Relational Database.  In this section we are going to leverage our Oracle Database Scheme AdvWorks to build a data source. 

Launch Essbase Studio

Before launching Essbase Studio please ensure that all software dependencies listed in the introduction section of this document have been met and all services started. 
The easiest way to launch EStudio is from a shortcut (if available).  However, you may use the program structure by Start > All Programs > Oracle EPM System > Essbase > Essbase Studio > Essbase Studio Console.
Once EStudio has been launched all designer elements are visible through the application navigation layout. For this section we will focus on creating a data source. We will expose the other application elements in future sections.

Create Data Source Procedure (Start MiniSchema)


  1. Select the Data Source… option from File > New on the file menu bar
  1. On the Connection Wizard Prompt complete the fields with the following information
    1. Connection Name = AdventureWorks
    2. Data Source Type = Oracle
    3. Server Name = localhost, or the name of the server where your DB resides (The name of my machine is “demomachine”).
    4. User Name = AdvWorks
    5. Password = Oracle1
    6. SID = XE (or, configure the TNS, etc. that connects to your db instance)
  1. Test the connection and ensure that the connection is successful.  If not, make sure that your username and password are set correctly.  If the AdvWorks user is causing problems you may wish to create a new user with read privileges and use the new user credentials here.
  1. Click Next
  1. On the Select Tables set-up step, import all objects except for the following:
    1. DimEmployee
    2. DimReseller
    3. DimSalesReason

 
  1. Click Next.
  1. On the Select minischema step choose Create a new schema diagram. The default name appears in the field, so do not change it.
    1. UnCheck Use Introspection to Detect Hierarchies
      1. This causes a default wizard operation to produce all dimension elements and hierarchies for us which we don’t want.
  1. Click Next.
  1. In the Populate Minischema set-up page ensure all Available tables (left) are placed into the Tables in Schema section (right)
  1. Click Finish.
  2. Click OK on the “Data Source created successfully” prompt.

We successfully opened Essbase Studio and created a Data Source.  In addition we created a minischema based on the data source.  What this section showed you was how wizard driven Essbase Studio is. It also showed you from the connection wizard that you as a modeler have control over how the modeling elements are built.  We set out to create a data source, and in the second step of the Connection Wizard, after selecting tables, we could have simply clicked the Finish button to complete the data source set up.  Instead we went on to the “select a minischema” step which could have been skipped entirely or started from another wizard in EStudio at a later time. We now have a data source and a minischema to work with. Let’s continue.


Chillin’ with the MiniSchema

The MiniSchema may be best described as an Entity-Relationship-Diagram (ERD) that federates and plays nicely with other data sources.  You may model a minischema using multiple fact tables and/or multiple data sources (which should relate in some way) in order to create the relationships that will eventually become an Essbase model/outline.  You can also have multiple minischemas depending on what your final Essbase models’ business needs are.  In this section we’ll only inspect the minischema and use the auto layout option just to get our feet wet.  If this were classroom training we would dive deeper into minischemas with more detail.

MiniSchema Window Layout

The first thing to notice in our EStudio environment after finishing the connection wizard is the minischema layout that appears.  One should note that in a well prepared datawarehouse such as the one provided by AdvWorks scheme that EStudio automatically recognizes the explicit foreign key / primary key relationships and creates our joins (i.e. 1.n, 1.1, etc.) for us.

In our example and for the sake of this tutorial our modeling is done.  But to get an understanding of how we would use a flat file source, etc. that doesn’t have FK/PK relationships, right-click anywhere in the minischema to see the options available to you.

While you have the options window open, select the Lay out schema option.  As you can see, clicking this option conducts auto formatting for the layout of your minischema diagram.  This is a handy option to ensure the best visibility of your objects when modeling.

Organizing Metadata


This is a quick section aiming to relay what should be a best practice – organizing metadata. All metadata, which includes hierarchies, Essbase models, cube schemas, dimension elements, derived text measures, and drill-through reports are considered metadata.  Since EStudio can house multiple objects of each kind of metadata across subject areas, etc. it should be common-sense to organize the Metadata Navigator with a logical structure.  In this section we will organize a folder structure for our AdventureWorks analysis so that we isolate it from any other projects that arise down the road.

In EStudio locate the Metadata Navigator pane on the left-side of the EStudio application to begin.

Metadata navigator Structuring Procedure


  1. Right-click on the Root folder and select New > Folder

  1. In the Name field for the Properties prompt enter AdventureWorksAnalysis and click OK.

 
  1. Right-Click on the AdventureWorksAnalysis folder and create four new child folders under it with the following names:
    1. Dimension Elements
    2. Hierarchies
    3. Cube Schemas
    4. Drill-Through Reports

 

When complete it should look like the image below.

 

Dimension Elements

After creating the Metadata Navigator structure in the last section we now need to fill it with meaningful content to continue modeling our project.  In this section we will add Dimension Elements to our Metadata.  Dimension Elements are best defined as being analogous to what we recognize as members in the OLAP world.  There are two ways to add Dimension Elements to our metadata. The first is to drag a column from a data source.  The second, is to right-click on a Metadata Navigator folder and select New > Data Element.  We will be using both.

Populate Dimension Elements Procedure

Since Dimension Elements are similar to OLAP members we only need to select a few from our data source columns.  We do not need every column from every table that we have brought over in our data source. To get started, set up your environment so that both the MetaData Navigator (left-side) and the Data Sources pane (right-side) can be seen simultaneously and follow below:

  1. Expand Data Sources > AdventureWorks > DimScenario.  Select ScenarioName and drag it over to the AdventureWorksAnalysis > Dimension Elements folder.

 
  1. Now continue to move more data source columns to the Dimension Elements folder by using the table/object list below:

  2.  

    DimCurrency
    -CurrencyName

    DimGeography
    - EnglishCountryRegionName
    - City
    - StateProvinceName

    DimProduct
    -Class
    -Color
    -SizeActual
    -EnglishProductName

    DimProductCategory
    - EnglishProductCategoryName

    DimProductSubCategory
    - EnglishProductSubCategoryName

    DimPromotion
    -EnglishPromotionCategory
    -EnglishPromotionName

    DimSalesTerritory
    -SalesTerritoryCountry
    -SalesTerritoryRegion
    -SalesTerritoryGroup

    DimTime
    - CalendarYear
    - CalendarQuarter
    -EnglishMonthName
  1. Your completed Dimension Elements folder so far should appear as the image below:

As you have probably noticed, we have yet only to move data source columns which describe the data into the dimensional elements folder.  What about Time and the Data?  These elements must also be placed as Dimension Elements and we start below with Time Dimension Elements. Please note that setting up time or an account structure here does not define any relation to the final Essbase outline Account or Time dimension labeling.

Populate Time Dimension Elements

Below we will continue with the dragging of data source columns to the Data Elements folder exercise as above.

  1. From the data sources panel under AdventureWorksDW > DimTime, drag FullDateAlternateKey to the Dimension Elements folder.

 
  1. Right-click FullDateAlternateKey and select Edit…
  1. In the Properties prompt change the Name field to Year.
  2. In the Caption Binding field delete the “ .toString” text from end of the existing text as this is not needed.  Move the cursor to the beginning of the field in preparation for the next step.
  1. Select the Functions tab on the formula panel and select SQL > Date > year
  3. Click the arrow button and move the year selection to the Caption Binding field


  1. Remove the $$DateOperand$$ text then cut (ctrl + x) the full blue text. “ connection….fullDateAlteratekey’” that was already there and paste (ctrl + v) it inside the parenthesis.
  1. In order to sort this column properly later in the time hierarchy we need to move the T imeKey column from the data source to the Sort Order section.  On the Source tab select DimTime > TimeKey and use the arrow button to move it.
  1. Click OK to close the Properties prompt.
  1. We just completed the creation of the year level dimension element from a source column.  Now we will create a shell Dimension Element without dragging a column from the data source.

Right-click on the Dimension Elements folder and select New > Dimension Element
  1. In the Properties prompt type Quarter into the Name field.
  1. Select the Functions tab, select SQL > Date > quarterAsString and use the arrow to move this function into the Caption Binding field.
  1. Highlight the text $$DateOperand$$, select the Source tab and pick the data source column DimTime > FullDateAlternateKey. Click the selection arrow. Using the selection arrow should overwrite the $$DateOperand$$ text with the full blue identifier string for the selected DimTime column.
  1. Select the column CalendarQuarter from the same dimension, DimTime, and move this column into Sort Order field.
  2. Click Apply and then click OK.

 

 
  1. Complete steps 8 through 11 for the Month dimension element.  Replace the function quarterAsString with monthShortName.  No sort column is necessary.

This above approach to looking at creating time related Dimension Elements was so that you could understand that functions are available to modify the captions and logic for each element.  However, there is a better and cleaner way to create Time dimension elements.  Here are the quick steps for EStudio mastery:

  1. Delete the Year, Quarter, and Month dimension elements you just created. It’s okay, it won’t hurt. (right-click each and select delete).
  1. From the Data Sources pane drag DimTime > FullDateAlternateKey over to the AdventureWorksAnalysis > Dimensions Elements folder again.
  1. Right-Click on the FullDateAlternateKey element and select Create Date Elements…
  1. In the prompt check the checkboxes for Year, Quarter, and Month.
  1. Click OK.  As you will see the time related dimension elements were created for you automatically simply because EStudio recognized FullDateAlternateKey as a date/timestamp data type.  If you wish to inspect the new date elements you will see they are just like the ones we previously created with the exception of the Sort Order column.

Populate Data (Measures) Dimension Elements

We are just about finished with setting up the dimension elements. 
Drag the following elements from our data source AdventureWorksDW > FactInternetSales over to the Dimension Elements table.

  • DiscountAmount
  • SalesAmount
  • TaxAmt

Your final Dimension Elements folder should appear similar to the above image.

Hierarchy Building


In this section we aim to build several hierarchies using dimension elements from the previous section.  It is important to note that these hierarchies will become the basis for our Essbase outline dimensions. Creating hierarchies is a drag-and-drop operation.  One can even create alternate hierarchies. Our example stays basic and creates only a single hierarchy for each.

How to Help a Hierarchy Happen?

Follow the steps below.

  1. In the metadata Navigator right-click on the AdventureWorksAnalysis > Hierarchies folder and select New > Hierarchy.
  1. In the new hierarchy window type Product in the name field.
  1. Drag the following dimension elements into the Hierarchy data column line by line in the following order:
    1. EnglishProductCategoryName
    2. EnglishProductSubCategoryName
    3. EnglishProductName
  1. Utilize the “Move Level Right” arrow button to move the EnglishProductSubcategoryName and EnglishProductName in a staggered fashion under EnglishProductCategoryName.
  1. Click Save and then Preview…

Here you see a representation of the hierarchy as it will appear in your Essbase cube.
  1. Conduct the steps 1 through 5 to create the following hierarchy names with following Dimension Elements and structure:
    • Geography
      • EnglishCountryRegionName
        • StateProvinceName
          • City
    • Promotion
      • EnglishPromotionCategory
        • EnglishPromotionName
    • SalesTerritory
      • SalesTerritoryGroup
        • SalesTerritoryCountry
          • SalesTerritoryRegion
    • Time
      • Quarter
        • Month
    • Year
      • Year

 

 
  1. Your final Hierarchies folder should appear similar to the following:

Using the Create Date Elements approach saves us time initially but you will see as we continue that the sorting of members is not correct since the latter option does not automatically generate the Sort Order field value(s).  We can come back and do this later but you now know how to do this from the manual operations we started off with for creating our Time dimension elements.

Create a Cube Schema


So far we’ve been modeling the metadata that our cube/outline will use to generate itself.  The MiniSchema, Dimension Elements, and Hierarchies have placed us just within reach of realizing our goal of creating a cube.  This section takes a quick, no frills approach to moving our progress along.  

Scheming on the Schema


  1. In the Metadata Navigator under our AdventureWorksAnalysis folder, right-click on the Cube Schemas folder and select New > Cube Schema
  1. In the cube Schema Name field type AdventureWorksCubeSchema.

    From the AdventureWorksAnalysis folder move all hierarchies except Geography into the Hierarchies section and all metric Dimension Elements (DiscountAmount, SalesAmount, and TaxAmt) into the Measures section.
  1. Click Next
  1. In the Cube Schema options prompt check Create Essbase Model and type AdventureWorksCubeSchemaModel in the model name field (default).
  1. Click Finish
  1. Your Essbase schema diagram will appear immediately and each dimension shows the hierarchies it has available within (notice the Accounts dimension was automatically created).  Right-click in the white space and select Essbase Properties…
  1. In the Essbase Model Properties prompt check the Duplicate member name support box.

Note:  Duplicate member name support enables the Essbase outline to contain a duplicate member name in two or more dimensions. It does not allow for a duplicate member name within the same dimension.
  1. In the Essbase Model Properties prompt you can configure each of the dimensions, their members, etc. by using a combination of selecting the element on the left and using the tabs on the right. Start by selecting the Time dimension (Gen1) and selecting the Info tab.  Make the following changes:
    1. Dimension Type = Time
    2. Dynamic Time Series = HTD(Gen2), QTD(Gen3)
    3. Dimension Storage = Dense
    4. Data Storage = Store Data

    Click Apply. (If the Apply button is not immediately visible be sure to expand/maximize the bottom section of the prompt window.)
  1. Make the following adjustments to the Year dimension and press the Close button to exit when finished.
    1. Dimension Type = Standard
    2. Dimension Storage = Dense
    3. Data Storage = Store Data

Click Apply.
  1. Click Close.
    1. If prompted to run a validation scan, click NO.

At this point your cube schema has been developed.  That was quick and painless.  You may have noticed when creating the model that under the Model Name field that the Accounts Dimension dropdown list was grayed out and not selectable.  This is because we did not define a detectable accounts dimension.  Since our cube will basically be a sales analytics cube and not a financial cube our accounts dimension will house our metrics (a measures dimension) that we defined on the first create Cube Schema step.  EStudio is so smart that it can automatically identify this distinction and it will assign and label our measures into an accounts labeled dimension for us.

Once we completed the Cube Schema wizard the schema diagram window was available to us for identifying and labeling elements of the entire schema or independently configuring each dimension with elements relating to the final cube.  We did just that and now we are ready to deploy this model to Essbase services.

Use the Cube Deployment Wizard (Finally)
All sections have led up to this point of deploying the cube to Essbase Services.  We’ll use the Cube Deployment Wizard to conduct this operation.  Be sure that your Essbase Server services are running so that there are not problems for the deployment destination.

Deploy, Deploy, Deploy

  1. In the folder AdventureWorksAnalysis, expand the Cube Schemas folder, expand the AdventureWorksCubeSchema, right-click on AdventureWorksCubeSchemaModel and select Cube Deployment Wizard.
  1. When you use the Cube Deployment Wizard for the first time you must select the Essbase Server connection you will deploy to (localhost, if on your main machine).  Your machine name may be different so make sure you know the name of the machine hosting your Essbase server.
  3. Once connected you will need to configure the Essbase Server deployment application and database. The application and database have not been created yet so we must manually enter them here. Replace any existing text for Application and Database with the following:

  4. Application = AdvWorks
    Database = IntSales

    Select Build outline and load data from the Load task type.

     



    Click Finish to begin the deployment.
  1. Wait for the deployment to complete
  2. Click OK.
  3. Click Cancel.
  1. In the AdventureWorksAnalysis folder navigate to Cube Schemas and expand the Model, right-click on the AdvWorks.IntSales cube and select Launch Essbase Administration Services Console.  (Ensure the EAS service has been started)
  1. Login when prompted by EAS. 

  2. (Check out the cool white background.)
    Expand the Essbase Server that you deployed to (localhost, demomachine, etc.), expand Applications, and expand the new AdvWorks Application and Database.
    Right-click the outline and select View.
  1. The deployment wizard only deployed the outline and loaded data but it did not calc our BSO cube.  We need to do this to have the data loaded to blocks above level 0.

  2. Right-Click on the database, IntSales, and select Execute Calculation…
  1. On the Execute Database Calculation prompt highlight the (default) calculation script and press the OK button to begin the calc.
  1. The calc will run quickly but it should be successful.  You may now connect to your favorite Essbase analysis tool, connect to the AdvWorks/IntSales cube and run queries.

 

Conclusion

EStudio is a great tool and clearly it will be a powerful contender in the race for the prime replacement tool for Essbase modeling and administration.  It is intuitive and easy to use.  This document showed the basics of getting a model from a data source to a fully functioning Oracle Essbase outline.  This document touched on basic and advanced aspects of EStudio but leaves several advanced features open to the reader’s future exploration.  For future development I recommend exploring the Alias Set Manager and the other tabs/functions of the Essbase Model Properties.  These advanced features are slick and will be where most custom and advance work will take place when working on a real project.

Christian Screen is a Business Intelligence evangelist living in Charlotte, NC. He is a Principal Consultant at BI Consulting Group, an Oracle Business Intelligence/EPM partner recently awarded the honor of Oracle's BI Implementation Partner of the Year.  In his spare time, Christian can be found spending time with his family or blogging on BI topics at http://www.artofbi.com.