Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the next model:
I want to take this: count(id_ruta) by pais_aeropuerto.
Dimension = pais_aeropuerto
Expresion = count(id_ruta)
The link is: pais_aeropuerto (airports) -> id_aeropuerto_ori(routes) -> id_ruta(routes)
but it doesn't work:
when I connect directly: id_aeropuerto_ori(routes) -> id_ruta(routes). It works:
Can anybody help me??
Thanks in advance
Hi Juan,
Since your airports table has both id_aeropuerto_ori and id_aeropuerto_dest, it's creating a synthetic link with the routes table.
Don't know aobut your data, but my guess is that the airports table doesn't have the id_aeropuerto_des and can't create the link in both tables with this synthetic key.
So, when you connect directly to the id_eropuerto_ori works because it has a direct link for only one existing field.
How did you create both (ori and des) fields in the airports table by the way?
Other thing is, why do you have this kind of information on the airports side? To me, this kind of model doesn't make much sense since the airports table is a dimension and your routes table is your fact.
Instead of creating both IDs, use some type of key to remove the synthetic key.
See this community post for some help:
Felipe.
Hi Juan,
Since your airports table has both id_aeropuerto_ori and id_aeropuerto_dest, it's creating a synthetic link with the routes table.
Don't know aobut your data, but my guess is that the airports table doesn't have the id_aeropuerto_des and can't create the link in both tables with this synthetic key.
So, when you connect directly to the id_eropuerto_ori works because it has a direct link for only one existing field.
How did you create both (ori and des) fields in the airports table by the way?
Other thing is, why do you have this kind of information on the airports side? To me, this kind of model doesn't make much sense since the airports table is a dimension and your routes table is your fact.
Instead of creating both IDs, use some type of key to remove the synthetic key.
See this community post for some help:
Felipe.
Hi Felip,
Yes airports is a dimension table of hc_routes and connect via tu fields;
id_airport_ori and id_airport_des, this fileds are in hc_routes but not
in dm_airports then I had to create it in dm_airports in order to make the links.
You can see in the scripts below
How can I manage this kind of relationship (role-playing dimensions)?
LOAD id_aeropuerto as id_aeropuerto_ori, id_aeropuerto as id_aeropuerto_des, *; /* añadimos ori y des para cruces */
LOAD @1 as id_aeropuerto, @2 as nombre_aeropuerto, @3 as ciudad_aeropuerto, @4 as pais_aeropuerto,
@5 as cod_iata_aeropuerto, @6 as cod_icao_aeropuerto, @7 as latitud, @8 as longitud, @9 as altitud
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
LOAD @1 as id_aerolinea, @2 as nombre_aerolinea, @3 as alias_aerolinea, @4 as cod_iata_aerolinea,
@5 as cod_icao_aerolinea, @6 as identificador, @7 as pais_aerolinea, @8 as sw_activa
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
LOAD RowNo() as id_ruta, *; /* anadimos id_ruta */
LOAD @1 as cod_aerolinea, @2 as id_aerolinea, @3 as cod_aeropuerto_ori, @4 as id_aeropuerto_ori,
@5 as cod_aeropuerto_des, @6 as id_aeropuerto_des, @7 as codeshare, @8 as paradas,
@9 as equipamientos
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Juan,
The way you want it, I can't see a simpler way then having two separate tables, one for origin and destination if you really need the data for the destination airport.
It will basically be a copy of the same table as origin airport, but with the separation of ids for origin and destination.
Other solution would be to create a generic key for each origin / destination aiport combination, but that would create so many new lines in your table, since I believe there would be a lot more combinations than simply duplicating the table.
Attached the code I believe is best.
Thanks for your help Felip,
No problem Juan, glad to help .