深入浅出SQL Server 2005 Analysis Services

需积分: 10 157 浏览量更新于2024-07-27 收藏 23.59MB PDF 举报

"SQL Server 2005 Analysis Services Step by Step" 本书《SQL Server 2005 Analysis Services Step by Step》是微软出版社发行的一本技术指南，由Hitachi Consulting授权，专注于介绍如何使用SQL Server 2005的Analysis Services进行数据管理和分析。Analysis Services是微软提供的一种强大的在线分析处理（OLAP）和数据挖掘工具，主要用于企业级的数据仓库和商业智能应用。在SQL Server 2005中，Analysis Services提供了多维数据集、数据挖掘以及报表服务等功能，帮助用户快速地对大量复杂数据进行聚合、查询和分析。这本书通过逐步指导的方式，让读者能够掌握以下关键知识点： 1. **安装与配置**：书中会详细介绍如何在服务器环境中安装和配置SQL Server 2005 Analysis Services，包括设置角色、配置数据库连接以及安全设置等。 2. **多维数据模型**：多维数据模型是OLAP的核心，本书将解释如何设计和构建维度和事实表，以及如何使用星型和雪花型模式来优化查询性能。 3. **Cube设计**：创建和管理数据立方体是Analysis Services的重要任务。书中的步骤会引导读者如何定义度量值、层次结构和计算成员，以及如何应用切片和切块、钻取和旋转等操作。 4. **数据源视图**：数据源视图（DSV）是连接到关系数据库并定义用于构建多维数据集的结构的关键组件。书会讲解如何创建和管理DSV，以及如何处理数据源的复杂性。 5. **脚本语言MDX**：多维表达式（MDX）是查询和操作多维数据的主要语言。读者将学习MDX的基本语法和高级技巧，包括编写复杂的查询和计算。 6. **数据挖掘**：SQL Server 2005 Analysis Services也包含了数据挖掘功能，允许预测分析和模式发现。书会涵盖数据挖掘模型的创建、训练和部署，以及使用Data Mining Extensions (DMX)语言。 7. **性能优化**：为了确保高效的数据处理，书中还会讨论如何监控和优化Analysis Services的性能，包括调整内存分配、缓存策略和索引设计。 8. **客户端工具集成**：了解如何在Excel、Reporting Services以及其他客户端工具中使用Analysis Services提供的数据，如创建PivotTable和自定义报表。 9. **安全性与权限管理**：分析服务的安全性对于保护企业数据至关重要。本书会讲解如何设置角色、权限和访问控制，确保数据安全。 10. **维护与升级**：最后，读者将学习到Analysis Services的备份、恢复和升级策略，以保持系统稳定并适应不断变化的需求。《SQL Server 2005 Analysis Services Step by Step》是一本全面的实战指南，适合SQL Server管理员、数据分析师以及希望深入了解企业级数据分析的IT专业人士。通过阅读本书，读者不仅可以掌握Analysis Services的基础知识，还能提升在实际项目中的应用能力。

4 Part I: Getting Started with Analysis Services

An EIS, however, often consisted of one person copying key data values from various reports

onto a “dashboard” so that an executive could see them at a glance. But the goal was still to

help the decision maker make decisions. Later, EIS applications were replaced by decision

support system (DSS) applications, which really did essentially the same thing. So what is so

different about BI?

The biggest change in the past few decades has been the need to create management reports

for all levels of an organization, and all types of decision makers. When you need to provide

fast-response reports for many purposes throughout a large organization, having one person

type values for another to read is not practical.

One useful way to think about BI is to consider the types of reports—and their respective audi-

ences. Typical reports fall into one of the three following general classes.

■ Dashboard reports These are highly summarized, often graphical representations of

the state of the business. The values on a dashboard report are often key performance

indicators (KPIs) for an organization. A dashboard report may display a simple summa-

tion of month-to-date sales, or it may include complex calculations such as profitability

growth from the same period of the previous year for the current department compared

to the company as a whole. A dashboard often includes comparisons to targets. A dash-

board report is often customized for the person viewing the report, showing, for exam-

ple, each manager the results for his or her department. Dashboard reports are often

used by executives and strategic decision makers.

■ Production reports These are typically large, detailed reports that have the same basic

structure each time they are produced. They may be printed, or distributed online,

either in Web-based reports or as formatted files. One advantage of a production report

is that the same information can be found in the same place in each report. A production

report may consist of one large report showing information about all parts of the com-

pany, or it may be “burst” into individual sections delivered to the relevant audience.

Production reports are often used by administrators and tactical decision makers.

■ Analytical reports These are dynamic, interactive reports that allow the user to “slice

and dice” the information in any of thousands of ways. As with dashboard reports, ana-

lytical reports can display simple summations or complex calculations. They typically

allow drill-down to very detailed information, or drill-up to high-level summaries. This

type of report is typically used by analysts or “hands-on” managers who want to under-

stand all aspects of the situation.

Much of the information you need comes from outside the organization. That’s why you read

the Wall Street Journal and keep a bookmark in your browser pointed at www.bloomberg.com.

But much of the information you need also comes from inside the organization, and much of

that information is numerical. This numerical information becomes more useful for decision

making when organized into a BI solution.

6-2199-3eBook.book Page 4 Wednesday, March 1, 2006 5:05 PM

Chapter 1: Understanding Business Intelligence and Data Warehousing 5

Reviewing Data Warehousing Concepts

A data warehouse is often a core component of a BI infrastructure within an organization. The

procedures that you’ll complete throughout this book use a sample data warehouse as the

underlying database for the analytical solutions that you’ll build. In this section, you’ll review

the characteristics of a data warehouse, the table structures in a data warehouse, and design

considerations, but details for building a data warehouse are beyond the scope of this book.

For more information about data warehousing, refer to http://msdn.microsoft.com/library

/default.asp?url=/library/en-us/createdw/createdw_3r51.asp.

The Purpose of a Data Warehouse

A data warehouse is a repository for storing and analyzing numerical information. A data ware-

house stores stable, verified data values. You might find it helpful to compare some of the

most important differences between a data warehouse and a transaction database.

■ A transaction database helps people carry out activities, while a data warehouse helps

people make plans. For example, a transaction database might show which seats are

available on an airline flight so that a travel agent can book a new reservation. A data

warehouse, on the other hand, might show the historical pattern of empty seats by flight

so that an airline manager can decide whether to adjust flight schedules in the future.

■ A transaction database focuses on the details, while a data warehouse focuses on high-

level aggregates. For example, a parent purchasing the latest popular children’s book

doesn’t care about inventory levels for the Juvenile Fiction product line, but a manager

planning the rearranging of store shelving may be very interested in a general decline in

sales of computer book titles (for subjects other than SQL Server 2005). The implication

of this difference is that the core data in a warehouse are typically numeric values that

can be summarized.

■ A transaction database is typically designed for a specific application, while a data

warehouse integrates data from different sources. For example, your order processing

application—and its database—probably includes detailed discount information for

each order, but nothing about manufacturing cost overruns. Conversely, your manu-

facturing application—and its database—probably includes detailed cost information,

but nothing about sales discounts. By combining the two data sources in a data ware-

house, you can calculate the actual profitability of product sales, possibly revealing

that the fully discounted price is less than the actual cost to manufacture. But no wor-

ries: You can make up for it in volume.

■ A transaction database is concerned with now; a data warehouse is concerned with

activity over time. For example, in a simple bank account, each transaction—that is, each

deposit or withdrawal—creates an instantaneous change in the account balance. The

transaction system rarely maintains historical balances, and even transaction logs are

usually archived after a month or two. In a data warehouse, you can store many years of

6-2199-3eBook.book Page 5 Wednesday, March 1, 2006 5:05 PM

6 Part I: Getting Started with Analysis Services

transaction data (perhaps summarized), and you can also store snapshots of historical

balances. This allows you to compare what you did today with what you did last month

or last year. When making decisions, the ability to see a wide time horizon is critical for

distinguishing between trends and random fluctuations.

■ A transaction database is volatile; its information constantly changes as new orders are

placed or cancelled, as new products are built or shipped, or as new reservations are

made. A data warehouse is stable; its information is updated at standard intervals—

perhaps monthly, weekly, or even hourly—and, in an ideal world, an update would add

values for the new time period only, without changing values previously stored in the

warehouse.

■ A transaction database must provide rapid retrieval and updating of detailed informa-

tion; a data warehouse must provide rapid retrieval of highly summarized information.

Consequently, the optimal design for a transaction database is opposite to the optimal

design for a data warehouse. In addition, querying a live transaction database for man-

agement reporting purposes would slow down the transaction application to an unac-

ceptable degree.

There are other reasons to create a data warehouse, but these are several of the key reasons,

and should be sufficient to convince you that creating a data warehouse to support manage-

ment reporting is a good thing.

The Structure of a Dimensional Database

One of the most popular data warehouse designs is called a multidimensional database. The

term multidimensional conjures up images of Albert Einstein’s curved space-time, parallel uni-

verses, and mathematical formulas that make solving for integrals sound soothingly simple.

The bottom line is that calling a database multidimensional is really a bit of a lie. It’s a snazzy

term, but when applied to databases it has nothing in common with the multidimensional

behavior of particles accelerating near the speed of light or even with the multidimensional

aspects of Alice’s adventures down the rabbit hole. This section will help you understand

what multidimensionality really means in a database context.

Suppose that you are the president of a small, new company. Your company needs to grow,

but you have limited resources to support the expansion. You have decisions to make, and to

make those decisions you must have particular information.

In the world of data warehousing, a summarizable numerical value that you use to monitor

your business is called a measure. When looking for numerical information, your first question

is which measure you want to see. You could look at, say, Sales Dollars, Shipment Units, Total

Defects, or Ad Campaign Responses. Suppose that you ask your personal financial analyst to

create a report of your company’s total Units Sold. Here’s what you’ll get (imagine that the

numbers are in millions, if you prefer):

6-2199-3eBook.book Page 6 Wednesday, March 1, 2006 5:05 PM

8 Part I: Getting Started with Analysis Services

Whether you display the values in a list like the one above (where the numerical values form

a line) or display them in a grid (where they form a rectangle), you still have the potential for

12 values if you have four monthly values for each of three products. Your report has 12 poten-

tial values because the products and the months are independent. Each product gets its own

sales value—even if that value is zero—for each month.

Back to the rectangular report. Suppose that your company sells in two different states and

you’d like to know how each product is selling each month in each state. Add another set

of labels indicating the states your company uses, and you get a new report, one that looks

like this:

The report now has two labels for the states, three labels for products (each shown twice), and

four labels for months. It has the potential for showing 24 values, even if some of those value

cells are blank. The number of potential values equals the number of states times the number

of products times the number of months. This is analogous to calculating the volume of a

cube in the physical world: Volume equals the length of the cube times its width times its

height. Your report doesn’t really look like a cube—it looks more like a rectangle. Again, you

could rearrange it to look like a list. But whichever way you lay out your report, it has three

independent lists of labels, and the total number of potential values in the report equals the

number of unique items in the first independent list of labels (for example, two states) times

the number of unique items in the second independent list of labels (three products) times

the number of unique items in the third independent list of labels (four months).

Because the phrase independent list of labels is wordy, and because the arithmetic used to calcu-

late the number of potential values in the report is identical to the arithmetic used to calculate

length, area, and volume—measurements of spatial extension—in place of independent list of

labels, data warehouse designers borrow the term dimension from mathematics. Remember

that this is a borrowed term. A data analysis dimension is very different from a physical dimen-

sion. Thus, your report has three dimensions—State, Product, and Time—and the report’s num-

ber of values equals the number of items in the first dimension times the number of items in

Cable Lock January 8

Cable Lock February 25

Cable Lock March 21

Cable Lock April

January February March April

WA Road-650 3 10

Mountain-100 3 16 6

Cable Lock 4 16 6

OR Road-650 3 7

Mountain-100 3 8

Cable Lock 4 9 15

6-2199-3eBook.book Page 8 Wednesday, March 1, 2006 5:05 PM

剩余397页未读，继续阅读

zhangdandan791016

粉丝: 0
资源: 1

深入浅出SQL Server 2005 Analysis Services

SQL Server 2005 Analysis Services Step by Step CD光盘

Microsoft SQL Server 2005 Analysis Services Step by Step

Microsoft SQL Server2005 Analysis Services Step by Step

microsoft sql server 2012 analysis services 高级教程 pdf

在SQL Server 2008 Analysis Services中如何设计高效的数据立方体（Cubes），并运用MDX进行性能调优？

在SQL Server 2008 Analysis Services中，如何通过MDX查询优化数据立方体的性能？请提供相关的MDX优化技巧和最佳实践。

如何在SQL Server 2008 Analysis Services中构建和优化数据立方体（Cubes）以提高多维数据分析的性能？

SQL Server Management Studio打开SQL Server Analysis Services

sql server analysis services

sqlserver 2000 升级到sqlserver 2012

最新资源