Planning vs Operational System - Dimension Source

I am building a DW for a Bus Transit Company and had a question on what would be the best practice around choosing a dimension source system. I have heard that System of Record should always be the source of a dimension. In this case the planning system generates bus route plan and pushes it to operational systems. What source should i consider for building a Block ( route, or other) dimension because there is always a variation between the planned bus service and the actual observed, considering the fact that transactions are only present in the operational system and not in planning system. Appreciate any guidance.
Thanks,
Ashish

Hi Ashish,

I believe System of record, should be the ultimate source of data. The system of record could be copied, transformed, manipulated etc and might end completely different than the source. So, in your case, I might choose to keep the system of record still as your planning system, but might want to keep the actual observed services as attributes in the same dimension as well.

I would appreciate any other thoughts/ differing opinions.

Thanks,
Dhar

How is the route plan a dimension? The plan and the actual route are facts. The route plan should come from the planning system and the actual route should come from the operational system. Things that describe characteristics of the route (stops, time, equipment, driver, etc…) are dimensions. These should come from wherever they originate.

Well you hit the nail right on head with that discussion. I am confused as to whether Route should be a Fact or Dimension. If it is treated as a Fact then i completely agree to you but the business users would like to see the measures sliced by Route ( Total Pax on a particular Route) in addition to the Stop, Time, Vehicle etc.. If I treat it as a Fact, then do i slice a Fact( measures) by another (Route Name)? Is that possible?

Thanks,
Ashish

The route identifier (and attributes associated with it, if any) is a dimension, as is "Route 123". The plan, be it a list of stops or a revenue/ridership projections or actuals, are facts.