Book Image

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Book Image

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Overview of this book

Microsoft's SQL Server Analysis Services 2008 is an OLAP server that allows users to analyze business data quickly and easily. However, designing cubes in Analysis Services can be a complex task: it's all too easy to make mistakes early on in development that lead to serious problems when the cube is in production. Learning the best practices for cube design before you start your project will help you avoid these problems and ensure that your project is a success. This book offers practical advice on how to go about designing and building fast, scalable, and maintainable cubes that will meet your users' requirements and help make your Business Intelligence project a success. This book gives readers insight into the best practices for designing and building Microsoft Analysis Services 2008 cubes. It also provides details about server architecture, performance tuning, security, and administration of an Analysis Services solution. In this book, you will learn how to design and implement Analysis Services cubes. Starting from designing a data mart for Analysis Services, through the creation of dimensions and measure groups, to putting the cube into production, we'll explore the whole of the development lifecycle. This book is an invaluable guide for anyone who is planning to use Microsoft Analysis Services 2008 in a Business Intelligence project.

Related Content you might be interested in

Current Title:

Small book image
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Jul, 2009 | 360 pages
No titles found
Table of Contents (17 chapters)
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
Preface
Preface
Free Chapter
1
Designing the Data Warehouse for Analysis Services
Designing the Data Warehouse for Analysis Services
The source database
Data modeling for Analysis Services
Physical database design for Analysis Services
Summary
2
Building Basic Dimensions and Cubes
Building Basic Dimensions and Cubes
Choosing an edition of Analysis Services
Setting up a new Analysis Services project
Creating data sources
Creating Data Source Views
Designing simple dimensions
Building a Simple Cube
Summary
3
Designing More Complex Dimensions
Designing More Complex Dimensions
Grouping and Banding
Slowly Changing Dimensions
Junk dimensions
Ragged hierarchies
Summary
4
Measures and Measure Groups
Measures and Measure Groups
Measures and aggregation
Measure groups
Dimension/measure group relationships
Summary
5
Adding Transactional Data such as Invoice Line and Sales Reason
Adding Transactional Data such as Invoice Line and Sales Reason
Details about transactional data
Drillthrough
Many-to-many dimension relationships
Summary
6
Adding Calculations to the Cube
Adding Calculations to the Cube
Different kinds of calculated members
Common calculations
Calculation dimensions
Named sets
Summary
7
Adding Currency Conversion
Adding Currency Conversion
Introduction to currency conversion
Where to perform currency conversion
The Add Business Intelligence Wizard
Measure expressions
Summary
8
Query Performance Tuning
Query Performance Tuning
How Analysis Services processes queries
Performance tuning methodology
Designing for performance
Performance-specific design features
MDX calculation performance
Caching
Scale-up and scale-out
Summary
9
Securing the Cube
Securing the Cube
Sample security requirements
Analysis Services security features
Administrative security
Data security
Dynamic cell security
Accessing Analysis Services from outside a domain
Managing security
Security and query performance
Summary
10
Productionization
Productionization
Making changes to a cube in production
Managing partitions
Managing processing
Analysis Services data directory maintenance
Backup
Copying databases between servers
Summary
11
Monitoring Cube Performance and Usage
Monitoring Cube Performance and Usage
Analysis Services and the operating system
Monitoring processing performance
Monitoring query performance
Monitoring usage
How to build a complete monitoring solution
Summary
Index
Index

About the Authors

Chris Webb () has been working with Microsoft Business Intelligence tools for almost ten years in a variety of roles and industries. He is an independent consultant and trainer based in the UK, specializing in Microsoft SQL Server Analysis Services and the MDX query language. He is the co-author of MDX Solutions for Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase, Wiley, 0471748080, is a regular speaker at conferences, and blogs on Business Intelligence (BI) at http://cwebbbi.spaces.live.com. He is a recipient of Microsoft's Most Valuable Professional award for his work in the SQL Server community.

First and foremost, I'd like to thank my wife Helen and my two daughters Natasha and Amelia for putting up with me while I've been working on this book. I'd also like to thank everyone who's helped answer all the questions I came up with in the course of writing it: Deepak Puri, Darren Gosbell, David Elliott, Mark Garner, Edward Melomed, Gary Floyd, Greg Galloway, Mosha Pasumansky, Sacha Tomey, Teo Lachev, Thomas Ivarsson, and Vidas Matelis. I'm grateful to you all.

Alberto Ferrari () is a consultant and trainer for the BI development area with the Microsoft suite for Business Intelligence. His main interests are in the methodological approaches to the BI development and he works as a trainer for software houses that need to design complex BI solutions.

He is a founder, with Marco Russo, of the site www.sqlbi.com, where they publish many whitepapers and articles about SQL Server technology. He co-authored the SqlBI Methodology, which can be found on the SQLBI site.

My biggest thanks goes to Caterina, who had the patience and courage to support me during all the hard time in book writing and my son, Lorenzo, is just a year old but he's an invaluable source of happiness in my life.

Marco Russo () is a consultant and trainer in software development based in Italy, focusing on development for the Microsoft Windows operating system. He's involved in several Business Intelligence projects, making data warehouse relational, and multidimensional design, with particular experience in sectors such as banking and financial services, manufacturing and commercial distribution.

He previously wrote several books about .NET and recently co-authored Introducing Microsoft LINQ, 0735623910, and Programming Microsoft LINQ, 0735624003, both published by Microsoft Press. He also wrote The many-to-many revolution, a mini-book about many-to-many dimension relationships in Analysis Services, and co-authored the SQLBI Methodology with Alberto Ferrari. Marco is a founder of SQLBI (http://www.sqlbi.com) and his blog is available at http://sqlblog.com/blogs/marco_russo.

Previous Section
Next Chapter