Hi Trev,

You are providing a good example of why the base layer of a data mart should be as detailed as possible

A grain of month, service station, product is a very high level. .

Sure, you can add attributes of the service stations but it would be normal for users to ask the 'next level down' questions pretty much as soon as you give them this data.....For example, are there any patterns for which fast food company sells more fuel on the weekend. Which you cannot do with monthly sales figures.

Since it's impossible to usefully divide the lowest level of a grain to it's constituent levels you will have to capture the detailed data and then summarise up to your current levels while also storing the more detailed data somewhere if you are to answer what are bound to be the next round of questions...

It is reasonable to expect your users to ask all sorts of detailed questions including things like sale volumes on particular days, perhaps by weather conditions, public holidays. Then later by other promotions that might even be unrelated to your company. For example if you have fast food vendors they often put on short term specials which might cause small 'spikes' in your sales, and your users might want to know about even them....

So, no, not impossible. Not even difficult if you can get the detailed sales data. So get the data in as much detail as you can and then roll it up...

Best Regards

Peter Nolan

Data Warehousing Consultant

Mobile: +353 879 581 732

-----Original Message-----

From: owner-dwlist@datawarehousing.com

Sent: 05 August 2004 08:23

To: dwlist@datawarehousing.com

Subject: dwlist: Datamart Design issue

From: "Trevor Howe" <Trevor.Howe@engenoil.com>

Hi All

I am sitting with a problematic design of one of my datamarts and was wondering if you could inform me of a solution. Here is the scenario:

I have designed a datamart for a petroleum company that reports on gasoline supplied to and gasoline sold from a chain of service stations situated around the country. My datamart has a grain of month, service station and product. My facts are gasoline purchases and gasoline sales.

Now the end users want to be able to query according to the facilities offered by the service station. Facilities might be something like: The service station is open 24 hours, the service station has an automated teller machine, the service station has a fast food outlet, etc. They can be in the form of indicators (yes no) or names (e.g. the name of the fast food outlet in the third example above). Unfortunately this does not fit in with my grain of month, service station and product. There is no way to say that the gasoline purchases can be recorded per fast food outlet - fast food outlets do not purchase and sell gasoline. In the three examples above, the users would like to see the volume of gasoline sold for service stations that are open 24 hours, or the volume of gasoline purchased for service stations that have a fast food outlet, or the volume of gasoline sold for service stations with a fast food outlet called <Fast Food Brand>.

Am I attempting the impossible.=20

Your help would be much appreciated=20

Thanks

TrevH