I will create a couple of tables in SQL Server for a client. Of special interest for this question is the
FactStoreSale table to be created and
DimProduct in which the first table will include all store's sales and include for instance
ProductKey and sales data.
DimProduct table will use complementary data in regards to Products, i.e.
ProductCategory etc. This table will be a slowly changing dimension, and store Product history in terms of price etc. for each store. The table will include the columns
ToDate which describes when the data was entered, and when it was replaced (with default value year 2999 in
ToDate until a new update of the Product is inserted) respectively.
I Believe this is very common. However, the
ProductKeys from the underlying database are very complex strings, such as
My alternatives as I see it, is to use
Checksum in SQL Server to convert the complex strings to integers, and store these in a separate column
and use these columns when doing JOINS. But checksum does not guarantee unique integer values, which would likely cause problems. When I tried Checksum on a Virtual PC and my own PC, doing Checksum on the same values returned different results which is a concern as well. I Believe these two together rules out Checksum, unless it can be manipulated somehow to make it more unique?
Another alternative is to use more complex functions to ensure that the string values are converted into integers, such as the one provided on a question of mine here. A solution such as this however also has issues, the values
1003 will for instance get the same result. It is also complicated in the sense that if someone who isn't that savvy in SQL need to try to find any issues with the Query that uses the function, it may be very hard to understand what is happening.
My third option seem to be to use the
DimProduct table and update this table first from the SQL Agent, and using an Indexing Key on this table and use this index as the ProductKeyInt in the
FactStoreSale table (where ProductKeyInt will be some kind of subquery in for instance
FactStoreSale to fetch the value with the biggest
ToDate in DimProduct corresponding to the ProductKey.
Does anyone have any input? Is there a simpler way? I don't want to have strings as JOIN keys because of for example increased CPU time