book

Pro SQL Server 2008 Analysis Services

by Philo Janus Guy Fouché

June 2010

Beginner to intermediate

479 pages

10h 29m

English

Apress

Read now

Unlock full access

1.1. From Pivot Tables to Dimensional Processing1.2. Data Warehousing1.3. Applications of OLAP1.4. History of OLAP1.5. SQL Server Analysis Services1.6. Data Mining1.7. Summary
2.1. Cubes and Their Components2.2. Defining Measures and Dimensions2.2.1. Schemas2.2.2. Dimensions in Depth2.2.2.1. Attributes2.2.2.2. Slowly Changing Dimensions (SCD)2.2.2.3. Hierarchies2.2.2.4. Time Dimensions2.2.3. Measures2.2.4. Types of Aggregation2.2.4.1. Additive2.2.4.2. Semiadditive2.2.4.3. Nonadditive2.2.5. Writeback2.2.6. Calculated Measures2.3. Actions2.4. XMLA2.5. Multidimensional Expressions (MDX)2.6. Data Warehouses2.7. Storage2.7.1. Staging Databases2.7.2. Storage Modes2.7.2.1. MOLAP2.7.2.2. ROLAP2.7.2.3. HOLAP2.8. Summary
3.1. Requirements3.1.1. Hardware3.1.2. Virtualization3.1.3. Software3.1.4. Upgrading3.1.5. Standard or Enterprise Edition?3.2. Architecture3.2.1. The Unified Dimensional Model3.2.2. Logical Architecture3.2.3. Physical Architecture3.2.4. Storage3.3. Cube Structures in SSAS3.3.1. Data Sources3.3.2. Data Source View3.3.3. The Cube Structure Itself3.3.4. Dimensions3.3.5. Mining Structures3.4. What's New in SQL Server 20083.4.1. Performance3.4.1.1. Management Data Warehouse3.4.1.2. Reliable Query Cancel3.4.1.3. Dynamic Management Views3.4.1.4. Block Computing—Optimization3.4.1.5. Writeback Performance Improvements3.4.1.6. Change Data Capture3.4.2. Tools3.4.2.1. Dimension Design3.4.2.2. Aggregation / UBO Designer3.4.2.3. AMO Design Warnings3.5. Summary
4.1. Business Intelligence Development Studio4.1.1. BIDS Is Visual Studio?4.1.2. Panes4.1.3. Solution Explorer4.1.4. Properties Pane4.1.5. Creating or Editing a Database Solution4.1.5.1. Create a New Analysis Services Project4.1.5.2. Open an Existing SSAS Database4.1.5.3. Open an Existing SSAS Database as Part of a Project4.2. SQL Server Management Studio4.2.1. Managing Analysis Services4.2.1.1. Data Sources4.2.1.2. Data Source Views4.2.1.3. Cubes4.2.1.4. Dimensions4.2.1.5. Mining Structures4.2.1.6. Roles4.2.2. Executing MDX Queries4.3. PowerShell4.3.1. A Convincing Example4.3.2. PowerShell for SQL Server4.3.3. PowerShell with SSAS4.4. Summary
5.1. Cubes Need Data5.2. Data Sources5.3. Data Source Views5.3.1. Designer Tour5.3.1.1. Finding Tables5.3.1.2. Replacing a Table5.3.2. Named Calculations and Queries5.3.2.1. Named Calculations5.3.2.2. Named Queries5.4. Summary

6.1. Dimensional Analysis6.1.1. Review of the Dimension Concept6.1.2. Star or Snowflake?6.1.2.1. The Advantage of Simplicity6.2. Dimensions in SSAS6.2.1. Creating a Dimension6.2.2. Analysis Management Objects (AMO) Warnings6.2.3. Dimension Properties6.2.3.1. Dimension Type6.2.3.2. ErrorConfiguration6.2.3.3. MdxMissingMemberMode6.2.3.4. Processing6.2.3.5. Storage6.2.3.6. UnknownMember6.2.3.7. WriteEnabled6.3. Attributes6.3.1. Attribute Relationships6.3.2. Attribute Properties6.4. Parent-Child Dimensions6.5. The Time Dimension6.6. Summary
7.1. Dimensions and Cubes7.1.1. Creating Cubes7.1.2. Using Measure Group Tables7.1.3. Selecting Dimensions7.1.4. Defining Dimension Usage7.2. Measures and Measure Groups7.2.1. Measures7.2.2. Measure Groups7.2.3. Calculated Measures7.3. Summary
8.1. Deploying a Project8.1.1. Project Properties8.1.2. Deployment Methods8.2. Using the Deployment Wizard8.2.1. Running the Wizard8.2.2. Input Files8.2.3. Deployment Scripts8.3. Synchronizing SSAS Databases8.4. Processing8.4.1. What Processing Does for Us8.4.2. How to Initiate Processing from BIDS8.4.3. Processing from SQL Server Management Studio8.4.4. Processing via XMLA8.4.5. Processing with Analysis Management Objects (AMO)8.5. Scheduling OLAP Maintenance8.6. Summary
9.1. Why the Need?9.2. Tuples and Sets9.2.1. Notation9.2.2. Tuples9.2.3. Sets9.3. MDX Queries9.3.1. SELECT9.3.2. WHERE9.3.3. MDX Functions9.3.4. Categories of Functions9.3.4.1. Tree Functions9.3.4.2. Aggregate Functions9.3.4.3. Time Functions9.4. Summary
10.1. Business Intelligence10.1.1. Time Intelligence10.1.2. Account Intelligence10.1.3. Dimension Intelligence10.2. Operators, Functions, and More10.2.1. Unary Operators10.2.2. Custom Member Formulas10.2.3. Attribute Ordering10.2.4. Currency Conversion10.3. Calculations Tab10.3.1. Calculated Measures10.3.2. Named Sets10.4. Other Cube Features10.4.1. Key Performance Indicators10.4.2. Actions10.4.3. Perspectives10.4.4. Translations10.5. Summary
11.1. Why Mine Data?11.2. Using Data-Mining Algorithms11.2.1. Microsoft Naïve Bayes11.2.2. Microsoft Clustering11.2.3. Microsoft Decision Trees11.3. Creating the Accessory Buyers Marketing Campaign11.4. Preparing the Data Warehouse11.4.1. Creating the Accessory Buyers Views in AdventureWorks11.4.2. Creating the Accessory Campaign Data Source View11.5. Finding Accessory Buyers by Using the AdventureWorks EDW11.6. Using the Data Mining Model Designer11.6.1. The Mining Structure View11.6.2. The Mining Models View11.6.3. The Mining Model Viewer View11.6.3.1. Exploring the Decision Tree11.6.3.2. Using Drill-Through to View a Model's Training Cases11.6.3.3. Using the Dependency Network11.6.4. The Mining Accuracy Chart View11.6.5. The Mining Model Prediction View11.7. Finding Accessory Buyers by Using Data Mining Extensions (DMX)11.7.1. Use the DMX Development Environment11.7.2. Create the Accessory Buyers Mining Structure11.7.3. Add a Naïve Bayes Mining Model to the Accessory Buyers Campaign11.7.4. Process the Accessory Buyers Campaign11.7.5. View the Accessory Buyers Mining Model11.7.6. Predict Our Accessory Buyers11.8. Summary
12.1. PowerPivot Support in SQL Server 2008 R212.1.1. Master Data Services12.1.2. Excel Writeback12.2. PowerPivot from Excel12.3. PowerPivot with SharePoint Server 201012.4. Summary
13.1. DBA Tasks13.1.1. Processing a Cube13.1.2. Processing Options13.1.3. Processing Architecture13.1.4. Profiler13.1.5. Performance Monitor13.1.6. Automation13.1.7. XML for Analysis13.1.8. Analysis Management Objects13.1.9. PowerShell13.1.10. Scheduling13.1.10.1. SQL Server Agent13.1.11. SQL Server Integration Services13.2. Security13.2.1. Authentication13.2.2. Authorization13.3. Performance13.3.1. Design13.3.1.1. Dimensions13.3.1.2. Attribute Design13.3.1.3. Partitions13.3.2. Aggregations13.3.2.1. Understanding Aggregations13.3.2.2. Creating an Aggregation13.3.2.3. Preparing an Aggregation for Use13.3.3. Scaling13.3.3.1. Large Data Size13.3.3.2. Large Number of Users13.3.4. Virtualization13.3.5. SharePoint Server 201013.4. Summary
14.1. Excel 200714.1.1. Data Source Connections14.1.2. Pivot Tables14.1.2.1. Pivot Table Task Pane14.1.2.2. Pivot Table Ribbons14.1.2.3. Formatting a Pivot Table14.1.2.4. Formulas Using Pivot Table Values14.1.3. Pivot Charts14.2. Visio 200714.3. SQL Server Reporting Services14.3.1. Reports14.3.2. Tablix14.3.3. Charts14.3.4. Report Builder 2.014.4. MOSS 200714.4.1. KPI Lists14.4.2. Excel Services14.5. PerformancePoint14.6. Summary
B.1. BooksB.2. Web Sites

Content preview from Pro SQL Server 2008 Analysis Services

Chapter 9. MDX

So, after eight chapters of referring to Multidimensional Expressions (MDX), you finally get to find out what those expressions actually are. Just as SQL is a structured query language for returning tabular results from relational databases, MDX is a query language for returning multidimensional data sets from multidimensional data sources.

MDX was introduced by Microsoft with SQL Server OLAP Services 7.0 in 1997. Since then it has been adopted by a number of vendors and has become the de facto OLAP query language. The latest specification was published in 1999, but Microsoft continues to add improvements.

Why the Need?

For an idea of a comparison between SQL and MDX, let's look at two examples before we dive into the details. We want ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Publisher Resources

ISBN: 9781430219958Purchase book

Pro SQL Server 2008 Analysis Services

by Philo Janus Guy Fouché

Chapter 9. MDX

Why the Need?

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Microsoft® SQL Server® 2012 Integration Services

Professional Microsoft SQL Server 2014 Integration Services

Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model

Publisher Resources

Chapter 9. MDX

Why the Need?

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Microsoft® SQL Server® 2012 Integration Services

Professional Microsoft SQL Server 2014 Integration Services

Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.