average price fact table

View previous topic View next topic Go down

average price fact table

Post  kiwiNspain on Tue Aug 11, 2015 9:59 am

Hello,

I'm designing a fact table for average prices on a transport route. Below is an example of the xml data that we need to model.
I'm having trouble Identifying the grain here. Would it be seat type per price range per customer ? So in the example of FirstClass would the grain be 1 line per combination giving a total of 177 rows in fact table (1 seat * 59 dates * 3 customers)

Any other ideas on how to model this?

Many thanks

Code:
<Route Type="OneWay" Vehicle="Bus" From="abc" To="abc">
<Seat Type="FirstClass">
<Prices>
<Price DateStart="20150101" DateTo="20150131" Price="10" Customer="All"/>
<Price DateStart="20150201" DateTo="20150228" Price="11" Customer="All"/>
</Prices>
</Seat>
<Seat Type="Economy">
<Prices>
<Price DateStart="20150101" DateTo="20150131" Price="5"  Customer="All"/>
<Price DateStart="20150201" DateTo="20150228" Price="7" Customer="All"/>
<Price DateStart="20150201" DateTo="20150228" Price="6" Customer="c"/>
</Prices>
</Seat>
<Customers>
<Customer Name="a"/>
<Customer Name="b"/>
<Customer Name="c"/>
</Customers>
</Route>


Last edited by kiwiNspain on Tue Aug 11, 2015 10:00 am; edited 1 time in total (Reason for editing : show xml correctly)

kiwiNspain

Posts : 6
Join date : 2013-04-12

View user profile

Back to top Go down

Re: average price fact table

Post  ngalemmo on Tue Aug 11, 2015 4:05 pm

What does the XML data represent? A price list or a list of customers and the price they paid?

You would need the latter to calculate an average seat price.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: average price fact table

Post  kiwiNspain on Wed Aug 12, 2015 2:07 am

the xml is a selling contract per transport route, so basically represents how much a customer can sell a seat for.

questions that would need to be answered would be things like average price per day/month (well all date attributes) per customer per seat type per route type etc

kiwiNspain

Posts : 6
Join date : 2013-04-12

View user profile

Back to top Go down

Re: average price fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum