SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combining businesses with people in one dimension


Combining businesses with people in one dimension

Author
Message
geoff5
geoff5
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 543
In the application data that I wish to transform into a data warehouse, people and businesses are basically treated in identical ways. In the OLTP database the core data about both types of entities are stored in the same table, with the "LastName" column doing double duty as the "BusinessName" column as well. There is an indicator column to distinguish individuals from business, but there really is very little else to distinguish them.

In the data warehouse, should businesses and people be commingled in the same dimension? If so, should the "LastName" column still do double duty, or should there also be a separate "BusinessName" column? Filters on the entities' names would be complicated by such separation, which is one reason that a single column is being used for both types of names in the OLTP database.

If the business data and people data are in separate dimensions, wouldn't that complicate the fact table structure? In such a case there would be two separate dimensions instead of one to reference for the "who did it" data for transactions.

Any help or insight is welcome.

Thanks in advance,
Geoff
Gregory Spear
Gregory Spear
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 92
Hello Geoff

If you are confident that true distinction between people and businesses won't occur then keeping them together presents no problem.

However, you still may want your fact table to represent both people and business so you would still require two foreign key values in your fact table to the base dimension so create two views based off this dimension.

By using views, you could then incorporate Business Name verses Last Name as well. Your end users will thank you for it. It's one less thing for them to deal with.


Greg
geoff5
geoff5
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 543
Thanks for the reply, Greg. Based on the use cases for the facts in the database I'm dealing with, I don't think any fact tables will reference more than one entity of the dimNames table at one time. So I think I will retain an "IsBusiness" flag column but I will leave the business name as a LastName value, just as it is in the OLTP. If I build a view exclusively for businesses, I will just put an alias of "BusinessName" onto the LastName column.

Speaking of the "IsBusiness" column, would you recommend that this column be a bit data type or should it be a varchar for literal "Yes" and "No" text? I ask because I know a data warehouse is supposed to be about making the data accessible to non-techies and to that end it should avoid "codes" for values. But I also know that a bit data type is much more efficient for storage and for querying than is a varchar data type.
Gregory Spear
Gregory Spear
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 92
Hi Geoff,
I would use a literal "Yes" or "No". (Or possibly a "Y" vs. "N" )

Greg
Bruce W Cassidy
Bruce W Cassidy
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3107 Visits: 1033
As a general rule, I go with "don't store different things in the same container". Your case is a little confusing, as I don't know how the data is reported on. Are they reporting on entities (which may well be either people or businesses) or are they reporting on people and businesses?

How you model the data should reflect how the DW users intend to report on the data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search