I have two StudentDetails dimension tables in my data warehouse and I want to know whether I should converge them, considering that they have limited attributes in common (keeping in mind Kimball DW design principles).

Table 1 is called DimStudentDetails and virtually all of its data comes from an ETL from another small departmental database. Table 2 is called DimUniStudentDetails and all of its data comes from an ETL from a large enterprise wide system (I work in a large university).

There are a very large number of students in Table 2 that don't exist in Table 1. There are a small number of students in Table 1 that don't exist in Table 2.

There is no current attribute cross-over between the tables, except for a common identifier for many students in Table 1 that also exists in Table 2. If I converge them as they stand presently, there would be a large number of Unknown or NA text values in the table.

Most of my fact tables do not require access to the two different data sets. One of my fact tables does. Presently, I would have to have two different StudentDetails FK relationships for this fact table - this feels wrong.

Equally, it feels wrong to have a dimension table where most of the attributes have Unknown or NA values.

It would be much appreciated if someone could advise me based on their experience of DW projects whether converging fairly disparate dimensions (in terms of attributes), albeit conceptually similar ones (things about students), makes sense. Should conceptual purity be promoted in spite of a somewhat unintuitive result?

  • 1
    IMO it makes sense to converge. A dimension should encompass all possible values, so if its about 'students' and there is a reliable unique key then id consolidate. There is nothing wrong with having different types of students where data may be optional provided it meets your needs. Mar 28 '17 at 7:40
  • Having a single dimension should simplify matters elewhere and help to provide more complete and consistent results. Mar 28 '17 at 7:41
  • If I'm following, DinUniStudentDetails applies to all students, and DimStudentDetails applies to students in a particular department (or possibly who have taken classes from that department). What is the likelihood of other departments having their own details to add? And, if they do, what is the likelihood that those would match the ones for the one department with its own details now?
    – RDFozz
    Mar 28 '17 at 15:01
  • Sort of. DimUniStudentDetails applies to say, 98% of students in the database. DimStudentDetails is predominately students who have taken classes with our department but who are also enrolled in other parts of the institution. There are a small number of 'external' students, however, who exist in DimStudentDetails and not in DimUniStudentDetails. There is no chance of other departments having details to add.
    – Ben
    Mar 29 '17 at 0:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.