jump start to sql server BI.pdf

IT Pro SERIES

Jump Start

SQL Server BI

Don Awalt

Larry Barnes

Alexei Bocharov

Herts Chen

Rick Dobson

Rob Ericsson

Kirk Haselden

Brian Lawton

Jesper Lind

Tim Ramey

Paul Sanders

Mark D. Scott

David Walls

Russ Whitney

Books

Contents

Section I: Essential BI Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Chapter 1: Data Warehousing: Back to Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

By Don Awalt, Brian Lawton

Common Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Establishing a Vision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Defining Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

The Essence of Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

The Rest Is Up to You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Chapter 2: 7 Steps to Data Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

by Mark D. Scott, David Walls

Step 1: Determine Business Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Step 2: Collect and Analyze Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Step 3: Identify Core Business Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Step 4: Construct a Conceptual Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Step 5: Locate Data Sources and Plan Data Transformations . . . . . . . . . . . . . . . . . . 8

Step 6: Set Tracking Duration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Step 7: Implement the Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Chapter 3: The Art of Cube Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

By Russ Whitney, Tim Ramey

Designing a Sales-Forecasting Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Providing Valid Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Chapter 4: DTS 2000 in Action . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

By Larry Barnes

Introducing the Create FoodMart 2000 Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Initializing Global Variables and the Package State . . . . . . . . . . . . . . . . . . . . . . . . . 16

Preparing the Existing Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Creating the FoodMart Database and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Change Is Good . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

Chapter 5: Rock-Solid MDX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

By Russ Whitney

ii A Jump Start to SQL Server BI

Chapter 6: XML for Analysis: Marrying OLAP and Web Services . . . . . . . . . . . . . 45

By Rob Ericsson

Installing XMLA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Using XMLA: Discover and Execute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Getting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

A Convenient Marriage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Chapter 7: Improving Analysis Services Query Performance . . . . . . . . . . . . . . . 57

By Herts Chen

Traffic-Accident Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Queries and Bottlenecks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Usage-Based Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Partition Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Guidelines for Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Chapter 8: Reporting Services 101 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

By Rick Dobson

Installing Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Creating Your First Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Creating a Drilldown Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Deploying a Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Viewing Deployed Solution Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Beyond the Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Section II – BI Tips and Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Improve Performance at the Aggregation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Using Children to Automatically Update Products . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Saving DTS Information to a Repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Intelligent Business . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Techniques for Creating Custom Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Using Loaded Measures to Customize Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . 76

Caution: Large Dimensions Ahead . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Decoding MDX Secrets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Improve Cube Processing by Creating a Time Dimension Table . . . . . . . . . . . . . . . . 78

Transforming Data with DTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Supporting Disconnected Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Dependency Risk Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Choosing the Right Client for the Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

Using Access as a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

Calculating Utilization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

Use Member Properties Judiciously . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Get Level Names Right from the Get-Go . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Aggregating a Selected Group of Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Determining the Percentage of a Product’s Contribution . . . . . . . . . . . . . . . . . . . . . . 86

iii

Avoid Crippled Client Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Setting OLAP Cube Aggregation Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

Use Views as the Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

Enter Count Estimates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Using Dynamic Properties to Stabilize DTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Leave Snowflakes Alone . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Create Grouping Levels Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Understand the Role of MDX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Using NON EMPTY to Include Empty Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Formatting Financial Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Analyzing Store Revenue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Use Counts to Analyze Textual Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Consolidation Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

Working with Analysis Services Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . . 93

Filtering on Member Properties in SQL Server 7.0 . . . . . . . . . . . . . . . . . . . . . . . . . . 93

Improving Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Using SQL ALIAS for the AS/400 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

Setting Up English Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

When Do You Use Web Services? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

The Security Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Section III – New BI Features in SQL Server 2005 . . . . . . . . . . . . . . . . . . 98

Chapter 1: Building Better BI in SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . 99

How are SQL Server 2005’s BI enhancements meeting Microsoft’s goals for

serving the BI community? And how long has your team has been working on

these enhancements? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

What kind of feedback have you been getting from beta testers, and which

features are they most enthusiastic about? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

According to news reports, Microsoft and some large customers have deployed

SQL Server 2005 Beta 2 in production environments. What is your recommendation

for deploying Beta 2 and running it in production? What caveats do you have

for businesses eager to move to the new version now? . . . . . . . . . . . . . . . . . . . . . . 100

How compatible are SQL Server 2000’s BI tools (OLAP, DTS, data mining) and

SQL Server 2005’s new BI tools? Because some of SQL Server 2005’s BI tools—

such as Integration Services—are completely rewritten, will they still work with

SQL Server 2000 data and packages? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

SQL Server 2000 Analysis Services supports only clustering and decision-tree

data-mining algorithms. Does SQL Server 2005 add support for other algorithms? . . . . 100

Microsoft relies on an integrated technology stack—from OS to database to

user interface. How does that integration help Microsoft’s BI offerings better

serve your customers’ needs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

iv A Jump Start to SQL Server BI

SQL Server 2005 will be the first release in which database tools converge

with Visual Studio development tools. Can you tell us what it took to align

these two releases and what benefits customers will realize from the change? . . . . . . 101

The introduction of the UDM is said to blur the line between relational and

multidimensional database architectures. This approach is new for the

Microsoft BI platform. What are the most interesting features the UDM offers?

And based on your experience, what features do you think will surface as the

most valuable for customers and ISVs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

What tools will Microsoft add to the Visual Studio 2005 IDE to help developers

create and manage SQL Server (and other database platforms’) users, groups,

and permissions to better insulate private data from those who shouldn’t

have access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

In one of your past conference keynote addresses, you mentioned that Microsoft

is adding a new set of controls to Visual Studio 2005 to permit reporting without

Reporting Services. Could you describe what those controls will do, when we’ll

see the controls appear in Visual Studio 2005, and where you expect them to

be documented? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

What benefit does 64-bit bring to SQL Server BI, and do you think 64-bit can

really help the Microsoft BI platform scale to the levels that UNIX-based

BI platforms scale to today? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Who are some BI vendors you’re working closely with to develop 64-bit

BI computing? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Did you leave out any BI features that you planned to add to SQL Server 2005

because of deadlines or other issues? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Your team puts a lot of long hours into your work on SQL Server BI.

What drives you and your BI developers to invest so much personally in

the product? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Chapter 2: UDM: The Best of Both Worlds . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

By Paul Sanders

The UDM Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

One Model for Reporting and Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Chapter 3: Data Mining Reloaded . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

By Alexei Bocharov, Jesper Lind

Mining the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Prediction and Mutual Prediction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Decision Trees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

Time Series . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Clustering and Sequence Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Naive Bayes Models and Neural Networks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

Association Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

Third-Party Algorithms (Plug-Ins) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Dig In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: