Revisions to Create Slowly Changing Dimension table from Repeated Data

added [performance-tuning] to 516 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:22

Community Bot

1

Notice removed Authoritative reference needed by CommunityBot

occurred Dec 10, 2018 at 3:01

Bounty Ended with no winning answer by CommunityBot

occurred Dec 10, 2018 at 3:01

Notice added Authoritative reference needed by user162241

occurred Dec 2, 2018 at 1:30

Bounty Started worth 50 reputation by CommunityBot

occurred Dec 2, 2018 at 1:30

added 75 characters in body

Source Link

edited Nov 30, 2018 at 7:41

user162241

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

Note: Data comes from a 1970 legacy file system containing historical data.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

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

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

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

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

Note: Data comes from a 1970 legacy file system containing historical data.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

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

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

added 93 characters in body

Source Link

edited Nov 30, 2018 at 6:10

user162241

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below. Also want last value to be 12/31/9999 or Current.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

(seems to be inefficient, especially when having 10 attribute columns, also want last value to be 12/31/9999)

 select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,ISNULL(min(product.LoadDate), '1/1/1900') as BeginDate
    ,ISNULLcase when max(product.LoadDate)  = (select max(subproduct.LoadDate), from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below. Also want last value to be 12/31/9999 or Current.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

(seems to be inefficient, especially when having 10 attribute columns, also want last value to be 12/31/9999)

 select
    Productid
    ,ProductTitle
    ,ProductCategory
    ,ISNULL(min(LoadDate), '1/1/1900') as BeginDate
    ,ISNULL(max(LoadDate), '12/31/9999') as EndDate
from dbo.Product
group by Productid, ProductTitle, ProductCategory

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. 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 table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

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/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/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

Current Repeated Data in File:

Expected Output:

Attempted Query

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

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

added 88 characters in body

Source Link

edited Nov 30, 2018 at 5:33

user162241

Loading

Post Undeleted by user162241

occurred Nov 30, 2018 at 5:32

Post Deleted by user162241

occurred Nov 30, 2018 at 5:13

added 71 characters in body

Source Link

edited Nov 30, 2018 at 4:01

user162241

Loading

edited title

Link

edited Nov 30, 2018 at 3:26

user162241

Loading

deleted 149 characters in body

Source Link

edited Nov 30, 2018 at 3:16

user162241

Loading

deleted 4 characters in body

Source Link

edited Nov 30, 2018 at 3:04

user162241

Loading

added 25 characters in body; edited title

Source Link

edited Nov 30, 2018 at 2:58

user162241

Loading

deleted 140 characters in body

Source Link

edited Nov 30, 2018 at 0:58

user162241

Loading

deleted 13 characters in body

Source Link

edited Nov 30, 2018 at 0:52

user162241

Loading

added 295 characters in body

Source Link

edited Nov 29, 2018 at 23:51

user162241

Loading

Source Link

asked Nov 29, 2018 at 23:43

user162241

Loading

Stack Exchange Network

Return to Question