hi,

Sorry I didn't get back to you yesterday, work got hectic.

I didn't realise this was for a DW, had it been a OLTP system I would have used the structure you proposed.

However, from a DW perspective I would actually do something different.

The first thing I would is create a Fact table that Pivoted the Hours so that hours becomes a Dimension, this has a number of benefits

1) You don't need to enter rows where there are no markers.

2) you can Cross Tab the data in Excel or a report so much easier

The Structure I would use is something like

Create Table dim_Hours

(

Hour SmallInt NOT NULL PRIMARY KEY

,HourDescription CHAR(3) --Formated to be 'H00' - 'H23'

)

CREATE TABLE Fact_discharge_planning_by_hour

(

EDD_Month INT --FK to Dim Period

,EDD_StartDate DATE --FK to Dim Calendar

,EDD_EndDate DATE --FK to Dim Calendar

,EDD_EffectiveDate DATE --FK to Dim Calendar

,AccountKey INT -- FK To Dim_Account

,Hour SmallInt -- FK to Dim_Hour

,Measure

)

So all you then need to do on the insert is the following

INSERT INTO Fact_discharge_planning_by_hour

SELECT

Month

,a.Start_date

,a.End_date

,calendar.Date EffectiveDate

,Account.AccountKey

,DATEPART(HOUR,a.StartDateTime)

,1

FROM

Hotel a

JOIN CMH_DW.dbo.Dim_Date Calendar

ON Dim_Date.Date>a.StartDate and Dim_date.date<=a.EndDate

JOIN Dim_Account Account

ON a.AccountNumber = Dim_Account.AccountNumber

To Get Data out its very simple with a Cross tab, or Pivot you can create the missing columns, very easily.

Eg

SELECT

Edd_Month

,Edd_StartDate

,Edd_EndDate

,Edd_EffectiveDate

, AccountKey

, Sum(CASE Hour WHEN 0 THEN Measure ELSE 0 END) [H0]

, Sum(CASE Hour WHEN 1 THEN Measure ELSE 0 END) [H1]

, Sum(CASE Hour WHEN 2 THEN Measure ELSE 0 END) [H2]

, Sum(CASE Hour WHEN 3 THEN Measure ELSE 0 END) [H3]

--ETC to

, Sum(CASE Hour WHEN 23 THEN Measure ELSE 0 END) [H23]

From Fact_discharge_planning_by_hour

GROUP BY

Edd_Month

,Edd_StartDate

,Edd_EndDate

,Edd_EffectiveDate

, AccountKey

This might give you a few ideas on a way forward.