Data Model Doubt

[#1]

Hi All,

Please correct me this data model.

Thank you.

Branch:
LOAD BranchID,
     Branch,
     CityID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Branch);

City:
LOAD CityID,
     City,
     CountryID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is City);

Country:
LOAD CountryID,
     Country,
     RegionID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Country);

Customer:
LOAD CustomerID,
     Customer,
     CityID,
     ContactNumber
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Customer);

Product:
LOAD ProductID,
     [Product Name]
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Product);

Region:
LOAD RegionID,
     Region
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Region);

Salesman:
LOAD SalesmanID as Salesman_ID,
     Salesman,
     CityID,
     ContactNumber as [Salesman ContactNumber]
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Salesman);

Transaction:
LOAD TransactionID,
     TransactionDate,
     ProductID,
     CustomerID,
     SalesmanID,
     BranchID as Branch_ID,
     SalesPrice,
     CostPrice,
     Discount,
     Qty
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Transaction);

[Anonymous]

I'd suggest you consolidate it into a single fact table.  Personally I'd use a series of ApplyMap() 's, but others may prefer joins.

[Colin-Albert]

Create a series of Mapping Tables for Branch, City, Customer, Salesman etc. Then you can use Applymap to bring this data into your transactions table.

See this post for more details.Don't join - use Applymap instead

[Anonymous]

Applymap is the best approach as suggested by Colin,

you dont need to load higher level hierarchy in the same table .

Ex.

City:

LOAD CityID,

     City,

     CountryID

No need to load 'CountryID' on this table .

(

City:

LOAD CityID,

     City)

load all the table with two columns (IDfield and Name)

You can later load it in other applymap table and directly populate Country in Fact table using Applymap function.

[ashfaq_haseeb]

Hi,

As suggested by all go with Applymap()

In case you have more then 1 description against any ID then ApplyMap() will not work here.

Regards

ASHFAQ

[#6]

Could anybody modify the entire script and provide.

[maxgro]

could you post some data as all your tables come from one file C:\QV\data.xlsx?

is much easier to answer with some data to work

[#8]

Enclosed the xls file in the first post. Please advise.

[maxgro · Accepted solution]

PFA

[#10]

Thank you All.