Logical and Physical Modeling for Analytical Applications

A good application starts with a good database design. Support analytical applications with a good 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.
Course info
Rating
(207)
Level
Intermediate
Updated
Oct 31, 2013
Duration
2h 27m
Table of contents
Description
Course info
Rating
(207)
Level
Intermediate
Updated
Oct 31, 2013
Duration
2h 27m
Description

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.

About the author
About the author

Dejan Sarka, MCT and SQL Server MVP, is an independent consultant, trainer, and developer focusing on database & business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality.

More from the author
Data Mining Algorithms in SSAS, Excel, and R
Intermediate
2h 59m
Jul 24, 2015
Working With Temporal Data in SQL Server
Intermediate
3h 6m
May 28, 2014
Section Introduction Transcripts
Section Introduction Transcripts

Designing Dimensions and Fact Tables
Hello, I'm Dejan Sarka, and this is the second module of the Logical and Physical Modeling for Analytical Applications course. In this module, I'm going to talk a little bit more in depth about designing dimensions and fact tables. So I will start with designing dimensions, and I will mention most of the common problems with dimensions in a data warehouse and also introduce standard and custom solutions for those problems. And then, I'm going to go a little bit more in-depth about designing fact tables, and problems and solutions with columns in fact tables. I will also show a lot of demos in this model.

Data Warehousing Data Access Problems
Hello. I'm Dajan Sarka, and this is the third module of the Logical and Physical Modeling for Analytical Applications course. In this model, I'm going to talk about physical problems with data access in data warehouses and multidimensional all up systems. I will start by introducing, or even better, reminding you on algorithms complexity. What does it mean in the terms of time and other resources consumption? Then I'm going to describe the data access problems in multidimensional systems like sequel server and analysis services. Then we'll talk about relational database management systems, specifically about joins in these systems. Then, we'll point out star join which is kind of common pattern in a star schema. I will also introduce the so called bloom filter, a filter that's sequel server uses to enhance star joins. And finally I will describe how people tried to resolve these problems in the past in the late 20th century the trans-relational model was introduced with the idea to remove all of the problems with relational database management system joins.

SQL Server Data Warehousing Support before Version 2012
Hello, I'm Dejan Sarka and this is the fourth module of the Logical and Physical Modeling for Analytical Applications course. In this and in the next modules, the last two modules of this course. I'm going to talk about SQL Server Data Warehousing Support about specific features intended to support Data Warehousing scenarios in SQL Server. In this model, I'm going to talk about support before version 2012 to show what was available in previous versions as well. I'm going to start with table partitioning which fits extremely well in data warehousing sound audios, and then I'm going to describe for you the filtered indexes, and the indexed views. Finally, I will mention data compression. I will support the slides with a lot of themes and, of course, you are welcome to download the code.

Sql Server Data Warehousing Optimizations in Version 2012
Hello. I'm Dejan Sarka and this is the fifth or the last module of the Logical and Physical Modeling for Analytical Applications course. In this module, I will talk about a specific new data warehousing optimizations in SQL server version 2012. I will start by describing extended support for Window functions, define Window functions and explain what is new in 2012. Then I'm going to introduce the columnstore indexes and finally I'm going to talk about batch processing. Of course, I'm going to support all of the slides with demos, and as usual, you are welcome to download the code as well.