Facttable

[#1]

Hello experts im actually wondering about Facttable, i have a task from school to do, it is about circular references, synthetic keys and make a Facttable, i wonder why i should have a Facttable, or do i really need it? 

[#2]

the first join about Itemmaster and itemnumber is done but i dont know how to do the second one ?  where can i write the join ?

[gautik92]

customertable:

Load *

from customer;

leftJoin(customertable)

Load *

from  state;

leftJoin(customertable)

Load *

from  region;

leftJoin(customertable)

Load *

from  Division;

leftJoin(customertable)

Load *

from  Customeraddress;

[#4]

Here is my script for now

BUDGET:

LOAD BudgetPeriod,

     [Budget Amount],

     BudgetYear,

     [Customer Number],

     textBudgetPeriod

FROM

Datasources\Datasources\BUDGET.QVD

(qvd);

CUSTOMERADDRESS:

LOAD [Address Number],

     City,

     Country,

     [Customer Address 1],

     [Customer Address 2],

     [Customer Address 3],

     [Customer Address 4],

     State,

     [Zip Code]

FROM

Datasources\Datasources\CUSTOMERADDRESS.QVD

(qvd);

CUSTOMERS:

LOAD [Address Number],

     [Business Family],

     [Business Unit],

     Customer,

     [Customer Number],

     [Customer Type],

     Division,

     [Line of Business],

     Phone,

     [Region Code],

     [Regional Sales Mgr],

     [Search Type]

FROM

Datasources\Datasources\CUSTOMERS.QVD

(qvd);

DIVISION:

LOAD [Division Name],

     Division

FROM

Datasources\Datasources\DIVISION.QVD

(qvd);

ITEMBRANCH:

LOAD [Item Number],

     [Item Branch Category Code 6],

     [Item Branch G/L Category],

     [Item-Branch Key],

     [Item Branch Reorder Quantity],

     [Short Name]

FROM

Datasources\Datasources\ITEMBRANCH.QVD

(qvd);

Join

ITEMMASTER:

LOAD [Master Planning Family],

     [Product Department],

     [Product Group],

     [Product Line],

     [Product Price Group],

     [Product Sub Group],

     [Product Type],

     [Short Name]

FROM

Datasources\Datasources\ITEMMASTER.QVD

(qvd);

PRODUCTGROUP:

LOAD [Product Group],

     [Product Group Desc]

FROM

Datasources\Datasources\PRODUCTGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTLINE:

LOAD [Product Line],

     [Line Number],

     [Product Line Desc]

FROM

Datasources\Datasources\PRODUCTLINE.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTSUBGROUP:

LOAD [Product Sub Group],

     [Product Sub Group Desc]

FROM

Datasources\Datasources\PRODUCTSUBGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

REGION:

LOAD [Region Name],

     [Region Code]

FROM

Datasources\Datasources\REGION.QVD

(qvd);

SALESDETAILS:

LOAD [Actual Delivery Date],

    [Address Number],

     CustKey,

     [Backlog Amount],

     [BackOrder Amount],

     DateKey,

     [Discount Amount],

     [Doc Type],

     [Family Class],

     [Invoice Date],

     [Invoice Number],

     [Item-Branch Key] as key ,

     [Item Class],

   //  [Item Number],

     [Line Desc 1],

    // [Line Number],

     [Line Type],

     [List Price],

     [Order Number],

     [Parent Address Number],

     [Payment Terms],

     [Promised Delivery Date],

     [Sales Amount],

     [Sales Amount Based on List Price],

     [Sales Cost Amount],

     [Sales Margin Amount],

     [Sales Price],

     [Sales Quantity],

     [Sales Rep],

     [Sales Unit Cost Price],

     [U/M],

     [Unit Price]

FROM

Datasources\Datasources\SALESDETAILS.QVD

(qvd);

SALESREP:

LOAD [Sales Rep],

     [Sales Rep Name]

FROM

Datasources\Datasources\SALESREP.QVD

(qvd);

STATEDESCRIPTION:

LOAD State,

     state_abbr,

     state_name

FROM

Datasources\Datasources\STATEDESCRIPTION.QVD

(qvd);

[#5]

could you make an example with prt sc ? it will be very helpful

[#6]

I got now exactly the same datamodel as you said with this script, is this correct?

CUSTOMERS:

LOAD

     [Address Number],

     [Business Family],

     [Business Unit],

     Customer,

     [Customer Number],

     [Customer Type],

     Division,

     [Line of Business],

     Phone,

     [Region Code],

     [Regional Sales Mgr],

     [Search Type]

FROM

Datasources\Datasources\CUSTOMERS.QVD

(qvd);

BUDGET:

join (CUSTOMERS)

LOAD  BudgetPeriod,

     [Budget Amount],

     BudgetYear,

     [Customer Number],

     textBudgetPeriod

FROM

Datasources\Datasources\BUDGET.QVD

(qvd);

CUSTOMERADDRESS:

join (CUSTOMERS)

LOAD [Address Number],

     City,

     Country,

     [Customer Address 1],

     [Customer Address 2],

     [Customer Address 3],

     [Customer Address 4],

     State,

     [Zip Code]

FROM

Datasources\Datasources\CUSTOMERADDRESS.QVD

(qvd);

DIVISION:

join (CUSTOMERS)

LOAD [Division Name],

     Division

FROM

Datasources\Datasources\DIVISION.QVD

(qvd);

ITEMBRANCH:

LOAD [Item Number],

     [Item Branch Category Code 6],

     [Item Branch G/L Category],

     [Item-Branch Key],

     [Item Branch Reorder Quantity],

     [Short Name]

FROM

Datasources\Datasources\ITEMBRANCH.QVD

(qvd);

Join (ITEMBRANCH)

ITEMMASTER:

LOAD [Master Planning Family],

     [Product Department],

     [Product Group],

     [Product Line],

     [Product Price Group],

     [Product Sub Group],

     [Product Type],

     [Short Name]

FROM

Datasources\Datasources\ITEMMASTER.QVD

(qvd);

PRODUCTGROUP:

join (ITEMBRANCH)

LOAD [Product Group],

     [Product Group Desc]

FROM

Datasources\Datasources\PRODUCTGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTLINE:

join (ITEMBRANCH)

LOAD [Product Line],

     [Line Number],

     [Product Line Desc]

FROM

Datasources\Datasources\PRODUCTLINE.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTSUBGROUP:

join (ITEMBRANCH)

LOAD [Product Sub Group],

     [Product Sub Group Desc]

FROM

Datasources\Datasources\PRODUCTSUBGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

REGION:

join (CUSTOMERS)

LOAD [Region Name],

     [Region Code]

FROM

Datasources\Datasources\REGION.QVD

(qvd);

SALESDETAILS:

LOAD [Actual Delivery Date],

    [Address Number],

     CustKey,

     [Backlog Amount],

     [BackOrder Amount],

     DateKey,

     [Discount Amount],

     [Doc Type],

     [Family Class],

     [Invoice Date],

     [Invoice Number],

     [Item-Branch Key] as key ,

     [Item Class],

   //  [Item Number],

     [Line Desc 1],

     [Line Number],

     [Line Type],

     [List Price],

     [Order Number],

     [Parent Address Number],

     [Payment Terms],

     [Promised Delivery Date],

     [Sales Amount],

     [Sales Amount Based on List Price],

     [Sales Cost Amount],

     [Sales Margin Amount],

     [Sales Price],

     [Sales Quantity],

     [Sales Rep],

     [Sales Unit Cost Price],

     [U/M],

     [Unit Price]

FROM

Datasources\Datasources\SALESDETAILS.QVD

(qvd);

STATEDESCRIPTION:

join (CUSTOMERS)

LOAD State,

     state_abbr,

     state_name

FROM

Datasources\Datasources\STATEDESCRIPTION.QVD

(qvd);

[#7]

could you please explain why the budget table is connected to Customer?

[#8]

Im ready with the datamodell could you help me to start with the layout ?