Putting ERP to Work in Higher Ed

Examining the ongoing challenges of delivering high-quality, value-added ERP services in Higher Education.

Friday, September 28, 2012

Jump-Start to OBIEE with Noetix

Last year around this time, I was on the exhibition floor at Oracle Open World in San Francisco, looking for inspiration. My shop runs PeopleSoft HCM, E-Business Suite Financials, and Hyperion Planning and over the last two years we have been mired in upgrades (R12 going live 59 days from now will be the last... and then we will not speak of upgrades for at least six months, I hope!) But I was grappling with one key issue: how to advance business intelligence and reporting, especially for the financial management community here. So I took full advantage of the crowded exhibition floor(s) at the Moscone Center, spending several hours watching demos and asking questions about packaged analytics for core financials, hoping something would click.

We chose Noetix in large part because of the Noetix Generator, a feature that converts those NoetixViews into an RPD (metadata repository) compatible with Oracle Business Intelligence. From all appearances, this was a "turn-key" solution that within mere hours (literally) would give my team a solid base from which to start delivering analyses and dashboards to our hungry-for-data business end users, heretofore accustomed to hours of manual manipulations. Of course I had heard about the (in)famous views and knew that many of our higher education EBS peers use the Views to power Discoverer (which we never implemented here).

Of course it didn't happen that fast... A seed planted in San Francisco in early October sprouted in the spring and blossomed in the summer. There were contracts and demos and reference calls and internal discussions before we made the decision; the software was installed to our test environment in mid-August. Which brings us to today, about to go-live in 2-3 weeks with a dozen Payables and Receivables reports despite an R12 Upgrade looming on the horizon...

So how does it work? After trying to find answers on the web, this blog intends to pay it forward and give a little insight into the process and what we have learned so far.

1. NoetixViews are NoetixViews are NoetixViews... If you know them, there is nothing else to say. If you don't, these pre-built views are installed in a new schema on your E-Business Suite database server. Standard views (not materialized) and although initial testing has been positive, many here harbor reservations about adverse performance impacts... We shall see! So far we have been pleased with the breadth / coverage of the views themselves, although there is a learning curve to navigating the views, which brings me to...

2. The incredibly intuitive Noetix Search tool (shown below) is my new best friend (when I am secretly developing reports instead of managing my department). The director of back-office operations told me that this feature alone might justify the investment. It is hard not to wish we had a data dictionary like this for our enterprise data warehouse...

Noetix Search: My New BFF

3. Noetix Generator automatically generates OBIEE application roles, meta-data repository, and pre-delivered report templates known as "Answers" (more on this below) -- the three critical components of the OBIEE solution. It really does work as advertised -- in mere minutes after installing the Views to EBS we had a fully functioning data model to write reports against!

Action Shot of NoetixViews in OBIEE

4. Noetix Workbench is a tool for customizing NoetixViews and the corresponding RPD... And you will need to customize, there's no two ways about it. We own this new module but have not yet had a chance to use it. since I laid down a "no customizations" edict for the first roll-out! We are building a backlog of required customizations; nothing earth-shattering, but it has become clearer to me that some degree of customization is inevitable for every customer...

And now for some initial observations / fun facts about our implementation so far:

The first fun fact about this Noetix/OBI solution is that my team was able to build a robust library of reports in a matter of hours. If you're starting with inexperience in OBIEE or lack of knowledge of E-Business Suite data terminology, the curve is steeper. But this solution represents exactly the jump-start I was hoping for. People were shocked.

The second fun fact is that the delivered Answers, which are plainly advertised as templates rather than ready-to-use reports, provide a useful window into the data model and potential performance issues. We executed a time-consuming but ultimately valuable exercise of creating a Confluence page for each report with a screenshot, high-level description, performance results, and an initial assessment of the functional fit given what we know of the University business. We then made these pages available to the business stakeholders to gather their feedback. The reports accomplished exactly what I expected -- they spurred conversation and helped my team learn their way around; none of the "Answers" will be deployed to our PROD environment, but several will serve as inspirations!

Analyzing Packaged Noetix "Answers" (Report Templates) in Confluence

The third fun fact is that by default the Noetix Generator RPD sets the cache property to "Never Expire" for all the Views... And the only other option is No Cache... We are working with them to figure out how to get to someplace in between.This also raises an issue that if you promise "real-time" to people but there's a data cache... Guess what? Not real-time after all!

(There is a much broader topic for future blogging on how to define a cache management strategy. The impact on the user experience is tremendous... Just think about the potential frustration from a user if a prompt list takes 45 seconds to render -- put that baby in cache!)

And the fourth fun fact: the Noetix subject area is NOT a dimension / fact structure the way that OBIEE gurus might expect it to be. These are big, fat, traditional views. This takes some getting used to... And there are some disadvantages if you've grown accustomed to star schemata. But it also proved something important to me -- are we making this thing (OBIEE) too complicated? Are there traditional views that might be fit for use without jumping through a bunch of data modeling hoops?

More questions than answers, but I am extremely excited about our prospects!

Labels: BI, Noetix, OBIEE, Oracle EBS

Sunday, August 19, 2012

Spinning Wheel of Death

What fun we had last week trouble-shooting predictably unpredictable failures on our OBIEE development environment. It all began with Noetix, a set of pre-packaged content for Oracle E-Business Suite Financials that we acquired to jump-start our transition to modern BI tools. (But it was not really Noetix' fault, as you'll see).

There were some new roles (as expected) and an RPD to merge (no biggie) and some catalog content, too (par for the course in multi-user development). All things we will face again and again in our future as multiple development teams author content for thousands of users.

For roughly 38 minutes after the install was done, everything looked great. And then we saw the following behaviors:

Perpetual spinning icon when trying to open the Dashboards Menu
No response (not even a browser error) when selecting New > Dashboard
Permissions error when opening ANY Dashboard object (not only new merged content)

Throughout this time I could create ad hoc analyses, run saved analyses from the catalog (both pre-existing and new Noetix content), etc.

Attempting to open any Dashboard prompted this error:

This message-- seemingly a failure related to one of the roles created during our Noetix installation process -- cried out for corruption somewhere... We started by scouring the permissions on Dashboards features: everything was fine. Checked the policy store in Weblogic / Enterprise Manager: looked fine. Checked the permission on catalog content; tweaked the settings -- problem solved! For 38-41 minutes, upon which I saw this, again:

We reverted to our RPD: no luck. Reverted to old RPD: spinning, spinning, spinning. Restarted services. Bounced servers. Cleared caches. Any "fix" we thought we found lasted 40 minutes.

Finally, we found relevant articles on My Oracle Support:

Oracle Support Document 1390497.1 (How To Clean Corrupted Permissions On An Upgraded OBIEE 11g Presentation Server Catalog)
Oracle Support Document 1388837.1 (OBIEE 11g: Clicking on "Manage Privileges" Throws
"Error retrieving user/group data from Oracle BI Server's User Population API")

Unfortunately, our first attempt at this catalog clean up failed. Third or fourth try worked like a charm (changing from "Report" to "Clean" mode also critical). The Oracle OBIEE 11.1.1.6 documentation actually describes the process rather clearly, but we had never thought to look there!

Problem solved; onward to new problems next week!

Labels: OBIEE, Oracle, Oracle BI

Monday, August 13, 2012

Dynamic Metric Selection from Prompt

There is a saying: "never use two words when one can suffice." Sometimes it seems that with Oracle BI (OBIEE) they have a different view: why provide one way to do something when six can suffice. At the minimum you have to deal with the semi-redundant functionality of the BI Publisher and Legacy Siebel feature sets. But even within those tools there is often more than one way to implement a given feature.

Imagine that you have this requirement: provide the end user an option for which metric to include in a table or graph. A common use case would be to toggle between order volume (# of transactions) and revenue ($).

The most obvious way to do this within Oracle BI is to use the "Column Selector" view. This is easy and fairly obvious.

1. Create your analysis with one metric.

2. From the Results Tab, insert a new view -- Column Selector.

3. Click the checkbox next to the metric you already chose, then double click other metrics from the subject area navigator to add them to the list.

4. View the report and note the drop-down for the user to choose the metric he prefers. (Below is a screenshot with two views on one dashboard).

But that does not seem to be Oracle's preferred method... Presuming that your plan is to deploy Analyses mostly via Dashboards, the recommendation seems to be to configure Dashboard Prompts and utilize Presentation Variables. This is pretty slick, especially in combination with the "go-less prompt" style of removing the Apply button. It isn't straight-forward and it does have some drawbacks, but it is a useful method to know!

1. Configure your analysis with the dimension columns and a single measure.

2. Edit the formula for the measure, entering syntax that presumes a presentation variable "{Met01}" but defaults to something (here: revenue)

Resulting column shown below...

3. Create a Dashboard Prompt.

4. Add a prompt for "Metric" -- the trick here is to replace the member formula with a fake text value -- '1' or something like that (see below)

5. The next trick is to bypass the normal way of adding members to the prompt -- since we are not using a real prompt, we need to trick the system; click the pencil icon in the upper right of the screen.

6. (Above) Key in member names from the fact table(s) of the subject area for this report. This has to be perfect -- I find it easiest to have two tabs of the same OBIEE instance open in Firefox and toggle between them.

7. Establish a default (although your query will default if you followed the above example) and define the presentation variable to be set as a result of the user selecting a value; name it "Met01" since that's what you assumed earlier!

8. Create a new Dashboard and drag the prompt and report into the body.

9. Run the report; select each radio button to see the view(s) refresh.

(And below, after toggling the prompt value...)

So that's pretty slick, and definitely the way to go if you will embed multiple analyses on a single Dashboard. My top concern -- although I would not be surprised to learn there is a fix for this -- is that the column selector has the added advantage of also toggling the column heading, whereas in the prompt/pres-var solution the column heading is something generic such as "selected metric." Nevertheless, I think I'll be teaching this trick to my team next week!

Labels: Business Intelligence, OBIEE, Oracle, Oracle BI, Tips and Tricks

Friday, August 10, 2012

Nifty Trick for Dimension Tool Tips

Not all users will remember the meaning of each dimension or even the values within that dimension. I found this simple (and not at all obvious or intuitive) trick buried deep within the OBIEE 11.1.1.6.2 BP1 Sample App, on Dashboard 3.21 (Other Tips). In a nutshell, the trick is to use the Data Format field on an Analysis column to embed a "tooltip" to enlighten the end user during mouse-over. What's even better is that it is also possible to quickly apply the Tool Tip universally to the column if you so desire!

This is quickie... Don't blink or you'll miss it.

1. On the Criteria tab of a new or existing analysis, invoke the menu for a column and choose "Column Properties"

2. On the "Data Format" tab of the Column Properties modal window, check the "Override Default Data Format" box and choose "Custom Text Format" in the drop-down for "Treat Text As" and enter HTML as shown below. In this text, the @ will be replaced at run-time with the name of the member (even though the same tip will appear for every member of the dimension).

3. To apply this universally to the column, click "Save as Default" at the bottom of the window and select the second option.

4. The completed configuration is shown below with some incredibly pithy text.

My one additional observation here is that as I dig into OBIEE I keep realizing the endlessness of the configuration options. One problem is knowing where to draw the line. Another problem is the lack of clear documentation on the variables that can be used within the HTML embedded in all these properties! I feel as if an entire style guide needs to be written! (And unfortunately, I'm not the guy to do it...)

Labels: Business Intelligence, OBIEE, Oracle, Oracle BI, Tips and Tricks

Wednesday, August 8, 2012

Nifty Trick for Emphasizing OBIEE Actions

I'm a big fan of the Action Framework in Oracle BI (OBIEE) 11g. This feature allows a report designer significant flexibility for "actionable intelligence" (another of Oracle's names for this feature along with Agents, in fact) including guided navigation from a cell on a report to relevant content. With a broad set of options, including: navigation to websites, invoking a web service, calling a Java method or intiating JavaScript, posting to an HTTP form. The Action Framework also is the primary mechanism for integrating one Analysis (Answers) query to another and opening relevant reports or forms in Oracle Hyperion EPM, Oracle E-Business Suite, or Siebel CRM. So it has a lot going for it, right? Did I mention that it is one of the more straight-forward configuration items in OBIEE?

Caption: "New Action" pop-up from the 11.1.1.6.2 BP1 Sample App; note that in this environment the options for EPM and Siebel have not been configured.

Once an action has been configured, there are basically four ways it can be invoked:

Directly from the Web Catalog UI
Directly from a Dashboard
Within the context of a scheduled Agent
From an "Action Links" menu embedded in an Analysis

The last of these is probably the most valuable, especially since it allows for the passing of relevant context from the cell the user picked.

In the above screenshot, for example, the term "Baseline Operations" will automatically be sent as a search criterion to our Confluence knowledge base if the user clicks "Search FSS Confluence for Definition" (a "Navigate to a Web Page" action) or the quarter and category will be passed to another BI Analysis report if the user clicks "Analyze Category in Detail." This works great... With one minor problem: there is NO indication to the user that navigation is possible from that cell. In many other cases, the default action would be a drill into details. This has the potential to confuse users.

And then I came across this nifty little trick when perusing the catalog in the latest OBIEE Sample App. See below:

The additional column "Actions" is used only for the purposes of exposing the Action Links menu. How do do it? Simple.

1. Create an analysis with dimensions and measures

2. Add ANY additional dimension to the end of the layout. It doesn't matter which one you choose; it won't get used.

3. Open the menu on the extraneous column and choose "Edit Formula"

4. Click the "Custom Headings" checkbox and overwrite the Folder and Column Heading with the text for the column in the report.

5. (Above) In the "Column Formula" field replace the contents with the hyperlink text (with single quotes around the text).

6. Configure the Action menu by opening the menu on the "Action" column and choosing "Column Properties."

7. On the "Interaction" tab choose "Action Links" as the "Primary Interaction."

8. Configure the links, either from a library of pre-configured Actions or by defining new ones.

9. Run the report and see your links in action!

Pretty slick little trick, if you ask me! I'm not saying you should use it every time, and I know it is a little busy, but a useful trick to have up your sleeve, especially if a client asks for something like this!

Labels: BI, Business Intelligence, OBIEE, Oracle, Oracle BI, Tips and Tricks

Monday, August 6, 2012

OBIEE Analysis as BI Publisher Data Source

BI Publisher Enterprise is a very powerful tool, especially because it can access so many different data sources without the burden of data modeling. By going around the RPD layer, BIP can access transactional data sources directly, which makes it an excellent option for replacing existing operational reports. BIP's powerful and flexible formatting including WYSIWYG parity between report layout and report output ("pixel-perfect" is another way to say this) make it a far superior tool to Analysis (Answers) or Dashboards for producing printable output.

But there are some compelling features about Analysis (Answers) that you may want to retain, including all that security you've spent so much time building into the repository (RPD). That alone may be a compelling reason to want to layer BIP on top of BI Analysis. But also consider these justifications: 1.) The ease of building queries in Analysis (Answers); 2.) the potential for "multi-use" for a single query -- perhaps you also want the data in a Dashboard as well as in a printable format.

On the face of things, this seems like a straight-forward integration. Right there in the Data Model configuration screen there is an option to use BI Analysis as a data source. Unfortunately, Oracle has made some design decisions that make this option less than perfect... Below I will walk you through the challenges of the provided integration and suggest an alternative path to meeting your requirements.

First, let's have a look at the query I created, which merges some customer indicative data along with revenue and order count by year.

Now let's look at the Advanced Tab for a moment -- especially the SQL generated by my query. I'll copy this and save it in Notepad for now... You'll see why in five minutes...

Now over to BIP, we create a new data model, choose BI Analysis as the data source, and voila -- there's my data model. With one little problem -- what is "Column1"? All columns are brought over with generic text.

Create New Data Model

Create Data Source "Oracle BI Analysis"

Navigate to the Analysis (Answers) Query

Imported Metadata from Analysis query

I can fix this problem by going to the "Structure" tab and revising the XML Tag Name to match the "Business view Display Name" at the far right. See below.

This alone would not be a major deterrent to taking advantage of the integration. However, it is not the only limitation... Back on the Diagram tab I should have some flexibility to restructure the XML. Note that for this data model, the only options available to me are Properties and Help. Options to create a group or define a link to another structure are not available.

This is by design and documented in section 2.8.1 of the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1). But I am not satisfied with this outcome, and I have another idea. Remember that SQL we copied from the Advanced Tab of my initial Analysis (Answers) query? Let's put that to use. Back on the Data Model screen, I will initiate a new SQL data source and use the Analysis SQL (with slight modifications).

Now when I try to manipulate the columns in my data structure I have complete flexibility -- note the additional options.

Below is a fully restructured XML diagram, with the customer indicative data pulled up (along with a grand total) and then detailed annual figures collected as children. This XML will be much easier for me to use in the report I want to build!

One other quick point of information... I have used this technique extensively in one of my applications, but found that a helpful companion configuration is to define an Agent for the original Analysis for the purpose of cache seeding. If the data is in the cache, the BIP reports are lightning fast with the SQL against the presentation layer!

Labels: BI, Business Intelligence, OBIEE, Tips and Tricks

OBIEE How-To Posts Coming

As those of you who follow me on Twitter may know, my team is trying to gain traction with an implementation of Oracle Business Intelligence (OBIEE). This has proven difficult for myriad reasons I need not get into here, but I have established bold and aggressive goals for replacing our existing reporting tools (for the communities we serve: finance, research administration, capital planning, student financials) over the next 16 months. I am taking an active, hands-on role in the project, including some light dabbling in solution design.

I tell people that Oracle BI is like the LEGO Star Wars Star Destroyer kit (see below for image, click here to buy from Amazon). It is an awesome toy, intricate and complicated, and looks great sitting on your dining room table at home...

LEGO Star Destroyer Set: Not for the meek!

Except there is a problem: imagine that the instruction booklet comes with only every third page. There is a lot to interpolate, heavy reliance on spatial thinking, and plenty of trial and error.

Figuring out how the pieces go together has become my latest off-hours avocation. The OBIEE Sample App has proven invaluable, as have blogs from Gerard Nico and Rittman Mead and the Learning Center from Performance Architects. The Oracle documentation has its moments, too, believe it or not!

Over the next few days, I will be posting an assortment of things I've learned -- usually after coming up empty on Google! I am hoping to "pay it forward" a bit in this domain; perhaps one or two of my observations helps somebody out there put a few more pieces together.

Labels: BI, Business Intelligence, LEGO, OBIEE

Monday, July 30, 2012

The New OBIEE Sample App

I love these sample application images for Oracle Business Intelligence. Although the cynical side of me sees Oracle’s every move as a sales tactic, I don’t think the typical individual running VirtualBox and experimenting with Oracle R Enterprise integration with OBIEE makes purchasing decisions… So I am going to choose to look at these images as awesome showcases for features and capabilities – it must be a fun job to configure these samples!

The new one for OBIEE 11.1.1.6.2 BP1 was released last week to the Oracle Technology Network and I just started monkeying around with it. Just take a look at this screenshot from the "What’s New" document:

Before talking about all that red text – the added features since the last image – just consider how much content is here: an illustration, complete with textual guidance, for every use case a BI developer could possibly think of (within reason). And plenty of examples some of us never would have imagined.

There is a lot here – I would literally need two weeks off to decipher and understand every bell and whistle. (To think I was proud of myself for modeling Essbase into the RPD last week!) But I am glad to have the option to try... The advanced visualizations are far from what my organization is ready for; I can’t let some people around here see these or they might get scared off! In some ways, these displays of might stand in contrast to Oracle BI’s selling points of simplicity and ease.

But I’m definitely going to have to try and replicate the JavaScript WordCloud visualization! That is just too damn cool...

The VirtualBox Image can be downloaded here.

(Just make sure you have a solid connection and plenty of hard drive space; the files are very large...)

Will be back soon with some more specific illustrations; I have a treasure trove of OBIEE content to publish but have not been on my blog game!

Labels: BI, Business Intelligence, OBIEE, Oracle, Oracle BI

Sunday, July 22, 2012

Pitch Anything in 15 Seconds...

The other morning I was carrying on as usual, reading Twitter on my iPhone while walking down the street and trying not to run into any street signs (I'm an early riser, so no real risk of encountering other humans on the sidewalk at that hour...) A headline caught my eye: "How to Pitch Anything in 15 Seconds" -- here is the link: http://www.forbes.com/sites/carminegallo/2012/07/17/how-to-pitch-anything-in-15-seconds/ and below is the YouTube video.

As I said on my own Twitter feed, this short video (4:35) was exactly what the doctor ordered. Although I have surely read about something similar before, I was struck by the simplicity of Carmine Gallo's "message map" approach.

The most pressing "sales" challenge in my day job is business intelligence, and more specifically a project to replace our aging reporting platform with Oracle BI. My struggle has been to explain the program quickly to various stakeholders from across my organization in a way that excites them rather than glazes their eyes. In part, this is the problem of IT people trying do marketing... and it also reflects a disconnect between priorities; the reasons end users want a modern platform may not be the same as the reasons IT wants it.

Within about an hour of watching the video, I drafted a new slide. I thought it was pretty good. Certainly better than any other one-pager we have assembled. The tool's call for brevity -- <140 characters for the tagline, 3 reinforcing points, 3-5 sub-reinforcements -- is wise, but tricky. At first I thought about the old writers' joke "why use one word when X can do?" But that can get dangerous if it leads you to using consulting words and jargon: synergy, robust, ad hoc, real-time, integrated, seamless. Not that there is anything wrong with those words... But they are hollow.

I have a lot more work to do before this message map is ready for prime time... Nevertheless, I'll share the first draft:

Tagline: "[BI Program Name] will simplify and expand your capability to answer important questions"

Self-Service for Everyone
Adapting with the Business
Robust, But Friendly

Like I said, it needs another round or two (or ten) of revisions. But what do you think?

Labels: #PMOT, OBIEE, Oracle BI, Project Management, Project Marketing

Tuesday, October 11, 2011

Oracle VirtualBox and OBIEE

Where have you been my whole life, Oracle VM VirtualBox and the OBIEE 11.1.1.5 image? Lurking in plain sight, probably advertised on Oracle corporate and partner Twitter posts just outside the latest 50 each time I pulled out my iPhone between meetings, in elevators, or waiting on line at SBUX. We went through so much trouble configuring a demo machine (with Windows Server 64-bit… ouch) when all we needed to do was hook up to a fat pipe, download you, and turn the key to start.

I learned about the VirtualBox Image last week at #OOW11 in San Francisco, in a throw-away comment from the product manager for OBI Scorecards and Strategy. Flew home and checked the specs – 4 GB RAM? I’ve got that on my home laptop. Fat pipe, not so much. It took nearly 12 hours to download the install packages from ftp.oracle.com but only another hour to get OBIEE up and running.

Other than 4 GB of RAM, what does one need for this install? 7zip (http://www.7-zip.org/) and an FTP client (FileZilla - http://filezilla-project.org/). And Oracle VirtualBox, which you can download from here: http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html

The real heavy lifting (for you network, anyhow) comes with the SampleApp V107 Image. As you can see at this link the download is roughly 25GB. This took forever over my slow Wifi on my slow at-home Comcast connection. The installation guide steps through all the (rather straight-forward) installation steps from that point forward; it also contains all the passwords one needs to get the party started.

Once connected, the “General Index” provides a learning map – literally hundreds of dashboard pages devoted to individual concepts and terminology in OBIEE. For example, dig into Section 0.2 to learn about the security set-up in the Sample App as well as key features in OBIEE such as the “Act As” proxy option. Or navigate to 3.20 to see various options for formatting your Answers reports. Or visit 8.7 to see how OBIEE allows fidelity of interactivity between both relational and flat-file data sources. After traipsing through each dashboard and drilling down to “Edit” with the objective of sorting out the underlying configuration, the power and flexibility of the technology become obvious—and far more tangible than literature, conference presentations, or canned demos.

More later this week after my hands are a little dirtier…

Labels: #OOW11, OBIEE, Oracle

Contact Me

Email
Twitter

Tweets by @jasonshaffner