Thursday, December 20, 2007

"Living in America"

If that's you, and you know your rpd from armpit, then please get in touch. I may be able to put you in touch with some projects that need OBIEE consultants.
I recently commented that Finance departments can start considering how OBIEE can replace/ compliment their MS Access systems. In response to a question on this I replied with the following:

"A little background - I have been implementing MS Access systems for 10 years, and I think they are great. The speed to deliver, the power of the analysis, the very useful VBA make a great product for Finance departments. I came across OBIEE (in previous forms) 5 years ago and found that it solved the big problem of Access databases being restricted to a local team. Using them across the world was very difficult. We needed to report in Australia and USA using European data.

OBIEE can be very quick to develop in (if your IT and project team will let you) you can create reports based upon a standard definitions (i.e. measures created in the rpd), and you do not have to extract massive volumes out of one database to be able to combine with another - we had to put CRM data next to HR data, next to Finance data.

Any good product can be badly implemented, and IT departments can hold you back from doing the quick data analysis you need to do for your job, so using Access gives you freedom to develop what you want when you want - YOU are in control. Treat OBIEE in the same way. Get the IT dept to set up the infrastructure, YOU control the rpd and the reporting.

Best of all - you can put OBIEE on top of Access - best of both worlds."

Wednesday, December 12, 2007

Quiet Christmas

I had a few emails, texts and calls today from people who are available for work. Good Siebel Analytics / OBIEE consultants who may be having an extended xmas holiday!

If you have a need for someone for short or long term please get in touch and I'll forward their details on.

You may have noticed me being busy on LinkedIn recently. I have managed to build a good network of OBIEE consultants, and even started a group specifically for Oracle Business Intelligence consultants. This should benefit us all when it comes to project requirements.
Go to and I'll add you in (I only add in OBI consultants and related workers).

I have also been busy on the Oracle OBIEE forum, reading the issues that people have. It annoys me when someone clearly hasn't read the manual or even tried to find the answer to something before posting a request.

Each day I always check out Venkats postings on his blog. He has a great job - he just gets to play with OBIEE all day and write about the new stuff.

Talking of new stuff, the new functionality in, which allows you to call Oracle functions will be a greta boost for the product. Up until now I have seriously wondered by you need a BLOB in an Analytics but now we get to put pictures from a database in you reports.

I was sceptical about the time series functions, particularly as they were causing some people problems, but I have now converted over completely and so far so good. Let me know what you think.

Have you noticed that when you use the functions editor you now get something more useful when you add the function - it actually now tells you what arguments it requires instead of <>. I tended to copy the contents of the edit box into TextPad but may start using the editor more now.

I notice there are more and more companies that are offering OBIEE and Siebel Analytics consultancy. Those of us that were using the product over 4 years ago are seeing others trying to muscle in. All I can say is How much do you really know?? There are so many stories at conference from clients that say their implementation failed due to the integrator not having the right skills. We ALL need to make sure the product is not undermined by consultants who frankly do not have a clue. If you are in a pitch to a client sell your real experience and don't let beginners run your project.

I am still building the OBIEE information website, if you would like to have a listing just let me know.

That's all for now Folks!

Thursday, December 06, 2007


One little 'feature' of OBIEE keeps popping up in my projects - Automatic Trimming.
If you have a field with data, or calculated field, that has a trailing space in then OBIEE can remove the space for you.
This can lead to some issues, particularly when drilling down.Lets say you have a report with :

DimA Top Level Fact 1.
ABC 123

and the true value of DimA is 'ABC ' - note the space
The value displayed in the report is ABC, clicking on the value will start the drill down, using a filter of 'WHERE DIMA = ABC'
Of course NO data is returned.

The solution is to either make sure none of the data has trailing spaces or add the TRIM function. TRIM ( BOTH ' ' FROM DIMA) will do the trick.


I noticed this first when using the TO_CHAR function.If you to_char a date into month name (usually using OBIEE Function MONTHNAME) then the answer you get back is always 9 characters, which means AUGUST has three trailing spaces.You therefore have to trim the answer.

Thursday, November 29, 2007


Airport lounge again.

Why do people think that business travel is glamorous?

ANYway, I'm bored enough to finally figure out how people are hiding their contacts in LinkedIn. There is a setting you can change in the profile, but I'll let you find out how.

The big question is why you would hide your contacts.

The whole point of LinkedIn is to NETWORK, make contact with people in your business area and share information, ideas, recruit, learn.

I suspect it the recruit thing that puts people off. I do know that recruitment agencies will use the system to their advantage, but surely it is to your advantage too - you get to find out about roles.

Anyway, feel free to browse through my contacts, and if you see someone you may need - a fellow OBIEE consultant maybe - then get in touch with them.

Wednesday, November 28, 2007

Complete Blogger

Like all good bloggers I am finally able to sit in an airport lounge and write something about my day.

I'm off to a client in the North so am flying out of Southampton.
I may go to the pub tonight and may have a dinner with someone.

Whilst there, I will work on some issues with OBIEE

done it.

I am now a complete blogger !!!

Monday, November 26, 2007

New LinkedIn Group

If you are using the Networking tool LinkedIn, I have created a group for Oracle Business Intellegence professionals. Look out for the group on my profile, and join in if you think it is appropriate for you.
I will connect anyone who is working in OBIEE, Siebel Analytics, Oracle Business Intelgence (such as OWB).

Friday, November 23, 2007

OBI EE Consultants Needed

The OBIEE world is still going strong and demand is high. We need a couple more consultants - one experienced contractor for a project in Germany where we are the technical leaders, and one junior (on a decent salary).

If you are ready for a new project/consultancy then do get in touch.

Monday, November 19, 2007

Remembrance Day petition

A petition has been launched requesting a new public holiday falling on the Monday after Rembrance Sunday in November each year.

To be known as the National Remembrance Holiday, it's purpose is threefold:

  1. To emphasise the remembrance of those servicemen and women who have given, and continue to give, their lives for Britain.
  2. To remind people of the importance of protecting our Nation and what it stands for.
  3. To break that 3 month period between the August Public Holiday and Christmas when there are currently no long weekends, especially as the UK has fewer public holidays than most European Countries.

If you are in agreement, please sign up to the petition
- it only takes a few moments - and it would be great if you were minded to forward the link to other people as well.

Thank you for your time.

Wednesday, November 07, 2007

Using Catalog Manager

If you want to have a consistent Header or Footer on each page of your dashboards then you can add the default settings to your custom messages file and all NEW reports will have the same settings. You can edit these settings per page afterwards. However, if you have built loads of pages on various dashboards and the Business Analyst comes along and says, 'oh I forgot to say we need a confidentiallity clause at the bottom of each page' you smile through gritted teeth and are faced with a large task of opening each page and editing the print formatting. If you have lots of formats to make this can take some time. The alternative is to dig into the Catalog manager and edit the xml for each page directly. This takes half the time to go through each page. Open the Catalog Manager and navigate to the _portals folder.

Now select the properties for the page (make sure you don't pick the dashboard layout).

Edit the page xml.

The trick here is not to write your own xml!! Copy the xml from one you set up manually in the dashboard editor.

Here I copied in a message about the system being restricted and a nice green message!

The tag added is saw:pageProps

notice saw:pageHeader show="false" / and saw:showFooter showondashboard="true" show="true"

simple really.

UI Messages

The most popular customisation for OBIEE is the colour scheme, which you will know is done by Skin/Schema.
This is great but you then come to change the wording and it is not done by skin/schema.
You can update the file uimessages.xml to change the links on the menu, so for example Dashboards could be replaced with 'Sailing' and the link can be changed (in the controlmessages.xml file), but this covers all skins being presented. On of the forum responses recently suggested that the javascript can be altered on a skin by skin basis but I can't find any reference to the messages being presented. I'll keep looking but if you know send a quick reply :)

The normal method for updating messages is to create an entry in your custom messages file, but htis requires you to know which message table the message relates to. This would be fine but this area is not fully documented (in the public domain at least:).

I'll experiment and let you know if I can find the relevent massage table setting to use. In the meantime this type of message customisation has to take place in the original files (which you hopfully have a backup of!)

Wednesday, October 24, 2007

New Version of OBIEE

Version of OBI Enterprise Edition is now released.

It's got some new stuff in.

How exciting!

Wednesday, October 17, 2007


NOT quite sure why it exists, but I just found a forum for ex-Oracle employees. Probably useful for networking with people who left to earn a decent wage contracting.
It was listed in the LinkedIn groups section.
Have a look at

Tuesday, October 16, 2007

Act as

Nicholas De Brabanter has a good article on this.
I found this link the other day. It lists the average rate for OBI consultants in the USA.

At 137 dollars for an average consultant and on a straight conversion these look slightly lower than the UK, but the cost of living over there is lower.

Thursday, October 04, 2007

Foreign Travel

So lets recap....

Ireland - Two OBI/Analytics required
Amsterdam - One senior OBIEE
Midlands - One OBIEE expert
France - 2 month contract for Siebel Analytics
London - Training x 2

then along comes Australia !!!! Anyone fancy going to Sydney for the winter?

Where's my suitcase.

Seriously, there is more work than I can handle, but rates are still tight. If you fancy trying out contracting then get in touch and I'll point you in the right direction

- Adrian

Wednesday, October 03, 2007

Sign up for the Arrows


"Organisers of the event say that the Arrows military background might be "offensive" to other countries taking part in the Games. The display team have performed at more than 4000 events worldwide, but the Department of Culture, Media and Sport have deemed the display team "too militaristically British". Red Arrows pilots were said to be "outraged", as they had hoped to put on a truly world class display for the Games, something which had never been seen before. Being axed from a British-based event for being "too British" is an insult - the Arrows are a symbol of Britain .

The Red Arrows have been excellent ambassadors for British overseas trade, as they display their British-built Hawk aircraft all over the world.

The Arrows performed a short flypast in 2005 when the winning bid was announced, but their flypast at the Games was to have been truly spectacular.

It is to be hoped that common sense prevails"

If you disagree with this decision, sign the petition on the link

Tuesday, September 25, 2007

Amsterdam anyone?

There is a contract in Amsterdam that is looking for an experienced analytics consultant. Please get in touch with me if you are interested. I can put you in direct contact with the manager there.

Wednesday, September 19, 2007

Role of Honour

I received lots of good feedback on my recent rantings about the market, so have decided to set-up a role of honour for OBI specialists. We know who the major players are, so I'll set-up a webpage listing consultants, companies, projects, agents and general information.

If you would like to be included just let me know, and send me a profile. If there is anything you specifically want to have included, let me know. If you want, I'll put a link on your profile, so people can make direct contact (via LinkedIn, Xing, facebook, etc).

I don't think I can put it on a page here (can I?) so may hvae to host it on or

Wednesday, September 12, 2007

Supply and Demand

Spain just called
so did Switzerland
and Sweden
London too (twice)
oh, so did Amsterdam
and of Course there's Munich
not forgetting Wiltshire

There's lot's of work out there

And there's not that many OBI / Siebel Analytics specialists either. A few are catching up, but I know of about 5 truely experienced consultants in the market.

So why, oh why do companies think they can get someone who is skilled for 800 Euros per day??? If you have the skills it's because you have invested in a risky market (come on Oracle SELL MORE), and you have some intelligence.
I am actually beginning to think that companies prefer a cheap rubbish job. Come on prove me wrong.

If you are a consultant. Don't take a low rate, don't let the agency take a huge cut and do Learn the product inside out. The success of OBIEE depends upon succesful implmentations.

If you are a company (or consultancy) looking for someone, let me know and I'll put you in touch with good consultants. I'm not an agency but I can probably give you some good advice and screen out the bluffers.

I know most of the companies that are installing OBIEE so if you are looking for your next contract, let me know and I'll put you in touch with project managers.

In fact, I think I'll put the details of projects in my BizIntel yahoo group. Come on, lets get this market working properly.

and the bad news...
We just had a second rejection for a Work Permit application to bring in a skilled worker from South Africa. Seems the government is happy for our work to disappear offshore, and let the skills shortage in the UK continue to hold back small copanies like ours.

If you know how I can get hold of more decent staff I would love to know!!

Is there a way to get people legally working in this country? How does IBM, Accenture and the offshore copanies do it?


Does anyone have any idea how some people are able to buy HSMP tickets?!!!

Friday, August 24, 2007

Cluster ....

It appears that there is a significant change that I had overlooked in the new OBI, compared to the Analytics 7.8 version.

I had noticed that there was a new webcat structure, that was easy, but one of our contacts spotted that there is a new way of clustering the new Webcat.

Previously, each Webcat server (BI Presentation server) had it's own copy of the webcat file, held within the Data folder.
If you had two or more webcat servers they would syncronise the contents of the webcat using a utility - something that did not work very well.

Now you have one webcat, irrespective of how many web servers you use. They all point to the same webcat.
This neatly solves the problems you get with Syncoronisation, but introduces one significant flaw - a Single Point of Failure.

What do you do? Put your webcat on a SAN, which has all the RAID you can get. Back up the whole webcat folder every night - obviously!
Now take snapshots of the webcat throughout the day, but be very careful how many you take. Too many will slow the system down.

One thing I have always felt though is that you should cluster for Customers' High Availability requirements NOT for performance.

The majority of performance issues should be dealt with by database design, prudent Caching (with seeding), adequate memory, and ETL design. Only when these have been worked through should you look for that extra 5% from clustered servers.

Saturday, August 18, 2007

Long live the King

Siebel Analytics is Dead, long live Analytics.

Actually it's not, it's still a damn good product, but the son of Analytics, OBIEE, is the new King.


Because I'm now having fun without Siebel ;-)

The new world looks like this:

An Apex application is the portal.
Identity management is by OIM (OID), managed by the Apex aplplication.
SSO uses OID to authenticate users.
Links on the portal, to your dashboards and Answer subject areas.
Database design by YOU, customise as you like.

Is the grass greener, oh yes.

Of course, Elvis is still the real King

Friday, August 10, 2007

Mad Cowes

All quiet here due to me being on holiday.

We're having a fantastic week in Cowes.

Best of all we are leading the fleet going into the last day.

Overall Results

Wednesday, July 11, 2007


Help!, we need somebody, Help, not just anybody, Heelp!!

Can you tell I've been listening to the Beatles tonight?

Anyway, we really do need more Analytics people. We have OBI and Analytics work coming out of our ears, so if you are looking, or know someone with Analytics then please get in touch. Now.

We need all levels, experts who want to contract through to juniors with one project who want to work as a permanent in the UK. (Work permits can be arranged).

Hardly enough time to work on the book. But don't worry it is progressing.

By the way, do you know how how the get totals working in a report that has TopN (or BottomN) in? Just go to the function dialogue for the field and set Totals to 'None'

Thursday, July 05, 2007

Catching up

As I have been in hiding on a secret client site (with no access to the internet) for a while, I thought that I would go for a large blog today.

To start with, there is some new functionality in 10.1 that you should know about. One feature is called a Presentation Variable. Like repository based variables but it is created in the BI Presentation system.

Bascially you set it up from a prompt. Once set it can be used all over the place, but my favorite is the Title of a prompted report, or column of a prompted report.

Try this

On the prompt, set a variable, which can then be accessed in the Title of the report.
For example, if you select a County of 'Wiltshire in a prompt, you set a variable name called VAR_COUNTY,
then in the Title you can display 'Details for Wiltshire' by changing the title to: Details for @{VAR_COUNTY}{All Counties}.

Neat eh?

In the good old days (last year!) we had to rely heavily on False Titles to perform the above. These may still be useful today. These use Narratives in reports to create what loks like a title. The report uses the same dashboard prompt as the report it is presented with. Using the @1 syntax you can present the value in the column, the there have a title which says something like 'Sheep in Wiltshire'. Formatted properly it will look like a title. Place it above the query in the same section. Viola.
Now I just tend to use the Section names as the titles.

I started using some Oracle dev tools this week, and I'm actually beginning to like them. Call me fickle but I have to say that I may be throwing out TOAD and starting to use Oracle SQL Developer instead. The main reason is that it is free!!

Having a quick look through my emails the other day I came across this message.

Someone asked the following question

We have a requirement to switch between the DBs based on the user login. We have two identical/similar dbs. But based on the users, the connection should switch.
Ex: user1 should see the data fram DB1 and user2 should see the data from DB2. Our plan is to keep a single stream of Physical, BMM ,Presentation catalogs and Webcat. Is this possible through Session variables. If yes, how?

Puna replied with this excellent answer

1) Create a table "db_details" in any db with the following details:
Login_Name dbname dblogin dbpassword

2) Populate this table as per your requirements. Create a connection pool in Physical layer which connects to this table (via database)

3) Create three Non-System Session Variables: dbname,dblogin,dbpassword. Create an initialization block with the following SQL Query:
select dbname,dbuser,dbpassword from db_details where Login_Name=':User' and polplute the the above created variables.
Set the connection pool of the initialization block which was created in step 2.

4) Now for the connection pool for which dynamicity is required as per the logged in user, in connection pool of that database in physical layer set the the following properties to:
Data Source Name: VALUEOF(NQ_SESSION.dbname)
User Name: VALUEOF(NQ_SESSION.dbuser)

Make sure if you are using ODBC or DSN's then DSN's with the name "VALUEOF(NQ_SESSION.dbname)" already exist in the system.
So now you will be able to switch DB's based on the Login_Name.
Hope this helps.

Nice one Puna.

If you read this (or know them) please send me your details and I'll invite you to our OBI Developers Forum.

Friday, June 29, 2007

Urgent Requirement

We need a Siebel Analytics consultant for a contract in the UK - not in the North :)

As usual, we have more work than people. If you are an Analytics consultant (i.e. know all about rpd development) then please get in touch.

There will be a technical interview, so please don't try bluffing me!

For the right candidate we'll pay £550 per day initially, rising to £600 after three months.

Start in mid July

If you know anyone who may be interested please get in touch.

07801 480900

SE One is here

Oracle have officially launched SE One - Like Siebel Analytics only cheaper.

This is the pricing package we have all been waiting for. There is no excuse for any company to not buy Analytics. This can be used for what Analytics is good at => RAD (DSDM) warehouse projects. Finance departments should take a really good look at replacing their Access Databases with this.

You'd think I had shares in Oracle (I Don't)!,289142,sid41_gci1262237,00.html

Wednesday, June 27, 2007

Freedom of the City

I had a great day today. I had to go to the Camberlains court and swear allegiance to the Queen and to the Lord Mayor. We (my wife and I) were then granted the Freedom of the city of London.
I can now drive sheep over tower bridge.

Monday, June 25, 2007

Interesting Note from Oracle

This note was published today. It may explain the questions I have been getting.

"The Standard Cost General table (W_STANDARD_COST_G) and the Exchange Rate General table (W_EXCH_RATE_G) are general reference tables.

Both tables have surrogate keys (ROW_WID). But unlike dimension surrogate keys, these surrogate keys are not referenced as foreign keys by any fact tables. Therefore the general tables do not participate in the star schema. They are not designed for use in reports and queries.

The general tables are intended for use as lookups during Extract-Transform-Load (ETL). For example, the Standard Cost General table provides cost information for a particular product in a particular inventory. The cost information will then be stored in the fact table directly. Similarly, the Exchange Rate General table provides the exchange rate between a pair of currencies for a specific day, given the exchange rate type.

The following are some WIDs in the general tables. They are not necessarily populated and they are not intended for use. These columns are:


If you need to have a cost dimension, you need to design a different table. It is not recommended that you alter the Standard Cost General table for this purpose. "

Thursday, June 21, 2007

Professional Indemnity Schedule

Just so I can point people to the evidence, here is our insurance certificate.

Professional Indemnity Schedule

Tuesday, June 19, 2007

Oracle Warehouse Builder Documentation

Today was spent installing Oracle databases and Warehouse tools onto one of the Majendi Laptops. It's good to keep doing these to keep your eye in.

A valuable resource if you cannot memorise everything is :

Oracle Warehouse Builder Documentation

Friday, June 15, 2007

A typical question

Please keep sending in your questions, it's interesting to see what challenges you guys (and Girls - Vicky!) face.

Here's a recent one

Hello Adrian,

I need little help.

Our company is implementing Oracle BI 10.x. We don't have much experience in Siebel Analytics/OBI.

We have few fact tables e.g. W_SALES_PICK_LINE_F, which we need/wish to extend. We are not sure about which methodology we should implement.
When we talked with few SA experts they mentioned we need to extend based on ROW_WID. Well, this column doesn't exists anymore in many fact tables, so currently we are thinking of creating ROW_WID on this F table manually and then create WC_SALES_PICK_LINE_FX and take these two tables in physical layer of RPD.

Additially, we need to create few Informatica mappings, again SA experts pointed out to use Image tables. Our data source is Oracle Applications, I don't have much idea about Image tables, but clearly there is not S_ETL_I_IMGAGe or so tables in Oracle Apps

Please guide us as how to proceed on these two issues.

Thanks in advance

and my answer

I am surprised to hear that ROW_WID has been removed from standard vanilla tables. I don't think your system will work without the ROW_WID field. Add the field back in quick! :)

My advice is to extend an existing table, not to create _FX tables. Providing you keep good control on your alter table scripts there are no issues with extending vanilla tables.

Take a copy of the vanilla mappings and customise these in your custom mappings folders. Do not update the vanilla mappings in place - they will be over written in an upgrade.

You can create your own image tables in the oracle db. The alternative is to use a last modified field in the table being imported.

You only need to create immage tables where there is lots of data, typically more than one million rows

Please can you send me more details on the project. It's good to understand where you are coming from, if you send me your rpd I can provide some feedback

Mobile: 07801 480900

Thursday, June 14, 2007

Analytics Projects - Who wants one?

Whilst looking around for my next client I realise that we, as Analytics consultants, need to talk to each other more. Whats more, the consultancies and clients really need a place to go for reliable, experienced Analytics specialists.

I was discussing one new project with Haider and it turns out he worked on it before. It's a small world, and Siebel Analytics has a relatively small client base (my wild guess is 400-500 UK clients, half of which are active).
I know about 75 Analytics consultants. There are probably another 100 out there, perm employees, that may one day become freelance and pop up in the Majendi radar.

At the moment the Agencies have a good control on the Market. There are some that put out adverts for non existant jobs. They are useful to the liquidity of the market and some work hard for their cut of you pay. Just be careful they are not taking too much, the best ones have a fixed percentage with their clients. More than 15% is excessive.

So how do we help consultants? We probably should look at the networking capabilty that's out there, linkedIn, Xing, ecademy, Yahoo, etc, and perhaps set-up an OBIEE/Analytics forum. There we could share thoughts on prices to charge, new projects, help needed, technical stuff, etc. I have started one group on Yahoo, if you fancy joining in, let me know - it's invite only. I'm not sure if this helps the clients though.

Any thoughts?

On the subject of rate. You are what you charge. If you charge a cheap rate, your will be percieved as local quality, if you charge a premium rate you should deliver a premium level of service.
What are those rates? My view is that a certified consultant with 2 years (min 3 projects) Analytics consultant should be charging a minimum of 600 GBP per day. Lead consultants, with 7+ project should be GBP 750 per day. The consultancies will obviously need to charge clients a higher level. Some of the cheaper ones are charging 6-700 euro per day, the better ones, with good experience and efficient work, will be charging 900 plus.
One client I saw recently had a greatdeal with a low cost provider, the problem was they didn;t trust the work, and the estimate for time was nearly double what was really required. The client ends up paying nearly the same for inferior work. Somehow we need to educate that quality is very important to them, not cheapness.

Insurance time

I just had to renew the Professional Indemnity insurance, which this years comes to over 1,300 pounds!

We use Trafalgar, seem to provide good cover, but not sure if they are the cheapest. However, you can do it all online which saves a huge amount of time.

Use the link to get a quote.

Dear *NAME*,

This mail has been sent to you by *YOUR FRIEND* from *YOUR COMPANY* through our website.

Since 1995, Trafalgar Risk Management has focused on providing computer contractors with specialist insurance cover – Professional Indemnity; Tax & VAT Investigation; Employers’ & Public Liability.

Using state of the art computer systems we can provide quotes; accept applications; accept premium payment and allow you to print out all your relevant documentation out online – within minutes!

Furthermore, by streamlining our purchase & renewal procedures, the reduction in administration costs can be passed back to you, as can be seen in the extremely competitive premiums we offer.

We hope the above will be of interest to you and for further information please visit our website through the following link:


Trafalgar Internet Sales
Trafalgar Risk Management
The Lloyds Building
12 Leadenhall Street
London EC3V 1LP
Tel: 0845 685 2610
Fax: 0845 685 2611

Trafalgar Risk Management Limited is authorised and regulated by The Financial Services Authority under registration number 311662.

Trafalgar Risk Management Limited is a member of the British Insurance Brokers Association

Tuesday, June 05, 2007

Majendi Day out pics

The team went for a day out on the Company Yacht!


Some say "SCIPE" others "SkyPee"

Whichever you say, it's worth getting it. Free calls. We use it for conferencing.

you can find me on the system as username 'adyward'

Thursday, May 31, 2007

Link to Me!

Come on you lot. Let get networking!

View Adrian Ward's profile on LinkedIn

Wednesday, May 23, 2007

The 'WebCatalogServiceSOAPKPI' Outbound Service Web Port

Here's a techie from Justin Townsend. We're working on some interesting ideas and clients (and client bids), where this is relevent.

Using the integrated application (Core Oracle/Siebel modules combined with Analytics & Marketing functionality) relies quiet heavily on the use of web services. These services are invoked in a 'traditional' SOAP interface, providing the communication links between the applications.

In a standard installation of the integrated application, web services are used for: pass-through authentication, execution of Marketing jobs and serving up of Analytics and Marketing resources within the integrated environment.

WebCatalogServiceSOAPKPI, as the name may suggest, serves up resources from the Analytics application which are stored in the web catalog file. Using this service you can access: dashboards, requests, iBots, segments, segment trees and list format definitions.

This WebCatalogServiceSOAPKPI is particularly important where the business has many pre-defined reports (visible on dashboards or via requests). In combination with the definition of a symbolic URL, the web service serves up these pages to the user when they access dashboards related screens in the integrated application.

Symbolic URLs are defined in the core Siebel application; when the application's services are started up, the definitions for these symbolic URLs are stored in the web server cache.

When making changes to symbolic URLs, you need to ensure the cache is cleared to make sure the amended definitions are used by the web service.

Symbolic URLs are defined in:

Administration - Integration > Symbolic URL Administration;

the cache is cleared in:

Administration - Marketing > Servers, Outbound Web Service Port (hyperlink) > Outbound Web Services

Tuesday, May 15, 2007

What Does Metadata mean

I'm i the middle of writing the book (along with Mark) and I was using the word Metadta alot. Then something happened on a client project which made me realise that few people probably understand what this word actually means.

The OraFaq makes a good start
"Data that is used to describe other data"

But does that clear it up?

How about
"Metadata is information about a particular data set which may describe, for example, how, when, and by whom it was received, created, accessed, and/or modified and how it is formatted. Some metadata, such as file dates and sizes, can easily be seen by users; other metadata can be hidden or embedded and unavailable to computer users who are not technically adept"

Every other definition I see just says "Data about data"

So that clears it up then!

Thursday, May 10, 2007

More from Oracle

I have been to two Oracle partner events recently, where I am mainly trying to ge tot he bottom of what their ETL tool strategy is.

The issue is on of platform AND application choice.

The fight is between Informatica, OWB and Data Integrator.

Informatica has a huge head start in the Siebel OLTP to warehouse ETL. The process is built and up-and-runnning in many companies.

However, Oracle just bought Sunopsis (and their Data Integrator tool). They also have OWB.

At one event the salesman assured me that Informatica was still the number one choice for CRM ETL, and there are no plans to replace it - it would just cost them too much money to rewrite everything.

At another event I saw this :

Oracle Only
•OBI – Standard Edition
•Oracle Warehouse Builder
•Oracle Fusion Intelligence (Requires OBI-EE)
•. . . . . . . . . . .

•OBI – Enterprise Edition
•Oracle Data Integrator
•Oracle BI Applications
•. . . . . . .

No mention of Informatica!

I'm off to build a new databae and ETL using Data Integrator...

Tuesday, May 08, 2007

Away day

Just got back from our away day. Great fun, talking shop, eating good food, going sailing and meeting up with the lads.

Revealing photos to follow!

Thursday, May 03, 2007

Loyalty Anyone?

Has anyone seen an Analytics repository which connects to the Loyalty vertical? We may be able to come to a commercial agreement with anyone has already built one.

Wednesday, May 02, 2007

My New Yahoo group

I'm going group crazy

Just added a fourth Yahoo group. They're great to share stuff with your mates. We have some club messages and information that needs sharing, so we put this is the group and hey presto everyone is up-to-date. No excuses!

Click here to join papercourt
Click to join papercourt

Monday, April 23, 2007

The Future of CRM

I attended one of Oracle's 'Ask the Experts' seminars on CRM strategy last week, not some thing we often have the time to do, but worth doing for an insight into what makes Oracle tick, and more importantly to meet some real people, many of whom at this particular event were not existing Oracle customers.

The first speaker was a research VP from Gartner, he offered some interesting feedback from their research, most of which should cheer up those of us in the business of configuring CRM apps. Generally speaking they have found there are still plenty of sizable organisations who have still to invest in a CRM solution providing a Single Customer View (the holy grail of CRM).
Their headline statement 'CRM is back in a big way' underlines this, however he went on to say that the majority of current projects are upgrades or reviews adding additional functionality or enhancing existing capabilities.

Marketing, sales and customer service are top of the list of areas perceived most likely to benefit from CRM investment, but it's clear that there are still reservations amongst top level management about the tangible benefits of this investment, and how to measure this. Thankfully, on average Analytics is rated third priority across all three of these areas, usually behind increasing customer satisfaction and improving cross selling opportunities. So more good news for those of us in the analytics business.

However, the flip side to all this optimism is that Gartner predict that by 2009 50% of new investment in this area will be in SaaS, or Software as a Service, also very interesting was the observation that some projects may be delayed until 2008, as purchasers wait for the current round of new releases to settle down and prove themselves.

Next up was Oracle's Sales Consulting Director who had a huge number of slides in his presentation explaining Oracle's current CRM strategy, I got the impression from questions asked by the floor, that this is what prospective purchasers, and some who bought Oracle CRM products pre the Siebel merger, were really interested in. The huge amount of information delivered was quite overwhelming as they tried to encompass JD Edwards, PeopleSoft and Siebel users to make all feel welcome, without making existing Oracle customers feel they might now have a second tier product. In practice the future is Oracle Fusion, a suite of middleware apps to link together all the products now in the Oracle stable, this should allow relatively seem less integration of for example analytics, with any of the CRM/ERP apps offered, with Oracle undertaking to develop these apps in parallel and not force upgrades or conversions on any customer, for Siebel the future 8.1 and 8.2 releases are said to be in the pipeline already.
As reassurance for Siebel users Oracle are using Siebel internally now, with Siebel Contact Centre underpinning the Oracle support team.

Next, a very nice lady from Irish Life, Ireland's biggest life assurance company, gave an interesting presentation on their experiences implementing Siebel, originally a very customised version 6, and currently an upgrade to 7.8, which will now be around 90% vanilla Siebel. The message being that they learned their lesson, and some times less really is more.

So finally we got to the demonstration of Siebel 8, unfortunately time was against us and rumbling tums could be heard, new features include Full System Search, Embedded Analytics, Electronic Signature Capture for mobile clients, task based UIs and a plain English rules engine to aid workflow and control data input.
Then there was a stampeed to the buffet!

Thursday, April 12, 2007

Init Blocks, init.

That's repository variable Initialization Blocks to you. Init blocks as they are more commomnly known retrieve the data that is held in memory by the Session and Repository variables associated with them. They are of course accessed through Manage>Variables in the Administration console, and this is another area where subtle changes have been made for Oracle BIEE, to add a new init block open the Variable Manager, click Initialization Blocks and then right click in the righ pane or choose Action>New>Repository>Initialization Block.

Then the configuration itself, no more tabs, one form with buttons.

Edit Data Source button gives access
to further options.

Edit Data Target launches what used to be on the Variables tab, click here to name your variables.

Anyway, enough pictures, click on Edit Data Source to reveal the Init Block form where you will compose the initialization String, aka the SQL. You can now select db specific SQL if you wish, and now is also the time to Browse for your connection pool.

Oracle has two functions for returning the current date, SYSDATE and CURRENT_DATE, both should be selected from system dummy table DUAL, as this will always return only one row, sysdate returns the current date on the server.

You can convert to and from datetime types using the following functions;
TO_CHAR ({datetime| interval}, format)
TO_DATE (string, format)
TO_TIMESTAMP (string, format) (Oracle 10g onwards)

To return a specific element of the current date use the EXTRACT function;


CAST(value as datatype) If value is a string it must match the default text representation of the datatype, so for example

CAST (’27-Mar-2007’ AS DATE) may work where
CAST(’27-03-2007’ AS DATE

In the following example note how the case format applied to the format part of the TO_CHAR function is used to format the results ‘TUESDAY’ and ‘March’, this could of course have a crucial effect if the value is used in a filter.
Thanks to Oracle’s built in LAST_DAY function, the formula to find the last day of the month, 3 months hence, is simple.

As an alternative method you could of course W_DAY_D as the source for the majority of your dates, pre-calculated columns such as cal_month, cal_week and week_ago_dt will make function statements shorter and easier to read. The SQL below extracts much the same as the previous one with addition of two Julian dates at the end, note also that you can use comments in these statements, always a good idea.


ADD_MONTHS((CAST(DAY_DT AS DATE) - EXTRACT(DAY FROM CAST(DAY_DT AS DATE))+1),1)-1, /* Last day of previous month */


Tuesday, March 20, 2007

Naughty 7.9

Apparently there is a little buggette

"Customers installing Oracle BI Applications Version 7.9 must first install Oracle Business Intelligence Enterprise Edition 10g Release 3 version with Quick Fix 150. "

tut tut

7.9 Is here, Huh?

So you thought that OBI EE was the new Analytics. Think again. Siebel analytics has jsut gone into version 7.9, but so far I have only seen Applications 7.9, so it looks like platform is either 7.8 or OBI EE :

There new names though:

Siebel Analytics Application now becomes

Oracle® Business Intelligence Applications (OBIA)

Siebel Analytics Repository now becomes
Oracle Business Intelligence Applications repository (OBIAR?)

Spotted the trend yet!!

This is from the documentation.
Changes to Oracle BI Repository
The Siebel Analytics repository (SiebelAnalytics.rpd) was renamed to the Oracle Business
Intelligence Applications repository (OracleBIApplicationsApps.rpd).
The Oracle BI repository contains the following changes:
■ Naming conventions. Logical tables have been renamed to start either with "Dim," to indicate
dimension tables, or "Fact," to indicate fact tables.
■ Data security. The position security design was modified to use the new table W_POSITION_DH instead of W_PARTY_LOGIN. For more information about data security, see the Oracle Business Intelligence Applications Installation and Configuration Guide.
■ Presentation layer. Some old catalogs have been replaced with new catalogs. The catalogs that
have been replaced are designated with a STOP icon in the presentation layer; these catalogs
will be deleted in a future release. It is highly recommended that you migrate your reports to the corresponding new catalogs.
■ Time dimension. A common time dimension is used in all new catalogs to indicate a unified time dimension across all facts. Some secondary date dimensions are still exposed when necessary, but they are not conformed to all facts, and, thus, should be used cautiously.

I never did trust using the W_PARTY_LOGIN so I'm glad it has gone. I'll let you knwo what I think of the replacement when I have tested it.
Good to see a common time dimension, but how does it work? Again mre testing will tell.

Connecting People

Is there a clear winner in the war for Networking platforms?

I started using LinkedIn a while back and have developed a great network of business contacts, but then I realised that wasn't the first one I had signe dup to. Friends Reunited was the leader originally and everyone flocked onto the system - until they started charging for it - I was quickly out of there but not before they had made their millions. I wonder if they would have made more money by not charging but by going down the free model with extra paid services?

View Adrian Ward's profile on LinkedIn

Next came Xing (was previously OpenBC). This is exactly the same as Linked in but based in Europe (Linked In is avery American).


But now I see that IT Toolbox is getting in on the act. Do I really have to maintain a profile and network there too?

Then I get an email from someone who has a neat business card attached, from Plaxo. Looks like I should have that too.

Of course there's messaging too, obviously I went for MSN - everyone did, now Yahoo looks pretty good and half my contacts on on that, but along comes Skype and it's another list to maintain.

In the end I think I'll be spending all day working on my online presence and not actually talking to people. Why can't I just get one system that talks to every other system (hey isn't that what customers were asking for 20 years ago out of IT).

Come on Microsoft, help me out here - use your dominacne to buy all these companies and shut them down - I only want one system.

Monday, March 19, 2007

All gone

Just 4 hours later and the snow is gone

Snow wave

Last week i was in t.shirt s now i need some skis to get to work.

Friday, March 09, 2007

Make the most of your repository

Quick and Easy Mail Merge

Why not make the most of your repository, as well providing the backbone of your reports it is also a great source of clean organised data that is easily accessible by other programs. Here’s one way to address envelopes or letters using MS Access and Word.

Create a new blank database in Access.

Create a new query in design mode, close the Show Table dialogue and go to Query>SQL Specific>Pass-Through.

Now click on the Properties icon on the toolbar and click the ellipsis next to ODBC Connect Str, this will open the Select Data Source dialogue.

Click the Machine Data Source tab and click New and follow the instructions to create a Data Source Name based on Oracle BI Server from the drivers list, follow the instructions and enter appropriate user names and passwords to suit your systems configuration. Enter your repository user name and password and answer yes to save this information in the connection string.

Type your SQL query. Click the icon to run the query and test you SQL.

Now create another new query in design view, click the queries tab in the Show Table dialogue and select your pass through query, add the columns to the new query.

Click the drop down arrow next to the Query Type icon and select Make-Table Query, save the query with an appropriate name, and click the Run Query icon.

You will be presented with a warning about the number of records to be pasted into the table, dependent on your needs you may decide to set some selection criteria to break the list into more manageable chunks, this may depend on whether you are merging to a letter, labels or envelopes. You could create multiple Make- Table queries to populate appropriately named tables based on select by County, State, City or Name beginning with…... They can all be based on the one pass through query.

If you have the coding skills you can automate the running of these queries and refreshing of the tables.

Once the make table query has run, click Tables in the objects list and you will see the table you have created, select it navigate to the Tools>Office Links>Merge it with Microsoft Office Word, or click the down arrow next to the Office Links icon, follow the instructions in the dialogue, simple.

My friend Carol

Nice new top

Wednesday, March 07, 2007

An MI Strategy for ….?

I was doing some research today and decided I needed to reference some work I did TEN years ago.
This is not such a big deal, I keep everything. Absolutely everything.

All my emails, texts, databases, spreadsheets, code – the lot. With storage technology so cheap this is not such a difficult thing to do.

The only hurdle is to remember old passwords. I don’t have a password repository (keep meaning to get one though) so sometimes have to resort to OfficeKey – It tells me what the passwords are on my Office files.

Whilst looking I came across an article which amused me, it’s not funny, it’s just that it’s still relevant today, 10 years later.

Her are some extracts
“This document lays out some ideas on how to manage the gathering and assimilation of the necessary information to help run the Claims function of XYZ.

There are three main areas to consider for capturing information

  • Claims,
  • Telephones and
  • People/Costs.

For each I have laid out some ideas on principles and methods that could be adopted.

The second part of the document looks at who and specifically what is needed for effective Information Management(IM).”

So, XYZ had some Information needs, and I’ll bet they still do!!


Efforts should be made to get the <> data onto an OPEN platform in a full Data Warehouse.
I would recommend SQL Server, Oracle, DB2 or similar package.

Specific application development should be limited to 'must have for operational effectiveness’ systems.

If the data is on an open platform then user friendly packages such as Crystal reports, Access and Ms Query can extract quickly and cheaply the information required. The added benefit of this method is that there is a transfer of skills from IT to the Claims department.

Whilst the IT department is the guardian of the data they should not be the owners. Keeping the data on the mainframe and developing small but expensive solutions to use the data only serves the IT department not the users."

IT not the owners of data, heresy!

"Telephone Activity

All Aspect related data is already kept in a data warehouse on an Open platform. The data is available to view and download using Crystal Reports.
Relevant extracts should be taken from the warehouse and put into a Claims Information System.

Only one system should be used for reporting Claims Telephone activity to all users within the department, and outside of it. All reporting activity undertaken by Team leaders and others within the Claims dept. should stop and be given to one person/team.

Now why did I say this bit? All users should stop creating reports – Sounds mad but actually when you see HOW they are doing it, then they should stop – everyone was downloading some data into Access or Excel and creating their own reports. No-one managed with the same set of consistent KPI’s.

People/Cost Management

There is now more focus on individual, team, section and department performance. To aid this process Claims are currently using Workware. Having searched the market on several occasions I have not found a better product than Workware for planning and monitoring workloads. The alternative would be to use spreadsheets which I have seen in action but they become unstable and difficult to manage very quickly. I understand that Workware is in constant development and therefore I would recommend that the software is used after the initial period.

The normal Absent, sickness and holiday planning and monitoring should be integrated with roster production and performance tracking. This integration can be developed further by including training plans/actual courses, skills matrices and PPP production. The main aim must be to reduce the re-keying of staff lists and the elimination ‘re-inventing of the wheel’

With the advent of section managers taking responsibility for the cost centres representing their span of control it is more important than ever that cost information is captured, verified and analysed.

There are two main sources to aid cost centre management;
Finance reports
Personnel Databases

Each month the cost centre reports need to be checked for their accuracy, i.e. people salaries in the right cost centre.

All costs should be tracked over time and used in ratio analysis, such as cost per Claim Closed.

All costs that are incurred in a month should be shown in the cost centre reports. It has been common practice for the temp staff costs and other invoices to be shown in the accounts 2-3 months after they were incurred. This gives a false picture as to what is going on and can lead to poor decisions being made with regards to expenditure.

The budgeting process within claims should not be a huge exercise, which is separate from normal business planning. Re-forecasting the next 12-16 months should be a regular exercise, perhaps linked to the overall plans within Workware.

An understanding of settlement cost behavior must be developed in order to determine where resources are best deployed. This is best achieved by tapping into the skills of the actuarial department.

For all new projects and tactical/strategic decision making there should be an understanding of the impact from a cost point of view. Models must be developed to add weight to the decision or prevent mistakes."

WOW, Is Workware really that good. Can you run a large number of teams using it? I thought so at the time, and I still think so.

Now this is the good bit….

"Information Management

As a general rule all information should be available to all (with a few exceptions)
Ideally all information users should be identified before solving their needs for them!
Data should only be captured and information created if it aids decision making, such that the decision is influenced by having the data. For example keeping sickness records allows for changes to pay if the threshold is reached.

A small team 2/3 FTE should be created within claims. These people can be the local 'IT' experts but with a good knowledge of claims Dept. processes. They can facilitate the routine information needs and provide detailed analysis on Claims activity, individual performance and other ad-hoc. They can also be used to help improve the PC skills across the department, but should not be used as a 'Help desk'.

Any data capture and report production currently undertaken elsewhere within the Claims dept. should be brought within the control of the MI team. This includes all reports supplied by Secretaries, Team Leaders, Section managers and support areas.

The MI team should also be capable of supporting the use of Workware (or whatever system is used for work management)

The MI team should make full use of the latest available technology and not be held back by XYZ Co. mass standard PC policies, i.e. Use Crystal Reports and Intranet technology and NOT the current standard Office95.

To capture, analyse and report data a Claims Information System(CIS) should be developed using Ms Office technology. This is not an all singing-all dancing solution to all information needs but an integrated set of databases and spreadsheets that minimise the effort required and reduce errors in keying. The creation and maintenance of the system should be the sole responsibility of the members of the MI team.

To deliver information to end users the ideal solution is by the use of an intranet. This is a cost effective method of getting data to the desktop of the users. This can be introduced quickly and cheaply and used for all department information and not just for activity reports e.g. Working Practices can be kept online and updated easily. This solution is available now to all those with a PC.

The profile of information in general needs to be raised across the whole dept. For example; use can be made of notice boards to show graphs of output, service and quality. The whole dept should know what the MI team are producing and what data they hold so that tasks are not duplicated.

The Claims management should fully support the work of the MI team, especially in the data capture and processes they introduce. The MI team should be able to supply information directly to the Claims manager, or any manager without fear, i.e. do not shoot the messenger if the results are not favorable.


To enable a better decision making process more information is required by all members of the Claims department. This can be enabled in a cost-effective manner by Claims staff using their own skill and knowledge. The specialists’ within the IT department or elsewhere should not dictate when and how the Claims staff get the information they require, and expensive development work is not always the answer (Just think about what has been delivered to date!). A small team with skills in communication, claims and IT within the claims dept can deliver when and how YOU want them to.

Powerful stuff. Information available to all, using Web technology. I wish I had seen Siebel Analytics back then (when did NQuire start up?)

Obviously the tools are now there to reduce the need for Office applications (Access Db’s for reporting), specifically Oracle BI (ex Siebel Analytics), Essbase, Microstrategy and dozens of others.

I wonder if XYZ Co ever put a reporting system in?

Saturday, March 03, 2007


Just showing off the new phone:-)


I'm sending this from my mobile. We're at reading watching the kids enjoying their sailing

Friday, March 02, 2007

Oracle to buy Hyperion!

Ha. Good for Oracle.

Essbase is a horrible tool in my view but the accoutants love it, so Oracle have taken on a popular product, and have a foothold in hundreds more companies.

Trouble is, now I'm stumped as to how they are going to integrate Essbase into the new Oracle BI strategy, can and should Analytics work alongside (or on top of) Essbase cubes?

Also, I thought that the majority of Essbase systems were based on other database technologies.

Who next? Cognos? Business Objects themselves! SAS!!!

News item here

Thursday, March 01, 2007

Bad News

Just heard that the Keel is still not attached to the bottom of our boat, and there's no mast either!

Looks like we're going to miss the first race of the season. Damn.

New Phone

Just a quck update to say that I ended up stayng with O2 but got a new phone, the Sony w880i

I just phoned them up to say I was going to switch and they haggled a bit, I pointed out better deals on Three and they matched them.

Now I'm due to pay £17.50 per month for a year for 600 minutes and 500 texts. Thats still only 20 minutes per day but it does work out at 2.9 pence per minute.

The phone looks really cool too


Wednesday, February 28, 2007

How good IS good?

I've been interviewing loads of candidates recently (you may have seen my ads on Jobserve and LinkedIn) for Siebel Analytics consultants.

By the way, if you would like to connect on linkedIn ( please send an invite to; in the note say you are reading the blog.

Most of the interviewees have stated that they are pretty good at developing Analytics applications. One said that he was an expert and knew all of the components in detail.

So, to test this I ask a few simple questions (see my previous blog for some examples). Before long it becomes obvious that they have over estimated their own ability. This makes for an awkward end to the interview because it's obvious that they have been exposed. So why do they do it? Do people really believe that they know the system like the back of their hand, or is it bluff to try and get past the interview and into the job where they will learn?

Perhaps my questions are too difficult?

Try this one, and see what you think.

Q. What Types of Physical Table are there?

A. There are 4 types of Physical table

- Ordinary
- Alias
- Stored Procedure
- Select

Not really a difficult questions, is it?

But no-one could answer it.

Whilst I’m on the subject of tables, the new OBI EE has a slightly different way of creating Aliases.

You now have to include the Orignial underlying Table in the model (something I always do but found others do not). Right click on the table and select New Object.. Alias.

Monday, February 26, 2007


I have the worst phone in the world.

It's the O2 XDA lli. Oh it may also be a PDA, has windows, can pay music, runs Excel/Word/PDF, has a web browser to connect to the internet, has email and can syncronise with my Outlook, has vidoe and a camera, plays games; but it's the worst phone in the world.

You'd think that is was brilliant with all those features, but sometimes you just need a phone.

You know how you see people on the tube holding on with one had whilst texting their mates with the other - I can't do that (there are no keys on the xda and it a big bulky thing).
Voice activate their phone to call their mates whilst driving - I can't do that
Have video calls with the kids - I can't do that.

and the battery only last two days.

It doesn't even connect to the web very well, it took me two years to get it configured and it's rubbish.

So, I have finally given up. I'm now looking for a new phone.

But here's the catch - last time I went looking for a great phone the xda sounded fantastic - All those features!! wow gotta have it. Now hate it.

for the new phone,
Do I get 3G?
Should I get Balckberry?
What about the camera, 2 megapixels or 3?
Do I need PDF viewer?
What about my Sudoku game I bought for the xda and Dope wars?
Do I need an RSS viewer?

Then there the question of service provider.
  • o2? (who I now hate),
  • three?,
  • Vodaphone? (no because I don't like Michael Schumaker),
  • Virgin Media? (got their broadband and it's really good).

And what price package? Turns out that o2 200 means paying £60 per month not £35.

The main problem is size (isn't it always the case!) For applications, games, emails, etc you need a decent size screen, but for normal phone operations you only need a 40mm square, and the ability to hold the thing in one hand, texting.

Is there a phone out there that has a fold out screen - ideally I would like 15 inch screen, and also a folder out keyboard for faster typing, runs applications and plays games.

You know, I think I'll just go and buy a data card for the laptop and go and get the smallest 3g phone in the world!

I'll get the wrong one, but it'll give me somethign to moan about.

Friday, February 23, 2007

On the bandwagon....again

Well if we are all spilling the beans on where we're going this year then here is my diary...

Spring Series - Warsash

One race each Sunday, starting March 11th. We have a full crew and repaired boat. Lets hope it's not too cold.

Cowes Week

August 4th - 6 days racing, 6 days partying. Cobra are sponsoring the beer, Planet Pursuits are sponsoring the clothing again. Need to find our Dobby.
Still have 4 places to fill.

Little Britain Challenge

September 6th-9th. A great opportunity to network in the building trade!

All the above on Beefeater, our Sigma38. She used to be called Yeoman and was sailed by Prince Phillip in Cowes week.

Zone Squad

(The RYA Team GBR that my son is in)
Hayling Island - March 24th/25th
Rutland WATER - April 14th.

Lake Garda

Big international event for Oppies with 500 boats expected.

IOCA National Events

Inland Championships - May - Grafham - Two days
National Championships - July 28th - August 4th (+plus pre-event training)
EOF - End of Seasons – October 18th/19th

Open Circuit

Warsash – May
Maidenhead – May
Island of Wight - June
Lymmington Main fleet - June
Lymmington Regatta Fleet - June
Hayling Island - June
Wraysbury - June
Mudeford - July
Papercourt - September
Poole- September
Chichester - September
Spinnaker Open - September

Now you know where I'll be during the weekends and holidays.

As for work time, probably on client site - Don't have too much time to go to conferences. Not that I'd know which one to go to anyway. Is there one for Siebel Analytics experts who knows a thing or two about sailing?

I’m also going to try to fit in a RIB treasure hunt so watch this space.....

Thursday, February 22, 2007

Documentation, It's MAD!

Let's face it, there's not many people who like documentation, except a good BA of course, but every single project needs it.

Now if the title and first sentence haven't put you off I'm going to let you into a few secrets.

1. A good set of design docs will save your project money
2. Post project documentation (actual designs, architecture maps, etc) will rarely, if ever, get read
3. Your documentation will be instantly out-of-date with release x.1

Still with me? OK, they're not really secrets, but they are issues we have to contend with. The motivation to write something detailed that will never get read and will be obsolete in 5 minutes, without the proper tools is pretty low. Especially if you have just had the go-live party the night before :)

So a typical Analytics (Oracle BI EE for the late comers) will need the following

a. Design of Datawarehouse - This includes Tables, fields, indices, schemas, tablespaces, parameters, views, Materialized views, etc
b. Design of DAC - EP's, Subjects, Table Groups, Tables, Indices (again!), Tasks
c. Design of ETL
d. Design of Analytics Repository (Oracle BI Repository)
e. Design of Webcat
f. Integration designs
g. Technical Architecture Map
h. Support Guide - What to do if.... scenarios

what did I miss?

(We are going to assume Analytics with Informatica ETL for this example)

Here's the catch. You have a reference to a physical table in at least four places - Db, DAC, Informatica, Physical layer. In your design you want to ensure referential integrity, i.e. don't refer to a table in the DAC that does not actually exist in the Db!. Another example where you have this is with tasks in the DAC that refer to Informatica Workflows.

My prefered approach is to create all these documents up front, i.e. in the design phase (say that last bit slowly with a deep voice), then update the documents at the end for what actually got built.

What tools do you have?

Most project sites that I go to will have MS Office, but you really need the professional version for Access and Visio Professional. You could try to publish the designs in Word or HTML, and you could even use Analytics to present the design!!
The Word vesion is probably the easiest (least technical) but requires most work, the Analytics version will need a place to be set up, with Webserver, db and Analytics server licence.

What do I use?

Glad you asked that because we have developed the MAD - Majendi Analytics Designer, system. This is a semi automated system that can document an existing Analytics aplication, from DB to Webcat. It produces MS Word documents which details all of the items in a) to e) above. I supplement this with Visio diagrams for star schemas (This also uses the design repository for the source tables).
As well as the Word documents templates being populated we also have a miniture Analytics system which details the meta data. The heart of the system is a set of Access databases which import metadata from txt files or directly using ADO (so it can work on any database) to conect to the databases. A side benefit is that building the system teaches you all the underlying data structures in the ETL and DAC.

How do you get the information you need?

You can query the repositories directly in the Oracle/SQL database.
You can use the documentation utilities of Analytics and the Web catalog manager
I also use the UDML file that you can get from Analytics, and parse this into an Access Db.

(If you want to know about UDML files I'll be posting a short note on that later).

The beauty about the MAD system is that I can document a whole complex system in a couple of days, even better is the ability to analyse an existing application to help with an upgrade, or with new releases - your documents can very quickly reflect the underlying new system.

So there you have it, Documentation may be boring but you can make it interesteing by using your technolgy skills to reduce the problem down to automated tasks, just like we did. :)

Thursday, February 15, 2007

Questions, Questions

Why have I not written a blog for ages ?
Does Siebel Analytics work on IE7 ?
Is there a version 8 of Analytics, if so what is it called ?
What is the difference between Siebel Analytics and Oracle BIEE ?
Where are all the best Analytics consultants ?
How do I find good crew for the next racing series ?
Is disconnected Analytics a good idea despite the technical challenges ?
How do you get more skilled in Analytics ?
What is the MAD system ?
What makes a good consultant ?
What does Siebel 8 look like ?
Is it better to be an employee or go contracting ?
When is a good time to buys flights to Portugal ?
Where is the next big Analytics Project ?
How do I pay my tax bill ?
Should I go on the Marketing Server Architect course ?
What technical questions should I ask an Analytics candidate ?

I better stop there. Since my last blog things have been busy, very busy. During that time I have been trying to think of a subject to write about, but so many things come to mind. Should it be technical, personal (to show that I'm a nice guy!) or political (don't get me started). So I'm going to give my views on all of the above.

Why have I not written a blog for ages ?

Because I went on a skiing holiday, went live at the Royal Mal on their 7.8 project, spent every weekend taking the kids sailing, did my tax return, invoiced clients, did my LTd company accounts for 2005/2006, had Xmas lunches and dinners(the Majendi one was really good), watched some rugby, developed the MAD system, had a kids night out due to sons birthday, paid lots of bills, updated the Majendi Accounts system, interviewed some candidates, ran some Analytics master classes and finally got some sleep. phew!

Does Siebel Analytics work on IE7 ?

I installed Analytics on my new laptop this week and it seemed to work fine. I've had some trouble with Oracle 10g Enterprise manager. It became corrupted so I had to uninstall everything and start again. EM is now a web based system which looks much better than the previous version and is easy to use but this corruption has been a royal pain.

Is there a version 8 of Analytics, if so what is it called ?

I don't think so. I've had a good look at the Oracle website and can still only find 7.8.5. I'll keep my eye open for news on this front, but let me know if you find anything.

What is the difference between Siebel Analytics and Oracle BIEE ?

Good question. I installed both this week on my Laptop, BIEE first. The first product worked well except the manual said the username/pwd was admin/admin, turns out it was Administrator/Administrator - Thanks! I liked the xml Publisher which is new, but the rest was mainly a renamed Analytics system, placed into a different folder structure, for example, C:\OracleBI\Server\.
Oracle BIEE looks like it's just a replacement for Analytics Platform, I can't see where you get the applications part from, other than installing Analytics Applications itself. When I tried this, it recognised that there was an existing Analytics platform so didn't create it owns structures (e.g. C:\SiebelAnalytics). I'll investigate more this week so let me know if you have a specific issues.
(I'm off to check Mark Rittman blog for more details)

Where are all the best Analytics consultants ?

I wish I knew. Majendi is always looking for consultants who have lots of experience with Analytics. I am looking for permanent consultants for Inforte. If you know of anyone who has at least a couple of Analytics project sunder their belts then please let me know ASAP.

How do I find good crew for the next racing series ?

I nearly didn't enter the Warsash Spring Series due to lack of crew. Biggest problem is that one of our team is getting married in the middle of the series, even worse, he's having a stag do the week before so taking out both weekends.
I would like to be competitive so need the best possible sailors. After some badgering I've now got a team that can compete most weeks so we should get better as the series progresses. Follow our results at WSS.

Is disconnected Analytics a good idea despite the technical challenges ?

I still don't think so. In the age of 3G and broadband everywhere it must be far better to get people to work online than go through the major hassle of disconnected Analytics

How do you get more skilled in Analytics ?

I read the ITToolbox posting with a mixture of glee and annoyance. So many beginners are being placed on analytics projects that clearly have not been on a course. They ask stupid questions which demonstrate a laziness to read the manuals, support web and even previous toolbox postings. The consulting firms that hire these people are not investing properly in their people and dong their clients a disservice. The clients who outsource to the cheapskates get the system they deserve, but the cost is that the general image of Analytics is undermined and investment is moved to other reporting tools. As an industry we really are shooting ourselves in the foot. I would like to see Oracle step in and really classify their partners so the clients know what they are getting.

What is the MAD system ?

It's a brilliant Analytics Design and Documenting system that we use to design a and document Analytics systems. It will analyse your current system and look for redundancy and flaws. It saves me weeks in a project and removes the boring work. I'm trying to get some reverse engineering in there now, along with embedded Visio diagrams.
Perhaps I should sell it?

What makes a good consultant ?

Patience, hard work and diplomacy. One of my pet hates is to see consultants surfing the net in Client time, but there are other things which are more subtle like remaining positive about Analytics even when it does do what you want - never let the client see you undermine the product as it is our lifeblood; Also, Keep you politics to yourself, even outside the office. You are never off duty with a client.

Is it better to be an employee or go contracting ?

On balance, being an employee gives you a better lifestyle. Having spent half my working life employed the other half a contractor I am leaning towards the employed model. Whilst contracting I have spent so many nights and weekends doing accounts, payroll, tax returns, company returns, VAT returns, meetings with solicitors, accounts and HR people. On the face of it the financial benefits are greater, but this UK government has found many new ways to tax anyone who dares to start up a business, even if you get away with IR35 there is other taxes that they will apply, and There's no getting away from National Insurance. Better to find a good employer who cares about your development and sees a partnership between you than try to go it alone.

How do I pay my tax bill ?

Don't you just hate Tax, especially when the government don't have a clue how to spend the money wisely. I paid mine online, ontime; lets see if I still get a fine.

When is a good time to buys flights to Portugal ?

Last year. We saw prices on BA flights go up by the day in January. Trying to buy online the system failed several times, and at the very end of the process - all very annoying, but even worse when you log in the next day and the prices have gone up 20 pounds, and the next. Ended up paying 150 pounds more for the flights, and cut down the holiday by two days. Next time we book really early. Come on BA get your act together.

Where is the next big Analytics Project ?

I think I know! The UK analytics market is getting more mature due to the reduction in the number of new CRM installations - companies are concentrating on upgrades. The German market is going very strong so time to brush up on those accents. The majority of customers I am speaking to are interested in Marketing, which as you know has Analytics at it's core. The other hot topic is data quality - many projects are delayed or failed due to poor quality of data, so I'm seeing what can be done to smooth the process of migration and cleansing. If you have any views let me know.

Should I go on the Marketing Server Architect course ?

Oh yes. Like I just said, this area is building up a strong demand. As an Oracle partner we get discounts for Oracle courses so will be putting as many people as we can on the course.

What technical questions should I ask an Analytics candidate ?

I am forever interviewing people, mainly using technical questions, but also using some competency based q's
Here is a selection of questions that I ask (I have an AQS - Analytics Question System which I have extracted some questions from)

Questions for you to establish if they know anything about analytics

W0 Warehouse

W1 What schemas do you recommend for an Analytics system
W2 How do you save changes to data in SQL
W3 What is the structure of a PL/SQL script
W4 What tools do you use to manage databases
W5 What is the difference between an OLTP and an OLAP
W6 Do you use indexing on a staging table?

I0 Informatica

I1 How do you call a stored procedure in Informatica
I2 When do you use re-usable objects
I3 Where do you create your own mappings in the Siebel Informatica Repository
I4 How would you add a field from S_ORG_EXT_X to the warehouse


D1 What is the main purpose of the DAC
D2 What objects are in the DAC
D3 How do you add an Index for a table (existing and not existing)
D4 How do you run a full load (not incremental)
D5 How does Change capture work

Con0 Analytics Config

Con1 Where do you change the config settings
Con2 How do you turning caching on
Con3 How do you add a new style
Con4 What default logging level is best for a 1000 seat company?

R0 Repository

R1 What are the three layers
R2 How do you set session variables and how do you set Repository variables?
R3 How do you update alias table definitions?

Web0 Webcat

Web1 How do you copy requests from one webcatalog to another?

Dash0 Dashboards

Dash1 How do you create a dashboard?
Dash2 How do you set the permissions on a dashboard

Ans0 Answers

Ans1 What is the Formulae to use in a request, to see a Repository Variable?

Del0 Delivers

Del1 Where are iBots stored ?

Mkt0 Marketing

Mkt1 What is a QLI ?

Int0 Integration

Int1 What is an Action link used for?

Too many questions, and not enough time to get all the answers (How many of the above do YOU know?

Monday, January 29, 2007

Just Another Date

Well there's lucky, Justin had dates in mind and so did I! I have been fortunate to
enough to have easy access to W_DAY_D and a plethora of pre-calculated YTD and
account period end figures so all plain sailing.
As we are working on Analytics for a Siebel Financial Services implementation we
have a large number of growth comparison figures and related % figures, all fairly
easy maths, divide one by the other and multiply by 100. If you are lucky enough the
have most of the figures pre calculated this is a quick and easy task, open the LTS
for your table, create a new column, open the expression builder and create you
expression to calculate the percentage. But beware, although analytics appears to
accept quite complex statements here, there are a few pitfalls, Siebel 7.8 does not
support nested aggregation statements so you can't for example do a SUM(Case.../
SUM(case....where ..Count(...... x100.
It will let you do Count(Case when...........) / Count(case when.....) x 100, but I
would recommend you avoid this, you may well find that if the resultant column is
included in a report with other similar columns the resulting SQL can look very odd
indeed and may not be an accurate reflection of what you originally intended, this
appears especially true if the report also includes other columns sum or count
aggregation set. Look at the SQL issued via your Admin/Manage Sessions option to see
the resulting SQL.
I would recommend creating seperate calculated columns and then a final column based
on them, to provide the result from a simple expression, this may add a small
processing overhead, the sql will be a little longer, but it will be clearer and
easier to trouble shoot. You will also have reusable metrics that may be of use in
further reports or perhaps to your client should they implement ad-hoc reporting in
the future.
Next up was small issue concerning the year, or last year to be precise, as our
current project is Siebel Financials, we started off with variables for this year,
last year, two years ago and so on, whilst these appear to offer everything you
might need, beware. Come the first week of January and you may think 'hang on a
minute, this says Dec '07, '06 and '05', this is the consequence of a report that
uses THIS_YEAR, LAST_YEAR and LAST_MONTH from the date variables to report on the
last complete month and previous years. The reporting is always a month behind, so
in January '07 they are reporting on Dec '06, the solution of course is easy, and
there are probably many variations on the theme, we have used a case statement added
to the Initialization block to recognise when the month is January, eg
Use this for suitably named variable such LAST_REPORT_YEAR, if you need to report
further back use the VALUEOF(LAST_REPORT_YEAR)-1 etc to ensure the other years are
in sync with the current year.
The final part of these date related fun and games has been creating a report that
looks 3 months into the future, a rare requirement judging by the various message
boards etc which are stuffed with date range queries relating to periods past. We
decided to use more variables, 1 because we like variables and 2 because should the
business ever decide they wish to report on 6 months in the future they can either
edit the init block or use it as a template to create a new range.
We used the following query in our init block and created two variables called
As an alternative to this you could of course use similar SQL in an expression based
filter for dates between X & Y.

Thursday, January 11, 2007

Date Formats in the Analytics Application

Justin Townsend, a member of the Majendi team has some useful advice...

Dates are often a point of consternation - for users and developers alike - in the
analytics application, but there are many reasons why they are used. Dates and
their derivations are often drawn from the warehouse table W_DAY_D, but what do you
do when this table isn't available in your subject area or your repository?

This post gives an example of how dates can be manipulated to provide the format you
need. Additionally, the example format below can be used to sort data correctly at
this level of aggregation (Year/Month).

Take the example of a warehouse table with a date present, e.g. W_ASSET_D.END_DT.
The corresponding column in Answers could simply be 'End Date'.

Select the 'End Date' and hit the Results tab, you probably get something in a
format similar to this: 09-Sep-07. The format isn't so important here. When you
make changes to the date from Answers, the function is shipped down to the database
in any case. Its important though, to get your syntax right.

So we're going to create the format YYYY / MM from the date 09-Sep-07. You need a
second column in your request that you can use to create the new format, any column
will do.

We can break this date function up into a few sections:

- we need the 4-digit year (YYYY), so we can use the standard Analytics function
called Year and we get a syntax like:
Year(End Date), which gives you 2007

- then we need the constant '/'. It looks like a bit of space either side would
make it more readable, so be sure to include that. So now it probably looks a bit
more like ' / '.

- next we need the 2 digit month (MM), so we can use the standard function called
Month and the syntax looks like this:
Month(End Date), which gives you 9. That's OK, but if the date was in October then
it would be 10 so it doesn't look even. We straighten that out later on.

- then we need to bring the 3 parts above together using concatenation. The CONCAT
function will help us here. It only likes string formats so we need to convert the
date bits to text, something like:

- CAST(Year(End Date) as VARCHAR(4))
- CAST(Month(End Date) as VARCHAR(2))

N.B. The month has been cast as width 2 to account for 10, 11, 12.

Once we've cast the dates, then we can add them to a CONCAT function below:

CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CAST(Month(End Date) as

You'll notice a couple of things about the expression, we're using some
double-pipes and there's only 1 comma. The Analytics CONCAT function works only
with 2 strings separated by a comma, so CONCAT('String1','String2'). The way to
add more sections to the string is by using the || (double pipes). There appears
to be no limit on the use of double pipes.

So this format is beginning to look pretty good, for 09-Sep-07, I now get '2007 /
9'. It's not quite perfect though, with dates in a list it will not look even when
compared with date '2007 / 10':

2007 / 9
2007 / 10

We can make a change to the string after the comma to add a leading zero to all
months that aren't 2-digits long.

- so a CASE statement is needed to complete the format to our satisfaction:

CASE WHEN Month(End Date) < 10 THEN CONCAT('0', CAST(Month(End Date) as
VARCHAR(1))) ELSE CAST(Month(End Date) as VARCHAR(2)) END

To finish this off, we insert it into the original CONCAT statement after the comma
and before the final ')', the resulting expression is:

CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CASE WHEN Month(End Date) < 10
THEN CONCAT('0', CAST(Month(End Date) as VARCHAR(1))) ELSE CAST(Month(End Date) as

Now all our dates look the same, '2007 / 09' and '2007 / 10'.

If you know you're repository developer well, ask him to add the logic to the
repository. That way you won't have to reproduce it every time you need it.

Look out for more on dates in the future.

Thanks to Justin Townsend for this blog.

The Cowes

The Cowes
Cowes Racing