Alternative to OUTER JOIN in a projection
No wonder I didn't get anything back to my question... seems I didn't post it!
So, I would like to turn some of our old materialised views in Oracle into projections. I understand that projections can't include outer joins, and that instead, one is supposed to use the appropriate primary and foreign keys on the fact and dimension tables such that an inner join can be used. However, I followed through one of the examples https://my.vertica.com/docs/6.1.x/HTML/index.htm#10391.htm and tried to apply the same logic to a copy of two of my own fact and dimension tables.
If I understand the example above correctly, the foreign key is placed on the fact table, and the primary key is placed on the dimension table. This itself seems counter-intuitive. I thought that the primary key goes on the fact table, which is "complete", and the foreign key goes on the dimension table, which may not have rows corresponding to all of those in the fact table.
In any case, I have tried both combinations, PK on fact/FK on dimension and FK on fact/PK on dimension. They both yield the same results when running a select with an inner join. Where there are rows "absent" in the dimension table, the "corresponding" rows in the fact table will not show when using an inner join on the two tables.
If it helps, an example of the data I am trying to load is shown here:
http://dtc.mrasco.com/DataFlow.aspx?FlowCounter=0001&FlowVers=1&searchMockFlows=False
Groups 001, 002, and 003 are always "complete", they are all mandatory groups/rows of data in corresponding 001, 002, and 003 tables. Therefore a select query with an inner join returns all rows from either 001 + 002, or 001 + 002 + 003. However, group 759 is not always complete, so trying to return 001 + 759, or 001 + 002 + 759, or 001 + 002 + 003 + 759, only ever returns rows where there is data in the 759 table.
Does anyone have experience of trying to make this sort of projection work? Hopefully the above makes sense.
Thanks
Ben
So, I would like to turn some of our old materialised views in Oracle into projections. I understand that projections can't include outer joins, and that instead, one is supposed to use the appropriate primary and foreign keys on the fact and dimension tables such that an inner join can be used. However, I followed through one of the examples https://my.vertica.com/docs/6.1.x/HTML/index.htm#10391.htm and tried to apply the same logic to a copy of two of my own fact and dimension tables.
If I understand the example above correctly, the foreign key is placed on the fact table, and the primary key is placed on the dimension table. This itself seems counter-intuitive. I thought that the primary key goes on the fact table, which is "complete", and the foreign key goes on the dimension table, which may not have rows corresponding to all of those in the fact table.
In any case, I have tried both combinations, PK on fact/FK on dimension and FK on fact/PK on dimension. They both yield the same results when running a select with an inner join. Where there are rows "absent" in the dimension table, the "corresponding" rows in the fact table will not show when using an inner join on the two tables.
If it helps, an example of the data I am trying to load is shown here:
http://dtc.mrasco.com/DataFlow.aspx?FlowCounter=0001&FlowVers=1&searchMockFlows=False
Groups 001, 002, and 003 are always "complete", they are all mandatory groups/rows of data in corresponding 001, 002, and 003 tables. Therefore a select query with an inner join returns all rows from either 001 + 002, or 001 + 002 + 003. However, group 759 is not always complete, so trying to return 001 + 759, or 001 + 002 + 759, or 001 + 002 + 003 + 759, only ever returns rows where there is data in the 759 table.
Does anyone have experience of trying to make this sort of projection work? Hopefully the above makes sense.
Thanks
Ben
0