average price fact table
2 posters
Page 1 of 1
average price fact table
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
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
Re: average price fact table
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.
You would need the latter to calculate an average seat price.
Re: average price fact table
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
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
Similar topics
» Unit Price Fact Table
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum