0

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
  • What indexes do you have on this fact table? – Randolph West Aug 23 '16 at 6:03
  • @RandolphWest - Just did a dump of the table structure for the prices table. I've got 1 clustered index on the PriceDate, ProductId and SalesPriceSchemeId (which make up the primary key and is the columns that are always used in the join condition to this table) – Lock Aug 23 '16 at 6:04
  • In my experience DWHs usually include a price history (if you don't do it now, you might switch to it in the near future), but there should be no overlapping periods of validity (FromDate/ToDate), this is a horrible design. Btw, SQL Server 2016 finally brigs support for System-Versioned (aka Temporal) tables sqlmag.com/sql-server/… – dnoeth Aug 23 '16 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.