Translating Salesforce Relationship into Qlikview Data Modeling

[#1]

We have two tables called Task and Event in Salesforce. Task could be a record created after logging a call, sending an email, or other to-do items. Event represents events in the calendar. We also have a Contact, Account, Case, Opportunity, etc. primary tables with which Task and Event have one(primary table) to many(event or task) relationship.

Task and Event table have the column called WhoId and WhatId. WhatId contains the ID of the parent record from the parent table (could be Account, Opportunity, Case, etc) with which Task and Event are associated with and WhoId is the ID of the record in the Contact table. The problem is, WhatId can contain the ID of the parent record any one of the parent table (could be Account, Opportunity, Case, etc) with which Task and Event are associated with.

How can we translate this into Qlikview Data Modelling? Please note that natively Salesforce tables follow relational modelling. For e.g. Each Account or Contact or other primary tables can have Task and Event but Account and Contact table are also related with each other.

Any pointers will be greatly appreciated.

Thanks

[evan_kurowski]

Hello Raish Shrestha,

Based on your description and diagrams, here's the starting point that I would use for the foundation of the Salesforce data model.

Obviously as you described, if you brought in full field listings from all tables, there would be additional potentials for circular references or compound-associations.  But there's no rule that says you have to bring them all in, and sidestepping some of the source system complexities is one of the perks of being the app designer.

Building from this point forward should be expanded to only what is absolutely necessary to the application.  Can you get the descriptive information the app needs to function from a single dimension field even if that field populates with the same info across multiple dimensions?  Take one set, leave the rest behind, etc.. Having the flexibility to make these introductions in a controlled manner should prevent your data model from getting overly complicated.

[#3]

Evan Kurowski - Thank you so much for taking the time out for your responding to my queries with such detailed example. However, I still have an issue translating how the 'WhatId' in Task table can be ID of a record in any one of the table (Case, Opportunity, Account etc.). Plus we can't really name ID of each table as 'WhatId' because it violates the fundamental relation among the object. Is there any other way we can link Task/Event with Parent Objects? Following's the way tables are formed in Salesforce backend..

[evan_kurowski · Accepted solution]

Hello Raish,

I see the same key values appearing through the TaskId and WhatId fields, and it seems clear how these tables need to interrelate.  You did mention that you were not inclined to alias the dimension ID fields, but the aliasing would only be within QlikView and not on the underlying SF tables, and it still seems an expedient route.  You may have additional reasons I'm not understanding why this approach may not be right (are you storing data on the completion side of this QV process?  There's still a way to separate the unified table back into its individual components if needed), but I'm going to demonstrate how these tables can be unified into a single FACT table, without losing the ability to separate or categorize.

You have 4 "strains" of data, so these 4 tables are all concatenated into a single unified table called FACT and each row is tagged with the values 'C','O','A','T' in the field OBJ_TYPE.

Though the data model has only 3 fields, you can see in the image and attached app using set-analysis around the OBJ_TYPE field, all of the original tables can be isolated from the main body of data based on any particular strain of OBJ_TYPE.

The division of tables is still there, just converted from table instantiation, to distinction through field values.  (The image shows how the UI takes the table FACT and dissects to isolate any strain)
Then from this base skeleton, (which is essentially a two-field link table with a categorization field snapped on), then you can begin attaching 1 to N descriptive dimensions in snowflake manner to either TaskId or WhatId, and adding additional UI controls that can perform the targeted analysis and are probably where the fields involved in KPI or metric definitions reside.

But the above framework could form the spine, and you can see in the next image how the controls on the right begin to flesh out how calculations would be applied.

Anyway.. not trying to push this, just offering options.  Hope you make good headway and thank you.

[#5]

Wow! Evan Kurowski , you're taking it to the next level and thanks for the time you've put into this..sorry if my questions confused you as i am new to qlikview data modelling..

Is it possible to share this app?

[evan_kurowski]

Sure thing Raish, happy to get a look at some of the Salesforce data structures, knowing what to expect and having a prepared approach will be helpful.

I suppose anyone who visits this thread can access the app, so sure.

[#7]

Hi Evan Kurowski..just to back tracking.while creating the data model in Qlikview equivalent to that of Salesforce everything makes sense until when there’s one field in say Table A whose value can be value in the corresponding field in any one of Table B, C, D……. Also, renaming id for all the parent objects like Account, Opportunity, CaseId to WhatId would create an undesired relationship between those tables because Account is parent object of many other objects other than Task or Event which have WhatId as field..Account has it's own Id(say Account.Id), Opportunity has it's own Id(say Opportunity.Id) and WhatId in Task can be equal to either Account.Id or Opportunity.Id..Please find following information on what I have so far in terms of scripting and also an extended example of tables with ids and their relationships.. Thank you so much for your help on this one Evan!

Salesforce Data Model

salesforce.com/us/developer/docs/api/Content/sforce_api_erd_majors.htm

salesforce.com/us/developer/docs/api/Content/sforce_api_erd_activities.htm

Qlikview Script

// I am creating multiple tabs and current the table viewer screenshot is attached at the end of the doc

TAB NAME: MAIN (Removed user name and password from CUSTOM CONNECT TO)

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//=======================================================================================================================================
// Salesforce DLL Connection
// API 29
//=======================================================================================================================================
CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;T29;XUserId=;XPassword=;";

TAB NAME: RELOAD HISTORY

//=======================================================================================================================================
// This section captures the last reload and current beginning reload times. These timestamps will be used for incremental loads.
// During the incremental load, the data entered or modified between vLastExecTime and vExecTime will be loaded, plus the historical
// data without duplications from Qvds.
// The data entered or modified after the start of the current reload will be ignored. 
// (by SNA on 04/12/2007)
// Updated GUI 05/10/2011 ICD
//=======================================================================================================================================

SET vLastExecTime   = 0; // resetting vLastExecTime
LET vLastExecTime   = timestamp(if(isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');
LET vExecTime              = timestamp(UTC(), 'YYYY-MM-DD hh:mm:ss');
LET vTodaysDate     = today();


//For the 1st reload, this section will be skipped.
LET FirstReload = isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'));

if Not $(FirstReload) then
// Read Reload History Data
       ReloadHistory:
Load
No,
[Last Reload Ended],
[Reload Started]
FROM $(vQVDPath)ReloadHistory.qvd (qvd);

end if

ReloadHistory:
LOAD
RowNo() as No,
'$(vLastExecTime)' as [Last Reload Ended],
'$(vExecTime)' as [Reload Started]
Autogenerate(1);

STORE * FROM ReloadHistory INTO $(vQVDPath)ReloadHistory.qvd;

TAB NAME: ACCOUNT

Account_SQL:
SQL SELECT
*
FROM Account;

QUALIFY *;

//UNQUALIFY Account.IsCustomerPortal;

Account:
LOAD
Id

RESIDENT Account_SQL;

DROP TABLE Account_SQL;

UNQUALIFY *;

TAB NAME: OPPORTUNITY

Opportunity_SQL:
SQL SELECT
*
FROM Opportunity;

QUALIFY *;
UNQUALIFY Account.Id;

Opportuntiy:
LOAD DISTINCT      
Id,
AccountId as Account.Id

RESIDENT Opportunity_SQL;

DROP TABLE Opportunity_SQL;

UNQUALIFY *;

TAB NAME: ACCOUNTSHARE

AccountShare_SQL:
SQL SELECT
*
FROM AccountShare;

QUALIFY *;
UNQUALIFY Account.Id;

AccountShare:
LOAD
Id,
AccountId as Account.Id
//UserOrGroupId as ??? could be Id from either User or Group..

RESIDENT AccountShare_SQL;

DROP TABLE AccountShare_SQL;

UNQUALIFY *;

TAB NAME: AccountHistory

AccountHistory_SQL:
SQL SELECT
*
FROM AccountHistory;

QUALIFY *;
UNQUALIFY Account.Id;

AccountHistory:
LOAD
Id,
AccountId as Account.Id

RESIDENT AccountHistory_SQL;

DROP TABLE AccountHistory_SQL;

UNQUALIFY *;

TAB NAME: AccountFeed

AccountFeed_SQL:
SQL SELECT
*
FROM AccountFeed;

QUALIFY *;
UNQUALIFY Account.Id;

AccountFeed:
LOAD
Id,
ParentId as Account.Id

RESIDENT AccountFeed_SQL;

DROP TABLE AccountFeed_SQL;

UNQUALIFY *;

TAB NAME: Contacts

Contact_SQL:
SQL SELECT
*
FROM Contact;

QUALIFY *;
UNQUALIFY Account.Id;

Contact:
LOAD DISTINCT      
Id,
AccountId as Account.Id

RESIDENT Contact_SQL;

DROP TABLE Contact_SQL;

UNQUALIFY *;

TAB NAME: Users

User_SQL:
SQL SELECT
*
FROM User;

//QUALIFY *;

User:
LOAD
Id

RESIDENT User_SQL;

DROP TABLE User_SQL;

//UNQUALIFY *;

TAB NAME: Event

Event_SQL:
SQL SELECT
*
FROM
Event;

QUALIFY *;
UNQUALIFY Contact.Id;

Event:
LOAD
Id,
// WhatId as ??? could be Id from Account or Opportunity or...,
       WhoId as Contact.Id

RESIDENT Event_SQL;

DROP TABLE Event_SQL;

UNQUALIFY *;

TAB NAME: Group

Group_SQL:
SQL SELECT
*
FROM Group;

QUALIFY *;

Group:
LOAD
Id

RESIDENT Group_SQL;

DROP TABLE Group_SQL;

UNQUALIFY *;

[evan_kurowski]

Hello Raish,

In this particular data model there are elliptical possibilities, depending upon the route of association.  If you load the entire field structure as is, you can see you'll start producing circular references, or scenarios where QlikView wants to loosely couple tables.

If you load the tables and massive synthetic associations start appearing, it can be a sign that redundant or parallel sets of data are being encapsulated in the data model.

Which table(s) are going to yield the finite list of ID's you are interested in analyzing can vary based on where you start your line of inquiry.  The reason we get errors for circular references is because you can only look at "the front half of the sphere" at any given time.  If you wish to view the back half of the sphere, you must have a second perspective or point of view.

For example, following your keyfield association through your sample data:

If you selected '001U' from [User Table] it associates with [ContactShare.ID] '01CS' and [AccountShare.ID] '02AS'.

If we then take these values to [ContactShare] table,

[ContactShare.ID] '01CS' associates with [ContactShare.UserOrGroupID] '001U', '001G'

Following this chain, it's impossible to isolate '001U' because it loops back on itself in the next table and 001U is lumped back in with 001G.

So it is necessary to define a starting point of reference and move outward from that direction.  Remove possibilities that reintroduce alternate routes of association that defy your primary categorization.

In this thread as we have been discussing, the primary "fact" driver has been the data of the combined Event & Task tables, so I will continue to center examples around that model.

Attached and illustrated are two-variations on the earlier architecture premise, but incorporating all the new tables and key values your latest example has expanded to include.

The first example preserves the concept that the "What" and "Who" ids must exist as two-separate sets of table entities.  This requires cloning the user groups into two end-points to avoid circular references

The second example operates under the premise that "Whats" and "Whos" can all really be categorized as "attributes" of a main identified event.