ASP 3.0 The Complete Reference

Chapter 16

Decision Support with ADO (Outline and Notes)

Creating a Decision Support System

OLAP

why a separate database

Decision support queries are usually very expensive

and can bog down transactional systems

Using Data Transformation services can build data sets that are optimized for exploring

Microsoft Data Warehousing

Business Trends -

Microsoft Business Intelligence Jumpstart Kit

Asking analytical questions

How did sales in this period compare to sales in same period last year?

since beginning of the year?

What is my growth per month?

Business Internet Analytics?

Business Internet Analytics (BIA) is an offering by Microsoft and our partners aimed at helping customers gain insight by analyzing customer Web click-stream data on large, multi-site/domain e-commerce sites.

BIA allows usersincluding marketing/business professionals and IT professionalsto analyze customer Web data and answer complex questions like:

Advertising - "Which referring sites are most effective in getting customers to my Web site?"

IT - "At which times do we get the most users?"

Marketing - "At what Web sites are people spending the most time?"

BIA is a set of tools, database schemas, and methodologies for rapid deployment of highly scalable data warehouses based on Web server log source data. BIA was developed by some of the leading data warehousing architects to be highly scalable and is built using Microsoft SQL Server 7.0 and Microsoft Office 2000 on the Microsoft Windows platform.

Overview of OLAP Data Structures

Star and Snowflake Schemas

Figure 1: Star Schemas

Figure 2: Snowflake Schemas

Database options

Beyond the scope of this book to discuss how OLAP databases are built

Cubes

Cubes are defined by dimensions and measures

Measures

Numerical values that are summarized for analysis

all contained in a collection called Measures

Typical measures:

Units Sold

Gross Sales

Dimensions

Descriptions by which the measures are separated or aggregated for analysis

Dimensions can have hierarchy such as:

Country, State, City,

Year, Quarter, Month, Week

private and shared dimensions

Calculated dimensions

Up to 64 dimensions in a cube including measures

almost always a time dimension

ADOMD

Multidimensional data sets for interfacing with OLAP databases.

Object Model

Figure 3: ADOMD Object Model

Type Libraries

The Example

Interactive Analytical Application

Figure 4: Screen Shot

Building Multidimensional Expressions (MDX)

MDX provides a way to read, build, and change multidimensional databases

Like SQL for relational databases

	SQL
1 2 3 4 5 6 7 8 9 10

Query Examples

Selecting Columns and Rows

Select statements set on Axes

on Rows, on Columns, on Pages

From (source cube)

	SQL
1 2 3 4 5	Select Time.year.members on Rows Measures.members on Columns Product.[Product Department].members on Pages From Sales

result set will be a multi dimensional matrix

a Cellset

show diagram of cube

execution only sovles the dimensionality cell calculations are only done when requested

Getting Data

specifying coordinates for data

cell names based on the axes collections

(Products.Clothing, Measures.Units, Time.Year.97)

	SQL
1 2 3 4 5 6 7 8 9 10

iteration techniques

prevMember nextMemeber on an Axis

	SQL
1 2 3 4 5 6 7 8 9 10

Lead and Lag

to jump - symmetrical commands use negative to jump in the other direction

	SQL
1 2 3 4 5 6 7 8 9 10

(Products.Clothing, Measures.Units.prevMember, Time.Year.Lag(3) )

Digging In

finding children and other descendants

Children

	SQL
1 2 3 4 5 6 7 8 9 10

Descendants

	SQL
1 2 3 4 5 6 7 8 9 10

Slicing Data with a Where clause

	SQL
1 2 3 4 5 6 7 8 9 10

CrossJoin function

	SQL
1 2 3 4 5 6 7 8 9 10

Supporting Calculated Members

This is swhere the complex business logic will take place

for example calculate the growth between two time periods

using the WITH statement

	SQL
1 2 3	WITH member Measures.Growth as '(Time.currentmember, Measure.Sales)-(Time.currentmember.prevmember, Measures.Sales)'

you can point to any point in the data sets when building a cellset

All the same calculation support of VBA Math and Format functions

String formatting

examples of functions

non empty

generator

cross join

TopCount, TopPercent, TopSum

Bottom

YTD()

Average over Time

	SQL
1 2 3 4 5 6 7 8 9 10

Filtering Data

	SQL
1 2 3 4 5 6 7 8 9 10

Defining Descendants

using the Descendants method

	SQL
1 2 3 4 5 6 7 8 9 10

Pivot Table Component

create from cellsets

must have direct access to data source

Figure 5:Pivot Table Screen Shot

	VBScript
1 2 3 4 5 6 7 8 9 10

Alternate Use

Figure 6: Screen Shot

	VBScript
1 2 3 4 5 6 7 8 9 10