Demystifying OBIEE Essbase Date Measure – The Solution

About the mystery

One of my readers, Saptarshi, asked an excellent question. Original post here: http://mondaybi.com/2014/08/07/an-essbase-obiee-integration-walk-through-with-tips-and-tricks/

He asked:

I am integrating Essbase and OBIEE. I have few measures (accounts) as date in Essbase, such as Receipt Date etc.

Now when I fetch this in OBIEE, they appear as numbers.

How to get date measures from Essbase to OBIEE exactly the way they are sitting in Essbase?

And he’s right! They do appear as numbers. So I Googled and see how frequent this question is asked and if there were already answers I can refer him to. This is what I got!

OBIEE_Essbase_Date_Google_Results

 

Opps, just questions, no answers! Questions like this comes in every few years. Saptarshi actually also posted in Oracle Community too, 16 hours ago! Good job Google! As promised last night, I’m going to demystify and give you a solution to this! In return, just like my Facebook page which was just launched yesterday! Click here!

Continue reading “Demystifying OBIEE Essbase Date Measure – The Solution”

Advertisements

OBIEE SmartView Part 4 – Beyond Answers with VBA

Another reason to use SmartView for OBIEE? The Power of VBA!!!

OBIEE SmartView Part 1 – Creating your first OBIEE SV Report

OBIEE SmartView Part 2 – Connecting to Essbase

OBIEE SmartView Part 3 – Your own secure mini reporting book

It is required to have the skills from Part 1, Part 2 and Part 3 before you can fully understand what I am doing here. Moreover, you will need some basic skills of VBA. I’m not a VBA or Excel expert, but coding and programming is more or less the same everywhere. There are loads of resources on learning VBA if you google it. So I won’t google it for your and I’m not going through VBA bits and pieces.

So what is this Part 4 all about? Let say you would like to export an Answer report to workbook with multiple sheets based on members 2 different selections. Answer won’t be able to do that and SmartView alone won’t be able to do that. In this post, I will use this as an example as to what you can do with SmartView OBIEE VBA. In short, I’ll call this example, MultiSheet Excel Export.

Continue reading “OBIEE SmartView Part 4 – Beyond Answers with VBA”

OBIEE SmartView Part 3 – Your own secure mini reporting book

Your own secure mini reporting Excel workbook or NOT?

OBIEE SmartView Part 1 – Creating your first OBIEE SV Report

OBIEE SmartView Part 2 – Connecting to Essbase

It is required to have the skills from Part 1 and Part 2 before you can fully understand what I am doing here. I will not provide screen shots for every step.

What you will achieve after reading this is a way to create your own mini reporting book or even a dashboard with full security and data refresh when needed.

First, you are required to create a new workbook and from Part 2, create Excel Table in sheet 1 (ExcelTable sheet in Part 2) and for the chart, click on cell H1, then click View Designer then follow steps in Part 2 to create the Line Chart. Your result should look like this:

OBIEE--SmartView-Part3-021

 

Continue reading “OBIEE SmartView Part 3 – Your own secure mini reporting book”

OBIEE 11.1.1.7.1 Bugs fixed / to be fixed now or 11.1.1.9 or 12c

I am going to go through a few important SR’s I have raised in the past few months and majority will have enhancements made by Oracle.

The reason why I’m writing this is because I’m hoping other could avoid these bugs if they come across it.

UNABLE TO SHOW TEXT MEASURES IN OBIEE FROM ESSBASE DATASOURCE

I would like to first give an update from my previous blog on Creating Text Entry in Essbase when I said there’s a bug when trying to read text entries from OBIEE. Recently, Dev team replied to have this fixed in 11.1.1.9 which is coming soon. However, there will be a three restrictions as follows (notice it says 11.1.1.8, this should be 11.1.1.9) (Bug 17515163):

Bug17515163

RELEASE NOTES:
  
This trans contains essbase text measure support feature. There are following three restrictions.
1)Essbase text measure member can only be defined on Measure dimension.
2)User need to manually mark the varchar data type in rpd physical layer for text measure.
3)The text measure can be supported only for flat cube model.

I would understand that the text member will need to be defined on a Measure dimension but point 2 and 3 doesn’t really make sense. This means a manual edit will be needed to mark as varchar data type. Moreover, flattening a cube isn’t a best practice. I guess we’ll have to make another suggestion to Oracle.

Continue reading “OBIEE 11.1.1.7.1 Bugs fixed / to be fixed now or 11.1.1.9 or 12c”

ODI 12c Petition – ODI KMs for Essbase, Hyperion Planning and Hyperion Financial Management in ODI 12c

Since ODI 12c doesn’t officially support Essbase, Planning and HFM, it becomes extremely hard to perform complex ETL with multi-source data with just FDMEE option, especially if you have OBIFS. Go here and sign this petition! Tell Oracle you want it! http://www.change.org/p/oracle-announce-support-for-the-odi-kms-for-hyperion-planning-and-hyperion-financial-management-in-odi-12c-by-december-31-2014

Tell Oracle you want them to stay committed to the Hyperion Knowledge Modules for Oracle Data Integrator.

The integration tool of choice for Hyperion has a history of poor commitment levels due do rebranding agreements.  Older users of Hyperion will remember Hyperion Application Link (HAL) which was a rebranded product from Vignette.  Then there was Data Information Management (DIM) which was Informatica rebranded.  Both have gone out of fashion and customers were left with solutions that are no longer supported.  We hope this does not happen with ODI.

When Oracle purchased Sunopsis and renamed the product to Oracle Data Integrator it positioned ODI as “a best-of-breed data integration platform focused on fast bulk data movement and handling complex data transformations.”  Unlike the previous rebranding agreements ODI is a product Oracle actually owns and has greater ability to develop and integrate with Hyperion.  The 11g version of ODI listed top new features including “Adapters for Hyperion Planning, Financial Management, and Essbase.”  What is new for 12c?  Is it the removal of Adapters for Hyperion Planning and Financial Management?  We hope not because many customers will be negatively affected by that decision.

Sign this petition and let Oracle know your interest in the Oracle Data Integrator Knowledge Modules for Hyperion.  Oracle has publicly stated they will support the Hyperion Essbase Knowledge modules for ODI, however no such acknowledgement has been made for the Hyperion Planning and Hyperion Financial Management Knowledge modules.  Rumor has it they are considering stopping support for these KMs.

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.

Continue reading “An Essbase OBIEE integration walk through with tips and tricks – Finale”

An Essbase OBIEE integration walk through with tips and tricks

Best Practices or Tips & Tricks?

It’s probably best practices for myself but tips and tricks for you.

During the past few months, I have been going through theories and logics of KPIs, Dashboards and Strategy management. There are still a lot to go through but I’ve decided to go technical again for those who are missing out on Essbase & OBIEE. I’ve been doing Essbase and OBIEE integration since 2009, accumulated more than 30 Essbase OBIEE projects and there are lots of improvements on the tools since then. Unfortunately, Hyperion experts usually don’t like OBIEE and vice versa. I know that feeling since I came from the Hyperion side and always was an Essbase fan, that was back in 2004. The integration is actually not as difficult as it seems nowadays. You just need some tips and tricks for Essbase OBIEE integration.

Continue reading “An Essbase OBIEE integration walk through with tips and tricks”