Looks like a fact-less fact but walks like an attribute

View previous topic View next topic Go down

Looks like a fact-less fact but walks like an attribute

Post  ryno1234 on Tue Aug 04, 2015 5:51 pm

I have a table which represents fleet vehicles. Those vehicles can be assigned to a driver which is the primary driver for an extended period of time on the vehicle. This feels like a fact table:

fact_driver_assignment
* assignment_date_id (dim_date)
* vehicle_id (dim_vehicle)
* driver_id (dim_drivers)

however, my dim_drivers table would have only 3 columns because the data for driver is basically just a lookup table in the source system:

dim_driver
* id
* driver_key (ID in source system)
* driver_name

In addition, I would like to be able to key off of something like dim_vehicle.is_assigned_to_driver for my queries, which makes me feel like maybe I should put the assigned driver in my dim_vehicle table as an SCD Type 2 like so:

dim_vehicle
* id
* vehicle_key
* is_assigned_to_driver
* driver_assigned
* driver_assigned_on_date
* etc.

How should this assignment be represented if I want to use that assignment as part of my query predicate when filtering dim_vehicle? Would it be appropriate to store the actual assigned driver on dim_vehicle?

Your input is greatly appreciated.
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Looks like a fact-less fact but walks like an attribute

Post  ngalemmo on Tue Aug 04, 2015 9:02 pm

I would have begin and end dates in the driver assignment fact and not put driver information in the vehicle dimension. Then it is real easy to query which vehicles are assigned at any point in time.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Looks like a fact-less fact but walks like an attribute

Post  ryno1234 on Wed Aug 05, 2015 8:29 am

Thank you, as always ngalemmo.

How would I go about querying if a given vehicle is currently assigned (regardless of who it's assigned to) when using the vehicle in other fact queries.

For example, if I had (along with the requisite dimensions):

fact_vehicle_driver_assignment
* id
* date_id (D)
* vehicle_id (D)
* driver_id (D)
* start_date
* end_date

fact_vehicle_mileage_checkin
* id
* date_id (D)
* vehicle_id (D)
* latest_mileage_reading (M)

How would I go about querying fact_vehicle_mileage_checkin for vehicles which are unassigned without doing a fact-to-fact table join (which I've thus far avoided like the plague)?

This is why I have thought that I must have my attribute of whether or not the vehicle is assigned as an SCD Type 2 in the dim_vehicle table. How would I address this need?
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Looks like a fact-less fact but walks like an attribute

Post  ngalemmo on Wed Aug 05, 2015 3:34 pm

Ok, fair enough. Given that use case it makes sense to store assignment information in the vehicle dimension.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Looks like a fact-less fact but walks like an attribute

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum