Skip navigation.

Rittman Mead Consulting

Syndicate content Rittman Mead Consulting
Delivering Oracle Business Intelligence
Updated: 15 hours 49 min ago

Using Python to ‘Wrangle’ Your Messy Data

Mon, 2016-02-08 00:00
 or How to Organize Your Comic Book Collection Based on Issue Popularity

In addition to being a product manager at Rittman Mead, I consider myself to be a nerd of the highest order. My love of comic books, fantasy, sci-fi and general geekery began long before the word ‘Stark’ had anything to do with Robert Downey Jr or memes about the impending winter. As such, any chance to incorporate my hobbies into my work is a welcomed opportunity. For my next few blog entries, I’ve decided to construct a predictive classification model using comic book sales data whose eventual goal will be to build a model that can accurately predict whether a comic will rocket off the shelves or if it will be a sales dud. The first blog of the series shows some of the pitfalls that can come up when preparing your data for analysis. Data preparation, or data wrangling as it has come to be known, is an imperfect process that usually takes multiple iterations of transformation, evaluation and refactoring before the data is “clean” enough for analysis.

While the steps involved in data wrangling vary based on the state and availability of the raw data, for this blog I have chosen to focus on the gathering of data from disparate sources, the enrichment of that data by merging their attributes and the restructuring of it to facilitate analysis. Comic book sales data is readily available on the interwebs, however finding that data in a usable format proved to be a more difficult task. In the end, I had to resort to dreaded process of screen scraping the data from a comic research site. For those of you who are lucky enough be unfamiliar with it, screen scraping is the process of programmatically downloading HTML data and stripping away that formatting to make it suitable for use. This is generally used as a last resort because web sites are volatile creatures that are prone to change their appearance as often as my teenage kids do preparing to leave the house. However, for the purposes of this series, as my friend Melvin the handyman would often say, “We works with what we gots.”

blog-ironman-pythonexclamation-point-icon-30522This leads us to the first issue you may run into while wrangling your data. You have access to lots of data but it’s not pretty. So make it pretty.  Working with raw data is like being a sculptor working with wood. Your job is not to change the core composition of the data to suit your purposes but to chip away at the excess to reveal what was there all along, a beautiful horse… er I mean insight. Sorry, I got lost in my analogy.  Actually to expand on this analogy a bit, the first tool I pulled out of my toolbox for this project was Python, the Leatherman of  programming languages.  Python is fast, plays well with other technologies and most importantly in this case, Python is ubiquitous. Used for tasks ranging from process automation and ETL to gaming and academic pursuits, Python is truly a multipurpose tool. As such, if you have a functional need, chances are there is a native module or someone has already written a public library to perform that function.  In my case, I needed some scripts to “scrape” HTML tables containing comic sales data and combine that data with other comic data that I retrieved elsewhere. The “other” data is metadata about each of the issues. Metadata is just data about data. In this case, information about who authored it, how it was sold, when it was published, etc… More on that later.  

blog-sales-tableLuckily for me, the format of the data I was scraping was tabular, so extracting the data and transforming it into Python objects was a relatively simple matter of iterating through the table rows and binding each table column to the designated Python object field. There was still a lot of unnecessary content on the page that needs to be ignored, like the titles and all of the other structural tags, but once I found the specific table holding the data, I was able to isolate it. At that point, I wrote the objects to to a CSV file, to make the data easy to transport and to facilitate usability by other languages and/or processes.

The heavy lifting in this process was performed by three different Python modules: urllib2, bs4 and csv. Urllib2, as the name implies, provides functions to open URLs. In this case, I found a site that hosted a page containing the estimated issue sales for every month going back to the early 1990’s. To extract each month without manually updating the hardcoded URL over and over, I created a script that accepted MONTH and YEAR as arguments,

The response from the urlopen(url) function call was the full HTML code that is typically rendered by a web browser. In that format, it does me very little good, so I needed to employ a parser to extract the data from the HTML. In this context, a parser is a program that is used to read in a specific document format, break it down into its constituent parts while preserving the established relationships between those parts, and then finally provide a means to selectively access said parts. So an HTML parser would allow me to easily access all the <TD> column tags for a specific table within an HTML document. For my purposes, I chose BeautifulSoup, or bs4.

BeautifulSoup provided search functions that I used to find the specific HTML table containing the sales data and loop through each row, while using the column values to populate a Python object.


This Python object, named data, contains fields populated with data from different sources. The year and month are populated using the arguments passed to the module. The format field is dynamically set based on logic related to the rankings and the remaining fields are set based on their source’s position in the HTML table. As you can see, there is a lot of hard coded logic that would need to be updated, should the scraped site change their format. However, for now this logic gets it done.

The final step of this task was to write those Python objects to a CSV file. The python module, CSV, provides the function writerow(), which accepts an array as a parameter and writes each of the array elements as columns in the CSV.

My first pass raised the an exception because the title field contained unicode characters that the CSV writer could not handle.

To rectify this, I had to add a check for unicode and encoded the content as UTF-8. Unicode and UTF-8 are character encodings; meaning they provide a map computers use to identify characters. This includes alphabets and logographic symbols from different languages as well as common symbols like ®.


Additionally, there was the matter of reformatting the values of some of the numeric fields to allow math to be performed on them later(ie stripping ‘$’ and commas). Other than that, the data load went pretty smoothly. A file named (MONTH)_(YEAR).CSV was generated for each month. Each file turned out like so:


While this generated tens of thousands of rows of comic sales data, it was not enough. Rather, it had the volume but not the breadth of information I needed. In order to make an accurate prediction, I needed to feed more variables to the model than just the comic’s title, issue number, and price. The publisher was not relevant as I decided to limit this exercise to only Marvel comics and passing in the the estimated sales would be cheating, as rank is based on sales. So to enhance my dataset, I pulled metadata about each of the issues down from “the Cloud” using Marvel’s Developer API. Thankfully, since the API is a web service, there was no need for screen scraping.

exclamation-point-icon-30522Retrieving and joining this data was not as easy as one might think. My biggest problem was that the issue titles from the scraped source were not an exact match to the titles stored in the Marvel database. For example, the scraped dataset lists one title as ‘All New All Different Avengers”. Using their API to search the Marvel database with that title retrieved no results. Eventually, I was able to manually find it in their database listed as “All-New All-Different Avengers”. In other cases, there were extra words like “The Superior Foes of Spider-Man” vs “Superior Foes of Spider-Man”. So in order to perform a lookup by name, I needed to know the titles as they expected them. To do this I decided to pull a list of all the series titles whose metadata was modified during the timeframes for which I had sales data. Again, I ran into a roadblock. The Marvel API only allows you to retrieve up to 100 results per request and Marvel has published thousands of titles. To get around this I had to perform incremental pulls, segmented alphabetically. 


Even then there were a few minor issues, as some letters like ‘S’ had more than 100 titles. To get around that I had to pull the list for ‘S’ titles sorted ascending and descending then combine the results, making sure to remove duplicates. So my advice on this one is be sure to read up on the limitations of any API you are using. It may enforce limits but you may be able to work around the limits using creative querying.


At this point I have my list of Marvel series titles, stored in some CSV files that I eventually combined into a single file, MarvelSeriesList.csv, for ease of use. Actually, I have more than that. While retrieving the series titles, I also pulled down the ID for the series and an appropriateness rating. Searching the API by ID will be much more accurate than name and the appropriateness  rating may be useful when building out the prediction model. The next step was to iterate through each row of the CSVs we created from the sales data, find the matching ID from MarvelSeriesList.csv and use that ID to retrieve its metadata using the API.

exclamation-point-icon-30522If you remember, the point of doing that last step was that the titles stored in the sales data files don’t match the titles in the API, so I needed to find a way to join the two sources. Rather than writing cases to handle each of the scenarios (e.g. mismatched punctuation, extra filler words), I looked for a python library to perform some fuzzy matching. What I found was a extremely useful library called, Fuzzy Wuzzy. Fuzzy Wuzzy provides a function called extractOne() that allows you to pass in a term and compare it with an array of values. The extractOne() function will then return the term in the array that has the highest match percentage. Additionally, you can specify a lower bound for acceptable matches (ie. only return result where the match is >= 90%).

Again, it took a few passes to get the configuration to work effectively. The first time through about only about 65% of the titles in the sales file found a match. That was throwing away too much data for my liking so I had to look at the exceptions and figure out why the matches were falling through. One issue that I found was titles that tacked on the publication years in the Marvel database, like “All-New X-Men (2012)”, had a match score in the 80’s when matched against a sales title like, “All New X-Men”. This was a pretty consistent issue, so rather than lowering the match percentage, which could introduce some legitimate mismatches, I decided to strip the year, if present, on mismatches and run it through that matching process again. This got me almost there. The only other issue I ran into was Fuzzy Wuzzy had trouble matching acronyms/acrostics. So ‘S.H.E.I.L.D.’  had a match score in the 50’s when matching ‘SHIELD’. That’s because half the characters (periods) were missing. Since  there were only two titles affected, I built a lookup dictionary of special cases that needed to be translated. For the purposes of this exercise, I would still have had enough matches to skip that step, but by doing it brought us up to 100% matching between the two sources. Once the matching function was working, I pulled out urllib2 and retrieved all the metadata I could for each of the issues.

The resulting files contained not only sales data (title, issue number, month rank, estimated sales), but information about the creative team, issue descriptions, characters, release dates and  associated story arcs. This would be enough to get us started with building our predictive classification model.
blog-csv-all That being said, there was still a lot of structural rearranging required to make it ready for the type of analysis I wanted to do, but we will deal with that in the next blog. Hopefully,  you picked up some useful tips on how to combine data from different sources or at the very least found solace in knowing that while you may not be the coolest person in the world, somewhere out there is a grown man who still likes to read comic books enough to write a blog about it. Be sure to tune in next week, True Believers, as we delve into The Mysteries of R!

The post Using Python to ‘Wrangle’ Your Messy Data appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Becky’s BI Apps Corner: OBIA install Perl Script Patching and troubleshooting when they fail

Thu, 2016-02-04 05:00

During a recent project installing Oracle BI Applications, I became much better acquainted with OPatch, Oracle’s standard tool for managing application patches. By acquainted, I mean how to troubleshoot when OPatch patching fails. Since, at last count, there are around 50 patches total for Oracle BI Applications, the first patching attempt may not apply all patches successfully. There are any number of reasons for a failure, like an extra slash at the end of a path, a misspelled word, Weblogic or NodeManager still running, or some other reason. We will take a look at the logs for each section, learn where additional logs can be found, and learn how to turn on OPatch debugging to better understand the issue. Then, following the ideas from a previous OPatch post by Robin, I’ll describe how to manually apply the patches with OPatch at the command line for any patches that weren’t applied successfully using the provided perl script.

*Disclaimers – Please read the readme files for patches and follow all Oracle recommendations. Patch numbers are subject to change depending on OS and OBIA versions. Commands and paths here are of the linux/unix variety, but there are similar commands available for Windows OS.

Perl Script patching

Unzip the patch files to a patch folder. I have included the OBIEE patch as well.

unzip -d patches/
unzip -d patches/
unzip -d patches/
unzip -d patches/

While installing the Oracle BI Applications versions and up, patches get applied with a perl script called Following Oracle’s install documentation for version of Oracle BI Applications here, there is a text file to modify and pass to the perl script. Both the perl script and the text file reside in the following directory: $ORACLE_HOME/biapps/tools/bin. In the text file, called apply_patches_import.txt, parameters are set with the path to the following directories:

WINDOWS_UNZIP_TOOL_EXE (only needed if running on Windows platforms)

Some pro tips to modifying this text file:
1. Oracle recommends you use the JDK in the ORACLE_BI1 directory.
3. Ensure WORKDIR and PATCH_ROOT_DIR are writeable directories.
4. Don’t add a path separator at the end of the path.
5. Commented lines are safe to remove.

Then you run the passing in the apply_patches_import.txt. If everything goes well, at the end of the perl script, the results will look similar to the following:

If this is the case, CONGRATULATIONS!!, you can move on to the next step in the install documentation. Thanks for stopping by and come back soon! However, if any patch or group of patches failed, the rest of this post is for you.

Log file location

First, the above patching report does not tell you where to find the logs, regardless of success or failure. If you remember though, you set a WORKDIR path in the text file earlier. In that directory is where you will find the following log files:

  1. final_patching_report.log
  2. biappshiphome_generic_patches.log
  3. odi_generic_patches.log
  4. oracle_common_generic_patches.log
  5. weblogic_patching.log

Open the final_patching_report.log to determine first if all patches were applied and identify ones that were not successful. For example, looking that this log may show that the Oracle Common patches failed.

vi final_patching_report.log

However, this doesn’t tell you what caused the failure. Next we will want to look into the oracle_common_generic_patches.log to gather more information.

From the $WORKDIR:

vi oracle_common_generic_patches.log

Here you will see the error, that a component is missing. Patch ######## requires component(s) that are not installed in OracleHome. These not-installed components are oracle.jrf.thirdparty.jee: Notice also that in this log there is a path to another log file location. The path is in the $COMMON_ORACLE_HOME/cfgtoollogs/opatch/ directory. This directory has more detailed logs specific to patches applied to oracle_common. Additionally, there are logs under $ORACLE_HOME/cfgtoollogs/opatch/, $WL_HOME/cfgtoollogs/opatch/, and $ODI_HOME/cfgtoollogs/opatch/. These locations are very helpful to know, so you can find the logs for each group of patches in the same relative path.

Going back to the above error, we are going to open the most recent log file listed in the $COMMON_ORACLE_HOME/cfgtoollogs/opatch/ directory.

cd $COMMON_ORACLE_HOME/cfgtoollogs/opatch/
vi opatch2015-08-08_09-20-58AM_1.log

The beginning of this log file has two very interesting pieces of information to take note of for use later. It has the actual OPatch command used, and it has a path to a Patch History file. Looks like we will have to page down in the file to find the error message.

Now we see our missing component error. Once the error occurs, the java program starts backing out and then starts cleanup by deleting the files extracted earlier in the process. This log does have more detail, but still doesn’t say much about the missing component. After some digging around on the internet, I found a way to get more detailed information out of OPatch by setting export OPATCH_DEBUG=TRUE. After turning OPatch debugging on, run the OPatch command we found earlier that was at the top of the log. A new log file will be generated and we want to open this most recent log file.

Finally, the results now get me detailed information about the component and the failure.

Side Note: If you are getting this specific error, I’ll refer you back to a previous blog post that happened to mention making sure to grab the correct version of OBIEE and ODI. If you have a wrong version of OBIEE or ODI for the Oracle BI Apps version you are installing, unfortunately you won’t start seeing errors until you get to this point.

Manually running Oracle BI Application patches

Normally, the error or reason behind a patch or group of patches failing doesn’t take that level of investigation, and the issue will be identified in the first one or two logs. Once the issue is corrected, there are a couple of options available. Rerunning the perl script is one option, but it will cycle through all of the patches again, even the ones already applied. There is no harm in this, but it does take longer than running the individual patches. The other option is to run the OPatch command at the command line. To do that, first I would recommend setting the variables from the text file. I also added the Oracle_BI1/OPatch directory to the PATH variable.

export COMMON_ORACLE_HOME=$MW_HOME/oracle_common
export WL_HOME=$MW_HOME/wlserver_10.3
export SOA_HOME=$MW_HOME/Oracle_SOA1
export ODI_HOME=$MW_HOME/Oracle_ODI1
export WORKDIR=
export PATCH_FOLDER=/patches

Next, unzip the patches in the required directory. For example, the $PATCH_FOLDER/oracle_common/generic might look like this after unzipping files:

Below are the commands for each group of patches:

Oracle Common Patches:

cd $PATCH_FOLDER/oracle_common/generic
unzip "*.zip"

$COMMON_ORACLE_HOME/OPatch/opatch napply $PATCH_FOLDER/oracle_common/generic -silent -oh $COMMON_ORACLE_HOME -id 16080773,16830801,17353546,17440204,18202495,18247368,18352699,18601422,18753914,18818086,18847054,18848533,18877308,18914089,19915810

BIApps Patches:

cd $PATCH_FOLDER/biappsshiphome/generic
unzip "*.zip"

opatch napply $PATCH_FOLDER/biappsshiphome/generic -silent -id 16913445,16997936,19452953,19526754,19526760,19822893,19823874,20022695,20257578

ODI Patches:

cd $PATCH_FOLDER/odi/generic
unzip "*.zip"

/$ODI_HOME/OPatch/opatch napply $PATCH_FOLDER/odi/generic -silent -oh $ODI_HOME -id 18091795,18204886

Operating Specific Patches:

unzip "*.zip"

opatch napply $PATCH_FOLDER/ -silent -id ,,

Weblogic Patches:

cd $PATCH_FOLDER/suwrapper/generic
unzip "*.zip"

cd $PATCH_FOLDER/weblogic/generic

$JAVA_HOME/bin/java -jar $PATCH_FOLDER/suwrapper/generic/bsu-wrapper.jar -prod_dir=$WL_HOME -install -patchlist=JEJW,LJVB,EAS7,TN4A,KPFJ,RJNF,2GH7,W3Q6,FKGW,6AEJ,IHFB -bsu_home=$MW_HOME/utils/bsu -meta=$PATCH_FOLDER/suwrapper/generic/suw_metadata.txt -verbose > $PATCH_FOLDER/weblogic_patching.log

Even though this is a very specific error as an example, understanding the logs and having the break-down of all of the patches will help with any number of patch errors at this step of the Oracle BI Applications installation. I would love to hear your thoughts if you found this helpful or if any part was confusing. Keep an eye out for the next Becky’s BI Apps Corner where I move on from installs and start digging into incremental logic and Knowledge Modules.

The post Becky’s BI Apps Corner: OBIA install Perl Script Patching and troubleshooting when they fail appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Corporate Social Responsibility (Where Can We Serve?)

Mon, 2016-02-01 03:00

At Rittman Mead, we believe that people are more important than profit.
This manifests itself in two ways. First, we want to impact the world beyond data and analytics, and secondly, we want our employees to be able to contribute to organizations they believe are doing impactful work.

This year, we’ve put a Community Service requirement in place for all of our full-time employees.

We’ll each spend 40 hours this year serving with various nonprofits. Most of our team are already involved with some amazing organizations, and this “requirement” allows us to not only be involved after hours and on the weekends, but even during normal business hours.

We want to highlight a few team members and show how they’ve been using their Community Service hours for good.

Beth deSousa
Beth is our Finance Manager and she has been serving with Sawnee Women’s Club. Most of her work has been around getting sponsorship and donations for their annual silent auction. She’s also helped with upgrading a garden at the local high school, collecting toys and gift wrap for their Holiday House, and collecting prom dresses and accessories for girls in need.

Charles Elliott
Charles is the Managing Director of North America. He recently ran in the Dopey Challenge down at Disney World which means he ran a 5k, 10k, half marathon, and full marathon in 4 days. He did the run to raise funds for Autism Speaks. Charles was recognized as the third largest fundraiser for Autism Speaks at the Dopey Challenge!

David Huey
David is our U.S. Business Development rep. He recently served with the nonprofit Hungry For A Day for their Thanksgiving Outreach. He flew up to Detroit the week of Thanksgiving and helped serve over 8,000 Thanksgiving dinners to the homeless and needy in inner city Detroit.

Andy Rocha

Andy is our Consulting Manager. Andy is a regular volunteer and instructor with Vine City Code Crew. VC3 works with inner city youth in Atlanta to teach them about electronics and coding.

Pete Tamisin

Pete is a Principal Consultant. He is also involved as a volunteer and instructor with the aforementioned Code Crew. Pete has taught a course using Makey Makey electronic kits for VC3.

This is just a sample of what our team has done, but engaging in our local communities is something that Rittman Mead is striving to make an integral piece of our corporate DNA.
We can’t wait to show you how we’ve left our communities better in 2016!

The post Corporate Social Responsibility (Where Can We Serve?) appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

The best OBIEE 12c feature that you’re probably not using

Thu, 2016-01-28 03:00

With the release of OBIEE 12c we got a number of interesting new features on the front-end.  We’re all talking about the cleaner look-and-feel, Visual Analyzer, and the ability to create data-mashups, etc.

While all this is incredibly useful, it’s one of the small changes you don’t hear about that’s truly got me excited.  I can’t tell you how thrilled I am that we can finally save a column back to the web catalog as an independent object (to be absolutely fair, this actually first shipped with

For the most part, calculations should be pushed back to the RPD.  This reduces the complexity of the reports on the front-end, simplifies maintenance of these calculations, and ultimately assures that the same logic is used across the board in all dashboards and reports… all the logic should be in the RPD.  I agree with that 110%… at least in theory.  In reality, this isn’t always practical.  When it comes down to it, there’s always some insane deadline or there’s that pushy team (ahem, accounting…) riding you to get their dashboard updated and migrated in time for year end, or whatever.  It’s quite simply just easier sometimes to just code the calculation in the analysis.  So, rather than take the time to modify the RPD, you fat finger the calculation in the column formula.  We’ve all done it.  But, if you spend enough time developing OBIEE reports and dashboards, sooner or later you’ll find that this is gonna come back to bite you.

Six months, a year from now, you’ll have completely forgotten about that calculation.  But there will be a an org change, or a hierarchy was updated… something, to change the logic of that calculation and you’ll need make a change.  Only now, you know longer remember the specifics of the logic you coded, and even worse you don’t remember if you included that same calculation in any of the other analyses you were working on at the time.  Sound familiar?  Now, a change that should have been rather straightforward and could have been completed in an afternoon takes two to three times longer as you dig through all your old reports trying to make sense of things.  (If only you’d documented your development notes somewhere…)

Saving columns to the web catalog is that middle ground that gives us the best of both worlds… the convenience of quickly coding the logic on the front-end but the piece of mind knowing that the logic is all in one place to ensure consistency and ease maintenance.

After you update your column formula, click OK.

From the column dropdown, select the Save Column As option.

Save the column to the web catalog.  Also, be sure to use the description field.  The description is a super convenient place to store a few lines of text that your future self or others can use to understand the purpose of this column.

As an added bonus, this description field is also used when searching the web catalog.  So, if you don’t happen to remember what name you gave a column but included a little blurb about the calculation, all is not lost.

Saved columns can be added from the web catalog.

Add will continue to reference the original saved column, so that changes to made to the saved column will be reflected in your report.  Add Copy will add the column to your report, but future changes to the saved column will not be reflected.

One thing to note, when you add a saved column to a report it can no longer be edited from within the report.  When you click on Edit Formula you will still be able to see the logic, but you will need to open and edit that saved column directly to make any changes to the formula.

Try out the saved columns, you’ll find it’s invaluable and will greatly reduce the time it takes to update reports.  And with all that free time, maybe you’ll finally get to play around with the Visual Analyzer!

The post The best OBIEE 12c feature that you’re probably not using appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

OBIEE 12c – Three Months In, What’s the Verdict?

Sun, 2016-01-24 13:22

I’m over in Redwood Shores, California this week for the BIWA Summit 2016 conference where I’ll be speaking about BI development on Oracle’s database and big data platforms. As it’s around three months since OBIEE12c came out and we were here for Openworld, I thought it’d be a good opportunity to reflect on how OBIEE12c has been received by ourselves, the partner community and of course by customers. Given OBIEE11g was with us for around five years it’s clearly still early days in the overall lifecycle of this release, but it’s also interesting to compare back to where we were around the same time with 11g and see if we can spot any similarities and differences to take-up then.

Starting with the positives; Visual Analyzer (note – not the Data Visualization option, I’ll get to that later) has gone down well with customers, at least over in the UK. The major attraction for customers seems to be “Tableau with a shared governed data model and integrated with the rest of our BI platform” (see Oracle’s slide from Oracle Openworld 2015 below), and seeing as the Data Visualization Option is around the same price as Tableau Server the synergies around not having to deploy and support a new platform seem to outweigh the best-of-breed query functionality of Tableau and Qlikview.


Given that VA is an extra-cost option what I’m seeing is customers planning to upgrade their base OBIEE platform from 11g to 12c as part of their regular platform refresh, and considering the VA part after the main upgrade as part of a separate cost/benefit exercise. VA however seems to be the trigger for customer interest in an upgrade, with the business typically now holding the budget for BI rather than IT and Visual Analyzer (like Mobile with 11g) being the new capability that unlocks the upgrade spend.

On the negative side, Oracle charging for VA hasn’t gone down well, either from the customer side who ask what it is they actually get for their 22% upgrade and maintenance fee if they they have to pay for anything new that comes with the upgrade; or from partners who now see little in the 12c release to incentivise customers to upgrade that’s not an additional cost option. My response is usually to point to previous releases – 11g with Scorecards and Mobile, the database with In-Memory, RAC and so on – and say that it’s always the case that anything net-new comes at extra cost, whereas the upgrade should be something you do anyway to keep the platform up-to-date and be prepared to uptake new features. My observation over the past month or so is that this objection seems to be going away as people get used to the fact that VA costs extra; the other push-back I get a lot is from IT who don’t want to introduce data mashups into their environment, partly I guess out of fear of the unknown but also partly because of concerns around governance, how well it’ll work in the real world, so on and so on. I’d say overall VA has gone down well at least once we got past the “shock” of it costing extra, I’d expect there’ll be some bundle along the lines of BI Foundation Suite (BI Foundation Suite Plus?) in the next year or so that’ll bundle BIF with the DV option, maybe include some upcoming features in 12c that aren’t exposed yet but might round out the release. We’ll see.

The other area where OBIEE12c has gone down well, surprisingly well, is with the IT department for the new back-end features. I’ve been telling people that whilst everyone thinks 12c is about the new front-end features (VA, new look-and-feel etc) it’s actually the back-end that has the most changes, and will lead to the most financial and cost-saving benefits to customers – again note the slide below from last year’s Openworld summarising these improvements.


Simplifying install, cloning, dev-to-test and so on will make BI provisioning considerably faster and cheaper to do, whilst the introduction of new concepts such as BI Modules, Service Instances, layered RPD customizations and BAR files paves the way for private cloud-style hosting of multiple BI applications on a single OBIEE12c domain, hybrid cloud deployments and mass customisation of hosted BI environments similar to what we’ve seen with Oracle Database over the past few years.

What’s interesting with 12c at this point though is that these back-end features are only half-deployed within the platform; the lack of a proper RPD upload tool, BI Modules and Services Instances only being in the singular and so on point to a future release where all this functionality gets rounded-off and fully realised in the platform, so where we are now is that 12c seems oddly half-finished and over-complicated for what it is, but it’s what’s coming over the rest of the lifecycle that will make this part of the product most interesting – see the slide below from Openworld 2014 where this full vision was set-out, but in Openworld this year was presumably left-out of the launch slides as the initial release only included the foundation and not the full capability.


Compared back to where we were with OBIEE11g (, at the start of the product cycle) which was largely feature-complete but had significant product quality issues, with 12c we’ve got less of the platform built-out but (with a couple of notable exceptions) generally good product quality, but this half-completed nature of the back-end must confuse some customers and partners who aren’t really aware of the full vision for the platform.

And finally, cloud; BICS had an update some while ago where it gained Visual Analyzer and data mashups earlier than the on-premise release, and as I covered in my recent UKOUG Tech’15 conference presentation it’s now possible to upload an on-premise RPD (but not the accompanying catalog, yet) and run it in BICS, giving you the benefit of immediate availability of VA and data mashups without having to do a full platform upgrade to 12c.


In-practice there are still some significant blockers for customers looking to move their BI platform wholesale into Oracle Cloud; there’s no ability yet to link your on-premise Active Directory or other security setup to BICS meaning that you need to recreate all your users as Oracle Cloud users, and there’s very limited support for multiple subject areas, access to on-premise data sources and other more “enterprise” characterises of an Oracle BI platform. And Data Visualisation Cloud Service (DVCS) has so far been a non-event; for partners the question is why would we get involved and sell this given the very low cost and the lack of any area we can really add value, while for customers it’s perceived as interesting but too limited to be of any real practical use. Of course, over the long term this is the future – I expect on-premise installs of OBIEE will be the exception rather than the rule in 5 or 10 years time – but for now Cloud is more “one to monitor for the future” rather than something to plan for now, as we’re doing with 12c upgrades and new implementations.

So in summary, I’d say with OBIEE12c we were pleased and surprised to see it out so early, and VA in-particular has driven a lot of interest and awareness from customers that has manifested itself in enquires around upgrades and new features presentations. The back-end for me is the most interesting new part of the release, promising significant time-saving and quality-improving benefits for the IT department, but at present these benefits are more theoretical than realisable until such time as the full BI Modules/multiple Services Instances feature is rolled-out later this year or next. Cloud is still “one for the future” but there’s significant interest from customers in moving either part or all of their BI platform to the cloud, but given the enterprise nature of OBIEE it’s likely BI will follow after a wider adoption of Oracle Cloud for the customer rather than being the trailblazer given the need to integrate with cloud security, data sources and the need to wait for some other product enhancements to match on-premise functionality.

The post OBIEE 12c – Three Months In, What’s the Verdict? appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Upgrade to ODI 12c: Repository and Standalone Agent

Wed, 2016-01-20 22:00

This is my first post, and I hope to have many more to come. My name is Brian Sauer, and I am fairly new here at Rittman Mead and proud to be here. I have close to 5 years of experience with Oracle Data Integrator, both 10g and 11c. I have been involved in ETL activities for close to 8 years. Prior to using Oracle Data Integrator, most of my activities in ETL utilized Perl. Ever since being exposed to Oracle and ODI, I have found a home that has been both comforting and challenging. It is a passion of mine to be able to work with any type of database and to get data from point A to point B, making the necessary changes to get user’s hands on it in a meaningful and useful way. Thus, I’ve put together this blog post to assist with the upgrade of ODI 11g to ODI 12c. I hope it’s useful to you.

After going through the process of upgrading from ODI 11g to ODI 12c, I found the documentation to be scattered around a bit and began putting together a roadmap of the information that was vital to the upgrade itself. This post is essentially a brief explanation of some added enhancements to ODI in and a straightforward guide to the upgrade process. I’m not addressing in this post, as many of our clients have been moving to due to stability and testing issues with I will take a look in my next post in this series.

ODI was released in November 2014, and along with it came a number of enhancements and significant changes from ODI I am going to focus on the upgrade process with this post and some of the more significant changes.

According to the Oracle documentation, the only supported starting points for upgrading to ODI 12c (12.1.3) are the following:

  • Oracle Data Integrator 11g Release 1 (
  • Oracle Data Integrator 11g Release 1 (

There are some significant changes to ODI from 11g. First and foremost is the way that the agents are handled in 12c. Standalone Agents are now managed by the WebLogic Management Framework, and they are installed in their own directories. Also, interfaces have given way to mappings and reusable mappings. Lastly, the repository upgrade to 12.1.3 validates name uniqueness for objects such as interfaces, folders, procedures, knowledge modules, packages, and profiles. If duplicates exist, the upgrade process will check for it, and you can print a report that will list the duplicates in the upgrade log. You’ll then need to reopen ODI 11g and manually fix the duplicates and restart the upgrade.

Below is a comparison of the ODI JEE Agent Upgrade topology. Notice that in 12c only an LDAP-based store can be used for OPSS as file-based stores are no longer allowed.

Picture 1

You’ll also notice the topology for Standalone Agents not registered with a WebLogic Domain have changed as mentioned earlier; they are now part of the WebLogic Management Framework. The agent is configured in a standalone domain unlike its predecessor in 11g.

Picture 2

For Standalone Agents in 11g that were registered with a WebLogic Domain nothing has really changed from a topology standpoint as shown below:

Picture 3

Some considerations to take into account before diving into the upgrade are the following:

  • Verify that the database which will host the schemas required by Fusion Middleware is supported. You can verify at:
  • ODI 12.1.3 requires a 64-bit operating system. Verify your machine is 64-bit, and, if not, then upgrade.
  • Verify you are running a supported version (,
  • Develop a backup and recovery strategy.
  • Plan for system downtime during the upgrade process.

Upgrade Process


The first and probably most important step before you even begin the upgrade is to make sure you have a solid backup of your 11g environment. There are many ways to do this, but you will want to make sure that you have a backup of the following so you can restore your prior environment in the event that a failure occurs:

  1. ODI Home Directory
  2. ODI Work Repository(s)
  3. ODI Master Repository

To preserve the ODI 11g Home Directory, I simply make a copy of the ODI_HOME directory, zip it up, and save in another location. To preserve the ODI Master and Work Repositories, simply use the export utility within ODI.


This will provide a saved copy of each. In addition to the backup, record the Repository IDs in the event you need to restore. Lastly, I create a database backup of the ODI schemas that will be upgraded. These are the schemas where the Master and Work Repositories reside.

Zero or Limited Downtime Approaches

An important question is, How can I upgrade my system with limited or even zero downtime?

Oracle recommends cloning the work and master repositories on your database in another database instance and then upgrading the cloned repositories. This will keep your current 11g repositories active and untouched during the upgrade process, and once the upgrade is complete and you’ve been able to test it out, you can switch to the new 12c repositories as your active ODI implementation. Oracle has provided sample scripts for different database environments at the following location:

As a part of your testing you will want to verify, after you have set up your cloned repositories, that the cloned master repository points to the correct work repository(s). Also, as a part of cloning the repositories, you’ll need to copy the row about the master repository in SYSTEM.SCHEMA_VERSION_REGISTRY$ from the old to the new instance.  This is not a supported change by Oracle, however it is required for the upgrade to be successful.

Another upgrade option other than cloning is to create a brand new ODI 12c repository on your database and then export your objects from 11g and import them individually to your new 12c repository using ODI Studio. To perform this task you’ll need to use the upgrade key which is new to 12c and which I’ve explained later in this post for the imports. This is not my recommended approach as it takes longer and is more tedious which can lead to missed objects during the export/import process.

However, if you choose to go this route, the export/import order I have used in the past is the following:

  1. Topology
  2. Models
  3. Global Objects
  4. Projects

Installing Oracle Data Integrator 12c Products

Now that backups have been made and we are confident that in the event of an unsuccessful upgrade we can recover and we have decided on a transition strategy, it is time to begin installing our 12c products.

We have a couple questions to answer first. Are we upgrading with a Standalone Agent or a Java EE Agent? If we are installing an environment with a Java EE Agent then we will need to download two separate files from Oracle:

  1. Oracle Data Integrator 12cR1 ( for all platforms install file.
  2. Oracle Fusion Middleware Infrastructure (for All Platforms).

If we are installing for a standalone environment which we are in this post, then the second file is not required or needed. You will need approximately 1.5GB of space for the home directory where you install ODI 12c. I would recommend an additional 1GB of space to account for domains for agents. The following link will give you the ODI 12c Studio installation instructions in detail:

Creating/Upgrading the Master and Work Repositories

Now that ODI 12c Studio is installed, we’ll need to create the Master and Work repositories in the database so that they can effectively communicate with ODI 12c Studio. To do this you’ll need to navigate to the <ODI_HOME>/oracle_common/bin directory and run either ./ or rcu.bat, depending on your environment.

Before beginning the creation of the 12c repositories, make sure you have a solid backup of the 11g repositories.                

You’ll want to make sure that you have DBA privileges for this step as you’ll be creating new schemas and tables in the database that will require these permissions. The wizard does give you the option of creating the scripts for the DBA to use later if necessary, although it may be best to work with the DBA directly as you create the repositories.

First, you will want to choose an existing prefix since this is how you identify the repository to be upgraded in the DB.

Secondly, you’ll need to check off the following components to identify them in your 12c upgrade:

  1. Oracle AS Repository Components/AS Common Schemas/Common Infrastructure Services which creates the new *_STB schema for 12c.
  2. Oracle Data Integrator/Master and Work Repository


As you enter the schema passwords and mapping information, the utility will ultimately reach the summary screen where it will show you the new schemas it will create. If any schemas have already been created, be aware that they will not be listed here since they do not need to be created again. At this screen, you click create, and the creation of the new schemas/components will commence.


Once you have created the new schemas necessary for 12c, we will start the Upgrade Assistant so the previous version of ODI can be upgraded to You will find the upgrade assistant at:


You’ll execute the ua.bat or ua file depending on your environment.

When asked what type of upgrade you wish to perform, you will want to select “Schemas” as this will allow you to identify the schemas where the upgrade will take place.


The upgrade assistant will then list the schemas available to upgrade. When asked what components to upgrade, you will select “Oracle Data Integrator”.


The only other item that I’d draw your attention to is the screen providing the “Upgrade Key”. This key is used to convert 11g object IDs into unique GUIDs. You will want to save this key as you will need this for additional 11g objects that may need to be imported at a later time. This key allows ODI to consistently calculate the same GUID for an 11g object when importing to 12c. The key identifies uniquely the set of repositories that were working together before an upgrade. Since the 12c repository has switched to using GUIDs to better guarantee unique ID’s, the 11g ID will not work without providing the upgrade key.


As you upgrade to 12c, the question will likely arises as to why would you need this upgrade key. The situation may arise where you would need to restore an object, import a scenario, or revert back to a prior topology. This key will allow you to do so with your 11g exports.

Once completion of the Upgrade Assistant has occurred, you will want to verify that the version of ODI has been updated in the schema. You can run the following command to verify:


You should see the version for your schema as: with a status of ‘VALID’

Creating the Standalone Agent

Our final step is creating the Standalone Agent. As I wrote earlier, this has changed with 12c since the WebLogic framework is being used now for all agents. Due to this, you cannot upgrade 11g standalone agents to 12c, and you will need to create new ones.

We will place our new Standalone Agent in a domain folder which will sit outside of the ODI_HOME.

To create the Standalone Agent, navigate to ODI_HOME/oracle_common/common/bin and execute either or config.exe based on your operating system.

The first screen that you will come across will ask you to either create a new domain or update an existing domain.  In our case here, we did not have an existing domain so we will create a new Standalone Agent Domain. The wizard will default to a location within your ODI_HOME. You will want to change this location so that it resides OUTSIDE the ODI_HOME as I wrote earlier. The reason for this is that in the future it could create problems for upgrading your ODI software if the domain is created in the ODI_HOME.


The rest of the steps are straightforward. You’ll choose to create the “Oracle Data Integrator – Standalone Agent –[odi]”, enter database configuration information, and choose a user name and password for the Node Configuration. Then, you will be prompted to create your agent.

Once you have completed these steps to start the agent, go to DOMAIN_HOME/bin and select either startNodeManger.cmd or depending on your operating system. You’ll be prompted to enter the credentials you chose when configuring the node above.

Once you’ve completed these steps, you’ll go through the process of creating your agent within ODI Studio both within the Physical and Logical Architecture.


Then run the following command depending on your operating system:


agent.cmd –NAME=<AGENT_NAME>

Once these commands have executed successfully you’ll want to confirm the agent is running without issue by clicking on the “Test” button by opening the agent you created in the Physical Architecture in ODI Studio.


I would shut down and remove the agent that existed in your 11g environment as you remove the 11g ODI home directory. Remember in our backup that we zipped a copy of the 11g home directory and stored it in another location. Once you have confirmed that ODI 12c is operating as expected, you may remove this directory if it’s on the same server.

Testing, Upgrade Issues, and Clean Up

The approach that I have used when testing has been to test each individual interface and load plan against the results in its 11g counterpart. I verify the counts initially and then have the users/owners verify the data. I then allow the load plans to run a few days to a week, and once the users/owners are comfortable with the results, migrate to the new implementation.

That being said, the following issues and clean up items may need to be addressed, which I and some of my colleagues at Rittman Mead have encountered while upgrading to 12c:

  • Variables in mappings/KMs are not recognized by the parser when creating scenarios, so they are not recognized as input parameters. Consequently, you’ll want to wrap each mapping in a package or load plan with the variables declared in it.
  • The variables are case-sensitive in 12c, so if you called a variable with a different case in a mapping, you will need to change it to the matching case.
  • Make sure to prefix all variables with the project code or global.
  • Do not start any data store alias with a number as this does not work well in 12c.
  • Temporary interfaces from 11g are triplicated in 12c during the upgrade and require cleanup in projects as well as the models. The upgrade creates these as a regular mapping and creates a target table in the models which can be removed. It also creates two copies of each temporary interface in Reusable Mappings with suffixes of RMS (Re-usable Mapping Source) and RMT (Re-usable Mapping Target). For the most part the RMT reusable mappings can be removed, however verify the mapping(s) using the temporary interface are suffixed by RMS before deleting.
  • Upgrade the mappings to flow-based using “Convert to Flow” on the dataset. (Optional)
  • You can check out Michael Rainey’s post on this at:

In my next post, I will go over the process that involves upgrading the Java EE Agent to 12c from 11g and highlight where the upgrade to differs from the upgrade to Stay tuned!


The post Upgrade to ODI 12c: Repository and Standalone Agent appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Mike Hibbert – A Celebration

Tue, 2016-01-19 16:18

Mike Hibbert

It’s been a long while since I graced the blog with my presence and I had always hoped that when I did return, it would be with something new, or exciting to say.  Instead, I find myself writing something much more personal and subdued:  a celebration of one of our dear colleagues who, after a 2 year fight, lost his battle to cancer just before Christmas 2015.

Joining us in September 2010, Mike Hibbert was one of our longest serving team members.  We still don’t have too many people with over 5 years service, but Mike was one of us.  I was lucky enough to work with Mike from day one:  he was parachuted into the project I was working on to cover the large gaps in my OWB knowledge and experience.  His role on the project was not that of simply building some mappings (we were already 7 months into the 9 month engagement and the OWB estate had already been built).  Instead, Mike’s task was to mentor the clients OWB team and ensure their ETL development capability was in place.  At the same time, he had to come to terms with the OWB estate, review it and where necessary, make it production-ready.  Landing on a project late in the day is never an enviable task for us consultants…even more so when it is your first day on the new job!

Mike brought bags of technical experience in both OWB and OBIEE from his previous roles with Edenbrook & Hitachi Consulting, but what impressed me most about Mike in that first project was the way he built up such a good rapport with all the members of the clients diverse project team.  He gained the teams trust on the technical side early but also engaged with people at a very personal level.  His approach was friendly and easy-going and he would always show an active interest in everything about the people he was working with, be it understanding their perspective on a particular technical challenge or simply talking about last nights TV, major family milestones or the travails of our chosen football teams. His sense of humour was disarming and his natural approach stood him in very good stead throughout his time with Rittman Mead.

Mike spent most of his time with us working on the continent, initially in Belgium, but in 2011 he began working with one of our major clients in the Netherlands.  This was the start of a long and valued relationship with the client, where Mike fulfilled several roles, mainly around OBIEE but also getting his hands increasingly dirty with APEX!  The scope of the projects and the personnel involved changed over time, but Mike was a constant.  He championed new agile delivery methods and became a key member of the clients team, delivering a number of critical solutions to their business units across Europe.  All told, Mike worked with the same client for over 4 years – a mark of his value and importance to them.  He continued to work through his illness and treatment, taking very little time off.  He was still working into his final week.  I came to learn that this was how Mike wanted to tackle his illness and the courage and determination he showed in facing it in this way is an inspiration to us all.

Rittman Mead UK vs Rest of the World 5-a-side, 2013

Mike was a vital member of our team.  He was a founding father of the Rittman Mead Fantasy Football League, now in its 4th year and, although he never admitted it, I think he was always a little frustrated that he never won the title.  I always looked forward to the start of each season in anticipation of the interesting team names Mike always seemed to come up with…often cryptic, always cheeky and always guaranteed to raise a chuckle!  As a Manchester United fan, we could always rely on Mike to give us his hints and tips on which of the Red Devils we should have in our teams…occasionally, his advice even made some sense!  Mike’s main interests lay in sport.  He kept the illustrious tennis career he had before entering the BI world under wraps for quite a while, but his main pursuits were running and cycling – two things that he put together (with a swim beforehand for good measure) to compete in the occasional triathlon.  Amazingly, he continued his cycling through his illness, raising money for both Prostate Cancer UK and The Christie.  In proposing a UK vs Rest of the World 5-a-side match, little did Mike know that he would initiate the companies longest e-mail debate.  It caught our imagination and we had our first match during our BI Forum in 2013.  Mike played on the Rest of the World side (justified by his working relationship with the Netherlands!) and despite giving away a few years on us all, he duly earned the Man of the Match award.  I hope that we will be able to continue these 5-a-side battles in his memory for years to come.


On a personal level, Mike and I lived relatively close to each other and for a period, this meant us sharing flights to/from Manchester airport.  We spent some very early mornings in MAN departures and many an hour waiting for delayed flights from AMS (and inevitably enjoying the odd continental beer!).  We coincidentally earned our FA Level 1 coaching badges at the same time and each of us coached our own sons teams.  Our conversations would always eventually turn into a discussion of recent results, tactics and training methods…just another example of Mike’s personable style and his interest in others.

Mike will be sadly missed as a friend and colleague and the collective thoughts and support of Rittman Mead go out to the family that he leaves behind.

We are keen to lend our efforts to the amazing charitable efforts that Mike started and his family continues. We have some fund-raising ideas, which I think Mike would appreciate and I hope can come to fruition in the near future. In the meantime, if you knew or worked with Mike it would be great to hear your memories of him.

The post Mike Hibbert – A Celebration appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Becky’s BI Apps Corner: Installing on Windows Server 2012

Thu, 2016-01-07 08:33

Recently I was installing Oracle BI Apps on a VM for a custom training, as mentioned in a previous post. I setup the VM with Windows Server 2012 after verifying in the certification matrix that the Oracle BI Apps version ( was certified to run on Windows Server 2012.

As I was going along, I was getting errors during some of the installs on the pre-requisite checks for the operating system (OS), as can be the case when the OS gets certified after the initial release of that version of software. The workaround is available for that error in MOS Doc ID 1577652.1, and involves adding the OS to an .xml file… Okay, I admit it. I didn’t actually update the .xml file. I just clicked Continue past the pre-requisite check. Since there didn’t seem to be any repercussions and the installs completed successfully, I continued on my merry way through the installation instructions.

When I was running OBIEE’s configuration (config.bat), it failed at 35% on the step Setting Up BI Publisher. The install_DATE_TIME.log file didn’t have enough information, so I dug into the install_DATE_TIME.out file. In it, there was the following error:

updateBIPConfigFiles: OSError: (0, 'Failed to execute command ([\'sh\', \'-c\', \'java -classpath C:\\\\Oracle\\\\Product\\\\BI_11.1\\\\Oracle_BI1\\\\clients\\\\bipublisher\\\\xdo-server.jar oracle.xdo.install.UpdateConfigFiles 9704 9703 9710 jdbc/mds/owsm C:\\\\Oracle\\\\Product\\\\BI_11.1\\\\user_projects\\\\domains\\\\bifoundation_domain\']): Cannot run program "sh": CreateProcess error=2, The system cannot find the file specified')

I left the config.bat open (It only takes one time to learn not to cancel during the config.bat or configApps.bat) and I took a trip over to my friendly neighborhood where I found MOS Doc ID 1580583.1. Essentially, it says to dig into and update a Jar file, and then directs you to Oracle’s Java Tutorials to learn how. I decided to do just that and post the steps here.

Forays into Java

The MOS doc says I need to update a file called by adding the string “Windows Server 2012” to the list of os.names. This file is archived inside a jar file located: \common\wlst\modules\jython-modules.jar.

A jar file is simply a package of a bunch of files. To view content of a jar file, the command is:
jar tf jar-file

In my command window, I navigated to the folder where the jython-modules.jar was. For this particular jar, the number of files was bigger than my command window would show, so I sent it to a text file.

The command I used was:

tf jython-modules.jar >log.txt

In the jython-modules.jar, there was a Lib\ Now that I found my file, I needed to extract the file from the jar. To do that, the command is:
jar xf jar-file [archived-file(s)]

Since I was still in the same directory, I used the following command:

xf jython-modules.jar Lib/

The file is now extracted and can be edited. I opened the file in Notepad++. You can open/edit it in your editor of choice. Not too far into the python script, I found the and the list of other operating systems. I’m not particularly skilled in python, but the change seemed pretty straight forward. As you can see from the screenshot, I just added a comma, and the ‘Windows Server 2012’ at the end of the “nt” operating systems. I am certain this would work for other operating systems added to the certification matrix after the software was released.

Once I’ve saved my changes, I need to get this file back into the python-modules.jar.

The command to update a file inside of a jar is:
jar uf jar-file input-file(s)

I used:

uf jython-modules.jar Lib/

With everything in order, I went back to the config.bat window, which was still running. I scrolled to the top, checked the box next to the first line, clicked Retry.

The install completed without any further errors! We got to play with Java Jar files and a python script to resolve an error with the config.bat installer. These steps will allow you to update for any OS, which can come in handy for any that are certified after the initial release of a version of software. Let me know in the comments all the different Operating Systems where you have installed BI Apps. So far, I’ve installed on RHEL 5 and 6, Solaris 10, Windows Server 2008, and Windows Server 2012, each with interesting little quirks. Also, if you have training needs, check out our new trainings for 2016, and contact us! And of course, keep an eye out for more Becky’s BI Apps Corner coming soon.

The post Becky’s BI Apps Corner: Installing on Windows Server 2012 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Kickstart Your 2016 with Rittman Mead’s Data Integration Training

Wed, 2015-12-30 19:38

Happy Holidays and a Happy New Year to all! As you begin your 2016 this January, it’s time to start planning your team’s data integration training. Look no further than Rittman Mead’s Oracle Data Integrator training course! We offer a 4 day Oracle Data Integrator 12c Bootcamp for those looking to take advantage of the latest and greatest features in ODI 12c. We also still teach our 5 day Oracle Data Integrator 11g Bootcamp, as we know sometimes it can be difficult to upgrade to the latest release and new data warehouse team members need to be brought up to speed on the product. ODI 11g is also still very much alive in Oracle Business Intelligence Applications, being the ETL technology for the 11g release of the product suite.

ODI12c training

Customized Data Integration Training

BI Apps 11g training has been a hot topic from the data integration perspective over the last couple of years. Rittman Mead have delivered custom BI Apps training for ODI developers several times just within the last year, prompting us to add a new public training course specific to this topic to our public schedule. This course walks attendees through the unique relationship between OBIEE and ODI 11g as the data integration technology, including configuration, load plan generation, and ETL customization. If you have an Oracle Business Intelligence Applications 11g team looking to enhance their ODI 11g skills, take a look at the new ODI for BI Applications course description.

The customization of training does not just apply to BI Applications, but to all aspects of Oracle Data Integration. Whether adding more details around Oracle GoldenGate installation and maintenance to the ODI 12c course, or learning about Oracle EDQ integration, the Rittman Mead data integration team of experts can work to deliver the course so your team gains the most value from its investment in Oracle Data Integration technologies. Just ask! Reach out and we can work together to create a custom course to fit your needs.

Public or Onsite Training?

Rittman Mead has several dates for each course, scheduled to be delivered out of our offices in either Atlanta, GA or Brighton, UK. Take a look here for our ODI 12c bootcamp, ODI 11g bootcamp, and ODI for BI Apps Developers offerings in the US. Look here for the same in the UK/Europe (Note: as of the writing of this blog post, the 2016 UK/Europe schedule had not been released). We also offer the same courses for delivery onsite at your company’s office, allowing our experts to come to you! Quite often our clients will combine consulting and training, ensuring they get the most out of their investment in our team of experts.

Why Rittman Mead?

Many folks in the Business Intelligence and Data Integration profession who are looking for a consulting company might think Rittman Mead only work on extremely challenging projects based on the depth of knowledge and type of problems (and solutions) we offer via our blog. The fact is, most of our projects are the “standard” data warehouse or business intelligence reporting implementations, with some of these additional challenges coming along the way. Why do I bring that up? Well, if you’re looking for the experts in Oracle Data Integration technology, with experience in both project implementation and solving challenging technical problems, then you’ve come to the right place to learn about ODI.

Unlike many other companies offering training, we don’t have a staff of educators on hand. Our trainers are the same folks that deliver projects, using the technology you’re interested in learning about, on a day-to-day basis. We offer you real world examples as we walk through our training slide deck and labs. Need to know why Oracle GoldenGate is an integral part of real-time data integration? Let me tell you about my latest client where I implemented GoldenGate and ODI. Want to know what to look out for when installing the JEE Agent in ODI 12c? We’ve done that many times – and know the tricks necessary to get it all working.

Our experts, such Jérôme Françoisse, Becky Wagner, Mark Rittman, myself, and many others, all have multiple years of experience with Oracle Data Integration implementations. Not only that, but we here at Rittman Mead truly enjoy sharing our knowledge! Whether posting to this blog, speaking at Oracle conferences, or on the OTN forums, Rittman Mead experts are always looking to teach others in order to better the Oracle Data Integration community.

If you or your company are in need of Oracle Data Integration training, please drop us a line at As always, feel free to reach out to me directly on Twitter (@mRainey), LinkedIn, or via email ( if you have any direct questions. See you all next year!

The post Kickstart Your 2016 with Rittman Mead’s Data Integration Training appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Action Links in OBIEE 12c – Part 2

Tue, 2015-12-22 07:40

Introduction and Scenario

Part 1 of this series on Action Links had us creating a simple navigate action and left us in suspense with the promise of a new means by which to filter one report from another. Now time to deliver! The scenario: say we’ve got a company dashboard that is pretty locked down in terms of adding any new user-facing content, and as a developer you occupy more of an analyst role where you can build analyses all day, but dashboards are a no go. So how then can we drive some of our own detail analyses from dashboard selections? The answer? Hidden driving documents! In a nutshell, these documents store selected dashboad values and act as a filter for our detail analyses, utilizing OBIEE’s “filter on the results of another analysis” filter option. It should be noted that we could simply set the target analysis’s filters to be equal to the presentation variables generated by the source dashboard prompt. However, the following technique is simply to illustrate a possible technique, say when you’re going between two different subject areas, that might be applicable in your current situation or at least spark an idea to get you headed in the right direction towards a possible solution. So, let’s start by getting the pieces to our puzzle together.

Start with the dashboard and prompt. We are going to navigate from a monthly sales trend to a detail report displaying product sales detail.

The Solution

Now that we’ve determined the dashboard analysis from which we want to navigate, we can set up our driving document. Again, the overall concept of a driving document is to act as an intermediary container for selected values that then get passed from one report to another. So let’s set this up. Given that our trend is prompted on Year, Company, Region, we need to place three dummy columns on our driving report to store the presentation variables generated by each prompt. I used the same three columns in my driving report for consistency, however any column will do. Note that the columns in the picture have custom names already are not the native columns from their respective tables.

Edit each column to store the presentation variables generated by your dashboard prompt. If the dashboard prompt does not have a presentation variable for each column prompt, go ahead and get those set up before completing this step. My year column variable in this example is appropriately titled SALES_YEAR. Be sure to put single quotes around your variable as in ‘@{SALES_YEAR}’ when entering it in the Edit Column dialogue.

You can now save your driving report and place it on your dashboard. Right away you should see that your driving report picks up the values in each column prompt. Note the use of a default value {2012}. Using these is ideal so that if we want to edit our driving document, we at least have some values to pass it, as opposed to it throwing an error on the Results tab.


Now for some bad news. You might have noticed an obvious limitation to this approach, which is the driving report’s limited selection of only one column value from each column prompt. Nevertheless, let’s run this thing through to the end and see what happens. The next piece of the puzzle is setting up your target analysis.

Our analyst in this scenario wants to set up two reports to be driven off of our dashboard selection. The first will be a top 10 report for products based on the selected month. The second will be another top 10, but for sales reps instead. Navigating to our desired subject area, let’s bring over the needed columns, formatting as needed. In this example, we’re going to include a custom calc which gives us the percent variance to the prior period which has also been conditionally formatted to indicate positive or negative growth.
We have placed the columns necessary to build each report and also added filters, as seen in the proceeding picture.

Now comes the crux of this neat trick. We need to add the proper filters to the analysis so that any values we pick from both the dashboard prompt and the source analysis itself will drive our target analysis.

In the target report, we added a filter for each column that we’d like our analysis filtered on and likewise, comes from our source report.

To set this filter, simply apply the ‘is based on results of another analysis’ filter to each column. Then, browse for the driving report we made and apply the ‘is equal to any’ Relationship option. As our driving report values will only have one value for each of the filtered columns, our target report columns should filter on each of these. Lastly, use the drop down to choose the corresponding column from the source analysis. In the following pic we are selecting the  Region column from our driving report so that our target report will be filtered on whatever Region is selected on our source dashboard.

As the last steps in our target analysis, make sure you’ve set all the filter columns to respond to the driver analysis and that you select the ‘is prompted’ filter for any additional dimension columns on your report that are not driven by a dashboard prompt. In the example, this is going to be the Month column. This will ensure that our target analysis “listens” to whatever month is selected in our source analysis, that is, our sales trend.

Don’t worry if your Results tab shows no values as this is expected behavior (we haven’t sent any values over yet!). If you’d like to set it up so that your target report actually yields results without clicking on the action link, simply apply default values to the presentation variables in your driving document, as described above in the Year example. The last piece of this puzzle is finally assigning the action link to our respective column. Follow the instructions outlined in the first part of this post in order to navigate to our target analysis. In our example, this is going to be our Sales column.

Now that we’ve got all the pieces of the puzzle, let’s put it all together and see what happens. Clicking on any sales point on our trend line, we should be taken to the target analysis which should be filtered on both our dashboard prompt values as well as our Month. In this example, I’ve applied a filters object to the target analysis to make sure the target analysis responded to all applied filters.

Looks like everything works great! At this point you can hide the driver report on your dashboard and get rid of the filters object on your target analysis. I’d like to hear about some neat ways people have used this technique or modified it to suit their particular business/use case. Feel free to post in the comments! Stay tuned for part three of this series on Action Links where we go in depth about the GoURL syntax and how it can be a powerful and customizable tool in navigating to web pages and OBIEE 12c content.

The post Action Links in OBIEE 12c – Part 2 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Using Linux Control Groups to Constrain Process Memory

Mon, 2015-12-21 03:00

Linux Control Groups (cgroups) are a nifty way to limit the amount of resource, such as CPU, memory, or IO throughput, that a process or group of processes may use. Frits Hoogland wrote a great blog demonstrating how to use it to constrain the I/O a particular process could use, and was the inspiration for this one. I have been doing some digging into the performance characteristics of OBIEE in certain conditions, including how it behaves under memory pressure. I’ll write more about that in a future blog, but wanted to write this short blog to demonstrate how cgroups can be used to constrain the memory that a given Linux process can be allocated.

This was done on Amazon EC2 running an image imported originally from Oracle’s OBIEE SampleApp, built on Oracle Linux 6.5.

$ uname -a  
Linux 2.6.32-431.5.1.el6.x86_64 #1 SMP Tue Feb 11 11:09:04 PST 2014 x86_64 x86_64 x86_64 GNU/Linux

First off, install the necessary package in order to use them, and start the service. Throughout this blog where I quote shell commands those prefixed with # are run as root and $ as non-root:

# yum install libcgroup  
# service cgconfig start

Create a cgroup (I’m shamelessly ripping off Frits’ code here, hence the same cgroup name ;-) ):

# cgcreate -g memory:/myGroup

You can use cgget to view the current limits, usage, & high watermarks of the cgroup:

# cgget -g memory:/myGroup|grep bytes  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 0  
memory.memsw.usage_in_bytes: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 0  
memory.usage_in_bytes: 0

For more information about the field meaning see the doc here.

To test out the cgroup ability to limit memory used by a process we’re going to use the tool stress, which can be used to generate CPU, memory, or IO load on a server. It’s great for testing what happens to a server under resource pressure, and also for testing memory allocation capabilities of a process which is what we’re using it for here.

We’re going to configure cgroups to add stress to the myGroup group whenever it runs

$ cat /etc/cgrules.conf  
*:stress memory myGroup

[Re-]start the cg rules engine service:

# service cgred restart

Now we’ll use the watch command to re-issue the cgget command every second enabling us to watch cgroup’s metrics in realtime:

# watch --interval 1 cgget -g memory:/myGroup  
memory.memsw.failcnt: 0  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 0  
memory.memsw.usage_in_bytes: 0  
memory.oom_control: oom_kill_disable 0  
        under_oom 0  
memory.move_charge_at_immigrate: 0  
memory.swappiness: 60  
memory.use_hierarchy: 0  
memory.stat: cache 0  
        rss 0  
        mapped_file 0  
        pgpgin 0  
        pgpgout 0  
        swap 0  
        inactive_anon 0  
        active_anon 0  
        inactive_file 0  
        active_file 0  
        unevictable 0  
        hierarchical_memory_limit 9223372036854775807  
        hierarchical_memsw_limit 9223372036854775807  
        total_cache 0  
        total_rss 0  
        total_mapped_file 0  
        total_pgpgin 0  
        total_pgpgout 0  
        total_swap 0  
        total_inactive_anon 0  
        total_active_anon 0  
        total_inactive_file 0  
        total_active_file 0  
        total_unevictable 0  
memory.failcnt: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 0  
memory.usage_in_bytes: 0

In a separate terminal (or even better, use screen!) run stress, telling it to grab 150MB of memory:

$ stress --vm-bytes 150M --vm-keep -m 1

Review the cgroup, and note that the usage fields have increased:

memory.memsw.failcnt: 0  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 157548544  
memory.memsw.usage_in_bytes: 157548544  
memory.oom_control: oom_kill_disable 0  
        under_oom 0  
memory.move_charge_at_immigrate: 0  
memory.swappiness: 60  
memory.use_hierarchy: 0  
memory.stat: cache 0  
        rss 157343744  
        mapped_file 0  
        pgpgin 38414  
        pgpgout 0  
        swap 0  
        inactive_anon 0  
        active_anon 157343744  
        inactive_file 0  
        active_file 0  
        unevictable 0  
        hierarchical_memory_limit 9223372036854775807  
        hierarchical_memsw_limit 9223372036854775807  
        total_cache 0  
        total_rss 157343744  
        total_mapped_file 0  
        total_pgpgin 38414  
        total_pgpgout 0  
        total_swap 0  
        total_inactive_anon 0  
        total_active_anon 157343744  
        total_inactive_file 0  
        total_active_file 0  
        total_unevictable 0  
memory.failcnt: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 157548544  
memory.usage_in_bytes: 157548544

Both memory.memsw.usage_in_bytes and memory.usage_in_bytes are 157548544 = 150.25MB

Having a look at the process stats for stress shows us:

$ ps -ef|grep stress  
oracle   15296  9023  0 11:57 pts/12   00:00:00 stress --vm-bytes 150M --vm-keep -m 1  
oracle   15297 15296 96 11:57 pts/12   00:06:23 stress --vm-bytes 150M --vm-keep -m 1  
oracle   20365 29403  0 12:04 pts/10   00:00:00 grep stress

$ cat /proc/15297/status

Name:   stress  
State:  R (running)  
VmPeak:   160124 kB  
VmSize:   160124 kB  
VmLck:         0 kB  
VmHWM:    153860 kB  
VmRSS:    153860 kB  
VmData:   153652 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       328 kB  
VmSwap:        0 kB  

The man page for proc gives us more information about these fields, but of particular note are:

  • VmSize: Virtual memory size.
  • VmRSS: Resident set size.
  • VmSwap: Swapped-out virtual memory size by anonymous private pages

Our stress process has a VmSize of 156MB, VmRSS of 150MB, and zero swap.

Kill the stress process, and set a memory limit of 100MB for any process in this cgroup:

# cgset -r memory.limit_in_bytes=100m myGroup

Run cgset and you should see the see new limit. Note that at this stage we’re just setting memory.limit_in_bytes and leaving memory.memsw.limit_in_bytes unchanged.

# cgget -g memory:/myGroup|grep limit|grep bytes  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 104857600

Let’s see what happens when we try to allocate the memory, observing the cgroup and process Virtual Memory process information at each point:

  • 15MB:

    $ stress --vm-bytes 15M --vm-keep -m 1  
    stress: info: [31942] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 15990784  
    memory.usage_in_bytes: 15990784
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep VmVmPeak:    21884 kB  
    VmSize:    21884 kB  
    VmLck:         0 kB  
    VmHWM:     15616 kB  
    VmRSS:     15616 kB  
    VmData:    15412 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:        60 kB  
    VmSwap:        0 kB

  • 50MB:

    $ stress --vm-bytes 50M --vm-keep -m 1  
    stress: info: [32419] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 52748288  
    memory.usage_in_bytes: 52748288     
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
    VmPeak:    57724 kB  
    VmSize:    57724 kB  
    VmLck:         0 kB  
    VmHWM:     51456 kB  
    VmRSS:     51456 kB  
    VmData:    51252 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:       128 kB  
    VmSwap:        0 kB

  • 100MB:

    $ stress --vm-bytes 100M --vm-keep -m 1  
    stress: info: [20379] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd        
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 105197568  
    memory.usage_in_bytes: 104738816
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
    VmPeak:   108924 kB  
    VmSize:   108924 kB  
    VmLck:         0 kB  
    VmHWM:    102588 kB  
    VmRSS:    101448 kB  
    VmData:   102452 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:       232 kB  
    VmSwap:     1212 kB

Note that VmSwap has now gone above zero, despite the machine having plenty of usable memory:

# vmstat -s  
     16330912  total memory  
     14849864  used memory  
     10583040  active memory  
      3410892  inactive memory  
      1481048  free memory  
       149416  buffer memory  
      8204108  swap cache  
      6143992  total swap  
      1212184  used swap  
      4931808  free swap

So it looks like the memory cap has kicked in and the stress process is being forced to get the additional memory that it needs from swap.

Let’s tighten the screw a bit further:

$ stress --vm-bytes 200M --vm-keep -m 1  
stress: info: [21945] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd

The process is now using 100MB of swap (since we’ve asked it to grab 200MB but cgroup is constraining it to 100MB real):

$ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
VmPeak:   211324 kB  
VmSize:   211324 kB  
VmLck:         0 kB  
VmHWM:    102616 kB  
VmRSS:    102600 kB  
VmData:   204852 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       432 kB  
VmSwap:   102460 kB

The cgget command confirms that we’re using swap, as the memsw value shows:

# cgget -g memory:/myGroup|grep usage|grep -v max  
memory.memsw.usage_in_bytes: 209788928  
memory.usage_in_bytes: 104759296

So now what happens if we curtail the use of all memory, including swap? To do this we’ll set the memory.memsw.limit_in_bytes parameter. Note that running cgset whilst a task under the cgroup is executing seems to get ignored if it is below that currently in use (per the usage_in_bytes field). If it is above this then the change is instantaneous:

  • Current state

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 9223372036854775807  
    memory.memsw.max_usage_in_bytes: 209915904  
    memory.memsw.usage_in_bytes: 209784832  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104775680

  • Set the limit below what is currently in use (150m limit vs 200m in use)

    # cgset -r memory.memsw.limit_in_bytes=150m myGroup

  • Check the limit – it remains unchanged

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 9223372036854775807  
    memory.memsw.max_usage_in_bytes: 209993728  
    memory.memsw.usage_in_bytes: 209784832  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104751104

  • Set the limit above what is currently in use (250m limit vs 200m in use)

    # cgset -r memory.memsw.limit_in_bytes=250m myGroup

  • Check the limit – it’s taken effect

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 262144000  
    memory.memsw.max_usage_in_bytes: 210006016  
    memory.memsw.usage_in_bytes: 209846272  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104816640

So now we’ve got limits in place of 100MB real memory and 250MB total (real + swap). What happens when we test that out?

$ stress --vm-bytes 245M --vm-keep -m 1  
stress: info: [25927] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd

The process is using 245MB total (VmData), of which 95MB is resident (VmRSS) and 150MB is swapped out (VmSwap)

$ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
VmPeak:   257404 kB  
VmSize:   257404 kB  
VmLck:         0 kB  
VmHWM:    102548 kB  
VmRSS:     97280 kB  
VmData:   250932 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       520 kB  
VmSwap:   153860 kB

The cgroup stats reflect this:

# cgget -g memory:/myGroup|grep bytes  
memory.memsw.limit_in_bytes: 262144000  
memory.memsw.max_usage_in_bytes: 257159168  
memory.memsw.usage_in_bytes: 257007616  
memory.limit_in_bytes: 104857600  
memory.max_usage_in_bytes: 104857600  
memory.usage_in_bytes: 104849408

If we try to go above this absolute limit (memory.memsw.max_usage_in_bytes) then the cgroup kicks in a stops the process getting the memory, which in turn causes stress to fail:

$ stress --vm-bytes 250M --vm-keep -m 1  
stress: info: [27356] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd  
stress: FAIL: [27356] (415) <-- worker 27357 got signal 9  
stress: WARN: [27356] (417) now reaping child worker processes  
stress: FAIL: [27356] (451) failed run completed in 3s

This gives you an indication of how careful you need to be using this type of low-level process control. Most tools will not be happy if they are starved of resource, including memory, and may well behave in unstable ways.

Thanks to Frits Hoogland for reading a draft of this post and providing valuable feedback.

The post Using Linux Control Groups to Constrain Process Memory appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Becky’s BI Apps Corner: Oracle BI Applications, where art thou?

Thu, 2015-12-17 03:00

Hello! I would like to take a moment to introduce myself. My name is Becky Wagner and I’ve been working with Rittman Mead America since the beginning of the year. I have a background in data warehousing and ETL with a state government agency, where I was part of a project upgrading from DataStage to ODI 11g with Oracle Golden Gate in Oracle’s early adopter program for Oracle BI Apps 11g. Since coming to Rittman Mead, I’ve taught several ODI 12c bootcamps, designed and delivered custom trainings for BI Apps and ODI and been involved in ODI 11g, ODI 12c projects, and OBIA 11g projects.


Recently, I was putting together a BI Apps for Oracle Data Integrator (ODI) custom training for a client who is upgrading BI Apps and will be using ODI for the first time. On the Virtual Machine I was building for the training, I wanted the version of Oracle BI Applications to match the client’s installation. I found that Oracle’s recent website facelifts changed the way I was used to getting older versions of software. Oracle’s Downloads and Technetwork sites both have the most recent version of Oracle BI Apps available (currently but no earlier versions any longer. Edelivery has the earlier versions as well as the current version, but the site has changed enough that it took me a bit to understand how to get to the Oracle BI Apps software files to download.


Downloading Oracle BI Apps from Edelivery


From your favorite browser, go to and sign in.

Accept the Export Restrictions (of course, only after you have read, understand and agree to them.)


Fill in the Product* box with ‘Business Intelligence Applications’. You won’t see Business Intelligence Applications in the dropdown that appears as you start typing. What you do see are other Products that your company would have purchased to allow you to have a license of Oracle Business Intelligence Applications, such as Oracle Financial Analytics or Oracle Human Resources Analytics. Select the product (or one of the products) that was purchased by your company.

Click on the Select Platform button and check the box for your appropriate platform, such as Linux x86-64 or Microsoft Windows x64 (64-bit). Then click the Select button.

Once the Product you selected is displaying in the window, click on the Continue button.


Now Oracle Business Intelligence is showing. Interestingly, it doesn’t say Oracle Business Intelligence Applications, but currently OBIEE doesn’t have a version of, so we can be confident this is actually Oracle BI Apps. However, this still isn’t the Oracle BI Apps that you are looking for. Below the Available Release, you will see the option to Select Alternate Release.

This will allow you to drop down the box to select an earlier version.

With any version of Oracle BI Apps, there are several different components to download, which you can see by clicking on the triangle to the left of the Available Release. Once you have selected your desired version of Oracle BI Apps, click on the continue button to begin the download.

Please don’t forget to read the license agreements carefully and if you agree, check the box and click Continue.


At this point, you can now see the files to download. You can click on each blue link individually, or click the Download All button to use Oracle’s downloader tool. Also, for the Linux, Red Hat, and Solaris folks, notice at the bottom the WGET Options.

Downloading ODI for Oracle BI Apps from Edelivery


Get the downloads started for these files. We aren’t quite finished yet, though. ODI needs to be downloaded as well. Return to the Products page. Please note that to get the correct version of ODI, you must type in ‘Oracle Data Integrator for Oracle Business Intelligence’ (again, it probably means Oracle BI Apps, but points here for consistency at least). Select a Platform. Then click Continue.

Notice we are showing Oracle Business Intelligence again. You will want to click on Select Alternate Release, and pick the same version you selected above. This will save you from interesting OPatch errors later during the install process, but I will leave those fun errors for another blog post. Then click Continue.

Rinse and repeat.

And that is how you navigate the new Oracle EDelivery site to get Oracle BI Apps download files and previous versions. I would love to hear your thoughts if you found this helpful or confusing. Also, please leave comments below if you found other alternatives for downloading earlier versions of Oracle BI Apps, and/or your suggestions for ways to rename the files to something more descriptive that better identifies the zip file. Keep an eye out for more Becky’s BI Apps Corner coming soon and if you’re interested in OBIEE or ODI training (or even a custom Oracle BI Applications course), give us a shout at!

The post Becky’s BI Apps Corner: Oracle BI Applications, where art thou? appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

An Alternative Front-End for OBIEE using Web Services and D3

Mon, 2015-12-14 00:14

Working with OBIEE, it is common to get requests to add visualisations or otherwise improve the UI. Typically this has been done by using narrative views to embed HTML and Javascript to provide data driven, custom visualisations. To cope with the former, Tom Underhill developed the Rittman Mead Visual Plugin Pack (RMVPP), a web based plugin to allow configurable visualisations to work natively in OBIEE through the use of narratives. This technology is uses configurable Javascript templates for each plugin. Each plugin has a render function which will receive the dataset from OBIEE as well as any associated information. The features of the plugin are then only limited by the ability of the JS developer and current web standards. Plugin developers could be further aided by sharing common functions for display and data manipulation, and external libraries can be included simply.


Above shows the process of creating a Sankey diagram using the RMVPP framework. The code then produces the corresponding narrative required to generate this visualisation automatically.

When RMVPP was being developed by Tom, I was investigating the use of web services in OBIEE. Several of us at Rittman Mead had used the exposed web services to interface with OBIEE to automate various tasks programmatically, such as regression testing and web catalogue auditing. A client had a requirement to execute an OBIEE query on a webpage outside of the OBIEE front end in order to power a fairly sophisticated 3JS application that was not rendering within OBIEE (via a narrative). It appeared that the shortcomings were due to attempting to render the WebGL within the complex div and table structure of an OBIEE page. Soon, I was able to use the web services client side, in Javascript (so long as the page was hosted on the same server as OBIEE) as opposed to server side in Python or similar. Combining this breakthrough with Tom’s RMVPP framework seemed like the next logical step – as it would allow us to totally modify the OBIEE front end whilst retaining the benefits of using the BI Server like security and physical query generation.

An Alternative Front-end for OBIEE

The following section will describe and show some of the features I have developed in my alternative to the OBIEE front end. It uses the plugin framework from RMVPP but with a custom interface that works differently to OBIEE.

Creating a Visualisation

Below is a screencapture showing creation of a basic bar chart.

Creating Visualisations

The column mapping tab is configurable by the plugin developer and specifies the parameters that each visualisation will use. As a user, OBIEE presentation columns (displayed on the left) are mapped into these parameters. A flag can be set on a parameter to allow multiple columns to be chosen for that parameter. The plugin developer can then use the resulting data set accordingly. The columns selected here are effectively the Criteria from vanilla OBIEE as they determine which columns are used in the logical SQL. The parameter mapping allow the developer to use the correct columns from the data set in the render function.

The configuration screen shown there is customisable by the plugin designer, allowing developers to specify a list of options and UI elements that get fed back into the render function for the visualisation. UI options include:

  • Textboxes
  • Dropdowns
  • Radio Buttons
  • Colour Pickers

The next screen capture shows a Sankey chart and applies a filter to the query. The filter mechanism also has a sub search facility similar to vanilla OBIEE.

Sankey & Filtering

Conditional Formatting

The next example shows a pivot table being created and the conditional formatting functionality available.

Conditional Formatting

The conditional format tab allows you to choose the columns to apply formatting to as well as the formatting rule that should be applied. Additionally, a plugin developer can specify custom conditional formats to be available for specific plugins. The heatmap formatting style is one such example for the pivot table.

Adding and Editing Columns

Similar to vanilla OBIEE, columns formats and formulae can be edited and added for visualisations.

New Column


Visualisations (and dashboard pages) can be saved and loaded to and from the web catalogue. This means that security can be applied to them in the normal way.

Save & Load

They are saved to OBIEE as analyses with Static Text views in them containing the necessary information to build the visualisation with my API. OBIEE however, will not be able to render these properly and I’ve stopped developing that functionality for a number of reasons I won’t go into in this post. The screencapture shows a brief glimpse of the webcat explorer in my app, which is basic, but filters out any vanilla OBIEE objects, only allowing you to view and open custom objects that were created by the app.


The interface allows the user to create dashboards from the same app, without having to save individual visualisations first.


After creating and configuring a visualisation, they can be temporarily stored. Once stored, the user can switch to dashboard mode, which will have a blank canvas, allowing the user to add any stored visaulisations. The edit button in dashboard mode will allow visualisations to be freely placed on the canvas.


The interactivity framework is probably the feature with the biggest benefit over the vanilla system that I’ve added.


Plugin developers are able to create triggers as part of their visualisations. These triggers can be fired in most situations and tied to the data so that dynamic interactions between visualisations are possible. Users can specify what data should be passed to the target reaction. By default, every plugin can be hard filtered by an interaction, which modifies the query and goes back to the BI server. Plugin developers can also write their own reactions, allowing for interactivity only limited by the plugin designer’s web development capability.


Drilldowns between custom dashboard pages can be defined using the same framework as the other interactions.


Users need only enter the webcat path for the target page in order for the drilldown to function. Each visualisation of the target dashboard will be automatically filtered (if the subject area matches) on the criteria specified by the interaction.

Dashboard Prompts

Some of the vanilla features in OBIEE are very useful, so I replicated the functionality in my app.

Dashboard Prompts

This shows the implementation of prompts to a dashboard page. Unlike OBIEE, it is not required to set an ‘is prompted’ filter on each target. Rather, the prompt will automatically filter any visualisations on the page that are from the same subject area.

Column Selectors

Column selectors can be added as either dropdowns or radio buttons.

Column Selector

The user chooses an array of columns to be in the column selector. If any visualisations in the dashboard contain a column in this array, it will be swapped when the column selector is updated.

Map Visualisations

Two of the visualisation plugins I have made are map based, for geographical data.


Both use LeafletJS, an open source Javascript mapping library. The first, is a bubble chart which relies on longitude and latitude being stored in the database and exposed via OBIEE. The second is a choropleth which requires a TopoJSON file of the regions in order to function. Additionally, the json file needs to have an attribute attached to each region which will act as a key to join it onto OBIEE data. So that region attribute needs to have a matching key exposed via OBIEE.


So that wraps up the major features of the app. I think it’s got potential, particularly for use with MI reporting and creating highly interactive and visually appealing dashboards. Next steps will be to include a portal for navigating dashboards and the catalogue, all of which has been proved possible.

For me, the interesting thing is that this effectively provides two SDKs, one to create reproducible and configurable visualisations, one to create and modify UI behaviour.

If anyone has any comments, ideas, features or just want to know more about the app and how it works, feel free to comment.

The post An Alternative Front-End for OBIEE using Web Services and D3 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Driving OBIEE User Engagement with Enhanced Usage Tracking for OBIEE

Thu, 2015-12-10 08:35

Measuring and monitoring user interactions and behaviour with OBIEE is a key part of Rittman Mead’s User Engagement Service. By understanding and proving how users are engaging the system we can improve the experience for the user, driving up usage and ensuring maximum value for your OBIEE investment. To date, we have had the excellent option of Usage Tracking for finding out about system usage, but this only captures actual dashboard and analysis executions. What I am going to discuss in this article is taking Usage Tracking a step further, and capturing and analysing every click that that the user makes. Every login, every search, every report build action. This can be logged to a database such as Oracle, and gives us Enhanced Usage Tracking!


Because the more we understand about our user base, the more we can do for them in terms of improved content and accessibility, and the more we can do for us, the OBIEE dev/sysadmin, in terms of easier maintenance and better knowledge of the platform for which we are developing.

Here is a handful of questions that this data can answer – I’m sure once you see the potential of the data you will be able to think of plenty more…

How many users are accessing OBIEE through a mobile device?

Maybe you’re about to implement a mobile strategy, perhaps deploying MAD or rolling out BI Mobile HD. Wouldn’t it be great if you could quantify its uptake, and not only that but the impact that the provision of mobile makes on the general user engagement levels of your OBIEE user base?

Perhaps you think your users might benefit from a dedicated Mobile OBIEE strategy, but to back up your business case for the investment in mobile licences or time to optimise content for mobile consumption you want to show how many users are currently accessing full OBIEE through a web browser on their mobile device. And not only ‘a mobile device’, but which one, which browser, and which OS. Enhanced Usage Tracking data can provide all this, and more.

Which dashboards get exported to Excel the most frequently?

The risks that Excel-marts present are commonly discussed, and broader solutions such as data-mashup capabilities within OBIEE itself exist – but how do you identify which dashboards are frequently exported from OBIEE to Excel, and by whom? We’ve all probably got a gut-instinct, or indirect evidence, of when this happens – but now we can know for sure. Whilst Usage Tracking alone will tell us when a dashboard is run, only Enhanced Usage Tracking can show what the user then did with the results:

What do we do with this information? It Depends, of course. In some cases exporting data to Excel is a – potentially undesirable but pragmatic – way of getting certain analysis done, and to try to prevent it unnecessarily petulant and counterproductive. In many other cases though, people use it simply as a way of doing something that could be done in OBIEE but they lack the awareness or training in order to do it. The point is that by quantifying and identifying when it occurs you can start an informed discussion with your user base, from which both sides of the discussion benefit.

Precise Tracking of Dashboard Usage

Usage Tracking is great, but it has limitations. One example of this is where a user visits a dashboard page more than once in the same session, meaning that it may be served from the Presentation Services cache, and if that happens, the additional visit won’t be recorded in Usage Tracking. By using click data we can actually track every single visit to a dashboard.

In this example here we can see a user visiting two dashboard pages, and then going back to the first one – which is captured by the Enhanced Usage Tracking, but not the standard one, which only captures the first two dashboard visits:

This kind of thing can matter, both from an audit point of view, but also a more advanced use, where we can examine user behaviour in repeated visits to a dashboard. For example, does it highlight that a dashboard design is not optimal and the user is having to switch between multiple tabs to build up a complete picture of the data that they are analysing?

Predictive Modelling to Identify Users at Risk of ‘Churn’

Churn is when users disengage from a system, when they stop coming back. Being able to identify those at risk of doing this before they do it can be hugely valuable, because it gives you opportunity to prevent it. By analysing the patterns of system usage in OBIEE and looking at users who have stopped using OBIEE (i.e. churned) we can then build a predictive model to identify those with similar patterns of usage but are still active.

Measures such as the length of time it takes to run the first dashboard after login, or how many dashboards are run, or how long it takes to find data when building an analysis, can all be useful factors to include in the model.

Are any of my users still accessing OBIEE through IE6?

A trend that I’ve seen in the years working with OBIEE is that organisations are [finally] moving to a more tolerant view on web browsers other than IE. I suppose this is as the web application world evolves and IE becomes more standards compliant and/or web application functionality forces organisations to adopt browsers that provide the latest capabilities. OBIEE too, is a lot better nowadays at not throwing its toys out of the pram when run on a browser that happens to have been written within the past decade.

What’s my little tirade got to do with enhanced usage tracking? Because as those responsible for the development and support of OBIEE in an organisation we need to have a clear picture of the user base that we’re supporting. Sure, corporate ‘standard’ is IE9, but we all know that Jim in design runs one of those funny Mac things with Safari, Fred in accounts insists on Firefox, Bob in IT prides himself on running Konquerer, and it would be a cold day in hell before you prise the MD’s copy of IE5 off his machine. Whether these browsers are “supported” or not is only really a secondary point to whether they’re being used. A lot of the time organisations will take the risk on running unsupported configurations, consciously or in blissful ignorance, and being ‘right’ won’t cut it if your OBIEE patch suddenly breaks everything for them.

Enhanced Usage Tracking gives us the ability to analyse browser usage over time:

as well as the Enhanced Usage Tracking data rendered through OBIEE itself, showing browser usage in total (nb the Log scale):

It’s also easy to report on the Operating System that users have:

Where are my users connecting to OBIEE from?

Whilst a lot of OBIEE deployments are run within the confines of a corporate network, there are those that are public-facing, and for these ones it could be interesting to include location as another dimension by which we analyse the user base and their patterns of usage. Enhanced Usage Tracking includes the capture of a user’s IP, which for public networks we can easily lookup and use the resulting data in our analysis.

Even on a corporate network the user’s IP can be useful, because the corporate network will be divided into subnets and IP ranges, which will usually have geographical association to them – you just might need to code your own lookup in order to translate to “Bob’s dining room”.

Who deleted this report? Who logged in? Who clicked the Do Not Click Button?

The uses for Enhanced Usage Tracking are almost endless. Any user interaction with OBIEE can now be measured and monitored.

A frequent question that I see on the OTN forums is along the lines of “for audit purposes, we need to know who logged in”. Since Usage Tracking alone won’t capture this directly (although the new init block logging in > probably helps indirectly with this) this information usually isn’t available….until now! In this table we see the user, their session ID, and the time at which they logged in:

What about who updated a report last, or deleted it? We can find that out too! This simple example shows some of the operations in the Presentation Catalog recorded as clear as day in Enhanced Usage Tracking:

Want to know more? We’d love to tell you more!

Measuring and monitoring user interactions and behaviour with OBIEE is a key part of Rittman Mead’s User Engagement Service. By understanding and proving how users are engaging the system we can improve the experience for the user, driving up usage and ensuring maximum value for your OBIEE investment.

If you’d like to find out more, including about Enhanced Usage Tracking and getting a free User Engagement Report for your OBIEE system, get in touch now!

The post Driving OBIEE User Engagement with Enhanced Usage Tracking for OBIEE appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Rittman Mead at UKOUG Tech’15 Conference, Birmingham

Tue, 2015-12-08 07:40

This week Rittman Mead are very pleased to be presenting at the UK Oracle User Group’s Tech’15 Conference in Birmingham, delivering a number of sessions around OBIEE, Data Integration, Cloud and Big Data.


If you’re at the event and you see any of us in sessions, around the conference or during our talks, we’d be pleased to speak with you about your projects and answer any questions you might have. Here’s the list our speaking slots over the four days of the event, and I’ll update the list with links to presentation downloads as they become available over the event.

In addition, if you’re interested in the OBIEE user adoption and retention area that Robin talks about in his Wednesday session, Rittman Mead have a User Engagement service using some of the tools and techniques that Robin talked about (datasheet here) and we’d be pleased to talk to you about how you can increase user engagement, adoption and retention for your OBIEE system. Other than that, come and speak to us if you see us at the Birmingham ICC, and look forward to more content on these areas in the New Year!

The post Rittman Mead at UKOUG Tech’15 Conference, Birmingham appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Oracle OpenWorld 2015 Roundup Part 3 : Oracle 12cR2 Database Sharding, Analytic Views and Essbase 12c

Sat, 2015-12-05 13:13

With the UKOUG conference starting tomorrow I thought it about time I finished off my three-part post-OOW 2015 blog series, with a final post on some interesting announcements around Oracle Database and Essbase. As a reminder the other two posts were on OBIEE12c and the new Data Visualisation Cloud Service, and Data Integration and Big Data. For now though let’s look first at two very significant announcements about future 12cR2 functionality – database sharding and Analytic Views.

Anyone who’s been involved in Oracle Data Warehousing over the years will probably be aware of the shared-everything vs. shared-nothing architecture debate. Databases like Oracle Database were originally designed for OLTP workloads with the optimal way to increase capacity being to buy a bigger server. When RAC (Real Application Clusters) came along the big selling point was  a single shared database instance spread over multiple nodes, making application development easy (no real changes) but with practical limits as to how big that cluster can get – due to the need to synchronise shared memory across all nodes in the cluster, and network bottleneck caused by compute and storage being spread across the whole cluster, not co-located as we get with Hadoop and HDFS, for example.


Shared-nothing databases such as Netezza, for example, take a different approach and “shard” the database instance over multiple nodes in the cluster so that processing and storage are co-located on the same node for particular ranges of data. This gives the advantage of much greater scalability that a shared-nothing approach (again, this is why Hadoop uses a similar approach for its massively-clustered distributed compute approach) but with the drawback of having to consider data locality when writing ETL and other code; at worst it means data loading and processing needs to be rewritten when you add more nodes and re-shard the database, and it also generally precludes OLTP work and consequently mixed-workloads on the same platform.


But if it’s just data warehousing you want to do, you don’t really care about mixed workloads and its generally considered that shared-nothing and sharding is what you need if you want to get to very-large scale data warehousing, such that Oracle went partly down the shared-nothing route with Exadata and push-down of filtering, projection and other operations to storage nodes thereby adding an element of data locality and reducing the network throughput between storage and compute.


But both types of database are loosing out to Hadoop for very, very large datasets with Hadoop distributed compute approach designed right from the start for large distributed workloads at the expense of not supporting OLTP at all and, at least initially, all intermediate resultsets being written to disk. For those types of workloads and database size Oracle just wasn’t an option, but a certain top their of Oracle’s data warehousing customers wanted to be able to scale to hundreds or thousands of nodes and most of them have ULAs, so cost isn’t really a limiting factor; for those customers, Oracle announced that the 12c Release 2 version of Oracle Database would support sharding … but with warnings it’s for sophisticated and experienced customers only.


Oracle are positioning what they’re referring to as “Oracle Elastic Sharding” as for both scaling and fault-tolerance, with up to 1,000 nodes supported and with data routed to particular shards through use of a “sharding key” passed the connection pool.


Sharding in 12c Release 2 was described to me as a featured aimed to the “top 5%” of Oracle customers where price isn’t the issue but they want Oracle to scale to the size of cluster supported by Hadoop and NoSQL. Time will tell how well it’ll work and what it’ll cost, but it certainly completes Oracle’s journey from strict shared-everything for data warehousing to more-or-less shared nothing, if you want to go down that extreme-scalability route.

The other announcement from the Oracle Database side was the even-more-unexpected “Analytic Views”. A clue came from who was running the session – Bud Endress, of Oracle Express / Oracle OLAP fame and more recently, the Vector Group By feature in the In-Memory Option – but what we got was a lot more than Oracle OLAP re-imagined for in-memory; instead what Oracle are looking to do is bring the business metadata and calculation layers that BI tools use right into the database, provide an MDX query  interface over it, simplify SQL so that you just select measures, attributes and hierarchies – and then optimise the whole thing so it runs in-memory if you have that option licensed.


Its certainly an “interesting” goal with considerable overlap with OBIEE’s BI Server and Essbase Server, but the goal of bringing all this functionality closer to the data and available to all tools is certainly ambitious, if it gets traction it should bring business metadata layers and simpler queries to a wider audience – but the fact that it seems to be being developed separately to Oracle’s BI and Essbase teams means it probably won’t be subsuming Essbase or the BI Server’s functionaliy.

The last area I wanted to look at was Essbase. Essbase Cloud Service was launched at this event with it positioned as a return to Essbase’s roots as a tool you could use in the finance department without requiring IT’s help, except this time it’s because Essbase is running as a service in the cloud rather than on an old PC under your desk. What was particularly interesting though is that the version of Essbase being used in the cloud is the new 12c version, that replaces some of the server components (the Essbase Agent, but not the core Essbase Server part) with new Java components that presumably fit better with Oracle’s cloud infrastructure and also support greater levels of concurrency


Apart from the announcement of a future ability to link to R libraries, the other really interesting part of Essbase 12c is that for now the only on-premise version of it is as part of OBIEE12c, and it’ll have a very fixed role there as a pure query accelerator for OBIEE’s BI Server – perhaps the answer to Qlikview and Tableau’s in-memory column-store caches. Essbase as part of an OBIEE12c store doesn’t work with Essbase Studio or any of the other standard Essbase tools, but instead has a new Essbase Business Intelligence Acceleration Wizard that deploys Hybrid ASO/BSO Essbase cubes directly from the OBIEE BI Server and RPD.


Coupled with the changes to Essbase announced a couple of years ago at Openworld 2013 designed to improve compatibility with OBIEE, this co-located version of Essbase seems to have completed it’s transformation into the BI Server mid-tier aggregate cache layer of choice that started back with the BP1 version of OBIEE – but it does mean this version can’t be used for anything else, even custom Essbase cubes you load and design yourself. Interesting developments across both database server products though, and that wraps up my overview of OOW2015 announcements. Next stop – UKOUG Tech’15 in Birmingham, where I’ve just arrived ready for my masterclass session in tomorrow’s Super Sunday event – on data reservoirs and Customer 360 on Oracle Big Data Appliance.

The post Oracle OpenWorld 2015 Roundup Part 3 : Oracle 12cR2 Database Sharding, Analytic Views and Essbase 12c appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Action Links in OBIEE 12c – Part 1

Mon, 2015-11-16 13:29


With the release of OBIEE 12c, let’s take a look at Action Links and how things may be different compared to the previous release, 11g. Over this three part blog series, we’re going to cover the more popular link types, which are navigating to BI content, and navigating to a web page. However, to sweeten the deal, I’ll also include some tricks for your tool belt which well enable you to do the following:


  • Navigate to a target report, while filtering it on parameters chosen in the source
  • Pass filter parameters via the GoURL syntax from a source report to another, target report
  • Become familiar with the GoURL structure and how to apply it to your business case


In the first installment of this three part series, we’re going look at how to navigate to other reports and dashboards in your catalog through the ‘Navigate to BI Content’ action. This will set you up for parts 2 and 3, wherein we show you some tricks using Action Links.


1. The Action Link UI

By now, there are likely lots of blogs talking about the new look and features of OBIEE 12c, so we can keep this bit short. Suffice to say it got a much needed face lift, with both changes in overall skinning and in its portfolio of icons. While this change in graphics may induce a bit of frustration on part of the developer, I believe this approach to design will end up being a good long term strategy to handle later releases of the product as trends in UX seem to have their feet firmly planted in the stripped down, the clean, and the subdued. Even with this shift, however, the basic processes and series of steps to implement most any of the features in Answers remains the same, Action Links being no different. Just follow these simple steps below to set up your Action Link! After you’ve got a hold of the basics, look to future posts in this series for some tips and tricks using Action Links.


In chosen column, go to the Column Properties menu:


Next, click on the Interaction tab:


Select ‘Action Links’ as the Primary Interaction value and click on the ‘+’ icon. This will display another dialogue box where we will set up the actual properties of the Action Link. Click on the running man icon (this little guy seems to be more intuitive than the green gear):



2. Navigate to BI Content

For the first example, we’re going to select the ‘Navigate to BI Content’ option. This simply allows us to go to another report or dashboard, as though you were clicking on a link in a web page. To implement this on your report, simply follow the steps above and then refer to the steps below.

After clicking on the running man icon, select the ‘Navigate to BI Content’ option. This will be followed by a dialogue box allowing you to select the object to which you want to navigate.


Confirm your selection and then click ‘OK’, not once, not twice, but thrice, at which point you’re taken back to the Criteria tab. From now on, this column will take you to the selected report.

And that’s it! Take a look back here for part 2 on Action Links in OBIEE 12c, which will outline a neat technique on how to implement what’s called a ‘driving document’ to filter values between disparate reports using the navigate action.

The post Action Links in OBIEE 12c – Part 1 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

OBIEE 11g and Essbase – Faking Federation Using the GoURL

Thu, 2015-11-12 14:56

This blog is going to address what happens when we can’t take advantage of the Admin tool’s powerful vertical federation capabilities when integrating relational stars and Essbase cubes. In the Admin tool, synonymously referred to as the RPD, vertical federation is the process of integrating an aggregate data source, in this case Essbase, with a detail level source from a data mart. This technique not only has the ability to increase query efficiency and decrease query time, it also has the added benefit of bringing together two powerful and dynamic reporting tools. But like most things, there is a pretty big caveat to this approach. But, before I jump into what that is, some housework. To start, let’s make sure things don’t get lost in translation when going back and forth between Essbase and OBIEE jargon. In Essbase speak, dimensions can be thought of as tables in a relational structure, whereas Essbase generations can be thought of as columns in each table, and members are the values in each column. Housework done, now the caveat. Often, dimensions in Essbase cubes are built in such a way as to not neatly support federation; that is, they are arranged so as to have an uneven number of generations relative to their corresponding relational dimension. It should be noted at this point that while federation is possible with a ragged hierarchical structure, it can get kind of messy, essentially ending up in a final product that doesn’t really look like something an Essbase-centric user community would readily and eagerly adopt. So what then, can we do when federation is out of the question? Let’s frame the solution in the form of a not-atypical client scenario. Say we’ve got a requirement per a large finance institution of a client to bring together their Essbase cubes they’ve used thus far for their standardized reporting, i.e. balance sheets, income statements and the like, with their relational source in order to drill to account detail information behind the numbers they’re seeing on said reports. They’ve got a pretty large user base that’s fairly entrenched and happy with their Smart View and Excel in getting what they want from their cubes. And why shouldn’t they be? OBIEE simply can’t support this level of functionality when reporting on an Essbase source, in most cases. And, in addition to these pretty big user adoption barriers to an OBIEE solution, now we’ve got technology limitations to contend with. So what are our options then when faced with this dilemma? How can we wow these skeptical users with near seamless functionality between sources? The secret lies with URL Action Links! And while this solution is great to go from summary level data in Essbase to its relational counterpart, it is also a great way to simply pass values from one subject area to another. There are definitely some tricks to set this up, but more on those later. Read on.

The Scenario

In order to best demonstrate this solution, let’s set up a dashboard with two pages, one for each report, and a corresponding dashboard prompt. The primary, source report, out of Essbase, will be something that could easily resemble a typical financial report, if not at least in structure. From this high-level chart, or similar summary level analysis, we’ll be able to drill to a detail report, out of a relational source, to identify the drivers behind any figures present on the analysis. In this example, we’re going to be using the Sample App, Sample Essbase subject area to go to the equivalent relational area, Sample Sales. Yes, you could federate these two, as they’ve done in Sample App, however they’ll serve well to demonstrate how the following concept could work for financial reporting against ragged or parent-child structures. Values for Product Type, in the following instance, could just as well be the descendants or children of a specific account, as an example. As well, there is no equivalent relational subject area to use for the sake of the SampleApp Essbase GL subject area. In the example below, we have a summary, month level pivot table giving us a monthly sales trend. The user, in the following example, can prompt on the Year and Customer segment through a dashboard prompt, but as you’ll see, this could easily be any number of prompts for your given scenario.

Monthly Trend Summary:

Solution 1:

In the sales trend example above, we are going to enable our user to click on a value for a revenue figure and then navigate to a detail report that shows products sold for the month by date. Again, this all must be done while passing any chosen parameters from both the dashboard prompt and analysis along to the detail analysis.

Proof of Concept

First, let’s start with the guts of the report example above. As you can see, there is quite a bit more under the hood than meets the eye. Let’s go over the approach piece by piece to help build a more thorough understanding of the method.

Step 1: Include the Columns!

So the idea here is that we want to pass any and all dimensional information associated with the revenue figure that we pick to a detail level report that will be filtered on the set of parameters at the chosen intersection. We can hide these columns later, so your report won’t be a mess. I’ll add here that you might want to set any promoted values to be equal to the presentation variable on its respective dashboard prompt with a default value set, as seen below. This will help to make the report digestible on the compound layout. The following picture shows the prompted values to drive our summary report on Year and Customer Segment. You can do this in the filters pane on the criteria tab with the following syntax:


                            All column values we want to pass need to be represented on the report:


                           Values that will be passed to detail report (in this case, BizTech, Communication, Active Singles, 2012, and 2012 / 11):

Step 2: More Columns!

In addition to the columns that comprise the report, we need to add an additional iteration of every column for all of those added to the report in the first place. In the pictures above, you can see that these are the columns titled with the ‘URL’ prefix. In the column editor, concatenate quotes to the column values by attaching the following string (this is a single quote followed by a double quote and another single quote w/ NO spaces between them):

‘ ” ‘ || “Table”.”Column Name” || ‘ ” ‘

While this step may seem extemporaneous, you’ll see a bit later that this step is all too necessary to successfully pass our column values through our URL Action Links. After you’ve created the custom columns, just group them along with their counterpart in the report, as in the pics above.

Step 3: An Approach to Handling Hierarchies

In the previous pictures, you can see the products hierarchy that comprises the rows to the report. In order to pass any value from the hierarchy as well as its members we are going to have to include its respective generations in the rows as well. For our example, we’re going to use Brand, LOB, and Product Type. In this way, a user can select any sales value and have all three of these values passed as filter parameters to the detail analysis through a URL. You’ll notice that we haven’t given these columns a counterpart wrapped in quotes as you were told to do previously. This is quite on purpose, as we’ll see later. These columns will provide for another example on how to pass values without having to implement a second column for the purpose of wrapping the value in quotes.


When first placing the hierarchy on your analysis and expanding it to where you’d like it for the sake of the report, you can simply select all the column values, right click and then select ‘Keep Only’. This will establish a selection step under the Products Hierarchy to ensure that the report always opens to the specified structure from now on. So, that’s good for now, let’s get to the magic of this approach.


Step 4. Set up the Action Link

In this case, we’re going to ‘drill’ off of the Sales column in our table, but we could really ‘drill’ off of anything, as you’ll see. So, pop open the Interaction tab for the column and select Action Links as our primary interaction. Edit that guy as follows (see URL procedure below). It used to be that we could do this via the ‘P’ parameters, however this method seems to be mostly deprecated in favor of the col/val method, as we shall utilize below.

URL Procedure – Server URL*
Portal&Path=@{1} – path to dashboard
&Page=@{2} – dashboard page
&Action=@{3} – action to perform, in this case navigate (there are others)
&col1=@{4} – column from target analysis we wish to manipulate (our sales detail analysis)
&val1=@{5} – column from source analysis with which we are going to pass a filter parameter to target
&val4=“@{11}” – will discuss these quoted parameters later on

*Note that this value can be made into a variable in order to be moved to different environments (DEV/TEST, etc…) while maintaining link integrity

The picture above details how to set up the URL link as described above. The col1 value is the column from the target analysis we want to filter using the value (val1) from our source. Be sure to qualify this column from the subject area from which it originates, in this case “A – Sample Sales”.

Ex: “A – Sample Sales”.”Time”.”T05 Per Name Year”

Val1, as these parameters exist in ‘sets’, is the column from our source analysis we want to use to filter the target analysis. This is where our custom, quoted columns come into play. Instead of using the original column from our analysis, we’re going to use its quoted counterpart. This will ensure that any values passed through the URL will be enclosed in quotes, as is required buy the URL. Note that we’re not using a value parameter in this case, but a column instead (the dropdown to the left of the text box).

Ex: ‘ ” ‘ || “Time”.”T05 Per Name Year” || ‘ ” ‘

You can proceed this way to pass as many values as you’d like to your detail analysis, with this coln, valn method. Again, just be sure that your columns are included in the source analysis or the values won’t get ported over. Once you’ve got all your columns and values set up, go ahead and enter them into the URL field in the Edit Action dialogue box, as above. Make sure you reference your variables using the proper syntax (similar to a presentation variable w/ an @ sign):

Ex: col1=@{4} – ‘4’ being the variable name (note that these can be named most anything)

Quoting Parameters

As an alternative to including an extra iteration of each column for the sake of passing quoted column values, we can instead, put quotes around the parameter in our URL, as in the example above. The limitation to this method, however, is that you can only pass a singular value, as in Year, for example. In later posts, we’ll address how to handle passing multiple values, as you might through a dashboard prompt.

Step 5. Set Up the Detail Analysis

For our detail analysis we’re going to set it up in much the same way as our summary. That is, we need to include the columns we want to filter on in the target report as. Unfortunately, our target report won’t simply pick them up as filters as you might put on your filters pane, without including them on the actual analysis. Again, any columns we don’t want visible to a user can be hidden. Below, we simply want to see the Calendar Date, Product, and Revenue, but filtered by all of our source analysis columns.

In the criteria view for our target, detail analysis, we need to make sure that we’re also setting any filtered columns to ‘is prompted’. This will ensure that our target analysis listens to any filter parameters passed through the URL from our source, summary analysis. As a last step, we must again fully qualify our filters, as in the picture below.

This picture shows our Year ‘is prompted’ filter on our target, detail analysis. Note that this column is also a column, albeit hidden, on this report as well. This will act as a filter on the analysis. It is being ‘prompted’ not by a dashboard prompt, in this instance, but by our source, summary analysis.

Step 6. Testing it All Out

Now that we’ve got all the pieces of the puzzle together, let’s see if it works! To QA this thing, let’s put a filter object on the target, detail analysis to make sure that the report is picking up on any values passed. So if we click on a sales value, we should be taken to the target analysis and see that all the parameters we set up were passed. The picture below confirms this!


Hopefully this can be one more trick to keep in the tool belt when faced with a similar scenario. If you have any hiccups in your implementation of this solution or other questions, please feel free to respond to this post. Stay tuned for additional articles related to this topic that go much more in depth. How do you handle passing multiple column values? How do I keep my report query time low with all those extra columns? How do I pass values using the presentation variable syntax? Can I use the Evaluate function to extract the descendants of a filtered column?



The post OBIEE 11g and Essbase – Faking Federation Using the GoURL appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Rittman Mead and Oracle Big Data Webcast Series – November 2015

Mon, 2015-11-02 12:45

We’re running a set of three webcasts together with Oracle on three popular use-cases for big data within an Oracle context – with the first one running tomorrow, November 3rd 2015 15:00 – 16:00 GMT / 16:00 – 17:00 CET on extending the data warehouse using Hadoop and NoSQL technologies.

The sessions are running over three weeks this month and look at ways we’re seeing Rittman Mead use big data technologies to extend the and capabilities of their data warehouse, create analysis sandpits for analysing customer behaviour, and taking data discovery into the Hadoop era using Oracle Big Data Discovery. All events are free to attend, we’re timing them to suit the UK,Europe and the US, with details of each webcast are as follows:


Extending and Enhancing Your Data Warehouse to Address Big Data

Organizations with data warehouses are increasingly looking at big data technologies to extend the capacity of their platform, offload simple ETL and data processing tasks and add new capabilities to store and process unstructured data along with their existing relational datasets. In this presentation we’ll look at what’s involved in adding Hadoop and other big data technologies to your data warehouse platform, see how tools such as Oracle Data Integrator and Oracle Business Intelligence can be used to process and analyze new “big data” data sources, and look at what’s involved in creating a single query and metadata layer over both sources of data.

Audience: DBAs, DW managers, architects Tuesday 3rd November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

Audience : DBAs, DW managers, architects

What is Big Data Discovery and how does it complement traditional Business Analytics?

Data Discovery is an analysis technique that complements traditional business analytics, and enables users to combine, explore and analyse disparate datasets to spot opportunities and patterns that lie hidden within your data. Oracle Big Data discovery takes this idea and applies it to your unstructured and big data datasets, giving users a way to catalogue, join and then analyse all types of data across your organization. At the same time Oracle Big Data Discovery reduces the dependency on expensive and often difficult to find Data Scientists, opening up many Big Data tasks to “Citizen” Data Scientists. In this session we’ll look at Oracle Big Data Discovery and how it provides a “visual face” to your big data initiatives, and how it complements and extends the work that you currently do using business analytics tools.

Audience : Data analysts, market analysts, & Big Data project team members Tuesday 10th November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

Adding Big Data to your Organization to create true 360-Degree Customer Insight

Organisations are increasingly looking to “big data” to create a true, 360-degree view of their customer and market activity. Big data technologies such as Hadoop, NoSQL databases and predictive modelling make it possible now to bring highly granular data from all customer touch-points into a single repository and use that information to make better offers, create more relevant products and predict customer behaviour more accurately. In this session we’ll look at what’s involved in creating a customer 360-degree view using big data technologies on the Oracle platform, see how unstructured and social media sources can be added to more traditional transactional and customer attribute data, and how machine learning and predictive modelling techniques can then be used to classify, cluster and predict customer behaviour.

Audience : MI Managers, CX Managers, CIOs, BI / Analytics Managers Tuesday 24th November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

The post Rittman Mead and Oracle Big Data Webcast Series – November 2015 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Oracle OpenWorld 2015 Roundup Part 2 : Data Integration, and Big Data (in the Cloud…)

Mon, 2015-11-02 01:27

In yesterdays part one of our three-part Oracle Openworld 2015 round-up, we looked at the launch of OBIEE12c just before Openworld itself, and the new Data Visualisation Cloud Service that Thomas Kurian demo’d in his mid-week keynote. In part two we’ll look at what happened around data integration both on-premise and in the cloud, along with big data – and as you’ll see they’re too topics that are very much linked this year.

First off, data integration – and like OBIEE12c, ODI 12.2.1 got released a day or so before Openworld as part of the wider Oracle Fusion Middleware 12c Release 2 platform rollout. Some of what was coming in ODI12.2.1 got back-ported to ODI 12.1 earlier in the year in the form of the ODI Enterprise Edition Big Data Options, and we covered the new capabilities it gave ODI in terms of generating Pig and Spark mappings in a series of posts earlier in the year – adding Pig as an execution language gives ODI an ability to create dataflow-style mappings to go with Hive’s set-based transformations, whilst also opening-up access to the wide range of Pig-specific UDF libraries such as DataFu for log analysis. Spark, in the meantime, can be useful for smaller in-memory data transformation jobs and as we’ll see in a moment, lays the foundation for streaming and real-time ingestion capabilities.


The other key feature that ODI12.2.1 provides though is better integration with external source control systems. ODI already has some element of version control built in, but as it’s based around ODI’s own repository database tables it’s hard to integrate with more commonly-used enterprise source control tools such as Subversion or Git, and there’s no standard way to handle development concepts like branching, merging and so on. ODI 12.2.1 adds these concepts into core ODI and initially focuses on SVN as the external source control tool, with Git support planned in the near future.


Updates to GoldenGate, Enterprise Data Quality and Enterprise Metadata Management were also announced, whilst Oracle Big Data Preparation Cloud Service got its first proper outing since release earlier in the year. Big Data Preparation Cloud Service (BDP for short) to my mind suffers a bit from confusion over what it does and what market it serves – at some point it’s been positioned as a tool for the “citizen data scientist” as it enables data domain experts to wrangle and prepare data for loading into Hadoop, whilst at other times it’s labelled a tool for production data transformation jobs under the control of IT. What is misleading is the “big data” label – it runs on Hadoop and Spark but it’s not limited to big data use-cases, and as the slides below show it’s a great option for loading data into BI Cloud Service as an alternative to more IT-centric tools such as ODI.


It was another announcement though at Openworld that made Big Data Prep Service suddenly make a lot more sense – the announcement of a new initiative called Dataflow ML, something Oracle describe as “ETL 2.0” with an entirely cloud-based architecture and heavy use of machine learning (the “ML” in “Dataflow ML”) to automate much of the profiling and discovery process – the key innovation on Big Data Prep Service.


It’s early days for Dataflow ML but clearly this is the direction Oracle will want to take as applications and platforms move to the cloud – I called-out ODI’s unsuitability for running in the cloud a couple of years ago and contrasted its architecture with that of cloud-native tools such as Snaplogic, and Dataflow ML is obviously Oracle’s bid to move data integration into the cloud – coupling that with innovations around Spark as the data processing platform and machine-learming to automate routine tasks and it sounds like it could be a winner – watch this space as they say.

So the other area I wanted to cover in this second of three update pieces was on big data. All of the key big data announcements from Oracle came in last year’s Openworld – Big Data Discovery, Big Data SQL, Big Data Prep Service (or Oracle Data Enrichment Cloud Service as it was called back then) and this year saw updates to Big Data SQL (Storage Indexes), Big Data Discovery (general fit-and-finish enhancements) announced at this event. What is probably more significant though is the imminent availability of all this – plus Oracle Big Data Appliance – in Oracle’s Public Cloud.


Most big data PoCs I see outside of Oracle start on Amazon AWS and build-out from there – starting at very low-cost and moving from Amazon Elastic MapReduce to Cloudera CDH (via Cloudera Director), for example, or going from cloud to on-premise as the project moves into production. Oracle’s Big Data Cloud Service takes a different approach – instead of using a shared cloud infrastructure and potentially missing the point of Hadoop (single user access to lots of machines, vs. cloud’s timeshared access to slices of machines) Oracle instead effectively lease you a Big Data Appliance along with a bundle of software; the benefits being around performance but with quite a high startup cost vs. starting small with AWS.

The market will tell which approach over time gets most traction, but where Big Data Cloud Service does help tools like Big Data Discovery is that theres much more opportunities for integration and customers will be much more open to an Oracle tool solution compared to those building on commodity hardware and community Hadoop distributions – to my mind every Big Data Cloud Service customer ought to buy BDD and most probably Big Data Prep Service, so as customers adopt cloud as a platform option for big data projects I’d expect an uptick in sales of Oracle’s big data tools.

On a related topic and looping back to Oracle Data Integration, the other announcement in this area that was interesting was around Spark Streaming support in Oracle Data Integrator 12c.


ODI12c has got some great batch-style capabilities around Hadoop but as I talked about earlier in the year in an article on Flume, Morphines and Cloudera Search the market is all about real-time data ingestion now, batch is more for one-off historical data loads. Again like Dataflow ML this feature is in beta and probably won’t be out for many months, but when it comes out it’ll complete ODI’s capabilities around big data ingestion – we’re hoping to take part in the beta so keep an eye on the blog for news as it comes out.

So that’s it for part 2 of our Oracle Openworld 2015 update – we’ll complete the series tomorrow with a look at Oracle BI Applications, Oracle Database 12cR2 “sharding” and something very interesting planned for a future Oracle 12c database release – “Analytic Views”.

The post Oracle OpenWorld 2015 Roundup Part 2 : Data Integration, and Big Data (in the Cloud…) appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing