Indeed it shall be a happy new year!

And, what better way to kick off the new year then to recap the previous year by taking a look at ArtOfBI.com’s traffic, our most read posts, much needed off time, and what we have to look forward to this year in 2010 (w/o HAL).

ArtOfBI.com 2009 Traffic

As you can see since ArtOfBI.com started last June or so we’ve had some steady traffic.  November was an awesome month for the site.  As expected, with the holidays, traffic tapered off.

Most Read Posts

I have to say that I am still baffled (this happens often) that the Informatica Video Tutorials on ArtOfBI.com are by far the most popular posts.  Behind that are the OBIEE Google Mapping and Integrating a Web Service into OBIEE.  If you want to shout out your favorite, post a comment and share.

Much Needed Time Off

I took a few weeks off over the holiday to relax with the family, get caught-up on some side-projects, play with some new gadgets. I have also been on a killer project doing some SAP B/W to OBIEE integration and some other fun stuff with a client so that has also been consuming my days.  Somewhere in there I’ve been doing some research on my upcoming blog topics - see below.

Things to Looking Forward to in 2010

Well damn it if our number one priority isn’t going to be getting ArtOfBI.com’s traffic numbers to something more respectable. The only way to do that is by providing some more “kick ass” blog posts.  I’ve got some great topics to push out and will start putting fingers to keyboard this week.  Get ready for some great Essbase Optimization, ASO Trickle Feed, Workspace & OBIEE integration, Essbase Data Mining, Informatica and SalesForce.com integration, plus more.

And of course, aren’t we all looking forward the release of OBIEE 11 (Oracle BI 11).  I look forward to better solution for RPD merging, OLAP integration, and integration with the Oracle/Hyperion suite.

A Big Thanks to You

Thank you everyone that has been finding ArtOfBI.com through their Google searches looking for answers to their BI conundrums.  Thank you also and especially for you comments. Please keep the comments coming as they really do drive me to write better content and keep the information flowing since I know it is being viewed.   I’ll continue to keep the lights on add a lot more videos.  The goal this year is to speak at Oracle Open World 2010.  Vote for me.

Cheers, Christian.

Whether you are a user or implementer of Smart View there comes a time when an upgrade is necessary. Since each user installs the smart view binaries on their workstation/laptop, the question of how to push a system-wide upgrade may arise. Fortunately this functionality is built into Essbase Administration Services.
Continue Reading

Sometimes I feel like I am the only evangelist out there for Essbase Studio and I wonder why that is since Essbase Studio is such a great tool.  This is especially true when you look at its ability to connect to a myriad of data source such as OBIEE, Teradata, MySQL, EPMA Dimension Server, netezza, etc.  Below is the top 10 list of why an organization should migrate their existing Essbase Integration Services models to Essbase Studio.  If your team is not an early adopter, run it in parallel.  If your team is scared of change, have them read “Who Moved My Cheese?“.  Otherwise, let’s make it happen.  Get with the program and enjoy the fruits of this fantastic toolset made for those who believe analytics are always evolving for the better.

Top 10 List

10. It has a familiar feel to EIS but its actually intuitive and user friendly.

9. It integrates with the new features of Essbase Server 11x.  Has EIS been touched since System 9x?

8. Introspection

7. There is a step-by-step tutorial on Essbase Studio Server made just for you (here).

6. Security provisioning and roles can be assigned in Shared Services.  Deliniate data modelers from administrators.

5. Essbase Integration Services is about to get sunset and Oracle Support will go bye-bye.

4. It connects to EPMA Dimension Server out-of-the-box. Ever heard of Master Data Management?

3. Essbase Studio Models are deployable with native MaXL Scripting.  Have you tried to automate a EIS deployment?

2. Essbase Studio comes with an Integration Services Catalog Migration Wizard. (hint, hint)

1. Larry Ellison wants you to.  Seriously, Oracle’s BI roadmap is taking Studio to the next frontier, not EIS.  Its a good train…get on it.

ASO does not provide an out-of-the-box version of dynamic-time-series (DTS) like its partner in crime, BSO. However, one straightforward approach to achieving this functionality is to implement a time aggregation dimension and leverage MDX functionality.  In this example we will house the Time Periods/Months (i.e.:  Mar, Apr, May, etc.) and the Years dimensions into two separate dimensions.  We then add an additional dimension called “Time Series” to the outline to support our ASO version of DTS.

Continue Reading

As you know, the Hyperion 11.1.1.3 release of Essbase Studio Server was not release as a windows service. After my last Essbase Studio implementation project, and being repeatedly asked, “How do we run Essbase Studio as a Windows Service?”, I decided to dig deep into the details and deliver a concrete answer to those that care to read on.  Of course, I was successful in my endeavor, hence this post. Now, I ask, Do you want to see Essbase Studio Server launchable in your services panel like this…?

I keep getting asked “Was the Essbase Studio installation ever provided as a windows service?”. My reply to that now is “Who cares?”. If the Oracle think-tank decided not to make life easier for us with a Windows Service for Essbase Studio upon install then so be it. At least now, I’ve got a great rebuttal and a top-notch solution. Now, no longer must this one piece of the Hyperion BI suite be the outcast with only the startServer.bat file to launch the server.
Continue Reading

By now every knows that the release of Hyperion 11x has provided us with the latest and greatest Essbase modeling application, Essbase Studio.  A lot of us are familiar with Essbase Integration Services and have been looking at Essbase Studio like a two-headed dog.  They either are scared and don’t want to go near it or they are so used to their purse-dog, i.e: EIS, that they stick with what they know best and haven’t unwrapped Essbase Studio yet.

This blog post should put and end to any reluctancy.  I introduce to you the first Essbase Studio tutorial on the web. 

You can download the PDF here. (I’ll eventually move this to a CDN if the downloads get to big, so please link to this post and not the document itself.)

Hyperion Essbase Studio Tutorial by Example (PDF)

I am also breaking the tutorial into a series with this being part 1.  There are a lot of cool new functions with Essbase Studio and you will immediately see the benefits it has over EIS. Soon, you’ll see other blog post here on Essbase Studio tutorials part 2, 3, etc. so be sure to check back often or subscribe to the RSS.

If this series is good, bad, or indifferent please let me know.  I would like to keep these up and with your feedback I can revise and make the documents that much more informative everyone.

Cheers.

Update 8/30/2009

- This should have been posted to the live site last week. I am in the middle of switching hosting providers for my blog and uploaded this post to my test site instead of this one. Sorry for the delay.

OBIEE has a smart query logging system and it happens to be mainly based on security logging levels.  This is great from an overhead perspective because you wouldn’t want every single user of the system to have a full introspective query log produced each and every time your hundreds of users run a query requests or dashboard view hit. 

Typically one will use logging during development, analysis, or troubleshooting efforts.  Otherwise there is no need to have logging on any higher than the default setting.  By default each user account’s “Logging Level” is set to 0 (zero) which is basically no logging.  For basic purposes of analyzing SQL or MDX syntax that make up the query, row counts, and recordset deliver time you will want to change the “Logging Level” to a 1 or 2.  These two levels start providing basic logging which can be seen from Settings > My Account > Manage Session in Answers.

Here’s how to change the “Logging Level” for a specific account:
Continue Reading

By default hiearchy drill-down links are removed when an OBIEE Delivers IBot notification is sent out via email.  The report becomes for the most part static in the email whereas if you viewed the report in the interactive Dashboard or in Answers you would by default have the link action associated with it.

The main reason that the drill-down hieararchy link is disabled in an email notification is because OBIEE uses relative paths to know which report to link to or which path to drill-down on.  With the addition of JavaScript, especially for the drill-down capability, it just cause errors when a user click on this time of linkage from an email.  Relative paths are only viable when they are on the same web root as the page launching the link.  Clearly an link in an email falls outside of that scope.  It would be like someone sending you a link to a product the found on Amazon but the only link they sent you in an email is “/product/hasbro-gi-joe-tunnel-rat”  instead of http://www.amazon.com/product/hasbro-gi-joe-tunnel-rat.  For more information on Relative Paths, click here.

OBIEE Delivers Email Inbox
Here is the default behavior of a simple answers report. The left-most column is modeled with a hiearchy and drill-down is enabled when viewed in Answers/Dashboard.

  Continue Reading

We’ve been working with Oracle for sometime now with the latest release Oracle EPM release and there has been a lot of demand for some demonstration and quick training on Hyperion Essbase Studio.  Since this is new to the 11x release there is not a lot for the general developer or user.

Check out the link to a quick 45 minute training video that was put together.  It should help you to get ramped up on the topic.

http://ilearning.oracle.com/ilearn/en/learner/jsp/rco_details_find.jsp?srchfor=null&rcoid=535889535

I plan to provide several tutorials on Essbase Studio over the next few months furthering this demo so check back or subscribe to the RSS feed.

Cheers,

Christian

Every software tool has its own language and nomenclature that comes along with it.  Most times that language it is plain, makes sense, and ultimately is accepted by the masses. However, sometimes it can be rather esoteric.  Again, this is great for us consultants because we can often appear even smarter than we already are by throwing out some big buzz words that grab our audiences’ attention.

To make a long story short, I was recently asked about integrating Hyperion Essbase with OBIEE for drill-through functionality.  In that conversation I faintly heard a business user mention something about “federating OBIEE”.  With my e-commerce background I immediately thought of federated servers.  I had implemented that infrastructure in the past for high-availability so I know if the faint discussion turned into a key discussion I would have no problem speaking to it.  Unfortunately that mention remained a non-topic.  However, later I thought to myself that that mention of “federating”  could have also been interpreted as a principal technique used in data-warehousing from years past. Anyway…

Just recently I came across a great web tutorial on Oracle by Example that talks about “Federating Essbase and Relational Data Sources in OBIEE”.  At this point, we’ve been doing the integration shown in this tutorial for a good while so this is nothing new to me.  But, there was that buzz word - “Federating”.  After reading the first paragraph of the document I extrapolated that Oracle is basically referring to “federation” as the combination of data sources (seemingly disparate heterogeneous data sources only) through OBIEE modeling.   Furthermore, they have broken the term down into two components, horizontal and vertical.  Here is a brief summary of the two:

• Horizontal Federation
  • Integrating two or more disparate data source having the same level of granularity joined by one or more conformed dimensions
  • Example: Essbase Sales cube with HR relational database
• Vertical Federation
  • Integrating two or more disparate data source having different levels of granularity joined by one or more conformed dimensions
  • Example: Essbase Sales cube to relational Sales detail data (drill-through)

Here is a link to the Oracle by Example tutorial: Federating Essbase and Relational Data Sources in Oracle Business Intelligence Suite Enterprise Edition Plus

I’ll be talking to our Oracle Sales reps here in the next few days to see if this term “Federating, Federation, or Federated” is something they are using going forward for this type of integration in the Oracle Roadmap.  I actually like the use of the term.  Let’s see if it will stick or disappear into the cosmos like so many terms in IT that have come before it.

Let me know what you think.

One of the smartest tools Oracle/Hyperion ever integrated into its product suite was the migration utility. Overtime this has evolved to the LifeCycle Managment utility or LCM for short. Actually Oracle/Hyperion has taken it even one step further by calling its fully integrated migration tool the BI+ Artifact LifeCycle Management utility even though they have retained the same acronym, LCM. I saw a job board posting for a BI+ administrator recently that touted knowledge of LCM as a “must have”. So, between that and a recent client project request, I thought I would shine some light and/or seek to demystify this tool, LCM.

LCM, has actually been incorporated into the Hyperion suite since Hyperion System 9x. I believe starting with 9.3 which is when I started using it. At that point it was really just a command-line tool. It was very much a kluge and in my opinion it still is especially after you do the first migration from dev to prod (or whatever you envrionment structure look like). I’ll go into that later.

Why do we need LCM?
Technically you don’t. You can get along without it like we did in the previous versions of Hyperion by copying objects and migrating them individually. One could still use the Essbase “Copy…” command in EAS to get a database from dev to prod and vice-versa with no problem. The same goes with getting security from one environment to another although that’s usually a bit more work that simply moving Essbase objects around.
  Continue Reading

Understanding web services in general is a big benefit for anyone using integrated technologies today.  That includes any system that integrates a web client on a closed intranet network or open network with access to the world wide web.  Because OBIEE presentation services is ultimately a web-based tool using your favorite web browser one would hypothesize that utilization of web services could also be incorporated by bringing in external data or reading from network data sources. 

This post will show how to leverage presentation services to pull in a web service and integrate the web service data into our Dashboard.  This is a low level example just to get you thinking about the grand possibilities of the integration. 

The data we will consume stems from a Yahoo.com web service built inside an Answers report view.  It does not require any database integration or schema modifications. We will display it in the dashboard.  In the dashboard our final product looks like this:

Continue Reading

I often get asked by clients when we are presenting the OBIEE solution or during an implementation what “iBot” stands for?  Of course, who wouldn’t want to know after seeing what OBIEE delivers is all about and we start talking about creating iBots?

iBot(s) is an acronym for Intelligent Bursting and Output Tool.

A colleague of mine is huge on Microsoft SharePoint (MOSS) and with good reason; It is Microsoft’s #2 best sold licensed software product behind Microsoft Office. It is a collaboration tool, a CMS, a document repository, and now with PeformancePoint 2007 rolled into the suite is will be an analytical tool. And, since quite a few organizations that have implemented SharePoint use it as their launchpad portal for all things internal (intranet) it only makes since to be able to integrate SharePoint with more powerful heterogeneous analytical tools like OBIEE. But the big question I have heard over the last few months is, “What is the best approach to integrating OBIEE into SharePoint?”.  In this blog post I will point you to a White Paper on the subject and some integration code you can use to get started with your integration.

Ultimately we need to look at the limitations of communicating with OBIEE as I describe below.
Continue Reading

In previous, blog posts I’ve talked about the power of Smartview, how it came from Hyperion, and all of that jazz, but today we want to learn how to immediately integrate Smartview and OBIEE.  The burning question is, How can the end-users download smartview to their workstations? We’ll if you’d like to see the option to download Smartview in the “More Products…” menu along with the Oracle BI for Office add-in download, then continue on, you’ll be delighted you did.

Here is what we want the users to see.

Continue Reading

If you haven’t explored the full depth of OBIEE and all of its majestic glory then you may have not yet seen the 10.1.3.4 sample RPD and web catalog which can integrate with your RPD to track user access statistics, longest running queries, etc. By the way Oracle aptly named this Usage Tracking. This is a fantastic tool and below I have put together the web’s first video tutorial on how to install and configure the basics of the Usage Tracking RPD. In subsequent posts I look to explore Usage Tracking in more detail as well as to show you how to migrate the schema from the basic, dare I say lonely Usage Tracking RPD, and integrate it with a more real world scenario RPD like the one your company runs its supply chain analytics from. The video is below - enjoy.

Remember, the toll for watching this video is simply to leave a comment. If you learn anything from the video then you must leave a comment and praise it heavily. : )

How to Install OBIEE Usage Tracking < 20 min from ArtOf BI on Vimeo.

Searching for some answers to an OBIEE issue a client was having led me through an extremely detailed excursion through the OracleBI andOracleBIData file system folder structures.  I bumped into the folder /OracleBI/Corda50/ which I hadn’t paid much attention to in the past.  This is where things got interesting.  Spelunking through that directory led me to http://www.corda.com, which low and behold is the owner of the PopChart software technology that OBIEE uses to render its charting graphics - all formats of all charts and graphs.

This PopChart technology is the core reason we have the seemingly superfluous Oracle BI Java Host service that we have all come to love/hate.  It is Java based and clearly fits directly into OBIEE’s infrastructure model.  Okay, so now that you now have that tid-bit in your arsenal, here are few questions that one could ask of their dev skills or conjecturing minds:

1. Why do the charts that PopChart show on their website look cooler than what OBIEE offers?  Could one download a trial, rip the new PopChart version binaries, and swap them in the /Corda50/ folder?
2. Why didn’t Oracle throw in the cool Corda mapping chart?  It would beat MapViewer any day.
3. Is PopChart the engine that we should expect to run graphs/charts in OBIEE 11G?
4. What licensing deal did Oracle set-up to get Corda PopChart in OBIEE?  It had to be a sweet deal for Corda.

Next up in the third party tool battery is a tool that Oracle BI leverages in a more ancillary fashion, SQLAnywhere.  If you haven’t heard of it before don’t worry, there’s only 10 Million or so copies in use  : ) .  SQLAnywhere is owned by Sybase. And as far as I know, even though Sybase had a stellar year in 2008 they have not yet been acquired by an larger firm like let’s say…Oracle.  Although Oracle did have Sybase on their acquisition list back in 2004 during the PeopleSoft takeover helmed by a younger Ellison.

For those of us that have actually completed a Oracle BI Disconnected Analytics implementation you will immediately know (well, you should) that SQLAnywhere is the database for disconnected analytics.  When installing Disconnected Anlaytics as part of a client tools install you get a folder within the OracleBI folder structure called SQLAnywhere.  I eventually plan on doing a Disconnected Analytics Tutorial just to show how it works from a client’s laptop, using Briefing Books, connecting to a local dashboard, etc in a later post.  BTW, SQLAnywhere looks for a “.db” extension for its database file. Without the .db file there is nothing in that default folder to query.

Conclusion

This post is about more that just rambling tid-bits.  It seeks to gain insight into the inner workings of OBIEE.  Let’s face the facts, the cost per license seat for Corda PopChart and SQLAnywhere are not cheap.  You can look it up yourself.   What’s more interesting is Oracle’s no to long ago purchase of Sun Microsystems who had purchased MySQL not to long before that.  Will MySQL replace Oracle BI’s use of SQLAnywhere?  I belive so.  MySQL is much more widely adopted and it is still open-source.  And, what about Corda PopChart?  We’ll soon see who gets the chart and graphs rendering job in Oracle BI 11G but I suspect that it won’t be Corda.  Why my suspicion? Just look at the screenshots from the upcoming 11G Answers preview and look at the Corda PopCharts website, if they did get the gig, they must have done some custom work which I would have brought in-house if I was Oracle BI development management.  Plus, there are some nice open source graphing tools out there, just do a Google search to find them.

All in all I think 11G will make us re-think what we know about OBIEE.  It is going to give us bloggers a lot more content to write about that’s for sure.

Credits

Screenshot for OBIEE 11G

• http://debaatobiee.wordpress.com/2009/10/06/obiee-11g-a-gospel-about-whirlpool-in-bi-space/

Working in OBI  Answers you will probably run into this code every now and again.  I typically bump into this error when I do a “Save As..” of an existing Answers view/report to create a new report built on only a few of the metrics which the original report was built.  The full error is: Cube Definition is invalid (Measure out of bounds) Error Codes: GOA4AK7Z.

Cause:
This error is displayed because the pivot table or view that was previously referenced no longer contains the metric selection that previously was chosen. More to the point, the view no longer contains any reference to a metric. This error mainly arises when using the pivot table view as the other views have default references that bind successfully when parsed.

Solution:
Quite simple actually. Just select the Criteria tab in the Answers window. Click the view drop-down and choose the pivot table view in question (Edit the Pivot Table). When the pivot table edit page opens you will see that the Measures box within the pivoting criteria window is empty. Move a metric from the ‘Excluded’ criteria box to the ‘Measures’ box and you should be good to go.

Here is the problem as shown when the pivot table is referenced after getting this error and then choosing to edit pivot table.

In lieu of this solution one could also just scrap the answers report and recreate it. However, it is best to identify and understand why the problem occurring, thus fixing it and preventing it from happening.

I was looking to create a formula that required averaging based on the number of week in the given month. As an example Monthly Sales Total / # Weeks In Month.  Since this is a fiscal calendar the weeks are usually standardized and shouldn’t change that often.  So, if the fiscal calendar is on a 4-4-5 or 4-5-4 the logic is rather straightfoward.

I couldn’t make changes to the underlying Calendar dimension table and I couldn’t recall how to do this programmatically in OBI so I went with what I will call a quick prototyping hack.  Here it is below.

CASE  WHEN “Indirect Sales”.DIM_TIME.MONTH_NUMBER IN (3, 6, 9, 12) THEN 5 ELSE 4 END

This worked out perfectly as a place holder as we can see by a quick ad-hoc look at the new calculated measure.

If anyone has the right or clever way of doing this leave a comment.

Recently a client wanted all the row counts from specific tables on a given scheme.  The list of tables to have their row counts documented was at about 50, which accounted for about 90% of the table objects in the scheme.  At first I was going to use the “Update Row Count” feature in OBI as this is where the project was grounded.  Then, I thought there had to be an easier approach.  Using the script below I was able to get the counts and simply eliminate the objects that where in the not needed 10%. The database we were using is Oracle so the PL/SQL won’t work for MS SQL Server. I will try to find the old SQL Server code I used to do this on a previous project and post it here as well at a later time.


set serveroutput on
declare
row_cnt number;
begin
for x in (select table_name
from user_tables
order by table_name) loop
execute immediate
'select count(*) from '
||x.table_name into row_cnt;
dbms_output.put_line(rpad(x.table_name,30)||lpad(to_char(row_cnt),7));
end loop;
end;
/


a variation of this is:

Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off


References:

http://vivekagarwal.wordpress.com/2007/07/17/how-to-determine-row-count-for-all-tables-in-an-oracle-schema/
http://www.tek-tips.com/viewthread.cfm?qid=1393349&page=23

In the spring of  2009 Sun Microsystems went live with their implementation of OBIEE and presented at the North California Oracle Applications User Group.  The original document is found here,

http://www.norcaloaug.com/seminar_archive/2009_05/Sun_Microsystem_OBIEE_Implementation.pdf

but I have also mirrored the file here,

http://www.artofbi.com/docs/Sun_Microsystem_OBIEE_Implementation.pdf

The document highlights some of the benefits of  bringing OBIEE into the organization along with pros and cons and usage of the BI Meta Data Repository.  At a minimum there are some great bullet-points here that could be leveraged for your next OBIEE presentation.

Recently I dusted off an old laptop of mine that was peforming terribly with Windows XP since it only had 1/2 GB RAM and the machine is about 4 years old.  So, I wanted to throw a Linux distribution on it.  I was going to wipe the hard drive and then thought to myself that I may actually want to keep the Windows OS as well and just run a dual-boot system.  I’ve done this before when I have installed both OS’s on on fresh HD and had no problems.

I thought this endeavor would also be no problem until I saw that the only volume on the laptop was set as the system partition - Basically only a C: drive consuming all 80GB of hard drive space.  I looked in WIndows Disk Management and there is no tool for reducing a partitioned space.  What?? You can’t shink a partition via Windows?

Then long and short of it is that I was not going to spend money on any Disk Management software like PartitionMagic, etc.  I called a Linux guru buddy of mine and he pointed me to a killer open source bit of software that runs on the Linux Kernel from boot via ISO download to CD burn.  The software is called GParted.  Just download the ISO, burn it to CD (or USB if so skilled), place the CD in the tray and reboot the machine.  GParted will start up (3 minutes or less) and the GUI is really intuitive.  Some user of GParted even wrote a nice detailed post with a checklist and warnings before partitioning a Windows environment, here.

Using GParted I was able to Shrink my parition (clearly I was not using the full HD on the laptop) by a substantial amount making room for a new parition which gets marked as “unallocated”. It took about 30 minutes to go from a full 80GB partition to a 40GB/40GB split.   I think the speed was due to the low RAM.  Once finished GParted reboots the machine and pops out the CD tray on exit.

I popped in my ISO download to CD burn version of Linux Mint 8, installed it and presto, a perfect dual-boot install.  Linux runs fast on this low RAM machine.  Time to install some BI stuff.

As a nice side note, I am referrencing a video that I recently found on YouTube for installing Informatica 8.6x on a Linux server.  Later I will post more detailed screenshots from my own installation but for now I figured this is great insight to be shared.