Design Question

Question

Hi,

I have multiple sources of contacts. Users want to intersect across the multiple sources and also get contacts from both of the sources. For example:

Query 1. Give me everybody that exist in Source 1 or Source 2.

Query 2. Give me everybody that exist in Source 1 and also in Source2.

In Query 2 they want the intersection between the contacts in source 1 and source2.

Question is should I be creating a Single Dimension of Contacts for Source 1 and Source 2 and create named set for Query 2 ? Or what is the best approach to follow ?

Thanks,

Answer 1

In my point of view you should go for a single dimension only. By the way do you need to show the source also while reporting ? If not needed while loading the data into the dimension , you will be loading the data from source 1 and from source 2 which is not available in source 1. In that way that should answer all your queries.

Answer 2

Thanks for the reply.

Yes users do want to see which source the data belongs to. This DW will be used also for Marketing purpose where the users will be querying the cube to see the contacts which exist in Source 1 but not in Source 2 etc..

So, in that case, are you suggesting a single dimension for all the contacts and custom named sets for the queries where users want to slice and dice between Source 1 and Source 2 ?

Answer 3

I would create a single dimension with one of two options.  If you have a limited number of sources, creating separate columns for each source allows for the simplest of the query options.  This is a method I have used quite often and would recommend it.

If you have multiple sources with an unkown number or a number that will grow, you might be better served by creating a dimension for sources and  a dimension for contacts and essentially a bridge table between them. 

---

Regards, Steve @dataonwheels http://www.dataonwheels.com

Answer 4

I wouls suggest a single dimension if the Column set for both the sources is one and the same. We can have the datasource Name to identify the source of the contacts.

If the column set of both the sources is different its advisable to go for two dimensions instead of one.

Thanks,

Sowjanya