Me down to a tee

http://peterthomas.wordpress.com/2009/04/01/pigeonholing-a-tragedy/

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."

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.

MICS

"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 - http://jonathanlewis.wordpress.com/
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

Brilliant

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

http://hekatonkheires.blogspot.com/

Good luck with that Christian.


News UPDATE.

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.

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!).


BIG NEWS

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

A.

OBIEE Forum

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:

http://www.rittmanmead.com/rittman-mead-bi-forum-brighton-may-14th-15th-2009/.

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

So long and hope to see you there,

Adrian

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?

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?

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 !

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.

Adrian

adrian.ward@majendi.eu

Another New Blogger

Hi Grubble, Welcome to the OBIEE blogging world

http://grubble.net/index.php?blog=1

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!


http://www.bi-localizer.com/en/index.shtml

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

How is your Croatian?

New blog on OBIEE.

Good luck with it Goran.


http://108obiee.blogspot.com/


Update:

Thanks to Daan we can now view it in English
http://translate.google.com/translate?u=http://108obiee.blogspot.com/

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.

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.

The LOGICAL MODEL.

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
from
"W_XREF_PERSON_M" T5303,
"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.

Summary

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.

HAPPY CHRISTMAS

I hope that at this time of year you are able to have some fun, see the family and get some rest from those projects!

Lets keep our fingers crossed that 2009 will turn out to be a better year for the world, and in particular the hard working IT consultants :)

Have some fun at http://www.speedysanta.com/


I'll post part 3 of the Schema discussion next week


Adrian

Stars and Snowflakes

Part 2

Continuing on the discussion about OBIEE (Oracle BI EE) structure of the Logical Layer - please read Part 1 before this chapter.

To set the context, The discussion is around your choices in table design in the warehouse and I am only referring to the reporting in OBIEE on the datawarehouse, and your design considerations for Logical Tables, logical Table Sources and Physical Joins in the Physical Layer. Another point to note is that I am not too concerned about the theoretical difference between Snowflakes and Normalised, in the example shown in Part 1 you could call that a Snowflake or a Normalised design. Some have described Snowflakes as semi-normalised stars which is a fair decription, but the important point for the discussion is that there is at least one join between dimensions, which instantly makes it a non-star design, and is on the road to normalisation.

In part three I will look at a mixed model - Logical stars with normalised physical tables.

I do believe the actual dataWarehouse design and ETL routines should follow what you decide in your logical design considerations. Having said that though, I don't think that the whole reporting system can be developed in a linear path, you often have to loop back at each stage when more clarity is found.

And finally, Please join in the discussion by posting your comments.

Now, on with the blog...

From Part 1 some of you may be thinking that I'm advocating that Stars are not always required in the warehouse. Well, I tried to put together a fair case for the use of normalised design as best as I could. Now, lets have a look at the case for a pure star.

Continuing with the Contact example used in part 1, we can redraw the model in a logical and physical pure star.

Running the same report.

And obviously it works, but look at the SQL:

SELECT

T3489."ATTRIB_11" as c1,

sum(T3517."BLAH_VALUE") as c2

from "W_PERSON_DX" T3489,

"W_PERSON_F" T3517

where ( T3489."ROW_WID" = T3517."CONTACT_WID" )

group by T3489."ATTRIB_11"order by 1

So this time the join is direct in the physical layer, and therefore the server does not add in another table to get the answer.
Is this reduction in joins THE main reason why you design a pure star? Or are there other reasons why star = good?

So on the face of it there is no problem with either method. The star method was very efficient in the query, minimising the number of joins. But at what cost to the ETL process? Here, in this example, none because it joined to an existing key, however if you were talking about say, Account Id, then the mapping would have to look it up and add it to the target table. Of course the Star Transformation in the db will struggle with the normalised schema (or will it – answers on a postcard).

But. I cheated. Twice. Firstly, this is a very simple model, with little data in, so design is not vital.
Secondly, what if I want to do this …..

Part 2A

Let's build a hybrid. Surely we can use a star and introduce some level of normalisation and all will be well? Consider the following design.

The above 'star' has all the physical dimension tables in the single dimension logical table.

We have a logical star. With Physical table sources arranged in a physical star, except we have a few ‘spokes’ around some of the physical dimension tables (this is essentially what mosst people would call a Snowflake!). A common use here would be a ‘List of Values’.

Does it work? and is it 'better'?

There are two considerations

1. Errors is Analytics generating SQL
2. Duplication of data.

I'll explore these in part 3.

Stars and Snowflakes

PART 1

We like to talk about the weather in England, and today it is fine - Sunny and Cold. Here in the West at night you can see all the stars and over in the East they are having light snow falls.
Nature has not yet figured out how to combine a clear, star lit night with snow fall.
You can have one but not the other at the same time.
But can OBIEE defy the laws of nature?

A recent debate got me re-thinking about why I design like I do in OBIEE / Siebel Analytics.


Let’s look at the arguments:
There are two main contenders, Star Schema verses Normalised. Inmon vs Kimball. (I’ll not go into the details of these, I’ll assume you know what each looks like.)

Bert Scalzo says in his book on Warehousing "You should not have a star schema with snowflakes in", but he does say 'AVOID snowflakes' which suggests they are not banned completely.

However, many clients I have worked for actively encourage snowflakes. They're not actually requesting them, but they can't see the point of spending lots of money waiting for the warehouse to be ready for reporting. "The data is in there, so just report on it", they say! This is not helped by sales men and women telling clients how easy it is to develop on your existing structure. One client I worked at was convinced that the layers in the database with complex ETL was purely Intellectual stimulus for bored contractors (Who also got paid by the day!).

Then there is the real killer, the POC – Proof of Concept. Certainly proves that you can report on their data without the hassle of ETL, schedules, downtime, etc.!!!

Another project I know of is full of snowflakes, and just like the real thing, each one is unique! Again the client is happy with the design because works for the current reports.

Up until now I have been a convert to the Star Schema, slavishly making sure every table fitted the philosophy, but I need to keep the client happy, which effectively means developing cheaper. When they send their development offshore they also tend to abdicate from significant design decisions, they just want the system up and running as quickly and cheaply as possible. But that’s life, this is the new game, get over it. Compete.

Before I revisit my design principles, Can Siebel Analytics/OBIEE really cope with snowflakes? Or is it smoke and mirrors? The answer depends upon whether you are looking at a Logical Level or Physical, and how well you implement a snowflake.

On first glance you look at the Business Model and Mapping Layer as a pure Logical Level. You normally have logical tables, arranged in a logical star. And in this logical layer the training documents have always decreed that you have a star.

But can you use another logical model? What would happen if you tried to organise your logical tables in a snowflake? Do you get an error when you check for consistency? No? Why not? Surely OBIEE does not understand snowflakes and it should tell you if there is a bad design!?.
Lets have a look at a snowflake, or normalised, logical layer.


Here we have one dimension joining to the fact, and two other dimension joining to it.









So, running a report,
First the list from W_PERSON_D on its own,








Now add the Fact,










NO problem!

Including normal aggregation




Now we add one of the ‘flakes’







And still no problem.

But what if we only query the Flake (W_PERSON_DX) and the fact?

The result is …..

It works !!!

Here is the SQL being produced.

select T519."ATTRIB_11" as c1,
sum(T547."BLAH_VALUE") as c2
from
"W_PERSON_DX" T519,
"W_PERSON_D" T301,
"W_PERSON_F" T547
where ( T301."ROW_WID" = T519."ROW_WID" and T301."ROW_WID" = T547."CONTACT_WID" )
group by T519."ATTRIB_11"
order by 1

It added in the W_PERSON_D table all by itself.
Remember there is no join between PERSON_DX and PERSON_F, not physically and not logically.

So, a normalised set of tables arranged with the joins in normal form does work.

So why bother with stars?

(part2 to follow)

Here we go again

Maybe I was a little hasty with the last message.

I think I was caught up in the hype of the moment.

Can you imagine flying all the way to San Francisco, having to stand in a queue for hours along with a bunch of IT execs, and then go through a boring HP advert for an hour. After that, and all the rumour mill, anything would have sounded great.

And on the face of it the new Hardware seemd exciting enough. Oracle is pretty good as it is, put on a box designed specifcally for it and it'll be great.

But then you finally remember that we have been here before. Does anyone remember the Net PC? Larry had a great idea that we would all use computers that were just shells. All the power and storage was on the web. Sorry but we are still 10 years away from the reality on that one.
Does anyone remember the previous Oracle forray into Hardware with Dell? There are plenty of commentators around this week who do (have look at the Oracle BI blogs).

The truth is that this 'Xbox' is not their first attempt (despite their claiming it is), and Oracle has a poor track record when getting involved with hardware.

Why the previous efforts failed I do not know, but I do know that the major outsourcing companies will not fall over themselves to get the 'HP' boxes in. Can you imagine IBM really telling their clients to go out and buy this?

Let hope Oracle have learnt their lesson, and find some way for everyone to win, not just the end client in theory.



BTW Didyou notice the new layout. I'm starting a campagn for all BI blogs to use this style :)