SQL Things

I am going to do a quick dimensional modeling primer before using the conceptual model to create a logical model.  Dimensional modeling creates a star schema comprised of two basic types of tables: Fact Tables - these tables contain measures and keys.  The measures are the facts.  If I am counting students then the fact may be as simple as using the value 1 to indicate the existence of registration for a specific term.  The measures can be additive, semi-additive, or non-additive.  Additive measures can be easily summed.  Semi-Additive measures like account balances for example would mean nothing if summed.  Non-additive facts such as GPAs may need to be broken down into their parts (IE individual grades).  The fact table will be the center of the "star". Dimension Tables - these tables are filters or slicers you will use to aggregate or refine your data sets.  For example, there may be a student dimension table which includes informatio...

Conceptual data models are easy to skip.  In a simple business process conceptual models can appear over simplified.  In the case of student retention the business process is very simple on the surface, which cohort does the student belong in and did they come back year over year?  However, I am learning that the conceptual model is a great way to communicate with colleagues who do not understand database theory or technical jargon.  Also, as part of a more complex ecosystem the conceptual model can more easily answer the question "What do we have in our Warehouse?" I put the following conceptual model together using One Note.  I am always tempted to look for tools that make things easier and more repeatable, but in this case just having some simple shapes stops the desire to over complicate the model and get into the implementation details too soon. An easy way to communicate with non-technical stakeholders. Next, if I start to add other subject area...