Hi All,
I am having issues with building an appropriate data relationship model and i was hoping someone could provide some advice.
To apply context, the purpose of the power BI model is to manage a telecommunications product (Mobile Phone services) which are ordered against an account, and then they have a connection date and a disconnection date.
I need to be able to link / determine a sales order date, a connection date and disconnection date for a product.
At a high level i have the following FACT tables: Accounts, Orders, Connections and Disconnections.
Accounts = Customer details
Orders = Orders created against the Account (Linked by Account Number that exists in both the Customers and Orders Tables)
Connections = Date the services was connected (Linked by ServiceID that exists in both the Orders and Connections tables)
Disconnections = Date the services was disconnected (Linked by ServiceID that exists in both the Orders and Disconnections tables)
I then have:
A Product Lookup, the Product exists in the Orders, Connections and Disconnections tables.
A calendar lookup, with dates existing in Orders, Connections and Disconnections tables.
I have tried linking the accounts to the orders (account number), orders to the connections (serviceID) and orders to the disconnections (serviceID)
My issue is the cardinality from the orders to the connections and disconnections starts to flow back into the orders which i would have assumed would have continued to flow the other way (accounts > orders > connections AND accounts > orders > disconnections)
The linking to the lookup tables (product and calendar) work fine
Anyway, i hope someone can assist here. (apologies for the long message)
Cheers,
Todd
