The Microsoft SQL Server 2000 Analysis Services Step by Step

The Microsoft SQL Server 2000 Analysis Services Step by Step

by Reed Jacobson, OLAP Train, Reed Jacobsen
     
 

MICROSOFT® SQL SERVER™ 2000 ANALYSIS SERVICES STEP BY STEP shows Microsoft Excel and Access experts, IS managers, and database developers how to build applications that take advantage of the powerful data-analysis services in Microsoft SQL Server 2000. You’ll discover why these services make it easier to analyze huge amounts of data quickly, and

…  See more details below

Overview

MICROSOFT® SQL SERVER™ 2000 ANALYSIS SERVICES STEP BY STEP shows Microsoft Excel and Access experts, IS managers, and database developers how to build applications that take advantage of the powerful data-analysis services in Microsoft SQL Server 2000. You’ll discover why these services make it easier to analyze huge amounts of data quickly, and you’ll learn how to develop a wide range of advanced dimensional-data applications—from enterprise reporting tools to advanced decision-support systems. The book’s easy-to-follow lessons begin with clear objectives and include real-world business examples, with a companion CD full of sample files that support each lesson.

This title shows you how to:

  • Administer the Analysis Manager: Understand the fundamentals behind data dimensions and hierarchies, data warehousing, and the Microsoft Analysis Services architecture, and use the Microsoft Analysis Manager application to define and populate data cubes.

  • Create and display cubes: Define and modify measures and dimensions. Design, build, process, and view cubes, from simple to advanced, with cube and dimension editors. Use Microsoft Excel PivotTable® Reports and the PivotTable List in Microsoft Excel to browse cubes, and create cubes using Microsoft Office.

  • Query cubes: Use multidimensional expressions (MDX) values and sets and the MDX Sample application to query and display dimensional data.
  • Perform advanced administration: Choose data storage options and the Storage Design Wizard to optimize, manage, and update data efficiently. Automate the Analysis server to process an OLAP database. Work with partitions and automate updates, and apply security to cubes.

A Note Regarding the CD or DVD

The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via O'Reilly Media's Digital Distribution services. To download this content, please visit O'Reilly's web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to booktech@oreilly.com.

Read More

Editorial Reviews

bn.com
Our Review
Data cubes. Multidimensional expressions. OLAP. If ever anything sounded hard, it's data analysis. With SQL Server 2000, Microsoft continues its quest to change all that. SQL Server's new Analysis Services is almost easy -- but not quite. Microsoft SQL Server 2000 Analysis Services Step by Step gets you over those final hurdles, with some of the clearest data analysis explanations and instructions we've seen yet.

Every chapter is organized as a lesson, with accompanying files on the CD-ROM. If you're a DBA, you'll learn how to create and manage your Analysis Services database. If you're a business analyst, you'll learn how to transform your spreadsheet models into a form you can scale -- and share -- throughout the enterprise. And if you're a business decision-maker, you'll discover exactly what you can expect from SQL Server 2000's Analysis Services.

You'll find step-by-step coverage of data cubes: imagining them, planning them, designing them, creating, them, processing them, and browsing them. You'll master SQL Server 2000's Dimension Editor, use it to work with star schema, time, and snowflake dimensions, and create real-world cubes for finance, forecasting, and Internet tracking.

Microsoft SQL Server 2000 Analysis Services Step by Step includes a detailed chapter on integrating with Office 2000's analysis components (such as Excel PivotTable reports). There's a full section on Multidimensional Expressions, which transform your large OLAP database into a sophisticated calculation engine. Finally, since you want rapid performance but don't have infinite hardware budgets, the book walks you step by step through optimizing both storage and processing.

This book's authors are professional OLAP and data warehouse trainers. It shows, through the masterful use of analogies and examples. They've figured out exactly how to communicate this stuff. If you're just getting into SQL Server 2000 data analysis, this book's a must.

Read More

Product Details

ISBN-13:
9780735609044
Publisher:
Microsoft Press
Publication date:
09/28/2000
Series:
Step by Step Developer Series
Edition description:
REV
Pages:
400
Product dimensions:
7.40(w) x 9.23(h) x 1.23(d)

Related Subjects

Read an Excerpt


Chapter 7 MDX Sets

In this chapter, you'll learn MDX query terminology and how to

  • Use the MDX Sample application to enter queries.
  • Use set functions.
  • Create sets by using member constants.
  • Create calculated members that aggregate sets of values.

    Start the lesson

    Start Analysis Manager. Right-click the server, and click Restore Database.

    Navigate to the folder containing the sample files for this book, select Chapter 7, and click Open. The archived Chapter 7 OLAP database is identical to the one used in Chapter 6, "MDX Values." Click Restore, and then close the Restore Database Progress dialog box.

    Open the Chapter 7 HTML file included with the sample files for this book. This HTML file contains a Microsoft Office PivotTable list that retrieves values from the Sales cube of the Chapter 7 OLAP database.

    Have you ever noticed that no matter how many times you travel to a location as a passenger, the route seems entirely different the first time you have to navigate-especially if you're alone. The Microsoft Excel PivotTable report and the Office PivotTable list features create multidimensional expressions (MDX) statements to retrieve values from a cube, but creating a report yourself can give you a tremendous appreciation for how Microsoft SQL Server 2000 Analysis Services works.

    An MDX query statement is different from an MDX expression. An expression is a formula that calculates a single value. A query is a command that populates a report with many values from a cube. The Office PivotTable list, the Excel PivotTable report, and the browser included in Analysis Manager all generate MDX queries to retrieve values from a cube. You can use tools like these to create reports without writing any MDX statements of your own. Unless you're a developer creating a custom report generator, you'll probably have little occasion to write MDX query statements. So, why should you learn how to create an MDX query statement? Because the most interesting MDX expressions involve creating a single result based on a large set of values from a cube. Those expressions, in effect, create a subquery behind the scenes. Learning how to create an MDX query will enable you to understand clearly what the subquery is doing when you create complex MDX expressions. In the first part of this chapter, you'll learn how to create explicit queries. In the latter part of the chapter, you'll use the concepts learned in creating the reports to create calculated members based on large sets of values.

    Preparing to Create MDX Queries

    The purpose of an MDX query is to extract values from an OLAP cube into a report. As explained in Chapter 1, "A Data Analysis Foundation," a cube has dimensions (up to 64, if you count Measures as a dimension). A report does not have dimensions; it has axes (typically, a row axis, a column axis, and a filter axis). An axis can include labels from more than one dimension. A cube contains all possible values for all members of all levels of all dimensions. A report contains only selected values from selected levels of selected dimensions. An MDX query statement consists of the instructions for extracting a report from a cube.

    Use a PivotTable list to understand MDX terms

    An MDX query statement uses several new terms. Seeing the terms in a familiar context will make them easier to learn and understand. Browsers such as the Office PivotTable list use MDX query statements to populate a report. You can use the Office PivotTable list to learn MDX terminology. The initial report in the Chapter 7 HTML file shows only a single cell showing the total value for the Sales Dollars measure. The grand total is a single value from the cube. To retrieve this value, Analysis Services used the default member for each dimension.

    In the Web browser, click the Field List toolbar button. Drag the Product, State, Employee, and Time Calendar dimensions from the PivotTable Field List window to the filter area of the report. Each dimension displays the default member in the filter area, and the total value never changes as you add the dimensions. (Since the Time Fiscal and Time Calendar dimensions are really hierarchies of the same dimension, you can include only one in the report at a time. The report still uses the default member of the unused dimension.)

    The default member allows a query to ignore some of the dimensions in a cube. If a dimension does not appear on the filter axis, it always uses the default member. If a dimension does appear on the filter axis, it can either use the default member or a different member. Putting a dimension on the filter area of a report overrides the default member for that dimension.

    Click the arrow next to the Product dimension list, select the Meat check box, and click OK to show only the total for meat.

    If you use nothing but the filter area, you create a report that displays only a single value, which is rarely useful. The row and column axes of a report allow you to display more than one member from a dimension. More than one member from a single dimension is called a set.

    Drag the State dimension to the row axis. The three countries-plus the total-appear. The row axis now displays a set that contains four positions. Each position in the set corresponds to a member from the State dimension.

    Click the Country level label, and click the Expand toolbar button.

    The regions appear. The row axis now displays a set that contains eight positions. Even though the labels are split into two columns-to show the levels of the dimension-each position in the set still contains only a single member from the State dimension.

    Drag the Sales Units measure to the data area. The column axis now displays a set that contains two positions, each of which corresponds to a single member from the Measures dimension.

    For each cell value you see in the report, you can determine a complete tuple that includes one member from each dimension in the cube. For the cell in the Sales Dollars column with the value 6233.27, the Product dimension contributes Meat, the Employee Dimension contributes Sheri Nowmer, the Time Calendar dimension contributes All Time, and the State dimension contributes North West. Every value in an MDX report requires a single member from each dimension. The filter axis can include only a single member for any one dimension. The row and column axes can include only sets, which include multiple values from a single dimension.

    It's not unusual for a cube to contain at least six or seven dimensions. Not counting the filter axis, a report based on an OLAP cube typically contains only two axes-columns and rows. Often, it's useful to combine multiple dimensions from the cube onto a single axis of the report.

    In the PivotTable List document, drag the Region and State level headings away from the row area, leaving only the countries and Grand Total on the axis.

    Drag the Product level from the filter area to the row area, to the left of the State level. Drag the Subcategory and Product Name labels away from the report. Select the Category label, and click the Expand toolbar button. The report now shows both categories and countries on the row axis. The row axis still contains a set-a set with 13 positions. But each position within the set now contains a two-member tuple-a coordinate from more than one dimension.

    Drag the Time Calendar dimension from the filter area to the row area, to the left of the Category label. Drag the Calendar Quarter and Calendar Month labels away from the report. Then select the Calendar Year label, and click Expand. The report now shows years, categories, and countries on the row axis. The row axis still contains a set-now a set with 17 positions. But each position in the set now contains a three-member tuple.

    A tuple is a way to specify coordinates from more than one dimension on a single axis. The filter axis can contain only a single tuple. Row and column axes can each contain a set of tuples. The bottom line remains: for each value in the report, there's a single member from each dimension that contributed to the complete coordinate for that cell in the cube. The member for a dimension can come from any of the axes-and more than one dimension can be represented on a single axis. Any dimension not found on the column, row, or filter axis uses the default member...

    Read More

  • Meet the Author

    OLAP Train is a curriculum-development company that specializes in OLAP and related data-warehousing technologies. Microsoft licenses OLAP Train's courseware for Microsoft SQL Server 2000 worldwide as Microsoft Official Curriculum titles.

    Reed Jacobson is a senior architect in Hitachi Consulting’s Business Intelligence practice. He has authored several training courses and books, including Microsoft SQL Server 2000 Analysis Services Step by Step.

    Customer Reviews

    Average Review:

    Write a Review

    and post it to your social network

         

    Most Helpful Customer Reviews

    See all customer reviews >