none
Need Help to Covert Calendar Model from RDBMS to DataWareHouse

    Question

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

    Thursday, February 23, 2012 5:38 PM

All replies

  • This is a subforum of System Center Service Manager (SCSM). Maybe its better to ask your question in a SQL Server forum: SQL Server

    Andreas Baumgarten | H&D International Group

    Thursday, February 23, 2012 7:02 PM
    Moderator
  • This is a subforum of System Center Service Manager (SCSM). Maybe its better to ask your question in a SQL Server forum: SQL Server

    Andreas Baumgarten | H&D International Group

    Thanks A lot for your suggestion.

    Regards

    Mubin

    Friday, February 24, 2012 3:43 AM