I am looking for your expert opinion on creating a decent data model. I watched Sam’s lectures on data modeling quite a few times and learned about star schema etc. However, in my current task, I am a bit confused about applying star schema.
I am trying to create a data model in Power BI. Data is coming from a commercial database product used for storing property-asset-related information. They have their own ER diagram. I am trying to model three main aspects: Asset (property), contacts (person who may be living in the property or linked with property), and Rental information.
In my model I make sure that there is no M: M relationship between the table exists. There were a couple of situations when M: M relationship exists and I created a bridging table to convert it into a 1:M relationship.
The problem I am facing here is an error that appears from time to time called 'Relationship does not exist between two or more fields. I posted a couple of questions related to the same error on the forum and got answers. However, the same error appeared in some other relationships. Now I am completely convinced that I am making some fundamental mistake that is causing the error again. I am sharing the ERD of my PBI and tried to annotate it for better understanding.
For example, one of the recent issues I am having is mapping information from Asset and contact information. If I tried to show AssetID (from Asset table), contactID (from Contact table) on the table visual, it works fine. However, when I put more information (such as information from the ContactRelationship table) it throws the same error.
Please accept my apologies if I am making silly mistakes in the model. I am still in learning phase and learned a lot from your forum. I am not sure in my situation how can I convert these tables into star schema (where there are multiple dimension tables and one fact table) to avoid the error. Moreover, Any general advice to make the data model would be highly appreciated. I have also attached the sample file (which only contains information about the tables mentioned in the example).
Sorry for the lengthy post.