OLTP

Chapter 2 - Video # 5

OLTP vs. OLAP

Chapter 2: Business Intelligence & Data Warehousing with SSAS Course: SQL Server 2008/R2 Analysis Services Course Id: 165 Presented by Scott Whigham 1 1

p. 1

Business Intelligence

• Overview of Chapter • Defining Business Intelligence • BI and SQL Server

• OLTP vs. OLAP • “Where should I put my data warehouse?”

• Multidimensional databases • Data Mining • What is Analysis Services?

• New Features in SSAS 2008

2 p. 2

OLTP vs. OLAP

• File this video under “Things all people who work with databases should know” – That would be you

3 p. 3

OLTP vs. OLAP

• Modern databases fall into one of two categories: – Online Transaction Processing (“OLTP”) – Online Analytical Processing (“OLAP”)

• Let’s look at each!

4 p. 4

OLTP vs. OLAP

• OLTP databases: – It’s in the name: they are designed to handle transaction processing • Great for data entry applications such as ticketing systems, shopping carts, et al

– Optimized design for write activity • Little to no duplication of data speeds up inserts/updates

5 p. 5

OLTP vs. OLAP

• OLTP databases (cont.): – Let’s say we work for AdventureWorks Cycling Company and we just signed a new distribution deal with ACME Widgets, Ltd. We need to now add 50,000 products to our database • All products have same seller, same contact information

6 p. 6

OLTP vs. OLAP

• OLTP databases (cont.): – Sample data as represented in a spreadsheet:

7 p. 7

OLTP vs. OLAP

OLTP databases (cont.): • Sample data in a “normalized” OLTP database:

8 p. 8

OLTP vs. OLAP

• OLTP databases (cont.): – While we have optimized for insert/update, our reads (i.e. “queries”) have become very complex • To retrieve the same information in the spreadsheet now requires an eight-table JOIN • This was a simple spreadsheet; a more complex spreadsheet could easily require 30+ tables to be JOINed

9 p. 9

Data Warehouse

Data entry

Data warehouse

Reporting

Training

10 p. 10

OLTP vs. OLAP

• OLAP databases: – Are designed to handle analytics • Great for data warehousing needs such as reporting

– Optimized design for read activity • Lots of duplication of data speeds up reads

11 p. 11

OLTP vs. OLAP

• OLAP databases (cont.): – Sample set of table-based representation of data

12 p. 12

OLTP vs. OLAP

• OLAP databases (cont.): – Now that we have optimized for reads/queries, our query is a single table • No more JOINs? • Are there any downsides/considerations for this approach?

13 p. 13

Where, oh where‌

Data entry

Data warehouse

Reporting

Training

14 p. 14

Next up

• Where Should I Put My Data Warehouse?

“Dreaming about being an actress, is more exciting then being one.”

Marilyn Monroe

15 p. 15