Difference Between OLTP and Inmon Data Warehouse

Some issues to bring up:

• For OLTP, inserts will be really slow in a table that wide

• You will be wasting a lot of space by repeating redundant information

• Columnstore is a non-modifiable index type so you can't use it in an OLTP environment

• You greatly complicate referential integrity controls this way. You can't just make foreign keys to make sure you are getting valid values for fields.

• Indexing will be a nightmare

The real issue here is the developers not understanding design.

Keeping the client data in it's native format is FINE. I do this kind of thing for a living, and I get tables with 500+ fields all the time. The way to handle it is to separate your RAW data from your BUILT data.

If the client gives you a massively wide table, you need to normalize it yourself to make a usable data set. There's nothing stopping you from creating a process that breaks out that data into appropriate tables.

Here are my answers:

1. Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less efficient or even using composite keys. It might be worth defining nonclustered indexes on your business keys for lookups during your ETL process, but queries against the data warehouse should utilize your surrogate keys.
2. Here's how I would approach your fact tables. If the different grades are the same granularity and have most if not all of the same dimensions in common, I would put them in the same fact table. Otherwise, I would put them in separate fact tables. You can always define cubes with multiple measure groups or put a view in place that combines the different fact tables into one that makes sense down the road.
3. You definitely can link one dimension to multiple fact tables. This is referred to as a conformed dimension and is usually more common that linking a dimension to a single fact table.
4. Typically, I create a date dimension that is at a single day granularity and link it to any fact table that has a date field in it. However, in an education context, you might have the need for a different granularity such as just assigning a fact to a quarter dimension instead. If that's the case, you could build out a separate dimension table at the desired granularity. However, for your example of an exam, you would just link that exam record to the date dimension based on that specific day. Furthermore, you should build out your date dimension to have fields such as quarter, school year, etc. One other thing to consider in your example is that it might be useful to calculate the duration of the exam based on the start and end dates if they are in a date time format and you want to know how long it took student to complete a specific exam and to see if duration correlates with results in some way.

Let me know if you have any follow up questions!