topic Re: From Scratch - Input Excel to Dimension Tables And Fact Table Load in Talend Studio

From Scratch - Input Excel to Dimension Tables And Fact Table Load

Anonymous — Tue, 13 Aug 2013 07:15:31 GMT

Got an Excel with Some Months Data.. Need to Unpivot those Months to Rows and Select the UniQue Column values for Dimension Tables.
From the Particular Column With Months Data need to Create Fact Table with Values.
Note:
Need to Create Unpivot From Excel..
And Pivot From Unpivoted Data.
Distinct Column Values for Dimension Table (With No Null Value)
From Pivoted Data Need to Form Fact Table with Dimension Table Join.

Re: From Scratch - Input Excel to Dimension Tables And Fact Table Load

Anonymous — Tue, 13 Aug 2013 07:20:36 GMT

Hi,

Got an Excel with Some Months Data.. Need to Unpivot those Months to Rows and Select the UniQue Column values for Dimension Tables.
From the Particular Column With Months Data need to Create Fact Table with Values.

It seems that you use tFileinputExcel-->tunpivotrow-->tuniquRow-->tDBoutput....
Could you please elaborate your case with an example with input and expected output values? So that we can get your requirement more precisely.
Best regards
Sabrina

Re: From Scratch - Input Excel to Dimension Tables And Fact Table Load

Anonymous — Tue, 13 Aug 2013 11:56:00 GMT

Input Excel:

India Chennai Oil Hair Oil P & G VVD 100 567 617 804 8089
India Chennai Oil Hair Oil P & G Parachute 100 6185 5868 8301 9569
India Chennai Oil Hair Oil P & G Amla 100 987 6020 173 7513
India Chennai Oil Olive Oil Others French 500 1936 7431 7278 114
India Chennai Oil Olive Oil Others German 500 8616 158 4921 7833
India Chennai Oil Olive Oil Others Italy 500 5323 8497 8273 6865
India Chennai Oil Refined Oil P & G Orysa 1000 1943 3992 4521 9224
India Chennai Oil Refined Oil P & G Idhayam 1000 9195 5877 5523 9238
India Chennai Oil Refined Oil P & G SSV 500 1081 4011 2223 3628
India Chennai Oil Refined Oil P & G Fortune 1000 5428 5053 8173 6556
1st Output: (Unpivot)

India Chennai Oil Hair Oil P & G VVD 100 JAN13 567
India Chennai Oil Hair Oil P & G VVD 100 FEB13 617
India Chennai Oil Hair Oil P & G VVD 100 MAR13 804
India Chennai Oil Hair Oil P & G 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:
Country,Location,Month (Distinct Values of Country Column and Location Column and Month Column)
Ex: Country and Location Dimension ID.
India - 101 (ID)
Chennai - 1001 (ID)
Jan13 - 012013
Feb13 - 022013
Mar13 - 032013
Apr13 - 042013
Product Table:

1 101 1001 Oil Hair Oil P & G VVD
2 101 1001 Oil Hair Oil P & G Parachute
3 101 1001 Oil Hair Oil P & G Amla
4 101 1001 Oil Olive Oil Others French
5 101 1001 Oil Olive Oil Others German
6 101 1001 Oil Olive Oil Others Italy
7 101 1001 Oil Refined Oil P & G Orysa
8 101 1001 Oil Refined Oil P & G Idhayam
9 101 1001 Oil Refined Oil P & G SSV
10 101 1001 Oil Refined Oil P & G Fortune
3rd Output: (Pivot from Unpivoted Data)

India Chennai Oil Hair Oil P & G VVD Jan13 567
India Chennai Oil Hair Oil P & G VVD FEB13 617
India Chennai Oil Hair Oil P & G VVD MAR13 804
India Chennai Oil Hair Oil P & G 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:

1 101 1001 1 012013 567
2 101 1001 1 022013 617
3 101 1001 1 032013 804
4 101 1001 1 042013 8089
The Above Table Comes from Pivoted Data. With Joining Dimension Tables (Country, Loc and Month) with Product Table.

Re: From Scratch - Input Excel to Dimension Tables And Fact Table Load

Anonymous — Thu, 15 Aug 2013 19:09:43 GMT

Hi sabrina,
I have elaborate my case with an Example, Please help me to find the Solution. Waiting for ur Reply.
Attached Excel Snap shots of Input & Output needed.