I am extracting sales turnover data from a Microsoft Dynamics Database into another SQL database that will be used by the BI tool of choice.
This data is coming from G/L (general ledger) entry items. Each entry contains information for the amount, date, branch, and sales division.
In the DW, I intend to use a star-schema, where the sales figure per day is given in dollars. (fact).
DaySalesId Sales (Fact) Date BranchCode SalesDiv
Assume the same thing for Date and SalesDiv tables.
The source data is unreliable and historical data may change at any time. The data warehouse is emptied each day for this reason, and restocked with the live data. This process is scheduled to run once daily.
The source data uses a varchar(2) to store the BranchCode as a primary key.
Would it be best practice to use the business branch code, or create an auto incrementing primary key (see below).
All of the branches are refreshed daily too.
DaySalesId Sales (Fact) DateId (fk) BranchId (fk) SalesDivId (fk)
Branch_Dim BranchId - auto inc PK BranchCode BranchName