SQL Things

I recently had a case where I wanted to get a regular academic term n terms from a given date.  For example if I was in the Fall Term of 2018, I wanted to be able to get the term 2 terms before which would be Spring 2018. The problem is we use term codes, which have changed formatting over the years, and we also have partial terms which I didn't want to consider.  The data ends up looking something like this: TermSK TermCode 1 201780 6 201810 17 201850     SELECT ROW_NUMBER() over(order by term_code desc) Row_Num, Term_Code     FROM dimTerm     WHERE Term_Type =  'Regular' Row_Num TermCode 3 201780 2 201810 1 201850 The results made it really easy to grab any term preceding by simply subtracting the desired previous terms from the generated Row_Num.

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

I am going to be doing a series of blog posts on data modeling student retention in higher education.  I recently saw a presentation which made me think a lot about data modeling in general and more specifically about student retention.  This series of blog posts will document the process my team is using to determine if the data model I observed is appropriate for our institution. The first step in creating a data model is understanding the business process.  I will start by laying out the rules and vocabulary when it comes to student retention. Student retention in higher education is based on an academic year.  New students starting in Fall are "retained" if they attend school the following Fall.  If a first time in college (FTIC) student attended school for the first time in Fall of 2010 and also attended in the Fall 2011, they were retained. The next important business concept and term is cohort .  For the purpose of student retention a cohort i...

The first thing I will do is create a System-Versioned  table in SQL Server 2016.  Before creating the table, I will determine if the object already exists and if true I will drop the table. If you have an interest in system-versioned (temporal tables) note the alter table statement before I drop the object. IF OBJECT_ID('WHILELOOPTEST') is not null BEGIN ALTER TABLE  [dbo].[WHILELOOPTEST] SET ( SYSTEM_VERSIONING = OFF  ) DROP TABLE   [dbo].WHILELOOPTEST END Next, I create the system versioned table so we can see the number changes caused by the following WHILE loop. Create TABLE WHILELOOPTEST ( ProductPK int identity(1,1) primary key, Product_Cost money, Start_Date datetime2 generated always as row start hidden NOT NULL , End_Date   datetime2 generated always as row end hidden NOT NULL, PERIOD FOR SYSTEM_TIME(Start_Date, End_Date) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WHILELOOPTESTHISTORY)); /** Insert Test Values ...

I have been asked many times the difference between a data mart and a data warehouse. I have often heard the terms used interchangeably. But let's face it, we can't all be right all the time. So, below is my break down of data warehouses and data marts. Data Warehouse A data warehouse is simply a database.  The exact kind of database technology you use for business applications can be used for a data warehouse; you can use Oracle or SQL Server.  But stop before going to tell your boss all of your databases are data warehouses.  The difference between your application database and your data warehouse is how the schema is designed and the source of the data.  A data warehouse is specifically designed to make reporting easier and to ensure reporting does not place an undue burden on the transactional source system. Data warehouses can include data from many sources all brought together for convenience.  Note: A data warehouse is not a data graveyard for old d...

This post is a very simple, possibly over simplified, example of conceptual, logical, and physical data models.  The conceptual model includes business objects and the cardinality between the objects.  The logical model includes the cardinality, attributes, and addresses potential primary and foreign keys.  The physical model includes table names, column names, and data types.  The problem with hiding the potential complexity of this process is giving the impression that data  modeling (aka modeling business processes) is easy and fast and therefore trivial.  The modeling process is not trivial.  Devoting adequate time to designing, implementing, testing, and redesigning the data models in a project can be the difference between a impactful project  and having to start over after the business objectives are not met. Conceptual Logical  Physical