Wednesday, November 04, 2009

OBIEE Recruiting

Below is the ad we just posted on Jobserve.

If any of you bloggers or readers can pass this on or know anyone coming available (who would pass the technical exam) I would be very grateful.

(what do you think of the ad? Too geeky, not enough, too salesy?)


Senior OBIEE Developer

This is a full time, London City based role, on site at the client offices. It is a investment banking client developing a world wide bespoke reporting system. We expect the role to last for a minimum of 3 months but there is a real probability that the role will continue beyond this for the right candidates.

The successful candidate will be responsible for:
Converting end user requirements into system design
Developing an appropriate solution
Testing the proposed solution against the individual requirements

Tool sets you will be required to use include:
OBIEE adminstration tool
Dashboard development tools, including answers, delivers and catalogue manager
Database tools such as Toad

Whilst we would prefer permanent employees we are reasonably flexible about our relationship with our consultants and will be happy with either an employed or contract arrangement which we can discuss at a later stage if appropriate.

If you are interested in working with us please provide details of where and how you have done the following, in addition to attaching your cv:
  • Repository development
  • Webcat development
  • Database modelling
  • Testing

When you provide details - could you please outline how you went about:
  • Converting user requirements
  • Creating the design
  • Developing the solution
  • Testing
  • Implementation

For successful candidates at this stage, the next stage will be a technical interview. In anticipation, could you please provide in your reply, your availability for a telephone interview of approximately 30 - 45 mins for the week following your application.

Tuesday, November 03, 2009

It's Movember

Sorry but I can't stay quiet any longer. I've had enough.

Why are people wasting my time. If you don't know OBIEE - DO NOT pretend you do know it.

The next person who turns up to an interview had better match his/her CV.

Am I asking too much?
- All I want is an expereinced rpd developer, with good Dimensional Modelling

And if it says you do rpd development on your CV, you better be able to back that up.

You have been warned.

Things have bee quiet on the blog because we're working long hours on some pretty big client projects. Well this weekend we went into production. Yippee.
Now, what do we do? There's loads more dashboards to build, reports to write, iBots to create etc.
Where shall we do that?
Given that there will be thousands of people accessing the system, across the world, where do we develop next? Production or Development. If Production, what safeguards are in place? If Development, how do we publish? Some clients use svn as a publishing tool, but Oracle only support Webcat Admin tool operations when working with two webcats.
We have gone for Production. It is actually the safest, easiest and quickest to customer.

It goes without saying that there are certain folders that are secure for only central obiee team to alter, which would be true of any method. We just add two other types of folder/sub-folder. Team based and Work in Progress.
The only issue is the development of reports on subject areas that have not yet been built. Probably go for creating in a deve environment, copy in the xml to a new report in Production.
Your comments are appreciated!

It's Movember, and I'm on the case - You can sponsor me. If I don't get enough sponsors I will shave it off. This is one for the men, so come on ladies give us your support too.

Tuesday, September 01, 2009

Jeff's New Place

Well worth keeping an eye on.

Jeff is one of the old boys!

Wednesday, July 29, 2009

Another new blogger

Have a look at Tips On OBIEE =>
I like the one about the short URL name, what shortname can I think of.....

Keep them coming. the more the merrier (as they say).

If you are wondering why I'm not blogging much, I have several excuses, including writing the book for the 11g release, way too much client work and it's the summer (not that you would know it looking out of the train window)

BTW did you see the one from Andreas about Vertical Text

Wednesday, June 24, 2009

State of the OBIEE Market

Recession, what recession?

Ever since I started up Majendi (Now Rittman Mead), the market for Siebel Analytics (now Oracle BI) has been a roller coaster. Looking back, supply and demand for consultants / contractors / permies has either been heavily biased to supply or to demand. Getting the balance right has been difficult for both sides.

Siebel Analytics never had a huge following, and hardly any outside of Siebel projects. Customers were reluctant to put significant investment into the product and therefore there were (relatively few opportunities for consultants to learn).

Then Oracle came along and changed all that.

Customers are now investing heavily in OBIEE, and all of it's features - not just Dashboards and Answers, but Delivers, BIP, and briefing books.

This Oracle takeover caused many new consultancies to switch from Disco, OWB and other Oracle products

In the meantime the great rush to send development offshore took place. Despite the difficulties companies pressed ahead with moving vital code building offshore because of the obvious cost savings. One client I was at tried to pretend that development went to India because they were the best in the world at developing OBIEE. It was nothing to do with the 300 dollars per day rate! (Their project failed btw).

Then the agencies moved in. I cannot tell you how many calls I get every day from someone saying they are specialist in the OBI marketplace. All I can say is that they think there is a pot of gold n the market, but soon disappear when they discover there isn't.

But whilst all this market activity was taking place there was a real economy. With real booms and busts. A friend of mine who has been in business for 10 years says this is the worst ever.

Well I can tell you that so far this has been our BEST year ever. Good ole OBI market does it again. Lots of implementations and few skilled, experienced consultants available.
Last year we saw a dip. That's when the real recession started, way before the banks started to feel the pinch. (In my view the recession was inevitable due to record oil prices).

I have two vacancies right now - you can start next week.
There are lots of jobs on Jobserve
but Contracts are keeping their heads down, so supply is tight

But companies have made an error of judgment, slashing rates by 10% across the board has upset a lot of contractors.

If you are an independent consultant with good OBIEE skills then do not believe what the agencies and companies are telling you - You are still worth the same as you were two years ago. Do not accept rates below GBP 500 per day minimum. If you have the skills and experience then do not be afraid to demand the proper rate.

And if you have just had your rate cut by 10% come to me and I'll show you where to earn properly again.

Wednesday, June 17, 2009

Dont Try This At Home Kids

Having just spent some time on a prjoect with Christian Berg and Venkat, and seeing some of the presentations at the BI Forum, I have come up with my all time top 10 of 'Don't do this without an Adult Supervisor'

1. Implement Essbase in OBIEE
2. Use xml editors to create reports
3. Have your repository created by UDML from a dynamic source
4. BPEL (Leave that to Tony H)
5. Use custom Javascript to manipulate the dashboards
6. Implement WebSSO
7. Use LDAP Groups
8. Use Dynamic Go URLs (Particularly when you have finished the development of the RPD)
9. Integrate multiple stars and Multiple dimensional attributes in a single Subject Area
10. Integrate BI Publisher

Relatively easy compared to the above is:

Settings up delivers, installing BI Apps in a Unix environment, creating multiple instances on a Linux blade, Briefing books, Configuring the DAC, customising BI Apps

But do your homework before going on the client site

Do you have any?

Tuesday, May 26, 2009

BI Forum Lunch

Not only were the presentations excellent, but the food was great too.

Although we did sneak off to the pier for fish and chips and donuts one lunchtime.

Mind you our hotel was a bit odd!

and Mark is very scary!

Thursday, May 21, 2009

Love Hate Essbase in OBIEE

Just you just love the speed of reporting on Essbase Cubes. Building the cubes is super fast. Having a simple report with one Measure is a pleasure to to build and run.

Now try doing anything that OBIEE is good at.
How about having a flat hierarchy? No Ok, what about Bins? No?
surely we can put a filter in place? Ah, Of course you can - providing the filter value exists, otherwise your report throws up an error.
Error, no data is an error!!

Ok lets have some nice looking data.
what do you mean every 'member' has to unique? And what a member when it's at home?
I want the value shown to be 'London' not 'London (location)'

I hate errors. I hate rubbish looking data. I hate ragged hierarchies. Give me back my Materialised views.

Christian, Venkat, Tony, Oracle - let's have some decent blogs on this for beginners like me.

BTW We are being good boys and using ASO as recommneded by Edward at the BI Forum ;)

Wednesday, May 20, 2009

What is BI Apps?

I keep getting this question, along with how does Informatica interact with the OBIEE, and just what is the DAC?

BI Apps can simply be described as 4 repositories.
- rpd file for BI Server
- Webcat files for Presentation catalog
- xml files for DAC
- rep file for Infomatica

After you have installed the Oracle Database for the repositories, the Java into ?:\Java, and the OBIEE platform (BI Server, Presentation Server) you then install the Informatica server and then the DAC server and client. Do not run the DAC client yet. You then install the BI Apps. This appears to mainly be just an extraction of files from a zip. This has to be done on a windows machine.

You then simply point your NQSConfig.ini file at the OracleBIAnalyticsApps.rpd
Then point your instanceconfig.xml at the EnterpriseBusinessAnalytics catalog
Then run the DAC client for the first time. It will create an empty repository in the database. When it is loaded, import the xml files from ?:\OracleBI\dwrep\DAC_metadata\DAC_Client\export. Now you can 'Restore' the rep file into the Informatica Repository service.

You have now installed the BI Apps.

Informatica does not interact with OBIEE, the only connection is that the DAC runs Informatica Workflows.

The issue of the DAC is a whole blog of it's own, so will do that in the RM blog.

Tuesday, May 19, 2009

BI Apps Installation on Solaris 10, Essbase on

Sorry I have n't been blogging much, life has been crazy busy.
I did start trying to keep a running commentry at the BI Forum, but found that I was getting too engrossed in the content to write properly.

It was great to meet evryone at the forum, some I had been in contact with for many years, and others I had worked with on Siebel Analytics projects over 7 years ago! A hangover nearly managed to destroy my presentation, but I enjoyed the opportunity to speak anyway.

The main reason for being busy is three clients, all with OBIEE projects using cutting edge tech.
I just finished the install of Financial Analytics on Solaris 10, with Obiee, and it went pretty well, taking 6 days all in.

We are short of staff for the many projects we are running so do
Get in touch if you are free.

The sailing went well for the kids thos weekend, so son is now ranked 20th in the country, but unfortunately we ended up on the rocks in the rescue boat!

The Market has recovered well in the last few weeks, so hopefully the recession is receeding,

Thursday, May 14, 2009

BI Forum - Session 3

Mark is showing us some new features in the DAC which helps to optimize the ETL and warehouse performance

Bi Forum - Session 1

Excellent preview of ODI BI Apps. Looks like a version of the DAC!

Not available for 7.9.5 but is

Not sure if I should tell customers to use it yet??!

Bee Eye

All I can say is 'thanks Borkur'

The first evening was initially led by Mark, but after he had paid the bill for everyone at the curry house, he sneaked off for an early night.
So up stepped Borkur to save the day, and the rest, as they say, is history.

Looking forward to the sessions today at the BI forum (said Bee Eye if you are in West street).

But first, where is the breakfast room?, I need some coffee!

Thursday, May 07, 2009

A sneak preview of 11g?

I think I have found my favorite client.

Not only are we analysing 4GB of data, 4 times every day, in a 10 minute ETL, but we're adding some cool features to the dashboards.

One feature is tree view. Those who use APEX will be familiar with how these work, and I suspect it's the same javascript code in the background.

I am using the tree viewing in two ways, one to navigate through data, organised in hierarchies, and the other as a reports/dashboard navigator. Using a look up table which contains the 'menu' items. This is useful where there are lots and lots of reports and dashboards and you want to give everyone a similar front end menu.

Whilst we were having fun with trees we then added some tabs inside a dashboard page - in fact we even have tabs inside tabs inside pages. The tricky bit is getting content into the tabs, which involves some xml fun and games - not recommended!.

One last bit of gadgetry we put in last night was a simple show/hide routine that is used to display whole reports, or just hide tables or charts on a report. This enables us to load lots of reports on a page then flick between them very quickly - no waiting for a page to load. You have more control of prompts here too.

Wednesday, May 06, 2009

Busy times and Rubbish Developers

Wow. Life got busy.

One minute we were Majendi, happily servicing our projects and keeping our heads down in the recession. Then BANG, we merged with RittmanMead, three new clients in a week, a conference to attend, a presentation to write, and some new admin to learn. I had the spring series final two weekends in the SB3, and the kids were sailing their Optimists in North Wales one weekend, then Weymouth the next.
The rib broke down, the dishwasher failed and this morning the hot water system failed.
I also have to get a new laptop, this time a MAC.

May is going to be a very expensive month!.

ON the client side we are having lots of fun with Essbase and OBIEE (I hate ragged hierarchies), as well as installing BI Apps on a Solaris 10 machine without the use of a windows box - Don't try that at home kids!

We have one client who are using a large integrator that doesn't know how to run a report on two separate subject areas so I will blog on the RM site to show them how. I just need to find 10 minutes spare time.

I have a contact at Oracle who asked me the other day for a sample database, in ACCESS - I laughed!

I also had another consultancy send me an rpd they struggled with. When I opened it I didn't know whether to laugh or cry. Laugh because it was a complete mess and showed a complete lack of understanding on OBIEE, cry because people are giving OBIEE a bad name by poor implementations.

That reminds me , One project I know of is looking to replace it's Siebel Analytics system, they want something more flexible and quicker to create reports than they currently have. The reason for replacing it is obviously because they badly customised it in the first place and are using cheap offshore resources that don't know how to configure so take a long time to produce bad code. This customer is suffering from cutting costs and going for the cheapest offer for development. It is now costing them a fortune to buy new software, train users etc, but they are still using the same cheap outsources, so the result will be the same poor quality reporting system.

Consultancies, I urge you to get your people trained properly and stop undermining the product reputation.
Oracle, get a properly managed accreditation system in place before the amateurs ruin your product sales.
Clients, check out the experience of the people implementing your system and do not accept poor quality - don't pay them when they deliver rubbish.

However tempting it is to cut costs, we will never set up an Overseas development centre just because it is cheap. And we will only ever employ experts. If you are expert already, and probably blog your knowledge then we would love to talk to you

Thursday, April 09, 2009

iMAP the iPhone

I may be a bit slow, but I catch up eventually.

Given that I am reading my emails on the iPhone I did not want to read them again in Outlook. Turns out that instead of using POP you can use IMAP, which keeps all the mail readers up to date with any change may in any other reader. So using webmail, outlook, and iPhone is now a breeze.

Thoroughly recommend it.

- @drian

Monday, April 06, 2009

Rittman Mead and Majendi Join Forces

I am pleased to announce today that Majendi is Joining forces with Rittman Mead.

All my technical bogging will now be on the Rittman Mead blog, but watch out for some personal stuff on here.

We finalised the deal over lunch in Zurich, a very civilised part of the world.

From Drop Box

- @drian

Friday, April 03, 2009

I was interviewed yesterday by Chad, on Business intelligence, in particular the state of the market, jobs, and skills.

It got me thinking about some of the reading I been been doing recently to keep up to date. And thought I should write things down before I forget them!

I always like to keep up-to-date with market developments, in particular technology advancements.

On that front I saw a presentation recently by a CIO which quoted change and how we need to be prepared for it in IT. Now we always hear that, but he put it in the context of three Laws, Moore's Law, Metcalfs Law and Gilders Law, which actually bought it home really well.

To remind you, Moores (Co-founder at Intel) predicted in 1965 that processing power will double at least every two years.

Metcalfe's law states that the value of a network is proportional to the square of the number of connected users of the system (n^2).
The law has often been illustrated using the example of fax machines: a single fax machine is useless, but the value of every fax machine increases with the total number of fax machines in the network, because the total number of people with whom each user may send and receive documents increases.
Two telephones can make only one connection, five can make 10 connections, and twelve can make 66 connections.

Gilders Law states that the total bandwidth of communication systems triples every twelve months. In my street they are installing 50MB cable soon, so in a year that should be 150MB!!

So, computing power will blow our minds in ten years, networks are really useful when more things get connected, and speed of information flows will be even more awesome and useful for gaming.
BUT, none of the above has directly demonstrated why Oracle and SAP became powerful, or why Oracle is preferred over SQL Server for large databases, or why Column databases are better at certain reports than rowwise databases.
Or why we don't program in BASIC anymore (although it would probably be called ADVANCED now!).

So technology improvements enable us to work from home, with access to fast databases, but why am I using TOAD on Oracle, with OBIEE on top?
More importantly, what do I need to use next year, the year after, and so on. When do I give up learning and go into management!

The Cloud Compute model is already removing the need for hardware in the office, and software on your laptop (except for a browser!). The skills for databases and reporting have been commoditised, offshored and devalued. IS this pattern true for thought leadership?

Maybe I should invent a Law of my own.

How about, The second you master a technology it is obsolete.

There is actually a whole list of 'laws''s_law

Thursday, April 02, 2009

Me down to a tee

The day after April Fools

I hope you all got fooled yesterday. I fell for one, hook line and sinker!

Anyway, to continue with happy thoughts for a day or two longer...

A man flying in a hot air balloon suddenly realizes he’s lost. He reduces height and spots a woman down below. He lowers the balloon further and shouts to get directions, "Excuse me, can you tell me where I am?"

The woman below says: "Yes, you're in a hot air balloon, hovering 30 feet above this field."

"You must work in Information Technology," says the balloonist.

"I do" replies the woman. "How did you know?"

"Well," says the balloonist, "everything you have told me is technically correct, but It's of no use to anyone."

The woman below replies, "You must work in management."

"I do" replies the balloonist, "But how'd you know?"

"Well", says the woman, "you don’t know where you are, or where you’re going, you expect me to be able to help. You’re in the same position you were before we met, but now it’s my fault."

Wednesday, April 01, 2009

New iPhone

I finally took delivery of my iPhone today. Although it 16Gb, which is more than enough for my music collection, I know it will be out of date by tomorrow. So can someone tell me what the iPhone replacement will be please!

Of course on it I will be able to run my OBIEE dashboards. Next week I'll show you how to connect to the Majendi sample/development site with iPhone, PC or simple imagination!

On the subject of new features, whilst reading about the iPhone dashboard features, and the database development it reminded me to test out the integration of the Oracle DB fone with OBIEE.
Developed intially so that the Oracle development teams around the globe (particularly between US and India) could communicate cheaply, this little known Db feature is basically like Skype or MSN, but built into the Database. Larry insisted on the development when it became obvious that offshoring his operations had increased costs and damaged the communication process.
The system uses a Db to Db network and is usually done behind your company firewall. see the Oracle documentation for details on how to enable it (from version 10g).
As yet there are no in-built features in OBIEE you can use, so you have to rely on javascript (make sure HardenXSS is false) to embed/run a thin client.
You will need a microphone (there is no video as yet, but should be in 11g). In my net post I'll show the screen shots and code on how to do this.

- @drian

April 1.

Tuesday, March 31, 2009


"KISS is out, MICS (make it complex stupid) is hot"

Great Article by Toon Koppelaars.

If you are more than 21 then this will be an interesting read. Everyone else, stick to the PS3 /PSP tonight!

Many of you will have heard of Jonathan Lewis -
who has some great quotes on his front page, check them out

My favorite is:

Niels Bohr: An expert is someone who has made all the mistakes that can be made, but in a very narrow field

Monday, March 30, 2009


The brilliant, yet undiscovered talent that is Christian Berg finally launched into the blogging world

Good luck with that Christian.


Final details are being put to the announcement which will be out very soon now.

Can you guess what it is yet?

Market Update

Who do you believe? We recently advertised a role in OBIEE. We got three applicants. It is well paid interesting work throughout the UK and Europe.
I spoke to an agent last week who said he had 60 applicants for his OBIEE role, many of whom were good for the work. The rate the client wants to pay is well below 500 per day, because, he said, there are dozens of skilled workers who have been unemployed for 6 months desperate to get into a project.

Now, as far as I can tell there is plenty of OBIEE work for properly skilled workers, with the right visa for Europe, but clients have the usual cost pressures.
Those pressures normally translate into 'Day rate' savings. So a client prefers to take someone on 400 per day instead of 800. OK, seems simple enough. And the agent is happy because their margin increases.

BUT the client pays more in the long run, and the OBIEE project suffers a poor image. A great consultant can implement a really useful system that can be expanded and upgraded and part of the future for BI in the organisation. A cheap consultant who is gaining experience can make a dead end.

Who do you believe? the agency, the salesman for an offshore organisation, or the leading experts in OBIEE?
I challenge any client to compare the costs and benefits of a cheap outsource vs more expensive on shore consultancies.
I also challenge Oracle to set some benchmarks for a skilled OBIEE consultancy and consultants. Just because you reply to simple questions on OTN does not make you an expert.

Friday, March 13, 2009

What Occuring

As usual I have been keeping an eye on the Jobs Market, checking out the availabilty of consultants and roles.
As you will see from previous posts I have been guaging the state of the Oracle BI / Siebel Analytics market by the number of jobs advertised, and number consultants I know who are looking.
With the current doom and gloom being propogated by the BBC among many others it has been noticable how much the BI market is being talked up, but what is th real picture?

We have certainly been kept busy, and could be on two or three more projects now, as well as doing all the marketing stuff, playing with the new technology and writing updates for the book. And of course there is the conference presentation to finish up.

I do know of a couple of contractors that are at home waiting for the phone to ring, and some projects are closing soon (particularyly a big govenment bank one), but this level is not unusual.

The biggest issue I can see is the lack of roles being advertised in the UK for OBIEE and Siebel Analytics. I can't remember the last jobserve advert for Analaytics, and there is only one in the whole of Europe right now.
OBIEE has fared only slightly better, with 2 roles out there right now. But there never has been too many adverts anyway. This may be due to more direct networking, particularly using LinkedIn, and maybe due to an increase in the number of consultancies advertising themselves as Oracle BI experts. However, 2 is down from the peeks we had last year.

Beware the Squeeze

In the past downturns the agencies have strugggled to maintain their income and have therefore squeezed more from the margins. They do this by telling clients that good consultants are hard to come by as they do not look for new projects in a recession, and are therefore still expensive. At the same time they tell the consultants that the rates have come down due to the recession! Always ask the agent what margin they are on, anything more than 15% is taking the mickey.

If you are looking then do get in touch I can possibly point you to opportunities my network (and no I dont charge!).


Look out next week for some BIG NEWS which we are all excited about here at Majendi.


Wednesday, February 25, 2009


I am pleased to announce that I will be presenting at the Rittman Mead BI Forum in Brighton on May 14th and 15th this year.

Take a look at the agenda and I think you'll agree it has an excellentline up of OBIEE talent:

There are only limited numbers, so it's like always: the early bird catches the worm.

So long and hope to see you there,


Friday, February 20, 2009

More Big Tips Please

Reply from Phil as follows:

Of course Adrian, I've got loads.

How about this one:

When you do a combined request you can't change the column headings in the Edit Column Formula window as any changes you make there aren't saved. However, you can change the column name in the Column Properties \ Column Format window just fine.
(I have better tips than this but I'm just warming up).

Surely someone out there can challenge Phil to tip showdown?

Tuesday, February 17, 2009

Big Phils Tip - Page Moving

Imagine the scenario. You have a dashboard with 35 pages on and you have to add a new page, at the TOP!

Normally you add your page, and it goes to the bottom of the list, so you would click 35 times on the up arrow.

On this button the HTML is exposed so you can edit what it is doing (Other pages use Javascript to move items).
The format is:
http://majendiserver/analytics/saw.dll?PortalModify&Action=SetPosition&ItemPath=/users/ADMINISTRATOR/_Portal/My Dashboard&Position=35&Done=PortalProperties%26PortalPath%3D/users/ADMINISTRATOR/_Portal%26Done%3DEditDashboard%2526PortalPath%253D/users/ADMINISTRATOR/_Portal%2526Done%253DDashboard%2526Page%253DIntro

Which has the vital &Position=35 command. Just change this to the number in th elist you want.

Thanks Phil. Got any more?

Monday, February 16, 2009

Siebel Analytics Tip of the Day

I know I don't normally do these, but here is one for today:

When you work in MUD mode, and merge your rpd, the 7.8. Admin tool will not update the variables you set offline. It will also have trouble dealing with presentation column name changes - you will find #1 added to many columns and will have to clean this up prior to publishing merged rpd.

Tomorrow I will have a top tip from Phil Henson, legend of the Siebel Analytics world !

Wednesday, February 04, 2009

Tonsils Out

Not only has it been very busy on the client work front, busy on the sailing front, busy on the consultancy, and trying to dig out of the snow drifts, but I had my tonsils out. Will be back to shouting again soon I hope.

It went pretty well apart from two days after the OP I had a trip in an ambulance when blood started gushing from my throat. Never dialled 999 before, or been in an ambulance with the lights flashing and the siren going, all very excting.

Whilst I was at home recovering I managed to carry on working due to the wonders of modern technology - Broadband, VPN and Laptop. In fact I do much more when working from my home office, and get to save three hours per day travel.

Anyway, apart from the credit crunch there is still some work to do. I hear of one project that needs someone tomorrow for BI Publisher and someone else is after CTI skills.

If you are looking for a new project, drop me a line as I always hear about new projects alll the time.


Wednesday, January 21, 2009

Monday, January 19, 2009

Localisation of OBIEE

I have no idea who these people are, but just found an interesting service which claims to double increase of user acceptance. Thats a pretty tall order, but if it works thats great. The more the merrier!

They dont say which languages they cover but I assume German for now.

Thursday, January 08, 2009

How is your Croatian?

New blog on OBIEE.

Good luck with it Goran.


Thanks to Daan we can now view it in English

Informatica vs ODI

As you would expect, the debate still goes on as to what is happening on the OBIEE ETL front.

As all you ex-Siebel people will know Informatica is the system that every uses for their databases. It was licensed by Siebel to run their CRM ETL Packaged Applications. Then when Oracle bought out Siebel they stated that Informatica was still the supported platform, even though they had their own tool, OWB. Now, OWB appears to be a great tool, but Oracle did not want to re-write all the Application (Mappings, Workflows, DAC, Documentation) and force their customers onto new software.

However, they then bought an ETL product company and launched another ETL (or ELT) tool called ODI - Oracle Data Integrator. The immediate speculation was that this would replace the Informatica tool in the Applications development. At the conferences I went to last year Oracle were still saying that Informatica was the main tool, but hinted at the the possible use of ODI in the future. In this document from 2007 they only mentioned OWB in the BI suite! and This Oracle Fusion roadmap does not state which tool is king.

Now there is a rumour that Oracle are beginning to use ODI for some applications. Mark Silialks (Oracle employee) pointed out in a debate on LinkedIn that the Oracle stated direction for Financial Analytics is the use of ODI - I haven't verfied this yet, but I think Greg Hutchinson summed it up nicely when he said that "Using ODI for Financial Analytics busts one of the key design advantages....conforming dimensions across all 42 OBI EE modules. So imagine a company that buys an OBI EE SLA now. Does this mean they must support both ODI for one part and Informatica for the rest? What about upgrades? or buying additional modules? "

It is clear from customers that they like Informatica because it is skillset that is already known in the business and can be available from the market (contrators, consultancies, employees).
They have also invested a great deal of time customising - thats what I do! - Adding missing functionality, adapting for changes made in the OLTP, adding other external data.
They do not want to have to repeat all of that work, learn a new skillset and be held to ransom on rates because of a skills shortage.

I'll keep looking out for more signs from Oracle but for now stick to the Informatica stuff.

Friday, January 02, 2009

OBIEE Snowflakes and Stars - Part 3

A Quick recap:
  • From Part 1 we saw that you can organise logical tables in a normalised fashion, and in a simple model the SQL will work without errors. The use of a normalised model in the Data warehouse is not something that you would normally see being advocated by the Warehouse experts, and yet we face pressure from clients to reduce the development time and complexity of the warehouse, which can lead to so called sub-optimal performance.
  • From Part 2 we saw that organising the logical model into a proper star led to the most efficient SQL being generated. This SQL will enable STAR Transformation to work in an Oracle DB. We also see that for this to work you need all the relevant foreign keys on the fact.

In this section we are going to explore the Hybrid design. This design is common to most implementations that I have seen.
The logical model is designed as a star. However, the physical model is a Snowflake.


I have only shown one dimension here for simplicity, but there can be several dimensions attached to the fact.

Here I have shown three logical tables to demonstrate the different methods for the Logical Table Sources (LTS)
If you look at the Logical table sources in W_PERSON_D, you will see that I have three set up, with the LTS name being the same as the underlying Physical table:

In this example each one contains just one Physical Table. The first one contains just the HYBRID_PERSON_D:

And the column mapping is...

If we look at the column mapping for PERSON_DX

We can see that only those columns that exist in the physical table are matched to the Logical Columns. This will force the engine to use this LTS if any of these fields are used. Also note that I have mapped the ROW_WID field to the DX field that contains the Person Row WID.

Now let’s look at a report being run using this Logical Dimension.

No errors and the SQL does the magic of combining the separate Logical Sources

select T5260."FULL_NAME" as c1,
T5303."ACCNT_WID" as c2,
T5303."BRICK_WID" as c3,
sum(T5274."BLAH_VALUE") as c4
"W_PERSON_D" T5260,
"W_PERSON_F" T5274
where ( T5260."ROW_WID" = T5274."CONTACT_WID"
and T5260."ROW_WID" = T5303."CONTACT_WID" )
group by T5260."FULL_NAME", T5303."ACCNT_WID", T5303."BRICK_WID"

And if I just want to query against the Name:
Then the SQL shows that only the relevant tables are queried:
select T5260.`FST_NAME` as c1,
sum(T5274.`BLAH_VALUE`) as c2
from `W_PERSON_D` T5260, `W_PERSON_F` T5274
where ( T5260.`ROW_WID` = T5274.`CONTACT_WID` )
group by T5260.`FST_NAME`

(unfortunately I am using Access for my sample database and Access does NOT support comments in SQL, otherwise you would see which LTS has been used)

Now let’s look at Person V2

Here we can see that I have added just one LTS

But that LTS has three physical sources,

And if we run a request:
(the same one as above)

Then this time the SQL is as follows:

select T5260.`FST_NAME` as c1, sum(T5274.`BLAH_VALUE`) as c2
from `W_PERSON_D` T5260, `W_XREF_PERSON_M` T5303, `W_PERSON_F` T5274
where ( T5260.`ROW_WID` = T5274.`CONTACT_WID`
and T5260.`ROW_WID` = T5303.`CONTACT_WID` )
group by T5260.`FST_NAME`

As you can see W_PERSON_D and W_XREF_PERSON_M are included in the SQL but not W_PERSON_DX? Why? W_XREF_PERSON_M is not even used in the request columns and it does not help the SQL to answer the request!
We know that PERSON_DX is joined to PERSON_D using a physical foreign key, whereas PERSON_M is using a logical key. So let experiment with the keys to see what happens.
I changed the join between PERSON_D and PERSON_DX to a physical join, not a physical foreign key, and re-ran the report, which resulted in the following SQL.

select T5260.`FST_NAME` as c1, sum(T5274.`BLAH_VALUE`) as c2
from `W_PERSON_D` T5260, `W_PERSON_DX` T5267, `W_XREF_PERSON_M` T5303, `W_PERSON_F` T5274
where ( T5260.`ROW_WID` = T5267.`ROW_WID` and T5260.`ROW_WID` = T5274.`CONTACT_WID` and T5260.`ROW_WID` = T5303.`CONTACT_WID` )
Group by T5260.`FST_NAME`

This time the PERSON_DX tables was added into the SQL even though it is not used in the request.

Now let’s have a look at a third way. This combines the two methods above of creating the LTS’s.
Here I have created the dimension with 3 LTS

Each one contains the W_PERSON_D table. The first is on it’s own, the second contains both Person D and DX, the third Person D and XREF M table. The choice for the developer with this set-up is what fields to map where. Take ‘First Name’, do you map this to just the first LTS or all three?
If I map it to all three then run the following report
I get the following SQL
select T5260.`FST_NAME` as c1, T5267.`ATTRIB_11` as c2, sum(T5274.`BLAH_VALUE`) as c3
from `W_PERSON_D` T5260, `W_PERSON_DX` T5267, `W_PERSON_F` T5274
where ( T5260.`ROW_WID` = T5267.`ROW_WID` and T5260.`ROW_WID` = T5274.`CONTACT_WID` )
group by T5260.`FST_NAME`, T5267.`ATTRIB_11`
order by 1, 2

One piece of SQL gets all the answers
Now if I map ‘First name’ only to the HYBRID_PERSON_D LTS and re-run the report, I get
select T5260.`FST_NAME` as c1, T5267.`ATTRIB_11` as c2, sum(T5274.`BLAH_VALUE`) as c3
from `W_PERSON_D` T5260, `W_PERSON_DX` T5267, `W_PERSON_F` T5274
where ( T5260.`ROW_WID` = T5267.`ROW_WID` and T5260.`ROW_WID` = T5274.`CONTACT_WID` )
group by T5260.`FST_NAME`, T5267.`ATTRIB_11`
order by 1, 2

ITS THE SAME! The Analytics engine just puts together the SQL it needs, irrespective of your chosen method of LTS.


So what is going on? In the example model we have seen that
it does not matter what logical or physical model is used, OBIEE still works.
It does not matter how you set-up your logical table sources, OBIEE will put them together in a small query to get the results.
If you use a physical join, then OBIEE will always use the joined to table in the query, even if it’s not necessary.
If you use a Physical Foreign Key, and have the joined table in an LTS, it will only use the joined table when it is needed in the results.

So does design Matter?
Given the evidence so far, it would appear that you can do without a specific design. A complete beginner can do what they like and OBIEE will deal with it. So beginners can get away with building however they choose. If your dataset is small then performance will not be an issue, and if speed of response is bad then designers can always blame the network/db/server/web! When a project starts out, or is just a prototype then you do not need to consider the future addition of facts and dimensions so who cares what the technical stuff looks like. If you want to impress the client by saying you can install in 3 weeks then you had better forget design cause this takes longer, and once you have wowed the client you will get further work and no-one else will see your build (I know who you are!).
If a client has gone for the cheapest option, ie offshore, then they do not care about the quality of the work done, so build it however you like – they will never know.
But, I care what work I produce, and I normally work on very large databases which will not forgive poorly written SQL. If you have an Oracle environment then you want to maximise the use of Star transformations. For any large system you will want to take advantage if summaries. In my simple model I have used tables which join using the Person unique code. I have also not used any aggregations. There are no Hierarchies. There are no Level based measures. There are no visibility filters in place.
All of these features are what make OBIEE a useful tool for the analysis of large amounts of data, by end users with no need to fully understand SQL. Without these features, Drilling through data levels to find the specific issues would take too long. Summary reports would be too slow and users would revert back to Access, Excel and SAS. In fact, if your users still insist on using Excel or Access for a large amount of their analysis then your installation of OBIEE DESIGN is failing (You cannot totally remove Excel, in fact you should be encouraging some integration with OBIEE).

In the real world the joins in a dimension can be numerous, not use the Dimension keys and sometimes involve complex joins. The joining of tables in a one-to-many relationship can result in multiple results for a single dimension e.g. Person, which when used with a fact causes duplication of facts. If you have multiple Logical Table Sources then you have to be sure they will work together. If the user were to choose a field from each source do they get an error? If so why?

How do you incorporate Dimension attributes, for example a Contact could be a Doctor who works at a particular Hospital, but later changes to another Hospital. Do you store the doctors hospital in the facts where contact exists (true Star Schema style) or in the Contact dimension, or linking from Contact to Account (Snowflake) or in an SCD? If you use a pure normalised model then this is not a problem, or is it? How do you reflect history? Do all the patients the doctor has treated over time get added up in his new hospital?

So my point is, yes you can do quick and dirty, and you can use normalised schemas or snowflakes or Pure Stars in OBIEE. But please do so knowing the consequences, and have good reasons for doing so. Don’t blindly follow a star schema for small implementations, which need minimal amounts of ETL. Do use as pure a star as possible for very large implementations, or where multiple facts share common dimensions.

The Cowes

The Cowes
Cowes Racing