BI & Warehousing

Data Lake vs. Data Warehouse

Dylan's BI Notes - Sun, 2015-10-04 18:18
These are different concepts. Data Lake – Collect data from various sources in a central place.  The data are stored in the original form.  Big data technologies are used and thus the typical data storage is Hadoop HDFS. Data Warehouse – “Traditional” way of collecting data from various sources for reporting.  The data are consolidated […]
Categories: BI & Warehousing

Use DMZ to access BI from outside firewall

Dylan's BI Notes - Fri, 2015-10-02 14:12
DMZ is a technology that allows you to configure your network to be accessible outside firewall. Some of users may want to access some of corporate reports from mobile or from their personal computers. While VPN and Citrix may be useful for these cases, DMZ can provide another option. A good article – OBIEE Security […]
Categories: BI & Warehousing

Lessons Learned – Data Warehouse on Cloud – Amazon Redshift

Dylan's BI Notes - Tue, 2015-09-22 12:37
I feel that these are the rules applicable for any cloud based data warehouse solution.  In general, I feel that the on-premise data warehouse deployment probably will remain for a while. 1. For a columnar database, “select *” is bad I think that the projection needs to be done as early as possible and should […]
Categories: BI & Warehousing

Data Warehouse on Clould – Amazon Redshift

Dylan's BI Notes - Thu, 2015-09-17 16:50
Here is a brief summary of what I learned by reading these materials. 1. The data warehouse is stored in clusters It can support scale out, not scale up. “Extend the existing data warehouse rather than adding hardware” 2. Use SQL to access the data warehouse 3. Load data from Amazon S3 (Storage Service) using […]
Categories: BI & Warehousing

OTBI Enterprise

Dylan's BI Notes - Tue, 2015-09-15 10:39
OTBI Enterprise is the BI cloud service, a SaaS deployment of OBIA.  It is using a data warehouse based architecture.  The ETL processes are handled within the cloud.  The data are first loaded from either on premise or cloud sources using various means in the original formats.   The data are first loaded into the […]
Categories: BI & Warehousing

Is Apache Spark becoming a DBMS?

Dylan's BI Notes - Wed, 2015-09-09 22:02
I attended a great meetup and this is the question I have after the meeting. Perhaps the intent is to make it like a DBMS, like Oracle, or even a BI platform, like OBIEE? — The task flow it actually very similar to a typical database profiling and data analysis job. 1. Define your question […]
Categories: BI & Warehousing

Schema On Demand and Extensibility

Dylan's BI Notes - Fri, 2015-09-04 01:44
Today I see a quite impressive demo in the Global Big Data Conference. AtScale provides a BI metadata tool for data stored in Hadoop. At first, I thought that this is just another BI tool that access Hadoop via Hive like what we have in OBIEE.  I heard that that the SQL performance for BI […]
Categories: BI & Warehousing

Using Data Relationship Management to Maintain Hierarchies for BI Apps (1)

Dylan's BI Notes - Wed, 2015-09-02 11:52
DRM is a generic data management application. It provides a web based application that allows the deploying company to maintain the data. It is a collaboration tool that allows you to define the validation and set up the data security duties and share the maintenance. Earlier the tool was designed to maintain the account information.  However, […]
Categories: BI & Warehousing

Page Borders and Title Underlines

Tim Dexter - Wed, 2015-08-26 16:32

I have taken to recording screen grabs to help some folks out on 'how do I' scenarios. Sometimes a 3 minute video saves a couple of thousand words and several screen shots.

So, per chance you need to know:

1. How to add a page border to your output and/or

2. How to add an under line that runs across the page

Watch this!

If you need the template, sample data and output, get them here.

I'm taking requests if you have them.

Categories: BI & Warehousing

MRP process on standby stops with ORA-00600

Amardeep Sidhu - Thu, 2015-08-20 04:03

A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.

The error message was something like this

MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/
ORA-00600: internal error code, arguments: [2619], [539], [], [], [], [], [], [], [], [], [], []
Recovery interrupted!

Some googling and MOS searches revealed that the error was due to corrupted archive log file. Recopying the archive file from primary and restarting the recovery resolved the issue. The fist argument of the ORA-600 is actually the sequence no of the archive it is trying to apply.

Categories: BI & Warehousing

Kscope15 - It's a Wrap, Part II

Chet Justice - Thu, 2015-07-09 14:04
Another fantastic Kscope in the can.

This was my final year in an official capacity which was a lot more difficult to deal with than I had anticipated. Here's my record of service:
  • 2010 (2011, Long Beach) - I was on the database abstract review committee run by Lewis Cunningham. I ended up volunteering to help put together the Sunday Symposium and with the help of Dominic Delmolino, Cary Millsap and Kris Rice, I felt I did a pretty decent job.
  • 2011 (2012, San Antonio) - Database track lead. I believe this is the year that Oracle started running the Sunday Symposiums. Kris again led the charge with some input from those other two from the year before, i.e. DevOps oriented
  • 2012 (2013, New Orleans) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey (Tom Kyte OMFG!), OOW/ODTUG Coordinator, etc.
  • 2013 (2014, Seattle) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey, OOW/ODTUG Coordinator, etc.
  • 2014 (2015, Hollywood, FL) Content co-chair for the traditional stuff (Database, APEX, ADF)

This has been a wonderful time for me both professionally and, more importantly to me, personally. Obviously I had a big voice in the direction of content. Also and maybe hard to believe, I actually presented for the first time. Slotted against Mr. Kyte. I reminded everyone of that too. Multiple times. It seemed to go well though. Only a few made fun of me.

I was constantly recruiting too. "Did you submit an abstract?" "No, why not?" and I'd go into my own personal diatribe (ignoring my own lack of presenting) into why they should present. Sarah Craynon Zumbrum summed it up pretty well in a recent article.

But it was the connections I made, the people I met, the stories I shared (#ampm, #cupcakeshirt, etc), and the friends that I made, that's what has had the most impact on me. Kscope is unique in that way because of it's Collaborate or OOW, you'll be lucky to see someone more than once or twice, at Kscope you're running into everyone constantly.

How could I forget? #tadasforkate! This year was even more special. For those that don't know, Katezilla is my profoundly delayed but equally profoundly happy 10 y/o daughter. Just prior to the conference her physical therapist taught her "tada!" and Kate would hold her hands up high in the air and everyone around would yell, Tada! I got this crazy idea to ask others to do it and I would film it. Thirty or forty videos and hundreds of participants later...

So a gigantic thank you to everyone who made this possible for me.
Here's a short list of those that had a direct impact on me...
  • Lewis Cunningham - he asked me to be a reviewer which started all of this off.
  • Mike Riley - can't really say enough about Mike. After turning me away a long time ago (jerk), he was probably my biggest supporter over the years. (Remind me next year to you tell you about "The Hug."). Mike, and his family, are very dear to me.
  • Monty Latiolais (rhymes with Frito Lay I would tell myself) - How can you not love this guy?
  • Natalie Delemar - Co-chair for EPM/BI and then boss as Conference Chair.
  • Opal Alapat - Co-chair for EPM/BI and one of my favorite humans ever invented. I aspire to be more organized, assertive, and bad-ass like Opal.
That list is by no means exhaustive. It doesn't even include staff at YCC, like Crystal Walton, Lauren Prezby and everyone else there. Nor does it include the very long list of Very Special People I've met. I consider myself very fortunate and incredibly grateful.

What's the future hold?
I have no idea. My people are in talks with Helen J. Sander's people to do one or more presentations next year, so there's that. Speaking of's in Chicago. Abstract submissions start soon, I hope you plan on submitting. If you're not ready to submit, I hope you take try to take part in shaping the content by finding one of about 10 abstract review committees. Who knows where they may lead you?

Finally, here's the It's a Wrap video from Kscope15 (see Helen's story there). Here's Kscope16's site. Go sign up.

Categories: BI & Warehousing

Next Generation Outline Extractor Released

Tim Tow - Sun, 2015-06-14 09:36
We recently released an updated version of the Next Generation Outline Extractor. This new version,, addresses three issues:

  • Fixed an issue where the username and password passed via the command line were improperly logged
  • Fixed an issue reading MaxL XML data sources when the alias or UDA contained xml encoded characters such as the ampersand (&) character.
  • Updated labels on the Input Source tab of the user interface to clarify their purpose.
Here is a screenshot showing the updated labeling.

Due to the architecture of the Oracle Essbase APIs, it is generally much faster to use the MaxL Outline XML extracts when processing an Essbase Outline extract.  The Next Generation Outline Extractor still uses the Essbase Java API during this extract, but it is able to minimize the number of calls.  The second option shown above, Extract and Process MaxL Outline XML, will automatically extract the Outline XML from the cube during the processing.  The third option shown, Use Previously Extracted MaxL Outline XML, uses (obviously) an Outline XML file that has already been extracted.

Thank you to everyone who reported issues or made suggestions as you help make this utility better!

Categories: BI & Warehousing

WebCenter Content - A new Delivery Channel in !!

Tim Dexter - Wed, 2015-06-03 03:13

Hi Everyone

One of the new features introduced in is WebCenter Content (WCC) Server as a delivery channel. Prior to this release, we could manage delivery to WCC (formerly UCM) server using webDAV as explained by Tim in his blog "BI Publisher and WebDAV... done!". However, there were few restrictions

  • No way to include standard or custom metadata. Therefore, there was no description for the documents submitted and searching these documents in WCC was not convenient.
  • WebDAV uses folders to store the document and access to these folders have to be pre-configured by WCC Administrator. User can not select security group or account at the time of scheduling.

Moreover, with WCC as delivery channel you have the ability to use idc(s), http(s) and JAX-WS protocols. Refer to the documentation on RIDC protocols for more details on these protocols.

We have now videos in BI Publisher Youtube Channel to demonstrate how BI Publisher integration with WCC works. The videos are split in two parts: Part 1 (Title: BIPublisherWCC Part1) covers an overview and explains some of the WCC concepts, while Part 2 (Title: BIPublisherWCC Part2) walks you through all the steps necessary to make the integration work.

You can find additional details in the documentation guide. Navigate to the Books link and check the Administrators Guide for setting up delivery destinations and the Data Modeling Guide for Custom Metadata & Bursting related information. 

I am sure you will find this new feature very easy to configure and very useful for maintaining documents in WCC. Have a nice day !! 

Categories: BI & Warehousing

Oracle BI Publisher is available !!!

Tim Dexter - Mon, 2015-05-25 14:12

Hi Everyone,

I am happy to announce that Oracle BI Publisher is released, although I admit that this is almost a week old news now and I am sure some of you may have already known this by now from the BI Publisher homepage in OTN or from other sources. My sincere apologies for the delay here.

Thank you Tim for helping me to get back into this blog membership and being patient to allow me put this word out. My activity in the blog has been so less in the past that I am as good as a new member here. When I tried to login last week, I was greeted with this message -

"Sorry, you do not have the privileges necessary to access the page you requested. This system is available to Oracle Employees only. Oracle Employees who would like to request a blog account should click here."

So I had to start all over and get a fresh access created. Thanks to Tim and Phil from IT support for helping me with this. I will now make sure to use this space more often and share more features, tips and tricks.

Oracle BI Publisher was GA on May 19th and you can get the download, documentation, certification matrix and release notes here at the BI Publisher home page in OTN. Here is a quick snapshot of new features in this release. The download is also available at Oracle Software Delivery Cloud site. The documentation page has also been given a fresh new structure where in the left navigation you will notice "Task" and "Books" as two menu items. The task will provide quick reference to role based activities under different sub menu items such as "View & Publish", "Design Reports" etc. The "Books" menu will take you to the complete set of books. You can select Administrator's guide, or Developer's guide, Data Modelling Guide, Report Designer's guide and User's Guide for BI Publisher here. If you are looking for any feature, and do not find information under "Tasks" then check for the same under "Books" or use the search option.

Stay tuned for more updates on new features. Wish you have a good time exploring the new features!!

Categories: BI & Warehousing

BIP scheduleReport with Parameters

Tim Dexter - Wed, 2015-05-20 15:37

I have just spent an hour or so working on getting a sample scheduleReport web service working with parameter values. There are a lot of examples out there but none I have found have the parameters being set. Our doc is a little light on details on how to set them up :) In lieu of that, here's this!

        // Set the parameter values for the report. In this example we have
        // 'dept' and 'emp' parameters. We could easily query the params dynamically
        //Handle 'dept' parameter
        ParamNameValue deptParamNameVal = new ParamNameValue();
        deptParamNameVal= new ParamNameValue();
        // Create the string array to hold the parameter value(s)
        ArrayOfXsdString deptVal = new ArrayOfXsdString();
        // For individual values or multiples, add values to the 
        // string array e.g. 10,20,30
        // Asterisk used for a null value ie 'All'

        // add the array to the parameter object
        //Handle 'emp' parameter
        ParamNameValue empParamNameVal = new ParamNameValue();
        empParamNameVal= new ParamNameValue();
        ArrayOfXsdString empVal = new ArrayOfXsdString();
        // For individual values or multiples, add values to the string array 
        // empVal.getItem().add("Jennifer Whalen");
        // empVal.getItem().add("Michael Hartstein");

        // Asterisk used for a null value ie 'All'

        // add parameter values to parameter array        
        ArrayOfParamNameValue paramArr = new ArrayOfParamNameValue();
        //Now add array to values obj
        ParamNameValues pVals = new ParamNameValues();

 The pVals object can then be added to the report request object.


Hopefully, you can extrapolate to your code. JDev application available here, unzip and open the application.
Just the schedule report class is available here.

Categories: BI & Warehousing

Writing tips

Amardeep Sidhu - Tue, 2015-05-19 04:14

Tim Hall has written some brilliant posts about getting going with writing (blogs, whitepapers etc). This post is the result of inspiration from there only. Tim says that just get started with whatever Winking smile.

If you are into blogging and no so active or even if you aren’t you may want to take a look at all the posts to get some inspiration to document the knowledge you gain on day to day basis.

Here is an index to all the posts by Tim till now

Enjoy !

Categories: BI & Warehousing

New OlapUnderground Utility - The Essbase Outline Viewer

Tim Tow - Fri, 2015-05-08 08:54
Have you ever wanted your users to see an Essbase database outline structure, but had trouble giving them access to Essbase Administrative Services ("EAS") for fear that they may have access to change things they shouldn't change?  Maybe you didn't want to give them EAS and have to deal with the version of Java installed on their machine?  We have the solution to your problems.  After a bit of fits and starts, we have finally released the OlapUnderground Essbase Outline Viewer!

The OlapUnderground Essbase Outline Viewer is a read-only user interface built specifically to view the contents of an Essbase outline.  It is built in Microsoft .NET technology and should run on any Windows-based computer without any special downloads.  It also uses the MaxL outline xml format and, as your Hyperion administrators can create and distribute the outline xml file, end users do not need an Essbase login to view the outline.

Here is a screenshot of the Outline Viewer:

Click image to see it full-size

You can download the Essbase Outline Viewer from our website at  Let us know what you think!

Categories: BI & Warehousing

Is MERGE a bug?

Chet Justice - Wed, 2015-04-22 21:57
A few years back I pondered whether DISTINCT was a bug.

My premise was that if you are depending on DISTINCT to return a correct result set, something is seriously wrong with your table design. I was reminded of this again recently when I ran across Kent Graziano's post on Better Data Modeling: Are you making these 3 beginner mistakes in your data models?. Specifically:
Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.

CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key. So why MERGE?

I learned about the MERGE statement in 2008. During an interview, Frank Davis asked me about when I would use it. I didn't even know what it was (and admitted that) but I went home that night and...wait...I think he asked me about multi table inserts. Whatever, credit is still going to Mr. Davis. Where was I? OK, so I had been working with Oracle for about 6 years at that point and I didn't know about it. My initial reaction was to use it everywhere (not really)! You know, shiny object and all. Look! Squirrel!

Why am I considering MERGE a bug? Let me be more specific. I was working with a couple of tables and had not written the API for them yet and a developer was writing some PL/SQL to update the records from APEX. In his loop he had a MERGE. I realized at that moment there was 1, no surrogate key and 2, no natural key defined (which ties in with Kent's comments up above). Upon realizing the developer was doing this, I knew immediately what the problem was (besides not using a PL/SQL API to nicely encapsulate the business logic). The table was poorly designed.

Easy fix. Update the table with a surrogate key and define a natural key. I was thankful for the reminder, I hadn't added the unique constraint yet. Of course had I written the API already I probably would have noticed the design error, either way, a win for design.

Now, there are perfectly good occasions to use the MERGE statement. Most of those, to me anyway, relate to legacy systems where you don't have the ability to change the underlying table structures (or it's just cost prohibitive) or ETL, where you want to load/update a dimension table in your data warehouse.

Noons, how's that? First time out in 10 months. Thanks for the push.
Categories: BI & Warehousing

Data Mining Scoring Development Process

Dylan Wan - Fri, 2015-04-03 00:39

I think that the process of building a data mining scoring engine is similar to develop an application.

We have the requirement analysis, functional design, technical design, coding, testing, deployment, etc. phases.

Categories: BI & Warehousing

Next Generation Outline Extractor - New Version Available

Tim Tow - Mon, 2015-02-16 14:42
Today we released a new version of the Next Generation Outline Extractor, version  Here are the release notes from this new version:

Version supports the following Essbase versions:


Issues resolved in version

2015.02.15 - Issue 1355 - All Writers - Add functionality to replace all line feeds, carriage returns, tabs, and extraneous spaces in formulas

2015.02.13 - Issue 1354 - RelationalWriter - Changed the default database name from dodeca to extractor


2015.02.13 - Issue 1352 - RelationalWriter - Added Server, Application, and Cube columns to the CACHED_OUTLINE_VERSIONS table

2015.02.13 - Issue 1351 - Fixed issue with LoadFileWriter where UDA column headers were incorrectly written in the form UDAS0,DimName instead of UDA0,DimName

In addition, a number of fixes, etc, were put into 2.0.2 and earlier releases and those releases went unannounced.  Those updates included the following items:

  1. There is no longer a default .properties file for the Extractor.  This will force a user to specify a .properties file.  (
  2. Removed the "/" character as a switch for command line arguments as it causes problems in Linux. (
  3. Fixed issue when combining MaxL input with relational output where a "not supported" error message would appear due to certain properties were not being read correctly from the XML file (
  4. Command line operations resulted in an error due to an improper attempt to interact with the GUI progress bar. (
  5. Shared members attributes where not be properly written resulting in a delimiter/column count mismatch. (
  6. Added encoding options where a user can choose between UTF-8 and ANSI encodings.  The Extractor will attempt to detect encoding from selected outline and, if the detected outline encoding is different from the user selected outline encoding, a warning message appears.
Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator - BI & Warehousing