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.

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

 

 

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

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.

 

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.

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

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

 

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

 

This is what you should get.

 

 

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

 

 

 

 

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

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

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

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.

 

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

Looking at results, you get this kind of thing.

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:

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

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

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

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:

 

Go back to the dashboard to take a look.

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.

 

Go back to the dashboard and refresh.

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.

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

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.