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

Ephemeral Port Issue with Essbase Has Been Fixed!

Tim Tow - Fri, 2015-02-13 09:24
The issue that has plagued a number of Essbase customers over the years related to running out of available ports has finally been fixed!

This issue, which often manifested itself with errors in the Essbase error 10420xx range, was caused by how the Essbase Java API communicated with the server. In essence, whenever a piece of information was needed, the Essbase Java API grabbed a port from the pool of available ports, did its business, and then released the port back to the pool. That doesn’t sound bad, but the problem occurs due to how Windows handles this pool of ports. Windows will put the port into a timeout status for a period of time before it makes the port available for reuse and the default timeout in Windows is 4 minutes! Further, the size of the available pool of ports is only about 16,000 ports in the later versions of Windows. That may sound like a lot of ports, but the speed of modern computers makes it possible, and even likely, that certain operations, such as the outline APIs, that call Essbase many, many times to get information would be subject to this issue. Frankly, we see this issue quite often with both VB and the Java Essbase Outline Extractors.

We brought this issue to the attention of the Java API team and assisted them by testing a prerelease version of the Java API jars. I am happy to report the fix was released with Essbase In addition, there is a new setting that allows you to turn the optimization on or off:


It is our understanding that this optimization is turned on by default. I also checked the default files shipped with both Essbase and and did not see that setting in those files. It may be one of those settings that is there in case it messes something else up. The work of our own Jay Zuercher in our labs and searching Oracle Support seems to have confirmed that thought. There is apparently an issue where EIS drill-through reports don't work in Smart View if socket optimization is turned on. It is documented in Oracle Support Doc ID 1959533.1.

There is also another undocumented setting:


According to Oracle development, this value defaults to 300 ms but there should be little need to ever change it. The only reason it is there is to tune socket optimization in case more than 2 sockets are used per Java API session.

Jay also tested the version in our labs with the Next Generation Outline Extractor. With the default settings, one large test outline we have, "BigBad", with about 120,000 members in it, extracted in 1 minute and 50 seconds.  With socket optimization turned off, the same outline was only about 25% complete after 2 hours.   In summary, this fix will be very useful for a lot of Oracle customers.

Categories: BI & Warehousing

How do I ...

Tim Dexter - Thu, 2015-02-12 15:23

An email came in this morning to an internal mailing list,

We have an Essbase customer with some reporting requirements and we are evaluating BI Publisher as the potential solution. I'd like to ask for your help with any document, blog or white paper with guidelines about using BI Publisher with Essbase as the main data source.

Is there any tutorial showing how to use BI Publisher with Essbase as the main data source?

There is not one to my knowledge but trying to be helpful I came up with the following response

I'll refer to the docs ...
First set up your connection to Essbase
Then create your data model using that Essbase connection
Use the MDX query builder to create the query or write it yourself (lots of fun :)
Add parameters (optional)
Then build layouts for your Essbase query
annnnd your're done :)

Simple, right? Well simple in its format but it required me to know the basic steps to build said report and then where to find the appropriate pages in the doc for the links. Leslie saw my reply and commented on how straightforward it was and how our docs are more like reference books than 'how to's.' This got us thinking. I have noticed that the new 'cloud' docs have How do I ... sections where a drop down will then show maybe 10 tasks associated with the page Im on right now in the application.

Getting that help functionality into the BIP is going to take a while. We thought, in the mean time, we could carve out a section on the blog for just such content. Here's where you guys come in. What do you want to know how to do? Suggestions in the comment pleeeease!

Categories: BI & Warehousing

Essbase VB API is Officially Dead

Tim Tow - Thu, 2015-02-12 11:06
It is with a sad heart that bring you the news that, as of Essbase, the Essbase VB API is officially dead.  I cut my teeth in Essbase working with that API way back in the mid-1990's, but the writing had been on the wall for some time.  Microsoft stopped supporting VB years ago, so it was only a matter of time before this time would come.  That being said, I haven't used the VB API for any new Essbase work since about 2001; the Essbase Java API is alive and growing so my efforts have been there.

Here is the official notification:

You can read the notification yourself in the Essbase Readme.
Categories: BI & Warehousing

Have your say ...

Tim Dexter - Mon, 2015-02-09 15:25

Another messaging exchange last week with Leslie ...

OK, so we practised it a bit after our first convo and things got a little cheesy but hopefully you get the message.

Hit this link and you too can give some constructive feedback on the Oracle doc for BI (not just BIP.) I took the survey; its only eight questions or more if you want to share more of your input. Please take a couple of minutes to help us shape the documentation of future. 

Categories: BI & Warehousing

OAUG BIP SIG ... we're getting the band back together

Tim Dexter - Thu, 2015-01-15 14:28

 Today's post comes to you from Brent at STR Software. If you could help out, it would be greatly appreciated, read on ...

First off, if you are not familiar with the term SIG, it stands for Special Interest Group. OAUG facilitates a number of SIGs to bring users together that share common or industries concerning certain Oracle products.

Unfortunately, the BI Publisher SIG has been offline for a number of years and has not been given the attention it needs to be a useful resource for members of OAUG. Well... I'm getting the band back together and I need your help!

The SIG itself was formed to specifically focus on BI Publisher embedded in Oracle EBS, Peoplesoft and JD Edwards. I have put together a survey that is being emailed out to previous members of the SIG to get thoughts on how the SIG can be of service. That list is pretty old and YOU may not be on it, so if you are interested in participating in the SIG (or even if you are not), have a look at the link below and let me know your thoughts. Our first official meeting will be at Collaborate 15 in Las Vegas, hope to see you there!

Take the survey -> here!

Categories: BI & Warehousing

Paginated HTML is here and has been for some time ... I think!

Tim Dexter - Fri, 2014-12-12 18:03

We have a demo environment in my team and of course things get a little beaten up in there. Our go to, 'here's Publisher' report was looking really bad. Data was not returning or being rendered correctly on the five templates we have for it.
So, I spent about a half hour cleaning up the report; getting things working again; clearing out the rubbish. I noticed that one of the layouts when rendered in HTML was repeatedly showing a header down the screen. Oh, I know where to get rid of that and off I click to the report properties to fix it. But what is this I see? Is it? Can it be? Are my tired old eyes deceiving me?

Yes, Dexter, you see that right, 'View Paginated'! I nervously changed the value to 'true' and went back to the HTML output.
Holy Amaze Balls Batman, paginated HTML, the holy grail of HTML rendered reports, the Mount Everest of ... no, thats too easy, the K2 of html output ... its fan-bloody-tastic! Can you tell Im excited? I was immediately on messenger to Leslie (doc writer extraordinaire) 

Obviously not quite as big a deal in the sane, real world outside of my head. 'Oh yeah, we have that now ...' Leslie is so calm and collected, however, she does like Maroon 5 but, we overlook that :)

I command you'ers to go find the property and turn it on right now and bask in the glory that is, 'paginated html.!'
I cannot stop clicking back and forth and then to the end and then all the way back to the beginning. Its fantastic!

Just look at those icons, just click em, you know you want to!

Categories: BI & Warehousing

File Encoding in the Next Generation Outline Extractor

Tim Tow - Tue, 2014-12-09 20:11
We had a couple of issues reported with the output of the Next Generation Outline Extractor where the exported file did not work properly as a load file. After some investigation, we found that the file encoding was incorrect. We were encoding the files using the Unicode/UTF-8 format. We chose this encoding so that we could write all characters in all languages, but we did not consider that UTF-8 is only valid for loading Unicode databases in Essbase.

To resolve this issue, we decided to add a configuration to the Next Generation Outline Extractor to allow users to select the file encoding. Here is a screenshot showing the new configuration setting.

As of yesterday, December 8, 2014, the updated Next Generation Outline Extractor is available on our website. The first version to feature this functionality is version Version is available for all Essbase versions from Essbase 9.3.1 forward. We are also happy to announce that his version of the Next Generation Outline Extractor is the first version to support the recently released Essbase

If you encounter any issues with the Next Generation Outline Extractor, please don't hesitate to contact our support team at

Categories: BI & Warehousing

Bordering Text

Tim Dexter - Tue, 2014-11-18 16:08

A tough little question appeared on one of our internal mailing lists today that piqued my interest. A customer wanted to place a border around all data fields in their BIP output. Something like this:

Naturally you think of using a table, embedding the field inside a cell and turning the cell border on. That will work but will need some finessing to get the cells to stretch or shrink depending on the width of the runtime text. Then things might get a bit squirly (technical term) if the text is wide enough to force a new line at the page edge. Anyway, it will get messy. So I took a look at the problem to see if the fields can be isolated in the page as far as the XSLFO code is concerned. If the field can be siolated in its own XSL block then we can change attribute values to get the borders to show just around the field. Sadly not.

This is an embedded field YEARPARAM in a sentence.

translates to

 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" 
  xml:space="preserve">This is an embedded field <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> in a sentence.</fo:inline>

If we change the border on tis, it will apply to the complete sentence. not just the field.
So how could I isolate that field. Well we could actually do anything to the field. embolden, italicize, etc ... I settled on changing the background color (its easy to change it back with a single attribute call.) Using the highlighter tool on the Home tab in Word I change the field to have a yellow background. I now have:

 This gives me the following code.

<fo:block linefeed-treatment="preserve" text-align="start" widows="2" end-indent="5.4pt" orphans="2"
 start-indent="5.4pt" height="0.0pt" padding-top="0.0pt" padding-bottom="10.0pt" xdofo:xliff-note="YEARPARAM" xdofo:line-spacing="multiple:13.8pt"> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve">This is an embedded field </fo:inline>
  <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
   font-family-generic="swiss" font-family="Calibri" background-color="#ffff00">
    <xsl:attribute name="background-color">white</xsl:attribute> <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve"> in a sentence.</fo:inline> 

Now we have the field isolated we can easily set other attributes that will only be applied to the field and nothing else. I added the following to my YEARPARAM field:

<?attribute@inline:background-color;'white'?> >>> turn the background back to white
<?attribute@inline:border-color;'black'?> >>> turn on all borders and make black
<?attribute@inline:border-width;'0.5pt'?> >>> make the border 0.5 point wide
<?YEARPARAM?> >>> my original field

The @inline tells the BIP XSL engine to only apply the attribute values to the immediate 'inline' code block i.e. the field. Collapse all of this code into a single line in the field.
When I run the template now, I see the following:


Its a little convoluted but if you ignore the geeky code explanation and just highlight/copy'n'paste, its pretty straightforward.

Categories: BI & Warehousing

Are you ready for Windows 10? Dodeca Is Ready!

Tim Tow - Wed, 2014-10-15 23:38
Microsoft recently released its first preview version of their next desktop operating system, Windows 10. In our business, we have seen many large companies just migrate to Windows 7 and the general consensus seems to be that Windows 8 is a 'consumer' operating system. In other words, it will be a while before you have to think about Windows 10, but it is our business to be thinking about this now. We have the Windows 10 preview downloaded and installed in our labs. We made a very smart decision years ago when we decided to write the Dodeca client layer in .NET technology because it works in Windows 10 unchanged!

We are ready. Will you be ready?
Categories: BI & Warehousing

Kscope15 Abstract deadline is Wednesday, October 15!

Tim Tow - Sat, 2014-10-11 14:08
Kscope15 abstract submission will close in just a few days and the ODTUG content teams want you!

I am sure there are many of you who have never done a presentation at a conference and may not feel comfortable speaking in public. Well, I have a secret for you, despite the fact that I won the Best Speaker Award at Kscope14, I share your discomfort with speaking. What works for me, however, is that I only speak about topics that I know very well. Do you have some area of expertise that you know really well? If you do, please share it! It will be fun and all of your peers who learn something new, from you, will appreciate it!

With that, take the next step and click here to submit session!
Categories: BI & Warehousing

Multi Sheet Excel Output

Tim Dexter - Thu, 2014-10-02 18:28

Im on a roll with posts. This blog can be rebuilt ...

I received a question today from Camilo in Colombia asking how to achieve the following.

‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiple sheets in with the BIP Advanced Options, but what if I want to have 1 report / sheet? Where each report in each sheet has a independent data model ….’

Well, its not going to be easy if you have to have completely separate data models for each sheet. That would require generating multiple Excel outputs and then merging them, somehow.

However, if you can live with a single data model with multiple data sets i.e. queries that connect to separate data sources. Something like this:

Then we can help. Each query is returning its own data set but they will all be presented together in a single data set that BIP can then render. Our data structure in the XML output would be:


Three distinct data sets within the same data output.

To get each to sit on a separate sheet within the Excel output is pretty simple. It depends on how much native Excel functionality you want.

Using an RTF template you just create the layouts for each data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP will place each output onto a separate sheet in the workbook. If you want to name each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as sophisticated as it gets with the RTF templates. No calcs, no formulas, etc. Just put the output on a sheet, bam!

Using an Excel template you can get more sophisticated with the layout.

This time thou, you create the layout for each data model on separate sheets. In my example, sheet 1 holds the department data, sheet 2, the employee data and so on. Some conditional formatting has snuck in there.

I have zipped up the sample files here.


Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:Calibri; mso-bidi-theme-font:minor-latin;}
Categories: BI & Warehousing

Oracle Exalytics X4-4 - Bigger, Better, Stronger

Look Smarter Than You Are - Sun, 2014-09-28 11:49
X4-4 - Same price as the X3-4 but with more powerThe big announcement about it is today at OpenWorld (it would be awesome if they mentioned it during the Intel keynote tonight), but the Exalytics X4-4 is actually available now.  It's the same price as the X3-4 ($175,000 at list not including software, maintenance, tax, title, license, yada yada).  This does mean the X3 is - effective immediately - no longer available, but then again, since the new one is the same price, I'm not sure why anyone would want the older one.  No word yet on if you can upgrade an X3 to an X4, but since they did offer an upgrade kit from X2 to X3 (though I never heard of anyone buying it), I'm guessing there will be one for those wanting to make an X3 into an X4.
X4-4 SpecsThe main improvement over the X3 is the number of cores: it's still 4 Intel chips, but those chips all now have 15 cores on them, meaning the X4 has 60 cores compared to the X3's 40 cores.  Here are the important details:

  • 4 Intel Xeon E7-8895v2 processors running at 2.8 - 3.6 GHz
  • 8 - 60 cores (capacity on demand, more on this in a second)
  • 2 TB of RAM
  • 2.4 TB of PCI flash
  • 7.2 TB of hard disk running at 10K RPMs (not that fast these days)
  • 2 Infiniband ports running at 40 Gb/s
  • 4 Ethernet ports running at up to 10 Gb/s
Cool Thing 1: Variable Speed & Cores

You probably heard about this last July.  Oracle worked with Intel to design a line of their Xeon E7-889x chips specifically for Oracle.  What we didn't realize until we saw it show up on the X4 spec sheet was that the chips were going in the Exalytics X4.  Simply put, on the fly, Exalytics can vary how many cores it uses and when it's fewer cores, the speed goes up.  If it's running 15 cores per chip, Intel sets the speed to 2.8 GHz.  If it's only using 2 cores per chip the speed goes all the way to 3.6 GHz (a GHz is one billion clock ticks per second).

But wait, you math geniuses say.  Isn't 3.6 * 2 less than 2.8 * 15 (so why wouldn't Oracle just always leave all 60 cores on at the slower speed)?  Well, yes, if you're actually using all those cores, and this is where you know the chip was apparently designed for Essbase (though it did premiere in Exadata first).  As much as I love my Essbase, there are still transactions that end up single threading (or using far less than the available cores on the box).

Say I'm running a massive allocation and despite my best efforts (and FIXPARALLEL), it's still single threading or running at 8 CPUs or fewer.  In this case, Exalytics is now smart enough to talk to those impressive new E7-8895v2 chips, scale down to as few cores as are actually needed, and in the process, up the clock speed for the remaining cores.  Take that, commodity hardware.  This really is the killer feature that makes Exalytics do something no other server running Essbase can do.

On a side note, Intel seems to be dropping the power on the non-used cores to nearly zero when not in use meaning the power consumption of your Exalytics box actually lowers on-demand.  So if your boss won't sign off on your new Exalytics X4, tell her she hates the planet.

Cool Thing 2: You Don't Need BIFS
Per the current Engineered Systems Price List (buried down in note 13), you longer have to purchase BIFS (BI Foundation Suite) to buy Exalytics (either the X4 or T5).  You can now own BIFS, OBIEE, Essbase+, or Hyperion Planning+ without having to get a VP to sign off for a special exemption.  That's right, Planning people preferring to purchase pure premium power, you can now buy Exalytics.  With this change, I presume that any new Planning customer looking for the best user experience will be buying Exalytics X4 along with Planning.

Also buried in the footnotes, you apparently can now buy Exalytics for as few as 20 named users.  Last time I checked (and I don't read every edition of the footnotes, haters who think I have no life), the minimum was 100 named users.

What's Next: HFM on Exalytics
We heard about it on the opening developer's day at Kscope: HFM should finally run on Exalytics in version (which we're hoping to see by the end of 2014).  I'm not sure if it will run on both the T5 (Solaris) and the X4 (Linux) by year-end, but Linux is almost a given.  That said, I don't work for Oracle, so don't base any buying decisions on the belief that HFM will definitely run on the X4.  Just when it happens, be pleasantly surprised that you can now consolidate all your major Oracle Business Analytics apps together.

So any T5 news?  Not at the moment. It's still available running it's 128 cores with 4 TB of RAM (and other cool things) so if you're looking for major horsepower and server consolidation, look to the T5.

I'll be updating this post after the OpenWorld keynote to include any new Exalytics news but if you hear any other Exalytics updates in the meantime, post it in the comments.

Categories: BI & Warehousing

Database Links

Tim Dexter - Thu, 2014-09-25 14:39

Yeah, its been a while, moving on ...

I got a question a week back asking about how BI Publisher could handle dblinks. The customer currently has db links from DB1 to DB2 and uses them in their queries. Could BIP handle the syntax and pass it on to the database in its SQL or could it handle the link another way?

select e1.emp_name
, e1.emp_id
from emps e1
, emps@db2 e2
where e1.manager_id =

Well, there is the obvious way to create the join in BIP. Just get rid of the db link alttogether and create two separate database connections (db1 and db2). Write query A against db1 and query B against db2. Then just create a join between the two queries, simple.

 But, what if you wanted to use the dblink? Well, BIP would choke on the @db2 you would have in the sql. Some silly security rules that, no, you can not turn off if you want to. But there are ways around it, the choking, not the security. Create an alias at the database level for the emp@db2, that way BIP can parse the resulting query. Lets assume I create an alias in the db for my db linked table as 'managers'. Now my query becomes:

select e1.emp_name
, e1.emp_id
from emps e1
, managers e2
where e1.manager_id =

 BIP will not choke, it will just pass the query through and the db can handle the linking for it.

Thats it, thats all I got on db links. See you in 6 months :)

Categories: BI & Warehousing

Next Generation Outline Extractor Webcast - Oct 8th!

Tim Tow - Fri, 2014-09-12 17:07
I am doing a repeat of my Kscope15 'Best Speaker' award-winning presentation as part of the ODTUG webcast series. Here is the official announcement from ODTUG:

Wednesday, October 8, 2014 12:00 PM - 1:00 PM EDT

Next Generation Essbase Outline Extractor Tips and Tricks
Tim Tow, Applied OLAP

The Next Generation Outline Extractor is the follow-up to the classic OlapUnderground Essbase Outline Extractor used by thousands of Essbase customers. This session, which was the highest-rated session at Kscope15 in Seattle, explores some of the new capabilities of the Next Generation Outline Extractor, including command line operations exported directly to a relational database. Attend this session to learn how to leverage this free utility in your company.
Make you you sign up to join my on October 8th; you can register here!.
Categories: BI & Warehousing

Oracle Tours Africa and the Middle East

Look Smarter Than You Are - Sun, 2014-06-15 11:33
Happy Father's Day, everyone!  I got up early this morning to write about my recent experience traveling the world on Oracle's behalf.  I got to attend the first annual Oracle Technology Network tour of Africa and the Middle East.  It made 2 stops in North Africa (both in Tunisia), 2 stops in Saudi Arabia, and the final stop was in Dubai, UAE.

Tariq Farooq first mentioned the idea of doing a MENA (Middle East & North Africa) tour to me in Beijing last fall.  He asked if I'd be willing to travel half-way around the world to speak to people in English that primarily spoke French and Arabic, and I - of course - said "yes."  Here's Tariq being interviewed by Lillian Buziak at Collaborate 2014 (audio is a bit difficult to hear):

I had two reasons for wanting to go: I do love educating/evangelizing for Oracle EPM, BI, and Business Analytics.  The possibility of reaching new audiences for the first time was exciting. My other reason for going was that I wanted to experience totally different cultures than I ever have before.  I've spoken on 5 continents (now 6 after this tour and I'm anxiously awaiting the OTN Tour to Antarctica) before and have seen presented everywhere from a women's college in Mumbai that was 95F with no air conditioning in the presentation room to a ballroom in the Philippines that had 3 simultaneous English sessions going on (in one room!) all happily observed by smiling Filipinos.  From China to India to Australia to Germany, I have seen some amazing slices of life, but nothing prepared me for the differences I saw on this tour.

In each of the sections below, I have linked the header to a blog from my new best German friend, Bjoern Rost.  He blogged after every stop and unlike me, he actually understood all the Oracle RDBMS sessions on the tour.  Visit to see his entertaining blog posts.  (Warning: though I think Bjoern is hilarious, being German, you may find his posts to be 'not funny.'  German humor is an acquired taste.)

I left for the first stop, Tunisia, on Memorial Day (in the USA), May 26, 2014...

Tunisia, May 27To get to Tunisia in time for my session, I left Dallas. Texas at 10AM on Monday, flew to JFK (New York City), flew to Rome, flew to Tunis, and had a nice car waiting for me at the airport compliments of our hosts in Tunisia.  I landed at 10:30AM on Tuesday and considering I was flying to Africa, I felt that the trip went by quickly.  I made it through customs in Tunis in about 15 minutes, walked out the front door of the hotel, and was in an entirely different world.

Speaking in North America, South America, Europe, Asia, and Australia had done absolutely nothing to prepare me for Africa.  The closest thing I could compare it to in my life (but the comparison does not do it justice) was the cities of India: chaotic, dirty, cramped, foreign, and chaotic (worth mentioning again).  Now take all that, remove the cows, and make it Muslim.

My host picked me up in a nice car and we began the hour drive to Beja where the conference was being held.  We passed mounds of trash piled up in the center of the roads though thankfully the heat (high was ~75F when I arrived) didn't make the place smell horribly.  Traffic laws seemed to be non-existent, but it moved fairly quickly being in the middle of the day.  I loved looking out the window at the shops along the road and carts selling watermelon approximately every 100 feet (I was told by my host that it was watermelon season).

We left the city about 20 minutes after I got in the car and I was suddenly in Tuscany.  At least, it looked like Tuscany: fields of amber waves of grass, wide open spaces, wildflowers, lakes, olive groves, country life, gorgeous hills... truly one of the most beautiful countrysides I've seen in my entire life.  Since I had been traveling for over a day, the pleasant scenery soon lulled me to sleep.

My driver woke me up when we got to the technical university in Beja. I walked in to find Tariq trying to explain Oracle Enterprise Manager to a bunch of college students who didn't seem to understand databases let alone Oracle.  I tried to hide in one of the back seats, but Tariq immediately called me up on stage to answer a question about how to develop optimal databases.

Not long after I got there, we broke for lunch.  Our hosts took us to a traditional Tunisian restaurant in downtown Beja.  They were kind enough to make me vegetarian food.  Lunch is apparently a sacred event not-to-be-hurried in Tunisia, so we made it back to the university over 2 hours after we left, fully satiated.

I volunteered to give a session introducing Big Data and Analytics to the college kids, because I felt that it required little technical background.  It seemed to go over well.  My favorite part was when I told a joke about the differences in social media sites and only 10 people laughed.  The people on either side of those 10 then asked them to repeat in Arabic and French what I had said, which caused those people to laugh.  They then shared it around the room and it was like a disease vector of laughter that took 2 minutes to make it around to the 150+ students in the room.  In case you haven't seen it, here's essentially what I said out-loud:
After the sessions were over, I asked one of the professors why everyone listened so intently if they had no background in Oracle.  I mentally wondered if it was because I was an awesome presenter bringing the gospel of Oracle to the future of Africa.  I was told "they didn't understand a lot of what you were saying, but they love listening to people speak English."  So much for my future African disciples.
Our hosts offered to drive us to Dougga, the so-called "best preserved Roman ruins outside of Italy."  It sounded like an exaggeration, but it was actually an understatement. Dougga was once a "small" Roman town on the fringes of the empire... and the miles of town are for the most part still there.  We arrived shortly after they closed the gates for the day.  Our hosts got out of the front car in our 4-car caravan to talk to the guards.  I was in the last car and saw an interesting polite dialog when our hosts started pointing to my car.  I waved back.  The guard smiled and raised the gates for our caravan to enter.  I wondered if bribing had occurred, so I asked how we got in after hours.  Our gracious hosts explained that I was renowned historian, Edward Roske, a visiting professor from the United States of America whose sole purpose for being in Tunisia was to see the Dougga ruins.  They said I should take lots of pictures and walk around looking officially important.  I discovered later that they weren't kidding: they really did tell this to the guard, so I took at least 50 pictures and took some very official selfies to help sell my renowned historian status.

The ruins were truly majestic.  Every time I came around a bend, there was another temple, theatre, circus, road, market, tunnel, column, arch, statue, or something else 2,000 years old to be seen.  It is all in a semi-wild state with no borders separating the ruins from the countryside.  There were even wildflowers growing in the central square:
My favorite moment of the entire trip occurred when I broke away from the rest of our group to go explore some arches on the edge of the ruins.  I went to take a picture of one of the doorways, and I got photobombed:
I went through the doorway to discover a local sheepherder grazing his sheep right in the ruins:

They started on the edge of the ruins but eventually the herder marched his sheep right down the center of the 2,000 year old road leading through Dougga.  Tariq decided to join their herd:
I can't stress enough how amazing this site is.  I would encourage people to visit Tunisia if for no other reason than to see Dougga and Carthage.  You have never felt Roman society like you can wandering around the ancient town with only sheep to keep you company.

Eventually, the guards at the entrance (the only guards in the place, so far as we could tell) came to find our renowned historian group because they wanted to go home.  We stayed the night in the Golden Tulip Hotel in Carthage which was a 4-star hotel for under $200 USD per night.  I recommend it to anyone.  The next morning, the OTN MENA 5 (Tariq, Mike Ault, Bjoern, me, and Jim Czuprynski) headed for the flight to Cairo then on the Riyadh.

Saudi Arabia, May 29-31While the Islam is a part of the culture in Tunisia, Islam is the culture in Saudi Arabia.  I have never seen a country more dominated by a single religion than Saudi Arabia.  It is one of the most difficult places in the world to get a visa (they suspended tourist visas 5 years ago) and it's even harder for a non-Muslim like me.  I spent 4 hours clearing customs which gave me a lot of time to study up on what I was in for.

My guidebook (and several websites) told me about all the things I wasn't allowed to do, say, or maybe even think when I got to Saudi.  Here's what I was told versus what actually happened:
- Muslims everywhere.  Yes, 100% true.  They have calls to prayers everywhere and we had to stop presenting when it was time for prayer.  The whole city stops, for that matter, when it's prayer time.  I was lucky enough to be in a public park for evening prayer one day.  The sounds of the call to prayers across the city were beautiful.

- Traffic fatalities.  90% true.  Saudi apparently has the highest incidence of traffic fatalities in the world because traffic laws are more like traffic vague suggestions only to be followed if everyone has plenty of time and sort of feels like it.  I was prepared to almost die every time I got in a car, and while I saw no deaths, I saw multiple car accidents of the fender bender type each day I was in Saudi.  At one point, we were stopped at a red-light to make a left turn.  With traffic coming from both directions in front of us, a car behind us who wanted to make a left-hand turn felt that our stopping was delaying his day.  He didn't honk or behave rudely in any way: he just drove around us and made the left-turn on the red into oncoming traffic.  No one seemed annoyed at the man for doing it.
- Pornography.  100% nonexistent in Saudi.  They even go through the magazines in the shops and black out with a sharpie anything that's considered too revealing (shoulders, waists, knees, etc.).  They also sharply monitor the web and block out any site deemed inappropriate (including with safe search set to anything but strict).
- Pictures of other people.  0% true.  I was told before I went that Muslims do not believe in having images taken of people.  What I actually found was the most selfie-absorbed culture I've ever seen, and I live in America with a teenage child.  I couldn't walk 10 feet at the Riyadh or Jeddah events without someone taking a picture.  I was also told that you couldn't take pictures of public buildings or in public buildings (like the national museum).  Totally untrue: people were taking pictures of just about anything except women.
- Women being covered.  100% true.  All the women wore black abbeyahs at all times.  You're not allowed to film them or talk to them.  That said, I didn't see that many.  Both the events in Riyadh and Jeddah were male-only.  We did see women in the public places particularly near retail outlets and in the city parks & museums.
- Men wearing suits.  25% true.  For the most part, the men wore traditional dress shirts and head coverings.  I wore a suit (no tie) which considering it was 110F+, was quite a sacrifice.  My fellow presenters wore ties in addition to their suits which proves they're more willing to sacrifice for the cause of Oracle.
- No alcohol. 100% true but weird.  The first night I got to Riyadh, I opened my minibar to find... a Budweiser.  Closer examination revealed it was a "Budweiser NA" signifying no alcohol.  Every restaurant we went to offered us "Saudi champagne" or "Saudi wine" which apparently means alcohol-taste without any actual alcohol.  Since I hate the taste of alcohol, I didn't think non-alcoholic alcohol would taste any better, so I avoided it.
- No narcotics.  100% true so far as I was willing to test it.  I actually panicked during customs at the thought that maybe I had some prescription drug in my laptop bag that had a narcotic in it.  Narcotics are a capital crime in Saudi, and I spent most of my 4 hours wondering if Ambien counts as a narcotic.  Luckily for me, I wasn't executed for sleep aid smuggling.

After the worst customs experience of my entire life at the airport in Riyadh, I got to the Marriott hotel for about 4 hours sleep before the day was to begin.  I got to go up to the concierge floor for an elaborate breakfast and a great view of the Riyadh skyline.

The Riyadh venue was spectacular.  The hosts (eSolutions) put on one of the best events I've ever attended on an OTN tour.  From the venue to the signage to the elaborate Lebanese food lunch to the photographer to the videographer to the speaker gifts, it was all top-notch.  Like in Beja, I presented on "Taming Big Data with Analytics" to an enthusiastic audience.  There were about 75 (all male) people there including several male children of the attendees.  They were some of the best behaved children I have ever seen even though some were elementary school age.
I got introduced at one point as "Edward Roske from the United States, a rich man who did not inherit his business from his father."  That was unique and though it left me speechless, the audience seemed very impressed at my ability to become head of a business without my father having to die first.

After the conference, our hosts took us to the National Museum.  Like much of Riyadh, it looks like it was built in the last 5 years, and in the case of a museum, this worked very well.  Since they built it all at once (versus many amazing museums around the world that were created over hundreds of years), it was able to tell a complete story from the beginning of the universe up through today (as opposed to just having rooms of collections).  It was a very Muslim-centric view of history, but I found the educational aspects fascinating.  The sign on the way in said no photography, but since the massive museum apparently only had 4 people working in it (all at the front desk), everyone ignored it.  I found a cube in the first hall that seemed Essbase-like.

My favorite exhibit was a scale model of Mecca and Medina as viewed at night.  Being non-Muslim, I will never see Mecca, so this is as close as I will ever get:
After the museum, our hosts took us to Kingdom Centre, the tallest building in Saudi Arabia (for now).  It has 30+ open stories at the top with a skybridge connecting them.  Here's a view from the ground of the building (notice the necklace like architecture with the bridge on top) and a view from the building of the ground:

Our hosts took us to dinner (which was unfortunately a meat-on-a-stick restaurant, unfortunately for me since I'm a vegetarian).  Since I wasn't able to eat much, I got to talk at length to the CEO of eSolutions.  He was a fascinating man who told me all about how Saudi Arabia and the Gulf Coast Community is ready for analytics and reporting.  He was a former Pakistani military man, but his love of country and family shined through in everything he said.

The following morning, we journeyed to Jeddah over on the Red Sea coast of Saudi near Mecca.  The weather was like Riyadh except with crazy humidity.  It reminded me of Houston on the hottest day of summer.  We were in Jeddah for less than 24 hours so I don't admittedly have a ton to say about it.  I saw nothing more than the airport, the drive to/from the hotel, the venue where we presented, and a fast casual restaurant (more tasty Lebanese food!) where we had dinner.

The venue was small since it was a half-day event and lightly attended.  I did ask if I could present on a different topic since I had gotten a bit bored talking Big Data all the time.  This time, I spoke on "In-Memory Databases" which is a hot topic these days thanks to the SAP guys saying "Hana" at least once every sentence.  After the morning event, the OTN MENA 5-1 (Bjoern went straight to Dubai since there weren't enough speaker slots in Jeddah) headed for the airport for the flight to the United Arab Emirates.  Getting out of Saudi took 2 minutes at Customs which goes to show, I guess, that they're a lot happier to get rid of you than let you in.

Dubai UAE, June 1I honestly don't know where to begin with Dubai.  It is truly one of the most amazing cities on Earth and almost indescribable to anyone who hasn't actually witnessed it.  My flight landed on Saturday evening in the largest airport I think I've ever seen.  Clearing customs took minutes then I headed for the cleanest (and probably newest since it just opened in 2009) train system in the world.  The view from the Dubai Metro was stunning.  If Riyadh looks like it was built in the last 10 years, Dubai looks like it was built in the last 10 minutes.  It reminds me of New York City if they took out all the advertising, 90% of the people, and any building under 1,000 feet tall.

I was lucky enough to stay at a hotel in the Burj Khalifa, the tallest building in the world (by far: the Burj is over 200 stories tall at over 2,500 feet).  The Burj sets all kinds of "tallest" records including the tallest outdoor observation deck.  Here's a view of several buildings below that are all over 1,000 feet tall (with an Edward in it for perspective).

My room had a view of the Dubai Fountains.  No offense, Vegas, but these fountains put the Bellagio fountains to shame. They're more agile, faster, better lit, larger, and frankly, classier. And if you're staying in the hotel in the Burj Khalifa, you can listen to the fountain music through the TV:

At the base of the Burj Khalifa is the Dubai Mall, the largest mall in the world (you hear a lot in Dubai of "that's the largest _____ in the world").  It's 3-4 times the size of Mall of America if that puts it in perspective.  It has over 1,200 shops, over 150 restaurants, and some of the strangest (yet up-scale) stores you've ever seen.  There was one selling full-size metal camels, for instance (these were the only camels I saw on my trip).  I ate at the Dubai Mall every day I was in Dubai and felt I could eat there every day for a year without repeating an entree.  I am normally not a fan of malls, but I loved the Dubai Mall.  It wasn't crowded and the people that were there kept to themselves.  This could actually be said about everywhere in the Middle East: the people are nice, but they keep to themselves.  For an introvert like me, it's heaven.  You can be alone in a crowd.

The first full day I was there, I presented in the morning, burned my feet on the sands of a beautiful beach at lunch, and was skiing in the afternoon.  Yes, skiing.  The Mall of the Emirates (another mall that dwarfs anything we have in the USA) is not only massive, it has its own indoor ski resort inside.  It's not a tiny ski hill either: it's 1,200 feet long with three separate runs (a blue/intermediate run, a green/beginner run, and a small terrain park).  They also have lots of other fun activities to do including a penguin exhibit, ziplining over the ski hill, sledding, and large transparent hamster balls to roll down the hill in.

For around $50 USD, you get a lift ticket, ski pants, ski jacket, boots, skis, and poles for 2 hours.  A full-day pass is only around $15 USD more but I opted for 2 hours.  (You can only ski indoors in Dubai so much, obviously.)  There were at most 15 people skiing, but there were hundreds of people from the Middle East paying their $50 to ride up and down the ski lift basking in the glory of being cold.  The whole place is chilled to around 23F and the snow is glorious: it's soft and velvety because they actually make it snow indoors every night (unlike the ice blowers we use for man-made "snow" in the USA).  I skied for my full 2 hours without a break and I loved that I was so cold by the end that I had to go back into the mall and get a soy hot chocolate at Starbucks.

I had too many amazing experience to recount (and this is already seeming a bit like an advertisement for Dubai) but the most fundamentally changing experience of my trip was a visit to a mosque in Dubai.  Hosted by an eloquent British Muslim woman in a black abbeyah, she spent an hour educating a group of Westerners all about Islam.  I was taken aback by how... peaceful their religion is.  She covered the 5 pillars of Islam in a way that made me understand 1,000,000,000+ Muslims far better than I ever have.  I think that if everyone in the Western world could attend that one hour I did, we would have a level of cultural understanding that would ease a ton of our current fears.  They let us take lots of pictures and even let us video them doing 5 minutes of prayers.  It was moving and if you are ever in Dubai, make sure you visit the Jumeirah Mosque during one of their visitation hours (it's free unlike most everything else in Dubai).

My final presentation was in Dubai and it was a bittersweet end to a whirlwind week.  Every single day was spent traveling, speaking, or both, so it was nice to finally have a break.  That said, I will consider the other members of the OTN MENA 5 to be friends for life and I miss them already.  As I finished my presentation on Big Data (for the third time on the trip), I looked out at the anxious faces in the audience and realized that I would miss the Muslim world far more than I ever expected to.  

If there's ever a 2nd OTN tour of the Middle East, Africa, or both, sign me up.  Until then, thank you for letting me be a part of the most inspirational, educational tour I've ever experienced.

Categories: BI & Warehousing

Fun with SQL - Silver Pockets Full

Chet Justice - Wed, 2014-06-04 17:13
Silver Pockets Full, send this message to your friends and in four days the money will surprise you. If you don't, well, a pox on your house. Or something like that. I didn't know what it was, I just saw this in my FB feed:

Back in November, I checked to see the frequency of having incremental numbers in the date, like 11/12/13 (my birthday) and 12/13/14 (kate's birthday). I don't want to hear how the rest of the world does their dates either, I know (I now write my dates like YYYY/MM/DD on everything, just so you know, that way I can sort it...or something).

Anyway, SQL to test out the claim of once every 823 years. Yay SQL.

OK, I'm not going to go into the steps necessary because I'm lazy (and I'm just lucky to be writing here), so here it is:
select *
to_char( d, 'yyyymm' ) year_month,
count( case
when to_char( d, 'fmDay' ) = 'Saturday' then 1
else null
end ) sats,
count( case
when to_char( d, 'fmDay' ) = 'Sunday' then 1
else null
end ) suns,
count( case
when to_char( d, 'fmDay' ) = 'Friday' then 1
else null
end ) fris
select to_date( 20131231, 'yyyymmdd' ) + rownum d
from dual
connect by level <= 50000
group by
to_char( d, 'yyyymm' )
where fris = 5
and sats = 5
and suns = 5
So over the next 50,000 days, this happens 138 times. I'm fairly certain that doesn't rise to the once every 823 years claim. But it's cool, maybe.
YEAR_MONTH       SATS       SUNS       FRIS
---------- ---------- ---------- ----------
201408 5 5 5
201505 5 5 5
201601 5 5 5
201607 5 5 5
201712 5 5 5
128 more occurrences...
214607 5 5 5
214712 5 5 5
214803 5 5 5
214908 5 5 5
215005 5 5 5

138 rows selected
I'm not the only dork that does this either, here's one in perl. I'm sure there are others, but again, I'm lazy.
Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator - BI &amp; Warehousing