Forum Discussion
Bridge Table
- 6 years ago
Hi Earosenfeld ,
these are not bridge tables but dimension tables and therefore completely correct.
The question is, are there any master data tables in your system from which you can load the unique values for your dimension table?
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
My question is around the idea of "Bridge' or "Junction" tables in Fabric.
Are Fabric Warehouses built to use different schemas to bridge or join tables in other schemas? In other words, are Warehouses in Fabric/OneLake built to handle the use of multiple schemas when establishing relationships between tables in a Fabric Semantic Model?
As per my current experiments, the answer is that they are not built to do so. My experiments indicate that Junction or Bridge tables necessarily must be in the same schema as the tables they join.
My experimental findings are that junction tables must belong to the same schema as the tables they intend to join. I would be interested in other evidence in support or contrary to that. I have screenshots and I can provide further evidence to support that concept.
My hypothesis, which I'm hoping can be refuted or confirmed is this:
In Microsoft Fabric Warehouses, if you are going to use Semantic Models to build Reports in PowerBI, all tables with relationships must exist within the same database schema.
In other words, a table in the schema `junc` CANNOT be used to link two tables in the `dim` schema. If a relationship exists between tables [dim].[Store] and [dim].[StoreAgent], a third table establishing a relationship between those two tables must remain within the same schema, namely [dim].
Thanks for your consideration of my question and any response you may have. I look forward to learning more about dimensional database design as well as the technology of Microsoft Fabric. -Stephen