Forum Discussion
Different opinion on design needed
Hi,
I’m designing an MVP Fabric / Power BI model and want to compare practical approaches.
Sources:
- GetResponse: people/subscribers, lists, contact-list membership, contact attributes.
- Maconomy: client companies, CRM contact persons assigned to companies, projects, current total project revenue.
Proposed model:
| Table | Grain / purpose |
|---|---|
| dim_person | One canonical person/contact, matched between GetResponse and Maconomy by email where possible. |
| fact_contact_list_membership | One GetResponse contact + one GetResponse list. |
| dim_getresponse_list | One GetResponse list/campaign. |
| bridge_person_client | One person + one client company relationship. |
| dim_client_company | One client company with status, industry, country, source, etc. |
| dim_project | One project with manager/partner/service attributes. |
| fact_project_revenue | One client company + one project with current total revenue. |
| dim_date | Calendar table. |
Relationship idea:
dim_date -> fact_contact_list_membership <- dim_getresponse_list
^
|
dim_person
|
v
bridge_person_client <- dim_client_company
dim_client_company -> fact_project_revenue <- dim_project
Main rule:
Revenue stays only on the client/project path:
dim_client_company -> fact_project_revenue <- dim_project
No direct relationship from bridge_person_client to fact_project_revenue, because one company can have many contact persons and revenue could be duplicated.
Report needs:
- Contact report: contact KPIs, list counts, trend, table with person, email, company/companies, “is client contact”, and possibly other client attributes.
- Revenue report: revenue by client, project, manager/partner, service, and client attributes.
Current direction:
- active single-direction relationships
- bridge/factless fact for person-client relationships
- DAX measures for showing company/company-list, “is client contact”, and possibly other client attributes beside a person
- revenue calculated strictly through client/project
- optional company detail/drill-through page: revenue once, projects, assigned contact persons
Question:
For the contact report, would you show related client attributes via DAX measures through the bridge, materialize a flattened contact reporting table, use bidirectional relationships, or model this differently?
Hi jaryszek,
Thank you for reaching out to Microsoft Fabric Community.
Your proposed model looks good. I would keep the single-direction relationships and the bridge_person_client table and avoid bidirectional relationships as they can lead to ambiguous filtering and duplicate results.
If you only need to show a few client attributes in the contact report, use DAX measures. Only consider creating a flattened reporting table if the report becomes too complex or if you run into performance issues.
Thanks and regards,
Anjan Kumar Chippa
3 Replies
Hi jaryszek,
Thank you for reaching out to Microsoft Fabric Community.
Your proposed model looks good. I would keep the single-direction relationships and the bridge_person_client table and avoid bidirectional relationships as they can lead to ambiguous filtering and duplicate results.
If you only need to show a few client attributes in the contact report, use DAX measures. Only consider creating a flattened reporting table if the report becomes too complex or if you run into performance issues.
Thanks and regards,
Anjan Kumar Chippa
thank you, it is great to have somebody to take a look at my concerns.
