Logical and Physical Modeling for Analytical Applications

A bad data model leads to an application that does not perform well. Therefore, when developing an application, you should create a good data model from the start. However, even the best logical model can’t help when the physical implementation is bad. It is also important to know how SQL Server stores and accesses data, and how to optimize the data access. Database optimization starts by splitting transactional and analytical applications. In this course, you learn how to support analytical applications with logical design, get understanding of the problems with data access for queries that deal with large amounts of data, and learn about SQL Server optimizations that help solving these problems.

 
Logical and Physical Modeling for Analytical ApplicationsStar and Snowflake Schemask00:20:19
Introduction02:43
Normalized Schema03:51
Star Schema02:29
Shared Dimensions00:36
Dimension Denormalization00:42
Snowflake Schema00:52
Hybrid Approach01:14
Granularity02:03
Auditing Lineage01:39
DWTerms02:59
Summary01:11
Logical and Physical Modeling for Analytical ApplicationsDesigning Dimensions and Fact Tables00:38:19
Introduction00:53
Dimension Columns02:27
Hierarchies02:14
SCD Classic03:15
Referencing Monster02:38
SCD Custom02:24
Demo Dimensions05:45
Fact Columns01:30
Measures Additivity02:51
Demo Fact02:53
MtoM03:21
Demo MtoM07:10
Summary00:58
Logical and Physical Modeling for Analytical ApplicationsData Warehousing Data Access Problems00:21:49
Introduction01:40
Algorithms Complexity01:51
SSAS Addressing02:34
Joins02:32
Linearize Joins01:38
Bitmap01:04
Bloom03:28
Trans-Relational01:30
Columnar Storage03:54
Summary01:38
Logical and Physical Modeling for Analytical ApplicationsSQL Server Data Warehousing Support before Version 201200:33:18
Introduction01:05
Partitioned Table03:10
Sliding Window04:26
Demo Partitioning05:45
Filtered Index01:39
Indexed View02:14
Demo Filtered Indexed06:34
Data Compression05:20
Demo Data Compression01:57
Summary01:08
Logical and Physical Modeling for Analytical ApplicationsSql Server Data Warehousing Optimizations in Version 201200:34:01
Introduction00:56
Window Functions02:48
Demo Window Fun04:45
Columnstore Indexes01:19
Columnstore Compression02:48
Reducing IO01:36
Reducing CPU Batch03:22
Columnstore Restrictions02:56
Data Loading Usage02:40
Demo Columnstore Batch07:26
Version 2014 Summary03:25