Please advise on the ideal data structure when multiple business processes are included in a single

[yoshi_matsu]

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.

[lbendlin · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[yoshi_matsu]

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 · Accepted solution]

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)

[yoshi_matsu]

For example, separating the table by status.