An Essbase OBIEE integration walk through with tips and tricks – Finale

Continuing from the last post – viewed here

Summarizing the tips

  1. Essbase EAS – Give the hierarchies generation name
  2. Essbase  EAS or SubVar Manager – Use Essbase for Substitution Variable
  3. OBIEE RPD – Renaming Measure to Fact and Never Flatten Anything
  4. OBIEE RPD – Double Check Your Dimension & Hierarchy Types
  5. OBIEE RPD – Have a Time dimension. This is usually tagged once imported.
  6. OBIEE RPD – Have your Account or Measure dimension tagged as Measure Dimension and Value as Hierarchy Type.
  7. OBIEE RPD – Uncheck Nullable
  8. OBIEE RPD – Importing Essbase Substitution Variables
  9. OBIEE RPD – Making use of Alias Tables & Uncheck Cacheable

Since the Essbase and RPD is now done. Let’s take a look at Answers and see why these points are considered as best practices.

1. Essbase EAS – Give the hierarchies generation name

Creating a new Answer or Analysis report, everything becomes extremely clear. If we use Product Dimension and Market Dimension as an example, you will see that it states for every column which generation it is and giving it meaning.

So just by looking at the Column names, it becomes apparent to why we give Generation Names in such a way.

Essbase-OBIEE-Finale-004

Adding G2: Product Group and G3: State to the report.

 

Essbase-OBIEE-Finale-001

 

And looking at the results. The results also clearly states which generation you are in.

Essbase-OBIEE-Finale-003

If we replace Product Group with the hierarchy. And going to selection steps, choosing “Then New Step” -> “Select Members based on Hierarchy”. From relationship, if you choose “At Specified Hierarchy Level” or “Based on Level Relationships”, you will be able to see the generation names again. This is extremely useful when you have dimensions that go up to 5-6 generations. Please do ignore the naming convention in OBIEE, since “Level” isn’t the same term as Essbase Level. If they changed the wording to Hierarchy Generations or Generation Relationships, it would have been much clearer for Hyperion users when Essbase is used.

Essbase-OBIEE-Finale-002

 

2. Essbase EAS or SubVar Manager – Use Essbase for Substitution Variable & 5. OBIEE RPD – Have a Time dimension. This is usually tagged once imported.

Since we’ve created a subsitution variable in Essbase called CurMonth, we can now use it for OBIEE, SmartView, HFR, Dodeca, whatever your end tool is.

Add G3: Month to the criteria.

Essbase-OBIEE-Finale-014

If you view results, you’ll see Jan which is the first member of Gen3 in the Essbase outline.

Essbase-OBIEE-Finale-013

Go back to criteria and add a G3: Month filter. Choose Repository Variable from Add More Options.

Essbase-OBIEE-Finale-012

 

Now fill in details “Hostname:AppName:DBName:SubVarName” and for my case it’ll be steveyeung:Sample_U:Basic:CurMonth.

Essbase-OBIEE-Finale-011

 

This is what you should get.

 

Essbase-OBIEE-Finale-010

 

If you view Results page, you will only have Aug member for G3: Month. This is the substitution variable set in Essbase.

 

 

 

Essbase-OBIEE-Finale-009

 

Go back to criteria and instead of using Repository Variable, we’ll try out prompted. Now save the report. I’ve named mine MonthProductMarket.

Essbase-OBIEE-Finale-008

Our next step is to create a new dashboard prompt. Choose Column prompt for “Year”.”G3: Month”. For Default selection, choose Server Variable then fill in “Hostname:AppName:DBName:SubVarName”.

Essbase-OBIEE-Finale-007

As a result, you’ll get a default selection of Aug.

Essbase-OBIEE-Finale-006

Create a new dashboard and put the prompt and report in the dashboard. Your report will now read from the dashboard prompt with Aug as default using substitution variable in Essbase.

Essbase-OBIEE-Finale-005

 

3. OBIEE RPD – Renaming Measure to Fact and Never Flatten Anything

Using the same report created, we’ll add a few things to it. “G2: Scenario”, “Measures” and “Fact”.

Essbase-OBIEE-Finale-020

Looking at results, you get this kind of thing.

Essbase-OBIEE-Finale-019

Say you have a requirement of looking at This Month Actual Sales, Last Month Actual Sales, This Month Budget Sales and Variances.

Go back to Criteria, remove G2: Scenario, then add another 5 “Fact”.

Edit formula for the first “Fact”, rename it to “TM A Sales” for this month sales. Enter the formula: FILTER(“Fact”.”Fact” USING ((“Scenario”.”G2: Scenario” = ‘Actual’) AND (“Measures”.”Measures” = ‘Sales’) ))

Edit the formula for the second “Fact”, rename it to “LM A Sales” for last month sales. Enter the formula: AGO(FILTER(“Fact”.”Fact” USING ((“Measures”.”Measures” = ‘Sales’) AND (“Scenario”.”G2: Scenario” = ‘Actual’))),”Year”.”Year”.”G3: Month”,1)

Edit the formula for the third “Fact”, rename it to “TM B Sales” for this month Budget sales. Enter the formula: FILTER(“Fact”.”Fact” USING ((“Scenario”.”G2: Scenario” = ‘Budget’) AND (“Measures”.”Measures” = ‘Sales’) ))

For the fourth, rename to “Vs. LM” and FILTER(“Fact”.”Fact” USING ((“Scenario”.”G2: Scenario” = ‘Actual’) AND (“Measures”.”Measures” = ‘Sales’) )) – AGO(FILTER(“Fact”.”Fact” USING ((“Measures”.”Measures” = ‘Sales’) AND (“Scenario”.”G2: Scenario” = ‘Actual’))),”Year”.”Year”.”G3: Month”,1)

For the fifth, rename to “Vs. B” and formula: FILTER(“Fact”.”Fact” USING ((“Scenario”.”G2: Scenario” = ‘Actual’) AND (“Measures”.”Measures” = ‘Sales’) )) – FILTER(“Fact”.”Fact” USING ((“Scenario”.”G2: Scenario” = ‘Budget’) AND (“Measures”.”Measures” = ‘Sales’) ))

You should have something like this:

Essbase-OBIEE-Finale-017

Just a kind note, when you use AGO function within Answers, you’ll need to use the time hierarchy. So it’s “Year”.”Year”.”G3: Month”.

Essbase-OBIEE-Finale-018

Save it and go back to the dashboard. You’ll have your results.

Essbase-OBIEE-Finale-016

Change the prompts to Jul; Aug; and you can verify that your last month numbers are correct.

Essbase-OBIEE-Finale-015

To conclude, filtering within the fact gives you a lot more flexibility than using flattened measures.

4. OBIEE RPD – Double Check Your Dimension & Hierarchy Types & 6. OBIEE RPD – Have your Account or Measure dimension tagged as Measure Dimension and Value as Hierarchy Type

For time dimension, it’s basically covered in point 3. For the Value Type in Measures Dimension, If we remove all the Sales filter within the Facts and if we rename without the Sales word and take replace Measures with the Hierarchy. We can then use a selection step to add all decendants of Profit inclusive of member. You will get something like this:

Essbase-OBIEE-Finale-025

 

Go back to the dashboard to take a look.

Essbase-OBIEE-Finale-022

Let’s go to EAS to modify the outline a bit as to demonstrate what Value Type will do.

Add a dynamic calc member called Value Test and have Sales under it. Then do a all data restructure by saving the outline.

Essbase-OBIEE-Finale-024

 

Go back to the dashboard and refresh.

Essbase-OBIEE-Finale-023

You see how you get Value Test member and Sales member right away? This is because of the Parent Child relationship setup in the RPD. In all other cases, you usually have to add another Generation to the Physical Layer in the RPD. For dimensions that you see will have extra layers, levels or generations, whatever you call it, we recommend the use of Value Type.

Topics Left are:

7. OBIEE RPD – Uncheck Nullable
8. OBIEE RPD – Importing Essbase Substitution Variables
9. OBIEE RPD – Making use of Alias Tables & Uncheck Cacheable

I will not go through 7 and 8, since the benefits have been seen during the last few topics. I also won’t go through Uncheck Cacheable since you should have seen the benefits during the last restructure.

9. OBIEE RPD – Making use of Alias Tables

With Essbase, you can have different Alias Tables. Remember we have an OBIEE_AUTH table? Inside, there’s a column called Web_Language. It was default previously, let’s change it to ChineseNames because that’s one of the Alias in Essbase.

Essbase-OBIEE-Finale-028

Essbase-OBIEE-Finale-027

Log out and log in OBIEE again. View the Dashboard and check out how we’ve changed languages.

Essbase-OBIEE-Finale-026

Last Tip – Always use member names when using filters

In Essbase, when you pass Member Names, you will have the freedom to change Alias Names without having to modify anything since you will always use Alias for display purposes.

So that’s it for all Essbase OBIEE Integration Tips and Tricks. By the way, the translation for Cream Soda in Chinese is extremely wrong, if I translate this to English, it means Milk Essence Soft Drink. They better find a good translator instead of using Google Translate.

Advertisements

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist

7 thoughts on “An Essbase OBIEE integration walk through with tips and tricks – Finale”

  1. Thanks Steve, very good article on OBIEE-Essbase.

    I have a question on Essbase Substitution Variable:

    “Hostname:AppName:DBName:SubVarName”

    If we have DEV, QA and PROD environments, instead of maintaining 3 copies of same variable with 3 different host names, can you suggest an alternative or best practice?

    DEV:FIN:FIN:LastYr 2014
    QA:FIN:FIN:LastYr 2014
    PROD:FIN:FIN:LastYr 2014

    Like

    1. Hi Ashok,
      Thanks for the compliments!!! Right, I suppose you are mentioning OBIEE since the syntax is for OBIEE. OBIEE syncs these from Essbase directly, hence I suppose apart from a setup job, you don’t have anything else to do. Just tell it how often you need to sync. On the Essbase side of things, either you can write a MaxL or write an Excel VBA program or even use SubVar Manager from AppliedOLAP. These are all good ways to maintain Sub Vars.

      Do feel free to contact me if you need more info or me to elaborate on a specific point. I didn’t want to write too much until I have a better idea what you are trying to achieve.

      Regards,

      Steve Yeung

      Like

      1. Thanks Steve. Yes, I was referring to Essbase Sub Variables in OBIEE RPD.

        It is a painful process to update reports/dashboards with 3 different variable names in 3 environments. I’m trying to keep only one variable (LastYr) in RPD, instead of 3 variables (DEV/QA/PROD).

        Looks like I need to look at options on Essbase side to rename these variables.

        Like

      2. Maybe you should try using a session variable to so that it’s the ssn_variable:LastYr
        Session variable should by dynamic by dev, qa and prod concat :FIN:FIN
        This way, you maintain 1 set of sub var and 1 set of environment variables.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s