Essbase Studio First Impressions (Part 1)

So last week I started using Essbase Studio and figured I would start to share some of my first impressions.  When I installed Studio I had planned on working on a simple test case so that I could learn the product.  The plan was to build a version of sample basic but because I don’t have a relational schema with the sample basic content I had to do some prep work…

Step 1: Extract the data from sample basic in some form that resembles a fact table.

Step 2: Extract the dimension structures.

Step 3: Load the extracts into a relational database and build a star-schema.

For step 1 I used report scripts and found them to work really well except for the fact that I had to re-learn them.  Here is the script I ended up with to get my rough fact table:

{ROWREPEAT}
{DECIMAL 2}
{SUPMISSINGROWS}
{SUPFEED}
{SUPFORMATS}
{SUPPAGEHEADING}
{SUPCOMMAS}
{TABDELIMIT}
{MISSINGTEXT ""}
<SUPSHARE
<ROW (Year, Product, Market, Scenario)
<COLUMN (Measures)
<DIMBOTTOM  Year
<DIMBOTTOM  Measures
<DIMBOTTOM  Product
<DIMBOTTOM  Market
Actual
Budget
!

This gave me a file with all my dimensions in rows and all my measures in columns.  No keys or indexes but good enough for a quick fact table…

On step 2 I totally cheated, for this step Mike gave me some old dimension extracts he had with parent/child columns.  Not sure where he got them but they were perfect so I did not care.

Step 3 was a step I was hoping to avoid, Studio has a cool new feature for supporting text files as tables.  Not knowing the limitations I spent some time trying to build a “mini schema” with the text files.  Eventually I would find that text files have too many limitations in studio to use them for an entire model.  They would be great for some ancillary data but not the main fact and dimensions.  The main issue is that you cannot join 2 text files together in a “mini schema” like you can join 2 tables.  So I downloaded Oracle Express Edition, installed it and had the files imported in about 30 minutes.  Oracle XE is not supported by Studio and is not meant for any sort of enterprise deployment but it worked for what I needed and is quite a nice entry level database.

Once I finished my setup up I was ready to work with Studio to build my model.  The first thing to do is to create the data sources. Studio has a nice wizard to walk you through this and it was really easy.

The product supports a number of relational source and some other sources that might be handy like OBI.  It also lists Essbase as a source but don’t be fooled this, it is only so you can set up a target for your cubes to be deployed to later.  Right away I set up 2 sources one to my XE database and the other to the Essbase server where I was going to deploy my cube.

Once I had my source all set up I was able to create my Mini Schema and define all the relationships between my tables.  This can be done in the wizard for creating the data source, manually after the source is created, or skipped altogether if the joins are already defined in the relational database.  For this there is also a wizard to get you started:

One you have the Mini Schema created you can begin to define joins, this is as simple as drag and drop.

For each dimension table I needed to create 2 joins.  The first join was to the fact table; and this the normal join I would expect.  The second join was to join my parent column to my child column in each of the dimension tables.  If you have your dimension tables laid out like I did with a parent child relationship this has to be there for hierarchies to work properly later.  I’ll attempt to explain this better…

Each of my dimension tables have a column for Parent and a column for child and the hierarchy could be ragged.

ex:

Parent	Child
Market	East
Market	West
East	New York
East	Florida
West	Utah

This is opposed to having a column for each level in the dimension.  With the parent and child columns joined you will get the following resulting dimension; and this is what you want.

Without the join you will get the following hierarchy and your build will fail with duplicate member names later in the process.

Once you get the mini schema just right you are ready to begin creating metadata.  The next part of this post will dive into this topic so stay tuned!