DimEntity, containing persons and companies ...

View previous topic View next topic Go down

DimEntity, containing persons and companies ...

Post  ian.coetzer on Mon Oct 18, 2010 10:16 am

Hi,

After designing the first set of dimensional models for a subject matter area I was asked:
Ian, why do you have a dimension called DimEntity, why not split it into DimCompany and DimPerson?

I was not sure how to answer except for:

Well, the business currently has a table in which ALL entities are stored.
Each entity can belong to one or more groups of which we currently have 48 groups.

Examples of an entity 'group' is:
Customer
Bank
Supplier
Consultant
Property Owner
3rd Party
Branch
Court
Merchant
etc.
etc.
etc.

So my question:
If I split my proposed DimEntity into DimPerson and DimCompany,
which of these 'groups' are people and which are companies, and should I then hardcode this distinction in the ETL process?

Well this is where i'm at, any ideas?

Thanks,
Ian

avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ngalemmo on Mon Oct 18, 2010 12:37 pm

The question is, for the business, does it matter?

Do people and companies engage with the business in the same manner or are they independent and involved in mutually exclusive business processes?

If the former is the case, I have no problem with what you plan to do. It would help if there is a role attribute if you can identify who/what the entity is, and possibly add sub-type dimensions if there are significantly different additional attributes depending on role.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  BoxesAndLines on Mon Oct 18, 2010 1:20 pm

I would look to reporting to decide. What are the groupings for each of these roles? If there are hierarchies for some of the entities, break those out to make the model (and reporting) clearer.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ngalemmo on Mon Oct 18, 2010 1:38 pm

I tend not to worry too much about reporting, since you can always create views either in the database or the reporting meta-layer that breaks things out.

What is critical is if something like a 'property owner' can be a person or a corporation. One would think both are likely. If they are separate dimensions, a fact table would need multiple FKs depending on what type of entity owns the property. Such an arrangement is never good...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  Jeff Smith on Mon Oct 18, 2010 3:02 pm

I can't tell what the difference is between Company and Person. Are things done by a person who rolls up to a company or can things be done by either a person or company? You have a field called "customer" in the entity dimension. Can a company or individual be a customer? In any case, why wouldn't the dimension be called "Customer"? Entity is too general of a name for a dimension table unless the entity can be multiple things (Customer and Supplier).

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ian.coetzer on Mon Oct 18, 2010 3:20 pm

Hi,
Yes an entity is anyone that we do business with, suppliers , customers, banks, lawayer firms etc. they all share common attributes/fields some may just be Unknown for some type of entities.

another thing :
in our business an entity could be one of our branches, however there could be 5 other entities that are the actual agents / clerks who work n that branch ...

in other words everything is currently stored in this table and used for operational reporting with joins and filters galore!
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ian.coetzer on Fri Oct 22, 2010 3:46 am

Hi,

I would like some more feedback on this question and looking at one of the previous posts i want to discuss a bit more in depth:

"If they are separate dimensions, a fact table would need multiple FKs depending on what type of entity owns the property. Such an arrangement is never good..."

IF we end up with one dimension, let's call it DimEntity which contains all the entities on our systems.
(These entities can belong to one or more group, like Customer, Bank, Employee, Agent, Tracer etc.)

I have for example a fact table called FactAffordability, that will store the affordability details of our customers, like monthly income available, deductions etc. etc.
This FactAffordability table will link to DimEntity three times (thus 3 foreign keys)
* CustomerEntityKey
* AgentEntityKey
* BranchEntityKey

because the entity dimension DimEntity will contain all these types of entities.

In this case it does not matter if one entity is a Customer and a Bank - I just link directly to the DimEntity.

In OTHER analysis scenarios there may be the need to differentiation between Customers, Banks, Employees, etc. etc. and we may need to know that an entity that belongs to the group Customer also belongs to the group Bank.

Therefore I'm proposing another factless fact table called something like FactEntityGroup which peforms the role of a linking table to DimGroup so that one Entity can link to more than one Group.

If I do not do it this way then I must have several fields in DimEntity (boolean fields like)
IsCustomer
IsBank
IsIndividual
IsEmployee
etc.

So that I can load customers that are banks and individuals into the same entity dimension.
Example:
DimEntity
ID IsCustomer IsBank IsIndividual IsEmployee
1 True True False False
1 True False True False
1 True False False True

Any further comment?

We cannot come to a decisions as to whether to hold all types of entities in one dimension,
or whether to split them into 6 to 8 dimensions of which we have already identified

DimCustomer
DimEmployer
DimEmployee
DimAgent
DimBranch
etc.
etc.
etc.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  hang on Fri Oct 22, 2010 7:21 am

I don't see any good point in mixing multiple entities in a single table. The concept was infamously coined as OTLT (One True Lookup Table) commonly adopted by database newbies to somehow emulate polymorphism in OO programming. However in data modeling, a table should never be treated like a class in OOP. If not for special modeling technique like junk dimension, a dimension table should contain the data belonging to the same entity, period.

So split the table into more meaningful dimensions. If your OLTP system has some kind of OTLT design, don't carry it into data warehouse. It's especially important for a BI system to have an absolutely clear dimensional model in place, while compromise on good modeling practice may defeat the purpose of data warehousing.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ian.coetzer on Fri Oct 22, 2010 11:40 am

Hi,

Thank you this is exactly the type of Pros that I'm looking for to justify 'splitting' our entities into multiple dimension tables.

This will also simplify the linking of entities to multiple groups via a factless fact table.

Thank you again.
Ian
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

Post  ian.coetzer on Wed Oct 27, 2010 4:56 pm

Hi,

I'm very fortunate to be attending Ralph Kimball's ETL Architecture Master Class in Johannesburg South Africa this week.
I asked him this very question (what this thread is about) and he also suggested that we keep our entities like Customer; Agent; Branch etc. in separate dimension tables. One should limit the use of bridging tables to other scenarios like having dynamic lists of properties about a product.

Reasons for keeping these entities in separate dimension tables:
1) They are SPECIFIC business entities used in business processes
2) To pull a list of only customers from a generic DimEntity table would require a join on three tables which is bad for overall BI solution performance
3) Even if our entities are +- 45 now and a couple are added over time it does not matter - that amount of dimensions are not going to cause capacity issues on our disks.

So, this post is closed, answered.
leaving me a happy BI Lead.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: DimEntity, containing persons and companies ...

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