Business Intelligence Technology Environment – Welcome to the Buffet

Business Intelligence Technology Environment or BITE is my own little tag line and acronym (maybe I should copyright it) to express the host of solutions available in the Business Intelligence application world today. (It could also be used as a verb to describe the plethora of poorly designed solutions… ahh but that is another story.)

My current blog series will be Oracle EPM/BI+ solution centric while remaining Oracle EPM/BI+ application agnostic (now dictionary.com is paying off). I hope that you will enjoy this real life approach to the process of decision making on software solutions interspersed with some genuine tips and tricks of the trade — some that you have seen before and some you have never imagined.

In other words, I hope that you will not find this blog to be represented by my newly coined acronym — BITE.

Rules of conduct while at the Buffet

First we need a definition. Yes a definition! Don’t be afraid, definitions are a good thing, they keep us grounded, they set limits and finally they determine if we are true to our mission. I define BITE as processes, software and goals needed to precisely solution the business data critical to the legal, accounting and business decision needs of a specific entity.

Inventive techno junkies, single tool consultants and one track sales people – CLOSE YOUR EYES / SHEILD YOUR COMPUTERS for this next statement else you might go blind. “Precisely Solution” in the definition of BITE includes the moral imperative of not misusing software for intent other than its design and picking software that fits the current business life cycle of a company. (Those of you with Software Misuse problems, I will be posting a number you can call to get help. Remember the first step is admitting you have a problem.)

The application stack for EPM / BI+; HFM, Essbase (with all its add-on modules), Smart View, OBIE, OBAW, FDM, DRM, ODI and a few products you might not have heard about or you’ve heard about but never assessed for your purposes. NO, NO, No, no folks this is not a software sales blog, it’s a solutions blog and in our solutions toolbox we need to do more than use a single hammer creatively to remain competitive from an efficiency and business life cycle standpoint.

The Personalities in the Buffet Line

Now that we have some parameters (and I know it was painful for you left brainers) by which we can solution, we need some realistic company situations to solution. Let’s start with four companies each different in their business life cycle, staff sizes and demands for a BITE at success. You can email me if you will absolutely die without a very specific company example however, I cannot boil the ocean here in this blog (small ponds are all that will be possible).

Our four companies need to be different to see solutions in the work. Let’s pick a manufacturer, a technology company, a retailer and a commodity group. In my next addition we will outline the companies, their mission, their needs and their resources.

Creating an OBIEE Repository for Relational Data Source

OBIEE (Oracle Business Intelligence Enterprise Edition)

In this part of my OBIEE blog, I’ll guide you through basic steps to create an Oracle server repository and use it to bring over a relational data source for use in OBI Answers. As of OBIEE version 10.1.3.3.2, Essbase is supported as an OBI data source allowing the user to integrate Essbase data with OBI Answers, OBI Interactive Dashboards, and OBI Publisher. The latest version of OBIEE is 10.1.3.4x. Refer to my part 2 blog (OBIEE and Essbase – Defining OLAP Integration) for importing Essbase content into OBIEE.

For the below steps, I will be using SQL Server 2005 relational database as my source but you can use Oracle 10g or older versions of either tool.

To create an Oracle repository, open the Oracle BI Administration Tool and select File | New and name your new repository.

Set security by selecting Manage | Security. Assuming you are the admin, select Users | Administrator. Right click Administrator, select Properties to enter an admin password and confirm the password. Close out Security Manager.

To import a relational data source, select File | Import | from Database.

Select a connection type.

Select the relational data source, enter login credentials and click OK.

Enter the relational table to import. Deselect all but Tables, Keys, and Foreign Keys and click Import. If you are using Views instead of Tables, change accordingly.

The relational table will import into the Physical layer of the Administration Tool.

Test the import by right clicking a column (Branch_Name is selected in this example) and select View Data. Values for the selected column should populate in a view data table.

Drag the imported relational table folder from the Physical layer to the Business Model and Mapping (BMM) layer. In the BMM layer, here you can create logical tables and joins to develop the type of model needed for presentation.

Next, drag the relational table from the BMM layer to the Presentation layer to finalize its presentation for the user. Manipulation by presentation can be performed in this layer for Subject Area use in OBI Answers.

For your reference and as a recap from my part 2, here is the significance of each layer:

1. Physical layer – imported tables and views come from the relational data source; physical joins can be performed here

2. BMM layer – this layer organizes imports from the physical layer into logical categories

3. Presentation layer – BMM entities are organized for user presentation

Once your Presentation layer is complete, it can be made available for OBI Answers to create dashboard content bringing both relational and multidimensional data sources into one view. A sample of an Answers view is displayed below combining both relational and multidimensional data sources into a combo box allowing for choice among regions. Any selection of region updates both relational and multidimensional tables for view.

This is just one simple example of the product’s capabilities. OBIEE is redefining how we approach BI with the evolution of this product improving on how we develop it. There is a great deal of flexibility within OBIEE for relational and multidimensional reporting and those who understand how to leverage this tool will see its impact upon their organization for the better.

Adding Drill Through to Your Hyperion Implementation

This is part one of a two part blog where I will discuss a general overview of how within the framework of a Hyperion Planning or Hyperion Essbase implementation, organizations may configure Hyperion to provide drill through to transaction details. In this first blog, I will discuss implementation background, and part two will provide information related to implementation approaches.

The final output of most Hyperion Planning implementations is the generation of a Financial Plan.  While various “sub-ledger” plan types may exist to support the generation of this plan (Revenues by Customer and Product; Salary Expense By Employee; Capital Expenditures by Asset Class), the fundamental level of granularity is usually equivalent to the various chart fields in the organization’s general ledger, summarized on a MTD basis.  For certain ERPs, this corresponds to the GL Account Balances table, or an aggregate of the transaction details. Depending on your ERP package, this can be one of the following:

• Lawson – gl_trans
• Oracle E-Business – gl_je_lines
• Peoplesoft – PS_Ledger
• JD Edwards – F0902
• SAP R/3 –
  o Profit Center Accounting – GLPCT
  o Cost Center Accounting – COSP
• Microsoft Great Plains – GL11110

For financial implementations, you are usually looking at the following standard chart fields:

• Account
• Fiscal Period
• Fiscal Year
• Currency
• Legal Entity or Company
• Management Chart Fields
  o Department or Cost Center
  o Product or Profit Center
  o Geography or Location

Inherent within the Hyperion environment is an ability to drill to a lower level of detail, usually to answer a question.  This type of functionality manifests itself in several forms, and the following terminology is used:

Drill Down:  Drilling from one level to another of more detail in one dimension of a cube (for example, expanding quarters into months)
Drill Up: Drill from one level to another of less detail in one dimension of a cube (for example, collapsing quarters into year)
Drill Thru: Drilling out of an Essbase cube into a RDBMS source system (for example, expanding months into days)
Drill Across: Drilling from one Essbase cube into another, normally to explode dimensionality that exists there (for example, on a MTD basis, while looking at Revenue in the Financial Cube, drilling across to expose Customer Detail in a Customer Cube)

The actual drill process from a user interface could be as simple as expanding the rows on the user’s grid (as in a drill down), to launching a new grid which creates a new point of view in a drill-thru and drill-across operation.

Much like the relationship between Planning sub-types and the primary financial cube, there is a relationship between the General ledger, and various sub-ledgers that feed it.  Primarily, detailed information is summarized prior to being posted. For example, individual employee payroll data may get aggregated by department and posted as one line into the ledger. Or Ship-To Customer and individual product SKUs may get aggregated into Channels and Profit Centers prior to be posted. In any event, when talking about exposing additional granularity in Financial cubes, it is critical to define what types of details the user wants to see, as there may be multiple steps in that process.  Because of this, it is very rare that a simple drill through into a general ledger will yield all of the results a user wants to see, since many of the results, while at least expanded from a monthly to a daily basis, will still be summarized across the other dimensions a user may wish to see (such as Vendor). This leads to the design of two new components:

1. Delivery of additional operational cubes, that expose details (such as Employee, Customer, and Vendor) for Drill-Across operations
2. Development of custom-data marts that create hybrid views integrating different levels of granularity of multiple data sources.

The overall schematic might look something like the workflow below:

 

 

In the example above, a new vendor “view” or table has been created to support the drill through view requested by the user, in a top down fashion.  Notice that the table actually pulls from two sources:

1. GL Trans – the general ledger transaction table where individual journals are posted
2. The Accounts payable table, where individual transactions exist

The need for both tables occurs because accruals may be made to the accounts in question, and those entries are generally made directly in the GL Transaction table.  So for the detailed drill through table to tie to the financials, it must contain both elements. However, since the AP element exists in both (detailed in AP, summarized in GL), special care must be made to avoid double-counting the entries that come back.  Since many GLs contain a field that identifies the source system (AP, AR, etc), or has a journal mask to accomplish the same, this is usually readily available.

In my next blog (part 2), I will discuss practical techniques for implementing the above.

Mike Killeen – Ranzal and Associates
mkilleen@ranzal.com

For more than eleven years, Mike Killeen has scoped, designed, and implemented Planning & Essbase applications for Ranzal & Associates, an award winning, preferred consulting partner of Oracle Hyperion. Currently serving as practice director, Mike provides guidance to Hyperion product management, Ranzal customers, and internal consulting resources alike on best practices related to the implementation of Hyperion System 9. Mike is a certified Hyperion Professional, with certifications in Essbase, Planning, and HFM. He graduated cum laude from Cornell University with a degree in engineering, and received his MBA from the University of Connecticut.

Oh, The Good Old Days…

Who among us has not heard about the “good old” days?  My grandmother regularly peppered my Sunday dinner with stories, sometimes lectures, on how life was a little simpler way back when. However, if you apply some logic, it may be apparent that the premise might not hold water. 

Technology is our revolution which has freed us from monotonous tasks, as well as provided new opportunities for insight and growth. Surprisingly, we need to learn how to free ourselves from our old ways.

So do you still have this on top of your television?

Take the “Betamax” player.  Aside from taking the space of a small loveseat, it was pretty simple to use.  Press the “on” button, insert the tape and press play. Soon consumers demanded a few more features and we were all graced with the latest and greatest invention of all time, the programmable VCR.

What more could I want in life?  I now had the opportunity to record the debut of the “Thriller” music video while in detention room 101.  However, we all know life is not that simple.  I had to first learn to program the clock, channel and record time.  Those benefits came with a small price to pay.

As a veteran Oracle / Hyperion Instructor, I could train our Oracle / Hyperion Enterprise clients how to press the “on” button, insert the tape and press play in about a week. Following the incredible success of Oracle / Hyperion Enterprise, Pillar and Essbase, clients wanted more features and functionality. 

Oracle / Hyperion Solution’s met the challenges presented by their customers.  With the acquisition of Hyperion by Oracle, the Oracle / Hyperion suite of Enterprise Performance Management (EPM) products merges a host of financial, reporting and analysis, and data integrations tools.  These tools have refined how people work and interact with business information.  It has also changed how we must provide training in order to prepare project teams, administrators and end users.

The System 9 and Fusion generation of products touch a much more diverse population of users.  The products range from Executive Dashboards, to administrative budget collection forms and into the Information Technology (IT) back-office environment. 

A one-size fits all training solution is no-longer as effective as it once was, like in the “old days” of desktop reporting.  Now, we need to think about tailored training to meet the needs of each group of users and instruct them on how to most effectively incorporate the technology into their user-specific work day tasks. 

The stepchild of training is managing change. Everyone is “fired-up” during the implementation phase.  Each person striving to learn and understand all there is to know about the new system.  But what happens when some people move on and others move in?   Again, the scope of the latest products demands tailored training to meet the needs of the users, but also a plan to cover the entire cycle of ownership.

The challenge of providing training to an incredibly diverse user base, preparing users for multiple phases of projects, and how to live with and maximize their investment in technology has been exciting.  Training solutions now weave together, printed materials, customized courses; web-based training seminars and re-usable recorded instruction.  Training has become a separate and distinct project unto itself.

The products are not difficult to use, the challenge is ensuring people know how to thoroughly utilize all of the features and functions. Second, to make sure they understand how the technology fits into their daily tasks.

An Oracle / Hyperion competitor used to boast that one of their products was more user-friendly than HFM, and therefore required little training.  That should throw a warning flag to the consumer.  As the Oracle / Hyperion suite is an EPM set of applications, there must be an “enterprise” plan how to manage the needs of the users that the system will touch throughout the cycle of ownership. 

A well-defined training plan must address educational needs across all phases of an implementation.  It cannot be overemphasized that effective preparation can truly play a key role in keeping a project on schedule and budget.  The results are that project leaders can more effectively communicate and understand design decisions.  The members of the project team are able to test, validate and troubleshoot tasks more efficiently.  Lastly, users understand the purpose of the project and how it fits into their daily tasks.

You will find that having a comprehensive training plan will be invaluable when you need to prepare new employees, manage employee movement, and preparing everyone for software upgrades and enhancements.

The Workflow Revolution – Changes to Financial Reporting

Thomas Friedman first talked about how globalization impacts business life in The World is Flat.  In this book, he describes the ‘flattening of the world’ as the idea that workers from around the globe could collaborate and work across systems and wide spans of geography.  One specific part of this flattening is a change he refers to as the “quiet revolution in software, transmission protocols” that he calls “the ‘workflow revolution’ because of how it made everyone’s computer and software interoperable.”

I see this amazing transformation offered within financial software today, but many companies don’t completely understand the value or the concepts to implement this approach.

New financial systems today allow for the immediate submission of data.  The best practice applications of these systems allow for the validation, translation and commentary of this submission to be owned by the end users.

When I discuss the applied concept with clients, I speak of this ‘changing conversation.’  Before this workflow revolution, legal entities in remote parts of the globe would prepare financials and fax them, or teletype them, to a corporate office.   A process that was manual, slow and disconnected.

The end users owning the process changes the communication of the business.  The old typical conversation before might have been a submission of some financial data followed by a response that the data is incorrect or incomplete, and then a resubmission – all taking days to complete.  The process was also flawed in that it relied completely on the receiving member being proactive, and finding the errors.  Surprisingly, many companies still use this approach.

The technology exists to solve this problem and provide two major benefits.  First, products today make the validation systematic, hence reliable.  The end user knows immediately if the data is wrong, and can resolve the issues.  The system provide consistency and reliability that cannot be accomplished with people.  Second, the end users can be made aware of potential problems and begin researching proactively.  This proactive approach cuts days from the process and improves data quality.

Within my next blog posting, I will discuss many of the controls I am seeing in these systems like SAP’s BPC and Oracle’s HFM products, and how they improve data quality and speed of reporting.

 

Common Essbase & Planning Problems & Resolutions: The Essbase Add-In

Over the last 11 years of consulting, there are certain issues I have seen in the implementation of Planning and Essbase applications that are so common, that I have decided to list them here and their resolution in the hopes of sparing you all some undue pain. Today we will focus on a set of issues that probably affects the greatest number of users – the Essbase Add-In. Personally, I think the native Excel Essbase-Add in is still one of the most compelling features to Essbase – which is probably why there hasn’t been a mass migration of older Essbase clients to SmartView (yet). In the words of Mr. Heston – “Not until they pry it from my cold dead hands…”. I sort of feel the same way – so with that in mind, here we go!

Problem # 1 – More than 1 Instance of Excel Open
The Essbase Add-In technically only supports 1 instance of Excel open at a given point in time. If you have ever had issues with retrieving data, but not having anything come back, there is a good shot that this is the cause. As a matter of fact, if anyone ever tells me they have ANY problem with the add-in, this is the first thing I tell them to look at.
Since this is kind of tough to tell given how workbooks appear as separate “programs” in the taskbar now of your desktop, the clearest thing to do is to perform a <CTRL>-<ALT>-<DEL>, display Task Manager, click the processes tab, and do a sort in ascending order by process name. If you see more than 1 Excel.exe process listed, while it may appear that the add-in is working, you will get flaky results (for example, retrieving data and nothing returning). The solutions – terminate one of the excel processes.

Problem # 2 – Conflicts with Other Add-Ins
What other add-ins do you need besides Essbase? (Rhetorical question) Well, how about SmartView, or Peoplesoft’s Excel add-in for reporting? Even a Hyperion Enterprise Retrieve…I’ve had success making the various add-ins work together, but some functionality (such as double clicking) will cause conflicts with each other. To resolve, you can certainly load and unload the various add-ins as you need them, or on the Essbase side, there is a great option made available in the 7.x release called “Limit to Connected sheets” – it basically prevents Essbase from taking your mouse buttons until you manually connect a spreadsheet. Note this also has the added benefit of giving Excel users their native Double Click to Edit Cell, and right click pick from list functionality back. An always on setting for me…

Problem # 3 – Where did my add-in go?
This next problem I am bringing up only because it’s personally happened to me so many times. I call this the “I changed my mind” problem. What happens is the following:

• You are performing an ad-hoc analysis in a spreadsheet against Essbase
• You are done, and click X to close out Excel
• You are prompted to Save Changes for any open workbooks, and instead of saying Yes or No, you click Cancel, which stops the closing of Excel
• You are no longer able to run retrieval code or any add-in function

What happened? Clicking Excel unloads the add-in immediately, despite the fact on exiting the workbook, you have changed your mind

I have also seen this in a # templates with VBA code – generally if you starting getting negative numbers as return codes, this is an indication of that problem – the ad-in is loaded.

The solution is to goto your Tools->Add-Ins menu, deselect the add-in, and close, then go back to your Tools->Add-In menu, and reselect the Add-in, which will load it back up.

Problem # 4 – Why are my retrievals so slow?
Recently at ODTUG Kaleidoscope in New Orleans I gave a presentation on optimizing Essbase retrievals – I got a lot of great feedback from people, specifically related to a number of the tips not being your standard Essbase Common knowledge (you know, your classic IF on dense, FIX on sparse, which isn’t always true anyways). In any event, overall retrieval time is a combination of the server performance, the network performance, and the client performance. Assuming you are an end user, there isn’t much you can do about network or server performance, but there is a lot you can do about your own template configuration on the client, specifically including the following:

• TIP – Minimize the Usage of EssCell

  I have seen former Enterprise users and even HFM users who work with the Essbase add-in fall in love with this excel function that retrieves data into an individual cell in an Essbase grid. While OK for 1 or two values, each usage of this function executes a separate call to the Essbase server. You are better off doing a hidden retrieval sheet with one retrieval from Essbase, and then referring to the values from that spreadsheet.

• TIP – Minimize the Usage of Preserve Formulas
  
  When you preserve formulas on an Essbase retrieval, Essbase needs to check the value of each cell in the grid to see if a formula is there before retrieving it. On particularly large retrievals, this takes additional time.

• An alternative technique is to make use of locking/unlocking cells, and protecting the worksheet. In the attached retrieval sample from Sample:Basic below, % of Sales is a calculated column with an Excel formula.

• To “preserve formulas” without using the Essbase feature, the steps you would take would be as follows:

Step 1 – Select all cells in the grid, and unlock the cells

All cells in a spreadsheet by default are tagged as locked.

Step 2 – Highlight the Columns or Rows with Formulas, and Only Lock Those

Step 3 – Protect the Worksheet

Now, all of the cells you want Essbase to retrieve into will be able to overwrite the values since the cells are not locked, and the cells with formulas will be protected.

Note that for a retrieval this size, the above technique is really not necessarily, but particularly if you are using the cascade function, or doing batch workbook retrievals of spreadsheets with hundreds of rows, even a 4-5 second improvement is worthwhile, particularly when you amplify that over the course of multiple retrievals.

• TIP – Minimize the Usage of “Unknown Members” in Retrieval RangesIn the example above, I added a calculated member called % of Sales. This is not an Essbase member, and as a result, Essbase will let me know about it.

I can work around this annoying message, but I turning off the option for “Display Unknown Members” in the Essbase Options dialog box.

Now when I run my retrieval, I no longer get a message displayed on my client workstation. Problem solved! Well, it is for me, but all of those messages are still occurring, they just aren’t being written the client workstation. They still appear in the Essbase server log.

Imagine a spreadsheet with hundreds of labels that don’t belong – that’s 100 additional lines on every retrieval where the Essbase server is writing really useful messages to the log file instead of processing your query. For something this small, it’s not an issue, but on larger retrievals, I have seen this have an affect of a few seconds per retrieval.

An option to address this issue is to make use of labels in the Excel grids.

Now when the retrieval runs, because the value isn’t stored in a cell, problem solved on both client and server. This also has the added benefit of allowing you to keep “Display Unknown Members” on so in the event something happens that you do care about, say a member name getting renamed and blowing up your retrieval, you will be aware of it.

• TIP – Turn off Enable Flashback

  If you are doing cascade retrieves, or VBA batch retrievals in the Essbase add-in, this setting is unnecessary because it will use memory to store the previous operation only, which won’t necessarily allow you go to back more than 1 sheet. In really early versions of the add-in, we saw lots of issues with both this and preserve formulas on.

Closing Thoughts…

In the next installment of this series, I will spend more time focusing on the # 1 thing you can do to improve retrieval performance, specifically changing the orientation of your retrievals themselves, and selective usage of attribute dimensions. That should be a blog by itself!

Mike Killeen – Ranzal and Associates
mkilleen@ranzal.com

For more than eleven years, Mike Killeen has scoped, designed, and implemented Planning & Essbase applications for Ranzal & Associates, an award winning, preferred consulting partner of Oracle Hyperion. Currently serving as practice director, Mike provides guidance to Hyperion product management, Ranzal customers, and internal consulting resources alike on best practices related to the implementation of Hyperion System 9. Mike is a certified Hyperion Professional, with certifications in Essbase, Planning, and HFM. He graduated cum laude from Cornell University with a degree in engineering, and received his MBA from the University of Connecticut.