Datawarehouse Number of records timeline
<p>I don't have historical information on sales order in my ERP database. 
I have a table containing sales order. Once a sales order is approved, it is transformed in an invoice and the record sales order is deleted.</p>

<p>I can code in the ERP a historical table with only information I need. I thought that is something that should be solved by a data warehouse (also due to personal interest).</p>

<p>I want to see how much sales order I have by date. For example, I want to see that I have an average of X orders of a total value of XXX by month.</p>

<p>My thought where to just add columns "Valid from" and "Until to".
From there I'm blocked, my initial though where to use SCD but as I understand it is not possible to do that on the primary key.</p>

<p>Shall I need to create an new fact table (like I would do in the ERP database) ?
I don't want to program something also on the insert trigger or delete trigger from the sales order table. We clean the sales order table at the end of the day, it needs to be updated when we update the data warehouse in the night. 
For this case, I don't need to take into consideration that the order would not be approved and that I would store non-relevant records.</p>