Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ssis 2008 slowly changing dimensions

Posted on 2009-05-01
9
Medium Priority
?
315 Views
Last Modified: 2013-11-10
Hi,

I am trying to build dimensions tables and fact tables, My approach is to do datacleansing in staging level and load dimensions appropriately, I know we have very easy to use wizard in  ssis.But can any bodu let me know any different approach which can improve the performance of packages and also capture scd type1 and type2 appropriately and will have capabiliy to handle incremental loading also.  I am giving the structure of  staging table and dimension table below  please correct me if i am doing anything wrong.

 Thanks.
USE [Enterprise_Cube]
GO
 
/****** Object:  Table [dbo].[Stg_Address]    Script Date: 05/01/2009 11:51:06 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Stg_Address](
	[AddressID] [int] NOT NULL,
	[STREETADDRESS1] [varchar](50) NULL,
	[STREETADDRESS2] [varchar](50) NULL,
	[STREETADDRESS3] [varchar](50) NULL,
	[CITY] [varchar](50) NULL,
	[STATE] [varchar](50) NULL,
	[PostalCode] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[REGIONNAME] [varchar](25) NULL,
	[IATA Code] [varchar](4) NULL,
	[UPSCity] [varchar](50) NULL,
 CONSTRAINT [PK_Stg_Address] PRIMARY KEY CLUSTERED 
(
	[AddressID] 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
 
SET ANSI_PADDING OFF
GO
 
 
--Dimension table:
 
USE [Enterprise_Cube]
GO
 
/****** Object:  Table [dbo].[DimAddress]    Script Date: 05/01/2009 11:51:44 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[DimAddress](
	[AddressUID] [int] IDENTITY(1,1) NOT NULL,
	[AddressID] [int] NULL,
	[STREETADDRESS] [varchar](250) NULL,
	[CITY] [varchar](50) NULL,
	[STATE] [varchar](50) NULL,
	[PostalCode] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[REGIONNAME] [varchar](25) NULL,
	[IATA Code] [varchar](4) NULL,
	[UPSCity] [varchar](50) NULL,
	[StartDate] [date] NULL,
	[EndDate] [date] NULL,
	[Status] [bit] NULL,
	[CS_records]  AS (checksum([STREETADDRESS],[CITY],[STATE],[PostalCode],[Country],[REGIONNAME],[IATA Code],[UPSCity])),
 CONSTRAINT [PK_DimAddress] PRIMARY KEY CLUSTERED 
(
	[AddressUID] 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
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[DimAddress] ADD  CONSTRAINT [DF__Address_D__State__08EA5793]  DEFAULT ('NA') FOR [STATE]
GO
 
ALTER TABLE [dbo].[DimAddress] ADD  CONSTRAINT [DF_DimAddress_Start]  DEFAULT (getdate()) FOR [StartDate]
GO
 
ALTER TABLE [dbo].[DimAddress] ADD  CONSTRAINT [DF_DimAddress_End]  DEFAULT ('99990101') FOR [EndDate]
GO

Open in new window

0
Comment
Question by:sas77
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Accepted Solution

by:
Emes earned 600 total points
ID: 24281556
why do you have

 [EndDate] [date] NULL,
  [Status] [bit] NULL,

you do not need status.


0
 

Author Comment

by:sas77
ID: 24281732
Can you give me appropriate solution please if possible with mapping and code.
0
 
LVL 14

Assisted Solution

by:Emes
Emes earned 600 total points
ID: 24283444
you should not need the status you can look to the enddate field and check to see if it matches your endate.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 900 total points
ID: 24295957
Status is refered by kimball and almost people dont see any use to it... and I agree...
can you give us a textfile with some data for your staging table?
regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:sas77
ID: 24299444
I cannot give the data, i am sorry it is against our company policy.
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 900 total points
ID: 24300138
give test/sample data
0
 

Author Comment

by:sas77
ID: 24305021
Attached sample data in xlsx file
sampledata.xlsx
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 900 total points
ID: 24327438
Which atributtes in your dimension are Type1 and Type2? If all the same consider type 1?!
0
 

Author Closing Comment

by:sas77
ID: 31576970
Thanks ALL
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question