Forum Discussion
Advocate IOntology creating relationships using which tables
I today created an ontology based on a lakehouse with the adventureworks tables.
i created some elements according to the dimension and fact tables and want to create relationships.
as the adventureworks example is a simple warehouse structure there are one-to-many relations possible from the fact table to dimension tables.
creating a relationship in the ontology allows me to only choose the mapping based on an existing table.
if i choose the fact table, it offers me only the properties of the fact table to be used in specifying how to map to the dimension table.
if the names of the properties are the same, everything looks nice, if not, there is no way to specify how to join facts and dimensions.
in the adventure works example this happens connection the fact table with order date to the date dimension.
How am i supposed to handle that or where are my assumptions wrong, how to use the creation dialog in the ontology?
2 Replies
- apturlov
Super User
Hello mriedmuell. The scenario you described, is one of the more common situations, unfortunately. However, it works very similar to how you would create a relationship in a semantic model or in a relational database: you need to have to columns in each table in the relationship that mean exacly the same and have exactly the same data. In Fabric Ontology you configure a relationship using a source table and a target table. Source table must have both row identifiers that you want to connect. For example, in a Sales fact table you should have both SalesRowId and a DateRowId and the Sales fact table is the source table, while the Date dimension table is the target. So you select the SalesRowId as a Sales identity column and the DateRowId as a the Date identity column. You also must have both columns in each table marked as the Entity Identity. In such case, they will be suggested by a UI. Keep in mind, that the matching is done on the names of the entity columns in your Ontology, not in the underlaying data. While you cannot rename the columns in your underlaying data sources, you can name columns however you want in your Ontology and then bind the columns to the data sources accordingly.
So, yes, you absolutely need to plan the property/column names for relationship configuration in the Ontology and you have a full flexibility to rename them so the UI matching works correctly.Hope this helps, or provide more information with screenshots.
If you find this answer useful, consider giving kudos and accept as a solution.
- mriedmuell
Thank you for your reponse apturlow, it describes what i expected to find in the GUI defining relationships.
I tried again after my vacation and found:
I created a relationship between factinternetsales and calendar tables.The information onm both ends is correct an shows the defined keys for the two entity types.
The area in the moddle leaves my doubting whether i understand correctly waht to entrer here.
I can only chose factinternetsales as mapping table, as it is a many-to-one relationship and the keys are present in that table only.
I can chose the keys fron the sales table and the column/property OrderDateKey to be mapped to the DateKey from calendar.
As I do not get any error message, i suppose that this is correct.
I created two more relations accordingly für due date and ship date.
When i use this ontology in a data agent I get an error:This indicates that te relation is not constructed correctly.
