How to model a generic user

View previous topic View next topic Go down

How to model a generic user

Post  tdeffler on Wed Feb 24, 2016 9:10 am

I'm fairly new to dimensional modeling. I have an existing model that already has an employee dimension for people who work at the company and a contact dimension for people who do not. I now need to build a fact table, one of whose dimensions is around users of a system and those users may be either employees or contacts. What is the best approach?

1. Create an entirely new user dimension that replicates the attributes from employee and contact I care about. These attributes would be a partially, but not completely, overlapping set.

2. Create a new user dimension for just the information not already in either employee and contact and put its key, plus keys for the employee and contact dimensions in the fact table.

3. Other

tdeffler

Posts : 3
Join date : 2016-02-24

View user profile

Back to top Go down

Re: How to model a generic user

Post  ngalemmo on Wed Feb 24, 2016 10:13 am

Have a separate User dimension. A user is a different concept than an employee.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to model a generic user

Post  zoom on Wed Feb 24, 2016 12:59 pm

Existence of an ID ( either employee or contact) in the Fact table means they are the user of the system. Another option you have is to have employee and contact dim IDs into that Fact table. When loading employee's data in the  Fact table then contact id is null and when loading contact data then employee ID is null. If you do not like having null Dim ID values in the fact table, then you can create a "not applicable" or "dummy" row in each Dim and use that Dummy id instead a null dim id.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: How to model a generic user

Post  tdeffler on Wed Feb 24, 2016 1:51 pm

@zoom,

Having both employee and contact have IDs in the fact table with one always null is essentially what I was thinking about with regard to option #2 in my list.  Then I would put information that is specific to their role as a user (e.g., userid) into a third dimension that would also contribute a dimension key.

Thanks.

@ngalemmo,

That doesn't seem to address my question.  I understand they are different concepts.  However, they are related in terms of information people writing queries will want (name, address, manager, et al.) and the question is whether to duplicate all the data between the dimensions or to associate them via the fact table.

Edit
An example might be useful. Suppose you have:

1) An existing employee dimension in your database that has: key, name, manager, manager's manager, etc.
2) An existing contact dimension in your database that has: key, name, company, etc.

Now you want to look at posts in an online forum. Each post generates a fact about time, which user made the post, which user created the original post in a thread. You want to answer questions like:

"How many questions do external users (non-employees) make broken down by company and time."
"How many responses do internal users (employees) make broken down by their management chain."

That leads me to my question of whether you replicate all that information from EmployeeDim and ContactDim to a new UserDim whose key is in the fact table...or whether you have the fact table contain the keys from all three dimensions, with either EmployeeDimKey or ContactDimKey pointing to a dummy row.

tdeffler

Posts : 3
Join date : 2016-02-24

View user profile

Back to top Go down

Re: How to model a generic user

Post  ngalemmo on Wed Feb 24, 2016 3:00 pm

An employee is someone who works for the company. As an entity it has different attributes and is used in different ways. A user is someone who uses the system who may also be an employee. As a dimension is has a different population and used in completely different contexts. You can certainly carry attributes in the user dimension that identify the user as an employee, even go as far as having a FK to the employee dimension, but it is a bad idea to try to use a single dimension for both.

For one, there are confidentiality issues. As an employee, there is a lot of information that should not be shared. While HR can use it, no one else should. A user dimension would have non-confidential data and various attributes that are not applicable to an employee.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to model a generic user

Post  ngalemmo on Wed Feb 24, 2016 4:48 pm

Sorry I misread. The dimensions are contacts and employees.

If your surrogate keys are mutually exclusive (i.e. the surrogate keys for contact would never match the surrogate keys for employee), you can union the two to create a user dimension. It may be a materialized table or a view. If you are not maintaining mutually exclusive surrogate keys, you will need to build and maintain a user dimension that incorporates data common to employee and contact.

I've always try to build dimensions with mutually exclusive surrogate keys as it provides the flexibility of unioning existing dimensions for such situations.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to model a generic user

Post  tdeffler on Thu Feb 25, 2016 1:30 pm

That last is an interesting idea. While renumbering a dimension would be that hard at this point, I have to think about the implications for maintenance a bit.

Thanks.

tdeffler

Posts : 3
Join date : 2016-02-24

View user profile

Back to top Go down

Re: How to model a generic user

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum