From Scratch - Input Excel to DW Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

From Scratch - Input Excel to DW Tables

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-08-23 : 05:29:38

Hi,
Got an Input Excel with some fields Including Month Wise Data in Columns.
Have to Convert this Excel to Unpivoting Month Fields to row Wise.
And Have to Pivot Another Column Values as Rows from Unpivoted data.
From the Pivoted Data Unique Column Values will be Stored in Datawarehouse Dimension Tables and Fact Table will contains Pivoted Data.
Need to Built these Above Steps using SSIS. Please help me with Wahat Components I have to Use in SSIS to built this Module.

Regards,
Kalai

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-08-27 : 23:19:48

please show us sample of data what it looks like before transform and what you expect it to be after transform and pivot

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 03:27:06

based on your explanation you need a data flow task with following components

1. Excel source to connect to your excel file
2. Unpivot transform to unpivot the values
3. Pivot tranform to pivot rows
4. Multicast to create multiple copies of data flow
5. two OLEDB destination connecting two outputs of Multicast one to Dimension and other to fact

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-08-28 : 12:51:14

Input Excel:
[Location][Category][Brand][Volume][JAN13][FEB13][MAR13][APR13]
Chennai Oil VVD 100 567 617 804 8089
Chennai Oil Parachute 100 6185 5868 8301 9569
Chennai Oil Amla 100 987 6020 173 7513
Chennai Oil French 500 1936 7431 7278 114
Chennai Oil German 500 8616 158 4921 7833
Chennai Oil Italy 500 5323 8497 8273 6865
Chennai Oil Orysa 1000 1943 3992 4521 9224
Chennai Oil Idhayam 1000 9195 5877 5523 9238
Chennai Oil SSV 500 1081 4011 2223 3628
Chennai Oil Fortune 1000 5428 5053 8173 6556

1st Output: (Unpivot) (1st row Sample)

[Location] [Category] [Brand] [Volume] [Month] [Value]
Chennai Oil VVD 100 JAN13 567
Chennai Oil VVD 100 FEB13 617
Chennai Oil VVD 100 MAR13 804
Chennai Oil VVD 100 APR13 8089

Above Sample Output is for 1st Row. Unpivoting Month Columns. The Same follow with other Rows from Input Excel..

2nd Output: (Distinct Column Values to Dim and Product Table)

Dimension Table:
Location,Month (Distinct Values of Location Column and Month Column)

Ex: Location Dimension ID.
Chennai - 1001 (ID)
Jan13 - 012013
Feb13 - 022013
Mar13 - 032013
Apr13 - 042013

Product Table:
[PID][LocationID] [Category] [Brand]
1 1001 Oil VVD
2 1001 Oil Parachute
3 1001 Oil Amla
4 1001 Oil French
5 1001 Oil German
6 1001 Oil Italy
7 1001 Oil Orysa
8 1001 Oil Idhayam
9 1001 Oil SSV
10 1001 Oil Fortune

3rd Output: (Pivot from Unpivoted Data)

[Location] [Category] [Brand] [Month] [100] [500] [1000]
Chennai Oil VVD Jan13 567
Chennai Oil VVD FEB13 617
Chennai Oil VVD MAR13 804
Chennai Oil VVD APR13 8089

Above Output comes from the Unpivoted Data Sample.. The Same will be Repeated for Other Rows as Given in Sample.. While Pivoting Volume Values will be Transposed to Columns and The Data Remains Same.. Now I have given it for Volume 100.

Final Ouput : Fact Table

The Above Table Pivoted Table will be remain same as Fact Table.
With Product Table ID.
Fact Table:
[FID] [LocationID] [PID] [MonthID] [100] [500] [1000]
1 1001 1 012013 567
2 1001 1 022013 617
3 1001 1 032013 804
4 1001 1 042013 8089

The Above Table Comes from Pivoted Data. With Joining Dimension Tables (Loc and Month) with Product Table.
All the Above is my Actuall Process Work Flow to Develop an ETL using SSIS.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 13:14:58

whats the issue you're facing? didnt see any issues specified in last post.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-08-29 : 02:23:34

Need Try Catch Checks.
Have to Check Database Tables and Input Excel for Any New Entry or Duplicates etc.
And Later Need to do Ur Steps (Unpivot,Pivot,Multitask etc..)
And Importantly Need to store Metadata in Database (With Job Name, Job Data etc..)

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:39:57

quote:
Originally posted by Kalaiselvan

Need Try Catch Checks.
Have to Check Database Tables and Input Excel for Any New Entry or Duplicates etc.
And Later Need to do Ur Steps (Unpivot,Pivot,Multitask etc..)
And Importantly Need to store Metadata in Database (With Job Name, Job Data etc..)

Regards,
Kalai

you can use Event Handlers for try catch functionality that in SSIS. Events available are for pre execute,post execute, error, variable value changes etc
For storing metadata you can make use of internaly available system variables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources