Every now an then one needs to pad a column value with a character or symbol in order to conform the resulting value to some standard. For example, a company may represent their stores by a maximum of 4 digits and each character in that 4-digit store number must contain a value as to not be confused with any other values..  In other words, store #99 would be represented as 0099 instead of just 99.

This is not uncommon practice. Often times, similar functionality is required when pulling data from the mainframe where “super keys” are paramount.  Our good friends at MS have set up SQL Server so that this is universally simple.  The code snippet in its basic form looks like this:

RIGHT('0000' +  [COLUMN NAME], 4)

This is straightforward.  Use the RIGHT function by concatenating the first parameter to a set number of padding characters and the column name in your table. Be sure to CAST that column as a string data type if pulling a numeric field.  Then, for the RIGHT functions second parameter, make its value the same number of character spaces required for your final value.

Oracle just had to be difficult.  In order to conduct the same operation using PL/SQL you must use a function, which I am still not sure how it completely works behind the scenes.  The syntax is as follows:

SELECT to_char([COLUMN NAME],'FM0000') AS result FROM table;

Update
Recently a reader posted the use of the Oracle function LPAD, which is much cleaner than the use of the FM function and works better than the SQL Server function. The syntax would be

SELECT LPAD('Test', 8, '0') AS result FROM dual;

This LPAD function has been relevant since 8i.

Conclusion

Each of the code snippets above will provide a conformed look at a column value.  SQL Server’s method seems to be a little more cut n’ dry than PL/SQL’s but they both get the job done.

Another great source for Oracle functions can be found at http://www.techonthenet.com/oracle/functions/lpad.php

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.

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/

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.

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.

As promised, based on my previous post regarding setting up Essbase Studio Server and OC4J to run as a Windows Service, I have provided a video to visually assist you with your integration.

Essbase Studio Server & OC4J As a Windows Service from ArtOf BI on Vimeo.

If you have any questions or comments please post them so that everyone can benefit from them.

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.
Read the full story

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.

Read the full story