Monday, June 30, 2014

OBIEE - Marking Events on (timeline) Graphs

Customer uses timeline graphs and wanted to mark important events on the graph.
I suggested something like this, and here I will described how I did it.
(Done on OBIEE

There is no rocket science here.
I assume a table with date, event name and measure. in my case it's Revenue.
Something like this:

Since I didn't really had events data I faked it with a case formula:
CASE "Time"."Calendar Date"  
    WHEN DATE'2010-02-01' THEN 'Business Fair'  
    WHEN DATE'2010-02-17' THEN 'Business Fair' 
    WHEN DATE'2010-03-24' THEN 'Party' 
    WHEN DATE'2010-04-24' THEN 'Birthday' 
    WHEN DATE'2010-01-25' THEN 'Party' 

We can't really use it directly in Time-line graph (we will get an Error, since Event is not date). And even trying to create a regular line graph will end up with ugly results:

Why only one event and not 5? They are too many point to show all dates on the dates scale. We get random results.

 Now I created filtered measure column that repeated the Revenue value only on Events rows. For example, assuming we have a column Events.Event the formula should be:
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Birthday'))

Or actually 3 columns in our case:
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Birthday'))
Business Fair: 
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Business Fair'))
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Party'))

(Lets be honest, since i used the terrible case above what I really have is something like:
FILTER("Base Facts"."Revenue" USING (CASE "Time"."Calendar Date"  WHEN DATE'2010-02-01' THEN 'Business Fair'  WHEN DATE'2010-02-17' THEN 'Business Fair' WHEN DATE'2010-03-24' THEN 'Party' WHEN DATE'2010-04-24' THEN 'Birthday' WHEN DATE'2010-01-25' THEN 'Party' ELSE NULL END = 'Business Fair'))

After excluding the events column what I have is the following table:

Now I can create a timeline graph (remember to exclude the events column):

In the graph properties I make sure the Revenue is thin line without marks, and all the rest are marks without lines: 

We should have 0px Width lines here as many as they are event types.

While we are in the graph properties, we can change the default legend:

 This is the result:


Actually, John Minkjan did something similar, but different 7 years ago (here is the link).  I miss his blog.

Saturday, June 28, 2014

ODI 12.1.3 is available and it's great for Big Data

Oracle has released a new version of ODI (Oracle Data Integrator), version 12.1.3. 

You can download it here.
See the documentation here.
Read the new features here or in the paper here.
Download a free Virtual Machine of this release here.

ODI 12 is very powerful ETL (though they prefer to call it EL-T) tool that replaces both ODI 11g and OWB. I find the new 12c user interfaces much easier, and ODI was always a powerful tool. (An ODI 12c getting started paper is here, General ODI page is here and self training is here).

For ODI developers you will see important new features such as:
  • Hadoop SQOOP Integration - Apache Sqoop is used for loading data from relational databases to HDFS, Hive, and HBase and fFrom HDFS and Hive to relational databases. Sqoop enables load and unload mechanisms using parallel JDBC connections in Hadoop Map Reduce processes.
  • Hadoop HBase Integration - For HBase Integration ODI has:
    • LKM HBase to Hive (HBase-SerDe)
    • IKM Hive to HBase Incremental Update (HBase-SerDe)
    • RKM HBase
  •  Hive Append Optimization - Hive was supported in previous releases (see bellow), the new features enables Hive append, it makes a real append to existing files instead of creating new appended files.

ODI 12 had great Big Data features in previous release, as you might have seen at Rittman Mead blog:

Now it's even better.

Here is a post about "Importing Data from SQL databases into Hadoop with Sqoop and Oracle Data Integrator (ODI)" (using the new 12.1.3 features).  By the way, it is a great blog for ODI related materials (here) and Golden Gate as well (here).

Finally, here is big Data in a picture (always wondered when I could use this geeky stuff).

Thursday, June 19, 2014

Oracle Big Data - Free NoSql Book and free VM

You can download free Oracle NoSql book as described at MacLochlainns Weblog here.
The actual link is this. It requires a short registration and you recieve a link to the PDF file by mail. the book name is Getting Started with Oracle NoSQL Database and it's from Oracle Press.

If you are interested in Big Data you naturaly have seen the Oracle BDA 3.0 Virtual Machine and training materials here.It's described:

Oracle Big Data Lite Virtual Machine provides an integrated environment to help you get started with the Oracle Big Data platform. Many Oracle Big Data platform components have been installed and configured - allowing you to begin using the system right away. The following components are included on Oracle Big Data Lite:
  • Oracle Enterprise Linux 6.4
  • Oracle Database 12c Release 1 Enterprise Edition (
  • Cloudera’s Distribution including Apache Hadoop (CDH 5.0)
  • Oracle Big Data Connectors 3.0
    • Oracle SQL Connector for HDFS 3.0.0
    • Oracle Loader for Hadoop 3.0.0
    • Oracle Data Integrator 12c
    • Oracle R Advanced Analytics for Hadoop 2.4.0
    • Oracle XQuery for Hadoop 3.0.0
  • Oracle NoSQL Database Enterprise Edition 12cR1 (3.0.5)
  • Oracle JDeveloper 11g
  • Oracle SQL Developer 4.0
  • Oracle Data Integrator 12cR1
  • Oracle R Distribution 3.0.1

Monday, June 16, 2014

OBIEE - Problem with changing default port to 80 on Windows

In this post I briefly describe the process of changing OBIEE default port and talk about a specific problem we had with port 80 on Windows (and how to solve it.)

The process of changing the default port of Enterprise Installation of OBIEE (unlike simple installation) is rather simple. It was documented many times, in Weblogic documentation and many blogs. For example here, here (and if you insist on doing it without Oracle UI, here.)

In short, the steps are:
1. Enter the weblogic Console (server:7001/console).
2. Select Server / bi_server1 and shut it down.
3. Enter bi_server1 (Configuration / General) and change the Listen Port (from 9704 to 80 in our case)
4. Save.
5. Activate changes.
6. Start bi_server1.
7. Start OBIEE components using EM or command line opmnctl startall.

In our case changing to any port but 80 worked.

The note from support (OBIEE 11g: BI Server does not Start after Changing Analytics Port in Windows Platform (Doc ID 1286157.1)) that required restarting the Windows, didn't help.
Nor the advise to change the Windows StartStopServices.cmd content manually (from 9704 to 80, if needed).

The logical assumption was that the port is used by some other program.
We changed back the port to 9704. It's Windows. We run netstat -a and had ambiguous result. The port 80 was on the list, but it seemed nothing was listening on it.     

A business partner working at that site (Guy Rasis from Vision BI) found a solution. Apparently it's a Windows issue. The solution is described in this blog post: Windows PID:4 taking away port 80:

Just follow these steps to diagnose and resolve your issue:
  1. Get pid that is listening port 80: netstat -nao | find “:80″
  2. Open task manager, go to processes tab and check “PID” in Menu/View/Select Columns…, then look for the process using the PID found in last step.
  3. If it is a normal application or IIS, disable it or uninstall. Some programs (such as Skype) have the option to disable its use of port 80.
  4. If it is a System Process—PID 4—you need to disable the HTTP.sys driver which is started on demand by another service, such as Windows Remote Management or Print Spooler on Windows 7 or 2008.
    There is two ways to disable it but the first one is safer:
    • Go to device manager, select “show hidden devices” from menu/view, go to “Non-Plug and Play Driver”/HTTP, double click it to disable it (or set it to manual, some services depended on it).
    • Reboot and use netstat -nao | find “:80″ to check if 80 is still used.
    • Launch RegEdit.
    • Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP
    • Change the value of "start" to 4, which means disabled.
    • Reboot your computer.
My solution was step 4.


Sunday, June 15, 2014

OBIEE - Can't open catalog Offline or create new catalog

Readers digest: On my OBIEE server ( I couldn't open catalog offline (using catalog manager) or create new catalog. That was because of a row in my instanceconfig.xml stating: <UpdateAccountGUIDs>UpdateAndStartNormally</UpdateAccountGUIDs>
Once removed all worked fine.

Lets do the longer version:
For various reasons in my instanceconfig.xml file (located at MWHOME\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1) I had the following section:
 It's useful for certain cases.

When i tried to open the Catalog Offline (using Catalog Manager - runcat.cmd /, I had an error:
"Error initializing/loading existing Catalog: E:\mwhome\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite" 

 To check what is wrong I tried to create a new catalog. Creating a new catalog is a simple operation described in the System Administration Guide, chapter 17:
To manually create a new catalog:
1. Stop the service for Presentation Services.
The Oracle BI Server and WebLogic Server must be running.
2. Specify a new location (one that does not exist) for the catalog on the Repository tab of the Deployment page in Fusion Middleware Control.
See Section 10.2, "Using Fusion Middleware Control to Upload a Repository and Set the Oracle BI Presentation Catalog Location."
3. Ensure that the new location for the catalog is empty.
4. Save your changes and close the file.
5. Restart Presentation Services.

In my case I had an error to start Presentation Services. In sawlog.log file
(located at MWHOME\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1) I could see: 
[OBIPS] [ERROR:1] [] [saw.catalog.local.loadCatalog] [ecid: ] [tid: ] Cannot create new catalog while running in update GUID or recover mode
[OBIPS] [ERROR:10] [] [saw.catalog.local.loadCatalog] [ecid: ] [tid: ] Catalog creation failed: Deleting incomplete directories.
Now I understood what is wrong. I removed the <UpdateAccountGUIDs>UpdateAndStartNormally</UpdateAccountGUIDs> from instanceconfig.xml. I could start the Presentation Server and all worked fine. I could both create a new catalog and open the old one offline.

Saturday, June 14, 2014

OBIEE MAD PS1 (Mobile Application Designer second release) is available

Oracle has released the second version of the MAD - Mobile Application Designer PS1:
Patch 18794832: Oracle Business Intelligence Enterprise Edition for Generic Platform - PATCHSET BUG FOR MOBILE APP DESIGNER

 In OBIEE you have 2 options of Mobile UI. You can run existing dashboards on Apple IOS and Android, or you can create your own specific mobile applications. MAD takes care of the second option.

You can download it here.
The Readme is here.
The new features page is here

New features:

For those of you who don't like first versions of anything... This is you time.

Oracle Sample Sales 406 is available

The new Oracle Sample Sales 406 Virtual Machine is available here.
It includes the latest patches (OBIEE 140527 patch, MAD2), R/ORE, lots of new sample and workshops.
Please note the try it live option in that page!  ( TRY IT NOW )

there is also a EPM specific extension and an option of patching of previous environment.