0

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).

DaySales_Fact

DaySalesId
Sales (Fact)
Date
BranchCode
SalesDiv

Branch_Dim

BranchCode
BranchName

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.

DaySales_Fact

DaySalesId
Sales (Fact)
DateId (fk)
BranchId (fk)
SalesDivId (fk)

Branch_Dim BranchId - auto inc PK BranchCode BranchName

Improve this question
1
  • meaningless surrogate keys, except for dim_date where you can use 20140820 for example as the key. Use unique constraint/index for integrity.
    – Neil McGuigan
    Commented Aug 20, 2014 at 18:04

1 Answer 1

Reset to default
3

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: http://www.kimballgroup.com/1998/05/surrogate-keys/

Improve this answer
2
  • In addition to this John, ensure that you use TRUNCATE to empty your tables - this resets the primary key fields auto increment back to 1. I have seen situations where DELETE is used and the primary key ends up running out of space after a few years of inserting rows.
    – blobbles
    Commented Aug 21, 2014 at 15:42
  • Thanks. I have read that truncate is better where its use is possible, faster and less logging. Thanks for the suggestion though!
    – JohnLinux
    Commented Aug 26, 2014 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.