I am designing a data warehouse for a retail company.
The ERP stores prices by having a
FromDate and a
ToDate and these dates can overlap. The rules for getting the correct price is basically find the records that a certain date may be valid for, order by the primary key ID and the most recent is the one to use.
Our current data warehouse stores the values in the same manner, and then there is a
UDF that runs over this table to find the most recent price. The performance of this is not great.
The new data warehouse that I have designed basically stores a record for each day for each price, so that when you want to query pricing, you simply do a join onto the
Price table. The partitioned tables currently have just over 1 billion records. Performance isn't too bad.
The problem I'm now having is that each price could possibly have a
Backup price. If the price for that Pricing Scheme is null or 0, then we have to look into the same table for the price against the backup price scheme.
This is where the performance is going down the drain.
A query like this is what I am currently doing:
SELECT COALESCE(NULLIF(FactProductPrice.OriginalPrice, 0), BackupProductPrice.OriginalPrice) ....... LEFT OUTER JOIN dbo.FactProductPrice ON FactProductPrice.PriceDate = FactStock.StockDate AND FactProductPrice.ProductId = FactStock.ProductId AND FactProductPrice.SalesPriceSchemeId = MatWarehouse.SalesPriceSchemeId LEFT OUTER JOIN dbo.FactProductPrice AS BackupProductPrice ON BackupProductPrice.PriceDate = FactStock.StockDate AND BackupProductPrice.ProductId = FactStock.ProductId AND BackupProductPrice.SalesPriceSchemeId = DimSalesPriceScheme.BackupSalesPriceSchemeId
I'm willing to completely redesign how these are stored, but I'm struggling to think of a better way. I don't have any real requirement to keep historical pricing (no SCD requirements).
Is there any best practice or accepted way to storing range like data?
CREATE TABLE [dbo].[FactProductPrice2016]( [PriceDate] [date] NOT NULL, [ProductId] [int] NOT NULL, [SalesPriceSchemeId] [int] NOT NULL, [CurrentPrice] [numeric](9, 2) NULL, [OriginalPrice] [numeric](9, 2) NULL, PRIMARY KEY CLUSTERED ( [PriceDate] ASC, [ProductId] ASC, [SalesPriceSchemeId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[FactProductPrice2016] WITH CHECK ADD CONSTRAINT [CK__FactProductPrice2016] CHECK (([PriceDate]>='20160101' AND [PriceDate]<='20161231')) GO ALTER TABLE [dbo].[FactProductPrice2016] CHECK CONSTRAINT [CK__FactProductPrice2016] GO ALTER TABLE [dbo].[FactProductPrice2016] ADD PRIMARY KEY CLUSTERED ( [PriceDate] ASC, [ProductId] ASC, [SalesPriceSchemeId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO