Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do i get this model most perfect? I want Lng and Lat down into my FACT. (they should be linked together by CustomerID)
I have this Dimension
QUALIFY*;
UNQUALIFY SessionCustomerKey;
DimCustomer:
LOAD CustomerKey as SessionCustomerKey,
PartnerID,
CustomerID,
PartnerKey,
CustomerKey,
CustomerGuid,
Lng,
Lat;
SQL SELECT *
FROM dbo.DimCustomer;
UNQUALIFY*;
And this Fact:
FactRows:
LOAD TotalRowsKey,
CustomerKey,
CustomerID,
AntalRowsCleansed,
AntalRowsStaCloud,
CreateDate;
SQL SELECT *
FROM RaptorMetaCustomer.dbo.TotalRows
Order by CreateDate Desc;
You can get by Left join or ApplyMap. I always prefer ApplyMap if you have unique combination. Please try either of any one.
ApplyMap Method:
QUALIFY*;
UNQUALIFY SessionCustomerKey;
DimCustomer:
LOAD CustomerKey as SessionCustomerKey,
PartnerID,
CustomerID,
PartnerKey,
CustomerKey,
CustomerGuid,
Lng,
Lat;
SQL SELECT *
FROM dbo.DimCustomer;
UNQUALIFY*;
MAP_LAN:
MAPPING LOAD DISTINCT DimCustomer.CustomerID AS A , DimCustomer.Lng AS B Resident DimCustomer ;
MAP_LAT:
MAPPING LOAD DISTINCT DimCustomer.CustomerID AS A , DimCustomer.Lat AS B Resident DimCustomer ;
DROP Fields DimCustomer.Lng , DimCustomer.Lat; //Drop these fields if not needed in DimCustomer table.
FactRows:
LOAD TotalRowsKey,
CustomerKey,
CustomerID,
ApplyMap('MAP_LAN', CustomerID, 'NA') AS Lan,
ApplyMap('MAP_LAT', CustomerID, 'NA') AS Lat,
AntalRowsCleansed,
AntalRowsStaCloud,
CreateDate;
SQL SELECT *
FROM RaptorMetaCustomer.dbo.TotalRows
Order by CreateDate Desc;
Left Join Method:
QUALIFY*;
UNQUALIFY SessionCustomerKey;
DimCustomer:
LOAD CustomerKey as SessionCustomerKey,
PartnerID,
CustomerID,
PartnerKey,
CustomerKey,
CustomerGuid,
Lng,
Lat;
SQL SELECT *
FROM dbo.DimCustomer;
UNQUALIFY*;
FactRows:
LOAD TotalRowsKey,
CustomerKey,
CustomerID,
ApplyMap('MAP_LAN', CustomerID, 'NA') AS Lan,
ApplyMap('MAP_LAT', CustomerID, 'NA') AS Lat,
AntalRowsCleansed,
AntalRowsStaCloud,
CreateDate;
SQL SELECT *
FROM RaptorMetaCustomer.dbo.TotalRows
Order by CreateDate Desc;
Left Join (FactRows)
LOAD DimCustomer.CustomerID AS CustomerID ,
DimCustomer.Lng AS Lng,
DimCustomer.Lat AS Lat
Resident DimCustomer ;
DROP Fields DimCustomer.Lng , DimCustomer.Lat; //Drop these fields if not needed in DimCustomer table.