none
Which SSIS 2008R2 BIDS transform(s) appropriate to create multiple destination inserts based on prior Fact table entry?

    Question

  • I don't know whether to use some combination of data flow transformations or resort instead to a Script Component to accomplish my goal.  I am receiving electric usage odometer-type meter readings, anywhere from 0 to many per day for a given meter.  Granularity of Fact table flattens that out to 1 reading per meter per day.  So, today's odometer reading comes in and the calculated consumption is done by subtracting the most recent reading for this meter in the fact table from today's odometer value.  E.g. last time I got a reading it was 132 and now the meter says 150 so consumption between the readings is 18.  To complicate further, if for example 6 days have passed since that last reading I need to insert 1 reading for each day to the Fact table and spread that 18 value across them.  So, question is which transforms are most appropriate for having to look into the Fact table's recent past in order to determine record(s) to introduce to the destination pipeline?

    Example on Fact table:  

    Date: 7/10/2013  Meter: MeterA  Consumption: 5   Estimated? N   LastGoodReading: 132  

    --------------------------------------------------------

    Six days later, on 7/16/2013, MeterA reports a reading for 150.  From that I should create following output by using the earlier row, the last reading and the time that has elapsed between readings:

    --------------------------------------------------------

    Date: 7/11/2013  Meter: MeterA  Consumption: 3  Estimated? Y  LastGoodReading: NULL 

    Date: 7/12/2013  Meter: MeterA  Consumption: 3  Estimated? Y  LastGoodReading: NULL 

    Date: 7/13/2013  Meter: MeterA  Consumption: 3  Estimated? Y  LastGoodReading: NULL 

    Date: 7/14/2013  Meter: MeterA  Consumption: 3  Estimated? Y  LastGoodReading: NULL 

    Date: 7/15/2013  Meter: MeterA  Consumption: 3  Estimated? Y  LastGoodReading: NULL 

    Date: 7/16/2013  Meter: MeterA  Consumption: 3  Estimated? N  LastGoodReading: 150

    Friday, July 19, 2013 6:54 PM

All replies

  • Hi hmbrraymond,

    Please refer to the following codes:

    declare @odometer table
    (
    	Meter varchar(10),
    	Consumption int,
    	Estimated bit,
    	LastGoodReading int,
    	[Date] date
    )
    declare @n int;
    declare @Date1 date;
    declare @Date2 date;
    declare @currentDate date;
    insert into @odometer values ('MeterA',5,0,132,'7/10/2013'),('MeterA',5,0,150,'7/16/2013')
    select @date1=min([date]),@date2=max([date])
    from @odometer
    set @currentDate = DATEADD(day,1,@Date1)
    while (@currentDate<@date2)
    begin
    	insert into @odometer([Date]) values (@currentDate)
    	set @currentDate = DATEADD(day,1,@currentDate)
    end
    --select * from @odometer
    --order by [Date]
    IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
    ; with cte as
    (
    select Meter,Consumption,Estimated,LastGoodReading,[Date], ROW_NUMBER() over( order by [Date]) as RowNum from @odometer
    where Estimated = 0
    )
    , cte2 as
    (
    select c1.Meter as Meter,c1.[Date] as [date1], c2.[Date] as [date2],(c2.LastGoodReading - c1.LastGoodReading)/DATEDIFF(day,c1.[Date],c2.[Date]) as Gap,c1.LastGoodReading as LastGoodReading
    from cte c1
    inner join cte c2
    on c1.Meter = c2.Meter
    and
    c1.RowNum + 1
    = c2.RowNum
    )
    select * 
    into #temp
    from cte2;
    -- To update
    merge @odometer t1
    using #temp t2
    on t1.[Date]>t2.[date1]
    and t1.[date]<t2.[date2]
    when matched then
    update
    set t1.Meter = t2.Meter,
     t1.Consumption = t2.Gap,
     t1.Estimated = 1
    --, t1.LastGoodReading = datediff(day,t2.[date1],t1.[Date])*Gap+t2.LastGoodReading
    ;
    select * from @odometer order by [date];
    If you have any feedback on our support, please click here.

    Allen Li
    TechNet Community Support

    Sunday, July 21, 2013 3:56 AM
  • Thank you for your reply and coding sample, Allen Li.  I think this is on the right track, although now I find my input source is more complicated than I originally thought.  Rather than just receiving the *latest* meter odometer reading and comparing it to the prior I will be replacing a recent *set* or block of these rows for many meters. 

    Here is new sample input, followed by desired output.  Would I follow a similar type of CTE solution for this?

    Sample of Input to to be transformed

    Date     Meter  Reading Consumption EstFlag
    -------------------------------------------
    20130619 MeterA  9,348        12        N
    20130620 MeterA  9,363
    20130621 MeterA  9,369
    20130623 MeterA  9,397
    20130624 MeterA  9,402
    20130625 MeterA  9,422
    20130626 MeterA  9,433
    20130627 MeterA  9,438  
    20130629 MeterA  9,451
    20130630 MeterA  9,456
    20130619 MeterB     94        15        N
    20130626 MeterB    201
    20130627 MeterB    208
    20130629 MeterB    237
    20130630 MeterB    247
    20130625 MeterC  1,222        18        N
    20130626 MeterC  1,243
    20130619 MeterD  2,617       102        N
    20130626 MeterD  3,281
    20130629 MeterD  3,522
    20130630 MeterE    911

    Comments about this input:
    --------------------------
    1. Consumption needs to be calculated from current - prior odometer readings and where there are gaps in reading dates those must be filled in.  The granularity of the Fact table is one row per meter per day.

    2. The earliest input row for most meters is pre-seeded because it came from the Fact table (prior entry on the Fact table) while the other input rows represent new transactions from the source system.  The fifth meter (MeterE) in this example is new, thus there was no prior record for it on the Fact table.

    Sample of desired Output to be loaded into Fact Table follow, below.
    ... margin notes show Consumption spreading between readings

    Would it help to fill in missing date gap rows and/or default values for Consumption and EstFlag columns on the input data set *before* sending it to the Consumption calculator or can CTE do both calculate consumption and fill in date gap rows all at once?

    Date     Meter  Reading Consumption EstFlag
    -------------------------------------------
    20130619 MeterA  9,348    12.00 N 
    20130620 MeterA  9,363    15.00 N 
    20130621 MeterA  9,369     6.00 N 
    20130622 MeterA      0    14.00 Y 
    20130623 MeterA  9,397    14.00 Y Note: 28/2
    20130624 MeterA  9,402     5.00 N 
    20130625 MeterA  9,422    20.00 N 
    20130626 MeterA  9,433    11.00 N 
    20130627 MeterA  9,438     5.00 N 
    20130628 MeterA      0     6.50 Y 
    20130629 MeterA  9,451     6.50 Y Note: 13/2
    20130630 MeterA  9,456     5.00 N 
    20130619 MeterB     94    15.00 N 
    20130620 MeterB      0    15.29 Y 
    20130621 MeterB      0    15.29 Y 
    20130622 MeterB      0    15.29 Y 
    20130623 MeterB      0    15.29 Y 
    20130624 MeterB      0    15.29 Y 
    20130625 MeterB      0    15.29 Y 
    20130626 MeterB    201    15.29 Y Note: 107/7
    20130627 MeterB    208     7.00 N 
    20130628 MeterB      0    14.50 Y 
    20130629 MeterB    237    14.50 Y Note: 29/2
    20130630 MeterB    247    10.00 N 
    20130625 MeterC  1,222    18.00 N 
    20130626 MeterC  1,243    21.00 N 
    20130619 MeterD  2,617   102.00 N 
    20130620 MeterD      0    94.86 Y 
    20130621 MeterD      0    94.86 Y 
    20130622 MeterD      0    94.86 Y 
    20130623 MeterD      0    94.86 Y 
    20130624 MeterD      0    94.86 Y 
    20130625 MeterD      0    94.86 Y 
    20130626 MeterD  3,281    94.86 Y Note: 664/7
    20130627 MeterD  3,522    80.33 Y 
    20130628 MeterD  3,522    80.33 Y 
    20130629 MeterD  3,522    80.33 Y Note: 241/3
    20130630 MeterE    911        0 Y Note: Can't calc 

    Thank you!

    Wednesday, July 24, 2013 5:39 PM