Skip to main content
Database Administrators

Return to Revisions

4 of 13
deleted 140 characters in body
user avatar
user avatar

Create Slowly changing dimension table from Transaction

I am receiving transaction data from a file, its contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with Business Key on ProductId. Many attributes exist: ProductTitle, Category, this is a sample table, actually have around 10 more attributes. How do I create a Product Slowly Changing Dimension query which will insert into a table?

Searching for a performance optimized way, if I have 10 columns, not sure if group, partition by on 10 columns is optimal

With SQL 2016, is there some function to obtain this data? Should I use Lead/Lag Function? New analytics syntax? I have attempted query below.

Current repeated Data:

create table dbo.Product
(
    ProductId int,
    ProductTitle varchar(55),
    ProductCategory varchar(255),
    Loaddate datetime
)

insert into dbo.Product
values 
 (1,'Table','ABCD','3/4/2018')
,(1,'Table','ABCD','3/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/2018')
,(1,'Table','XYZ','3/9/2018')
,(1,'Table-Dinner', 'GHI','3/10/2018')
,(1,'Table-Dinner', 'GHI','3/11/2018')
....more data with ProductId =2,3,4, etc

Expected Output:

create table #ProductHistory
(
    Productid int,
    ProductTitle varchar(55),
    ProductCategory varchar(255),
    BeginDate datetime,
    EndDatetime datetime
)

 (1,'Table','ABCD','3/4/2018','3/6/2018')
,(1,'Table','XYZ','3/7/2018', '3/9/2018')
,(1,'Table-Dinner', 'GHI','3/10/2018','12/31/9999')

Attempted Query (seems to be inefficient, especially when having 10 attribute columns)

 select
    Productid
    ,ProductTitle
    ,ProductCategory
    ,ISNULL(min(LoadDate), '12/31/9999') as BegniDate
    ,ISNULL(max(loaddate), '12/31/9999') as EndDate
from product
group by Productid, ProductTitle, ProductCategory
user162241