Here is my approach..But can vary based on your many-many or one-many relations.
1. Add Region, State & City attributes to Location. U can use left join
Load ..... from ;
Load .... from city;
Load .... from state;
Load .... from region;
2. in ATM and Switch seems there is a Synthetic key.. This will solve 90% of the problem
Try creating this new columnn both tables and drop SwitchID & MapID from one of those tables. i
SwitchID & MapID as Key
3. Try to merge CALL tables as much as possible. Cant guess on how you can merge as i dont know the granularity.
Hi Kshitija, it depends on a lot of factors. But from a quick glance, I could tell you some advises:
1. You could put all the information of the Call into a single fact table (i.e Fact_Call), which means bring "CallForward", "CallLogStatus" and "CallService" to your current "CallLog" table.
2. Build a unique key to make the union between the new "Fact_Call" and the "CallClose_ATM" (I couldn't see any evident one right now, but for example something like CallLogId&RowNo() as Key_call_ATM)
3. Bring "Region", "State", "City" and "Location" to a single table (i.e Master_Location)
After those 3 steps you will have two fact tables, "Fact_Call" and "ATM" and a few dimensions (still not the best but better for Qlik than your current one.
Hope it helps,