I’m building a DW/DM for an educational institution which has many campuses. I’m tasked with building a DM template that would use the same schema and the same standard reports for each campus and I’m tasked with building one DW for the corp. Can I use a star schema design for DW and DM or what guidelines would you suggest for this case? Currently, I'm thinking of using the star join for the DW because it contains data across campuses and using a snowflake for each DM.


Scott Howard’s Answer: I think that you are concerned with the standardization that needs to be accomplished at the consolidated and reconciled central DW and the specialization that occurs at the departmental data mart level. Don't be concerned with star schemas at the DW level unless the DW is frequently accessed by result-seeking end-user queries (which I strongly recommend against). The DW should be the consolidation and reconciliation point which, in turn, feeds all of your data marts. The DW model needs to be temporally normalized or efficiently constructed with respect to the time dimension. This often leads to a fairly normalized structure much like your operational OLTP system. Only when you construct your data marts, that is data marts used for OLAP purposes, should you ponder star vs. snowflake schemas. This decision is often driven by physical performance reasons and by need to keep the end-user externalized model as simple and digestible as possible.

Joe Oates’ Answer: My advice is to use a multidimensional (star schema) approach to produce a single data warehouse design. I say this because you said that there would be " the same standard reports for each campus." Based on the volume of that I have seen at several universities, it should not be necessary to develop separate data marts. To be able to report by campus, by college, by department, by dean, by city, etc., you will need a reporting hierarchy table. This would enable you to provide the capability for individualized reporting as well as aggregate and drill-down capabilities for top management. Be sure to use conformed dimensions.

Clay Rehm’s Answer: It sounds like you are worried about the physical implementation before you even have the business requirements! Once conceptual and logical data modeling are done, then you will be prepared to determine if a normalized model, star, snowflake, hybrid, etc is appropriate. There is no one standard or guideline – the design supports the ever-changing business requirements.

A good database design will be able to accommodate business requirements that change over time. Each design option has its pros and cons. Each design option must be able to support specific and ever changing requirements. For example, will the data be viewed by non-technical users? Will it need to store active transactions? Questions need to be documented on how the data will be collected, stored, accessed and used.

The first step is to come up with a conceptual database design. This is the process of constructing a model of information used in an enterprise, independent of all physical considerations. This is a view of the enterprise, not your specific business area.

Once that is accomplished, create the logical design, which is the process of constructing a model of the information used in an enterprise based on a specific data model, but still independent of a specific DBMS and other physical considerations. This means your data model will support your business area but will also have an enterprise look and feel to it since you accomplished the conceptual modeling. Keep in mind we have not talked about anything physical such as a star schema.

The physical design is the process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations and indexes used to achieve efficient access to the data; and any associated integrity constraints and security measures.

Once that is documented, retain an experienced DW consultant to review your findings and recommend a physical design.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access