MultidimensionalDatabases and DataMarts, Part 2

Chapter 2 - Video # 8

Multidimensional Databases and Data Marts, Part 2 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

Multidimensional Databases

• Generating multidimensional reports from a relational database is expensive – Remember: relational databases are optimized for insert/update operations, not for “lightning fast analytics”

2 p. 2

OLTP vs. OLAP

• Remember this?

3 p. 3

Multidimensional Databases

• Multidimensional reports from relational databases: – Generally involves many, many tables – Requires use of aggregates (SUM, AVG, MAX, etc) – Requires use of GROUP BY for each dimension

4 p. 4

Multidimensional Databases

• In Course 160 we talk about how each expression (i.e. dimension) in the SQL GROUP BY makes the query more expensive – Course 160 is “Writing Queries for SQL Server” GROUP BY [Year], [Quarter], [Month], [Day], ProductCategory, ProductSubCategory, ProductModel, Color, Size, ProductName, ProductVendor, …

5 p. 5

Multidimensional Databases

• Example: – Single table – 1,000,000 rows – User is requesting SUM(UnitsSold) as agg across one dimension • A single dimension because there is no GROUP BY SELECT SUM(UnitsSold) AS Units FROM <table>

6 p. 6

Multidimensional Databases

• Another example: – User is requesting SUM(UnitsSold) as agg across six dimensions • Six dimensions because there are six expressions/columns in the GROUP BY …

GROUP BY [Year], [Quarter], ProductCategory, ProductSubCategory, Product, ProductVendor

7 p. 7

Multidimensional Databases

• But!!!!

– Subtotals are not calculated by default – Grand totals are also not calculated – This means SUM(UnitsSold) is only calculated at the exact level specified in the GROUP BY …

GROUP BY [Year], [Quarter], ProductCategory, ProductSubCategory, Product, ProductVendor

8 p. 8

Multidimensional Databases

• We can request that the GROUP BY include subtotals and grand totals – SQL will now compute aggregates at multiple levels – Over large sets this is not advisable except during off-house – The “how to do this in SQL” is covered in Course 160

9 p. 9

Multidimensional Databases

• Expand our example further: – User is requesting SUM(UnitsSold) as agg across six dimensions – 24 tables are required to get to the six dimensions – Over 30,000,000 rows are now involved …

GROUP BY [Year], [Quarter], ProductCategory, ProductSubCategory, Product, ProductVendor

10 p. 10

Multidimensional Databases

â&#x20AC;˘ Multidimensional data is visualized in a cube

11 p. 11

Multidimensional Databases

â&#x20AC;˘ The key, key, k-e-y point to know about multidimensional databases is that they store pre-calculated aggregates â&#x20AC;&#x201C; They do not store the detail records

12 p. 12

Multidimensional Databases

â&#x20AC;˘ When a user asks for the number of units sold, the MDB does not have to perform an aggregation â&#x20AC;&#x201C; The data is already aggregated!

13 p. 13

Multidimensional Databases

â&#x20AC;˘ When a user asks for the number of units sold by year, the MDB does not have to perform an aggregation â&#x20AC;&#x201C; The data is already aggregated, both overall and by year

14 p. 14

Multidimensional Databases

â&#x20AC;˘ When a user asks for the number of units sold by year and by quarter and by month, the MDB does not have to perform an aggregation â&#x20AC;&#x201C; The data is already aggregated, both overall and by year, by year/quarter, by quarter, by year/month, and by month

15 p. 15

Multidimensional Databases

• We can think of MDBs like a cube – More on what a “cube” is in the next video

16 p. 16

Multidimensional Databases

• The next video will delve more into more terms that will finish the puzzle: – Cubes – Facts – Measures – Dimensions – Hierarchies

17 p. 17

Multidimensional Databases

• Until then, the key points about MDBs that I want you to remember are: – They only contain pre-calculated aggregates – They are for OLAP, not OLTP – The term “multidimensional” refers to how we view the data, not because your records are stored in 3-D format

18 p. 18

Next up

â&#x20AC;˘ Measures, Dimensions, and Cubes

19 p. 19