Hi All,
Trying to establish Link table between 1 Fact and 4 Dim tables. Require your guidance.
[FACT]:
LOAD AutoNumber(memberid) as memberid, //member performing the activity under the named event
activity_date, //activity was perfomed on this date
activity_id, //activity performed by the Member
generated_power,
generated_power_time,
event_id as Fact_EventId; //event related to perform the activity
SELECT * FROM SF.FactData;
[Members]:
LOAD email as UserLogin,
firstName &' ' & lastName as FullName,
dob as DOB,
Replace(
Replace(gender,'0','male'),'1','female') as Gender,
serverEventId as EventId,
// memberid as memberid,
AutoNumber(serverEventId&'|'& memberid) as %Key_WEP_EventId_UserId;
Left Join (Members)
[EVENTS_DATA]:
LOAD EventId,
EventName,
EventType,
TotalParticipants,
Date(TimeStamp(Makedate(1970,1,1)+ EventStartDate /24/60/60)) as EventStartDate;
SELECT * FROM “SF.Test_EventsData";
[EVENT_REGISTRATION]:
LOAD last_event_category as LastEvent_CID,
recommended_category as Recommended_CID,
AutoNumber(event_id&'|'&memberid) as %Key_ER_EventId_MemberId;
Select* from SF.EVREG;
LINK_TABLE:
Load Distinct memberid Resident FACT;
//Drop Field memberid from FACT;
Concatenate(LINK_TABLE)
Load Distinct %Key_WEP_EventId_UserId Resident Members;
Concatenate(LINK_TABLE)
Load Distinct %Key_ER_EventId_UserId Resident EVENT_REGISTRATION;
Now, just wanna know when I have 2 common unique fields / identifiers in 2 different dimension tables.. is my approach correct to build a Link Table. Kindly check the screenshot.
How should I check and confirm this using Subset ratio and ID? Kindly help please.
