Problems linking dim table with fact table

• juanvg1972

  SSC Enthusiast

  Points: 184

  More actions

  September 13, 2017 at 5:33 am

  #150651

  I have a problem with SSAS trying to relate tables.

  I have this tables:

  1) hc_routes (airplanes routes):
  id_route, id_aeroline, id_airport_source, id_airport_destination
  PK: id_route

  2) co_route_equipments (planes of each route)
  id_route, id_equipments
  PK: id_route, id_equipments (I have N equipments for each id_route)

  3) dm_equipments
  id_equipment, desc_equipment
  PK: id_equipment

  4) dm_airport
  id_airport, ....(atributes)
  PK: id_airport

  5) dm_aeroline
  id_aeroline, ....(atributes)
  PK: id_aeroline

  I have made a data source and a cube with this tables. I define metrics for hc_routes y co_route_equipments
  (count of routes).
  In the dimension usage I can link:
  - hc_routes with dm_airpors and dm_aeroline
  - co_route_equipments with dm_equipments

  I would like to relate dm_equipments with hc_routes but I don't know how...
  I want to count how many routes has a equipment, but filtering by airport or airline then
  I need to use hc_routes

  dm_equipment - (id_equipment) 1:N -> co_route_equipment ->(id_route) 1: 1 -> hc_routes

  Any advice will be greatly appreciated

  

• PB_BI

  SSCoach

  Points: 17461

  More actions

  October 16, 2017 at 12:31 am

  #1963745

  juanvg1972 - Wednesday, September 13, 2017 5:33 AM

  I would like to relate dm_equipments with hc_routes but I don't know how...
  I want to count how many routes has a equipment, but filtering by airport or airline then
  I need to use hc_routes

  dm_equipment - (id_equipment) 1:N -> co_route_equipment ->(id_route) 1: 1 -> hc_routes

  Any advice will be greatly appreciated

  

  You could do this with a Many To Many relationship using the Co Ruta Equipamientos as the Intermediate Measure Group. More information on M2M relationships in SSAS from the follwoing links:
  https://docs.microsoft.com/en-us/sql/analysis-services/lesson-5-3-defining-a-many-to-many-relationship
  https://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf

  ---

  I'm on LinkedIn