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”

Advertisements

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 SmartView Part 2 – Connecting to Essbase?

Why would you use OBIEE SmartView Connection and Connect to Essbase?

Before we continue, you need to know how to use SmartView connecting to OBIEE, which was part 1 and can be found here: http://goo.gl/mE23az.

So why would you use OBIEE SmartView instead of SmartView Provider Services when connecting to Essbase? For 3 simple reasons:

  1. Easy creation of Tables instead of Pivot Tables. This could be used for data export, or filtering dimensions that you don’t want to be displayed.
  2. Creating simple charts without too much Excel skills.
  3. Sharing your report through OBIEE Catalog.

I’m sure you will think of other reasons, but let’s just start with those common ones for now. You might also want to read OBIEE Integration with Essbase before you continue reading: http://goo.gl/idX4Ml.

Continue reading “OBIEE SmartView Part 2 – Connecting to Essbase?”

OBIEE SmartView Part 1 – Creating your first OBIEE SV Report

I’m going to keep the title simple, OBIEE SmartView, and the main purpose of this part will be using SmartView to create analytical reports. Please feel free to ask questions.

Creating a SmartView OBIEE Connection

The version of SmartView I’m currently using: 11.1.2.5.216 which is the latest version when posting this article. You can download this version here: Patch 19858481

To create a SmartView connection for OBIEE, go to the SmartView ribbon, then click Panel, then click Private Connections. At the bottom, click create new connection and you will see a popup screen. You have two choices, SmartView HTTP Provider or Oracle BI EE. Obviously, go choose Oracle BI EE, then enter URL http://host:port/analytics/jbips

Replace host and port with your server configurations.

After that, you’ll be asked to save your connection.

Continue reading “OBIEE SmartView Part 1 – Creating your first OBIEE SV Report”

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”