Dimensions – COMBINATION or SEPERATION

0 pts.
Tags:
Data warehousing applications
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?
ASKED: July 26, 2006  10:45 AM
UPDATED: July 27, 2006  11:20 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

(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)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Solutions1
    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.
    0 pointsBadges:
    report
  • Szppt8
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following