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
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
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?