Dimensions – COMBINATION or SEPERATION
I recognize 3 areas of interest to my customer:
* Dealership Customers (aka Clients). People who bring their cars to dealerships.
* Dealership Employees. Service Advisors who make service recommendations.
* CompanyX Employees. Account Mgrs who sell the Product to the Dealerships.
1 approach is to have a SINGLE dim called PERSON given all 3 potential dim share common attributes such as Name, Addr, Phone, Email, etc. I have appx 300 CompanyX emp; 50,000 Dlrship emp; & 25,000,000 Clients.
To distinguish each from the other, I could deploy a field called CATEGORY ( C(lient), D(ealership), & X(CompanyX) ) within the Dim. Or I could distinguish each by its Surrogate Key where the Key would have a prefix of C, D, or X at the time it's created. Example, C12345 for Client, D45788 for a Dealer Emp, & X86443 for a CompanyX Emp.
2nd approach: Create separate Dims for each category.
I have service order measures which measure how many Repair OrdersService Orders were sold by a Dealership Employee. I don't envision a case where the measure would use multiple Dims (CLIENT, DEALERSHIP EMP, & Company X EMP). The measures would be separated out, meaning I would have a Client Fact Tbl, a CompanyX Fact Tbl, & a Dealership Fact Tbl.
Comments?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 26, 2006 10:45 AM
UPDATED:
July 27, 2006 11:20 PM
Answer Wiki:
(1) Never place two pieces of data in one field. So, the type should be a separate field.
(2) You can write the least code, and have the least initial and maintenance cost, by NOT duplicating things. Thus, the single "Dim", or record type, struct, or class with all of the fields will do. Extra fields can be in derivative items. Here are three C examples of how to do that. Note that Method 3 results in the least lines of executable code (but all methods generate exactly the same machine code).
Method 1: Overlay storage
struct myRecord {
char name[80];
char address[80];
union {
char dealerOnlyStuff[80];
char customerOnlyStuff[80];
} variablePart;
};
Method 2: Sub-structures
struct myMainStruct {
char name[80];
char address[80];
};
struct customerRecord {
struct myMainStruct mainData;
char customerOnlyStuff[80];
};
struct dealerRecord {
struct myMainStruct mainData;
char dealerOnlyStuff[80];
};
Method 3: Derivatives
class MainData {
char name[80];
char address[80];
};
class CustomerData: MainData {
char customerOnlyData[80];
};
class DealerData: MainData {
char dealerOnlyData[80];
};
---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
To see all answers submitted to the Answer Wiki: View Answer History.
Further suggestions: first, do careful analysis of the data over time. For example, a “customer” person could become a “dealer” or “employee”, etc. second, avoid trying to create “intelligent” keys (e.g., c1111 is a “customer”), because that can create user training issues – remembering all the data coding tricks, doing queries on substrings – and as well as complicating future software maintence issues (e.g., if the customer leases, is it the leasing company or the human customer that is the “customer?”, third, do some further reading on both object-oriented system design and especially use of XML.
Firstly, is this for an operational system or for a data warehouse?
They both have quite different needs and so the data modelling concepts are different.
Based upon what you?ve already said, I?m assuming that it?s a data warehouse.
The best reference you could read on this would probably be:
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, Second Edition by Ralph Kimball and Margy Ross John Wiley & Sons ? 2002
If you are building a data warehouse, then I?d expect you are doing it for a substantial enterprise. This would indicate that they should have data architects that would either have experience in dimensional modelling or they would have access to experts in that field. Speak to these people!
To answer your question:
I would not want employees, suppliers and customers mixed up in the one dimension (but you might). The reason is that each of these different types of Party (this is the standard generic term these days – they may be Companies rather than `Persons?) may interact with each other and if they?re all in the one dimension, you cannot necessarily capture those interactions without some fancy footwork in the ETL layer or even worse, in the reporting or data mining layer.
A data warehouse should include all the data and should not anticipate a certain type of query NOT being done. You cannot always anticipate what the users will want so you try to provide as much as possible with as few constraints as possible. That gives you a much quicker `time to market? capability.
I also agree with one of your other respondents ? don?t mix `types? into surrogate keys or natural keys. I?ve used such systems before & it is a nightmare!
Now, if it is for an operational system, mixing all these different Party Types isn?t just OK, it?s probably the best thing to do. That?s all I?ll say on that though.