Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 ?
customertable:
Load *
from customer;
leftJoin(customertable)
Load *
from state;
leftJoin(customertable)
Load *
from region;
leftJoin(customertable)
Load *
from Division;
leftJoin(customertable)
Load *
from Customeraddress;
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);
could you make an example with prt sc ? it will be very helpful
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);
could you please explain why the budget table is connected to Customer?
Im ready with the datamodell could you help me to start with the layout ?