Need Help to Design Calendar +day type model +fact table to meet requirement

[MubinShaikh]

Hi All,

Here By I am providing you My Scenario, Kindly Help If you have solution.

A Bus service can have multiple Vehicle journey within a day over different Journey path,

I have a Service table in which start date and end date of service is defined.

Service

ServiceId(PK), name, startdate, enddate

Vehicle Journey Table defines time table for each vehicle journey for this service

Vehicle Journey

VehicleJourneyId(PK),

RouteId,

ServiceId,

StartTime

EndTime

DayType defines that service will operate on which days.

DayType

DayTypeID (PK)

DayName

e.g. of Day Names

Monday,TuesDay,Wed,Thu,Fri,Sat,Sun,Mon-Fri,sat-sun,Not monday,Not Tuesday,Not NewYear, Not Cristmas

e.g Service will operate mon-fri day between 1-1-2012 to 31-1-2012.

another table Contains Service,vehicle journey and Day Type Mappings.

ServiceDayTypeMapping

ServiceDaytypeMapppingId

ServiceId

VehicleJourneyId

DayTypeId

A service can have multiple vehicle journeys , A journey can operate on Defined DayTypes such as(Mon-Fri) or (Not Sunday, means all days without sunday).

I have Date Range Table in which i have created All dates as range and defined daytype inside that.

DATERANGE

DataRangeId

StartDate

EndDate

DayTypeID

e.g. This table can contain start date 1-1-2012 to end date: 1-1-2012 daytpe 7 (sunday),

2-1-2012 to 2-1-2012 DayType 1 (Monday)

6-1-2012 to 10-1-2012 DayType 10 (Mon-Fri)

13-1-2012 to 17-1-2012 DayType 10 (Mon-Fri)

This Schema exist in my RDBMS and i need to design DataWare House schema such that, I can query about how many vehicle journey will operate on each date ,between specified date range, or which vehicle journeys will operate on specified date if date is within range of service start and end date.

I don't have date for each record of Scheduled vehicle journey for service ,service is defined over weekday, and start and end date of service is given.and calendar is as shown as above so how can i design my DW , so i can count Daily scheduled Vehcle journey for all services. or i can count vehicle journey for a service over date range.?

I have study Snowflake schema to relate my Calendar dimension and M:M relation ship table servicedaytypemapping , but not getting exact idea to do this. so please guide to resolve this issue.



Thanks & Regards.