MultidimensionalDatabases and DataMarts, Part 1

Chapter 2 - Video # 7

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

Where, oh where…

• In our last video, we discussed the Multidimensional data warehouse – OLAP database not stored on an RDBMS but on a multidimensional database management server – Uses cubes, measures, and dimensions • Discussed in the next video…

3 p. 3

Multidimensional Databases

• First we need to have an understanding of what multidimensional databases are – Abbreviated MDBs or MDDBs – Stores pre-calculated aggregates – Physical structure is just files in a file system like any other database

4 p. 4

Multidimensional Databases

• MDBs differ from relational DBs – First let’s consider how to think of relational data:

5 p. 5

Multidimensional Databases

• Relational tables are familiar – Rows and columns are easy to understand – Primary keys guarantee uniqueness – Indexes are used to allow multiple ways of sorting data for faster querying

6 p. 6

Multidimensional Databases

• You can consider a table to be a twodimension array – Each combination of column/row number refers to a unique position – Column[4], Row[6]

7 p. 7

Multidimensional Databases

• Result sets can often be multidimensional: – “Show me units sold by year and by product”

8 p. 8

Multidimensional Databases

• Result sets can often be multidimensional: – “Show me units sold by year, by product, and by country”

9 p. 9

Multidimensional Databases

• There is no functional limit to the number of dimensions requested – “Show me units sold by year, by quarter, by month, by day, by product category, by product subcategory, by product model, by product size, by product color, by product, by product vendor, by city, by postal code, by state/province, and by country. Please ”

10 p. 10

Multidimensional Databases

• This need/desire to expand the analytics leads to massive reports – Pages and pages of fun!

11 p. 11

Multidimensional Databases

• One technique to make multidimensional data usable is to use drilldown – More on drilldown and hierarchies later!

12 p. 12

Multidimensional Databases

• “Time out, Scott – I don’t understand where all of this is going. I thought we were going to talk about multidimensional databases?”

13 p. 13

Multidimensional Databases

• We are! But first we need to understand the limitations of relational databases – This is the “Why do I need a multidimensional database?” question!

14 p. 14

Next up

• But… we need to take a short break…

“I spend as much time finding the quotes for the videos as I do writing the material.”

Ttocs Mahgihw

15 p. 15