Need help with Data Model

[evgeniystuchalk]

Hello! I'm building data model for CRM. I have following entities:

Lead

Contact

Task

Note

Each Lead connected to Contact

Tasks and notes can be connected to Lead or Contact

All connections works via id-fields.

When i'm loading data in 'as is' structure, i'm gettig SynKey (as espected ). Model looks like this:

In attempt to avoid SynKeys, i created additional id fields, to follow the rule: "All tables connected with only one fields".

Tasks and Notes splitted in four tables instead two, for Leads and Contacts. SynKeys gone. Now model looks like this:

And finally, the question All of this tables contains another common fields. For example, Resposible-worker. Each deal, contact, task or note can have their own responsible worker (all from single list of workers). if i Qualify field Resposible-worker, i can't use it like single dimension for filtering. But i really need that What can i do with data model to reach that?

[martinpohl]

Hello,

I would concatenate Leads to Nodes (or in other order).

Example:

Leads_Notes:

sql select * from Notes;

conactenate (Leads_Notes) load *;

sql select * from Leads;

sql select * from Contacs;

sql select * from Task;

Like in your data model, lead_ID and Contact ID should have same fieldname

Regards

[evgeniystuchalk]

You mean, I need to join all my tables in the one, for all entities?

[Kushal_Chawda]

You can comment the ContactID from Notes & Task table as you have this already in Lead table. Try to connect all table on LeadID

[reddy-s]

Hi,

You can concatenate the field in the sync table to form a unique key. Use this unique key to link your tables, this will remove the existing synthetic key.

Thanks,

Sangram.

[kaushiknsolanki]

Hi,

Check this links for more help.

http://www.analyticsvidhya.com/blog/2014/11/synthetic-keys-qlikview-simplified/

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Regards,

Kaushik Solanki