How to structure data for Backlog Reportoing in SSAS

• Tempest

  SSC Veteran

  Points: 241

  More actions

  March 31, 2014 at 11:17 am

  #396506

  Hi I am really struggling with a scenario..

  I have a fact table that represent help desk calls. All my records have 3 status dates

  Registered Date

  Due Date

  Completed Date

  I need to be able to show on any one day or a month, Qtr, Year, side by side the number of calls registered, vs the number due, vs the number complete.

  To do that I have unioned all the records 3 times into 1 big fact table so they all appear together and I have 1 date dimension.

  My problem now is I need to also show along side these 3 measures is the overdue backlog for any given period.

  This is all the records whos Completed date is > than there due date.

  I also want to be able to group those overdue records in to buckets according to how long they have been overdue.

  What might be the best way to do this.. do I need to add it as another union in my big fact table or would using some sort of MDX calculation work better?

  Ay advice would be really really appreciated..I hope all that makes some sense!

  Thanks