Forum Discussion
Please advise on the ideal data structure when multiple business processes are included in a single
I am envisioning a data structure similar to that of Salesforce (SFDC).
For instance, consider a table where multiple statuses are stored, as shown in the table below.
In a sales process, for example, the process (status) progresses in the order of "Meeting Acquired → Meeting Held → Order Received," and all three are stored in a single table.
| ▼date | ▼status | ▼salses | ▼costomer |
| 2024/01/01 | Meeting Acquired | Mario | Yoshi company |
| 2024/02/01 | Meeting Held | Mario | Yoshi company |
| 2024/03/01 | Order | Mario | Yoshi company |
| 2024/04/01 | Meeting Acquired | Jake | CDE company |
| 2024/04/03 | Order | Mash | ABC company |
When using this in Power BI, what would be the optimal way to shape the table for ideal performance in analysis? I would like to maintain a table structure that is as versatile as possible.
※When implementing this, I plan to create a data mart by processing the original database before loading it into Power BI.
What you are trying to do is SCD2, and is NOT how SFDC does things. In SFDC you have an Object Field History table instead.
SCD2 is not a bad approach for what you are trying to do. All you need is a distinct record identifier and a "Modification Date" column.
Implementing Slowly Changing Dimensions (SCDs) in Data Warehouses (sqlshack.com)
4 Replies
- lbendlin
Super User
What you are trying to do is SCD2, and is NOT how SFDC does things. In SFDC you have an Object Field History table instead.
Apologies, you are right; SFDC was not relevant.
Does it make sense to transform the table structure into a more ideal format in the database before loading it into Power BI? If so, what kind of structure would be best?
- lbendlin
SCD2 is not a bad approach for what you are trying to do. All you need is a distinct record identifier and a "Modification Date" column.
Implementing Slowly Changing Dimensions (SCDs) in Data Warehouses (sqlshack.com)
