Point in time data question in SSAS


  • Hi all - I have q question about point in time data in SSAS.

    Attached is an example from the DW:  I have a unique row (denoted by NID and RID) which has three states at different points in time.

    16/05 shows the overall status be Open with a TotalPaid of 406.56.

    12/06 shows overall status to be Open with a different TotalPaid of 410.

    18/07 shows overall status now to be Closed with same TotalPaid.

    What I would like to do in the cube is upon filtering on a date tied to RunDate column, I would like to show the latest record at a given time - for example if I selected 14/06/2014 in a filter, I would get the row with RunDate 12/06/2014 (since this is the latest row at that time).

    There is no need for summing data, as the TotalPaid column is already doing the aggregation.

    Any suggestions?


    Friday, July 18, 2014 9:15 AM

All replies

  • Think of this as a SCD type 2, you need to have valid_from and valid_to date and filter in between.

    for example: WHERE Given_Point_In_Time BETWEEN valid_from AND valid_to

    That would give you the current status at a given time

    Microsoft Certified Trainer & MVP on SQL Server
    Please "Propose as Answer" if you got an answer on your question, and vote for it as helpful to help other user's find a solution on a similar question quicker.

    Friday, July 18, 2014 3:58 PM
  • Hi Mattias - thank you for your answer.

    Since I am new to all this, is the method I have described considered a standard practice, or could this be done in another way?  Thanks.

    Monday, July 21, 2014 8:11 AM