New book: Microsoft SQL Server 2008 MDX Step by Step

New book: Microsoft SQL Server 2008 MDX Step by Step

  • Comments 25

Microsoft SQL Server 2008 MDX Step by Step, by Bryan C. Smith and C. Ryan Clay,  both of Hitachi Consulting, shipped to the printer in January and is available now (Microsoft Press, 2009; ISBN: 9780735626188; 400 pages).


Here’s some information about Bryan and Ryan:

Bryan C. Smith


Bryan is a manager of specialized services with Hitachi Consulting’s
Microsoft Database Technologies team. As a member of this team,
he designs and implements business intelligence solutions for clients in
a variety of industries using the products in the Microsoft SQL Server
suite. Bryan has degrees from Texas A&M and Duke Universities, holds
a number of Microsoft certifications, and has more than 10 years
of experience developing solutions supporting data analysis. Bryan
lives in the Dallas area with his (amazing) wife, Haruka, and their two
(equally amazing) children, Aki and Umi.






C. Ryan Clay


C. Ryan Clay is a senior architect with Hitachi Consulting, specializing
in business intelligence, data management, portal and collaboration,
and SAP integration/interoperability solutions employing Microsoft
technologies. Ryan has implemented Microsoft Business Intelligence
solutions using Analysis Services and MDX for a variety of Fortune
500 clients in the retail, construction, finance, and consumer goods
industries. Ryan holds degrees in computer science as well as a number
of Microsoft certifications and is active in the Microsoft community
through speaking engagements and presentations at regional and
national events. He lives in the Dallas area with his wife and daughter.

And here’s a stretch from the book’s Introduction, which will give you a good sense of its coverage:


Microsoft SQL Server Analysis Services is a powerful tool for Business Intelligence. Many
organizations, both large and small, have adopted it to provide secure, high-performance
access to complex analytics.

MDX is the language used by Analysis Services for data access. Proficiency with this language
is essential to the realization of your Analysis Services databases’ full potential. The innovative
and elegant model underlying the MDX language makes it a very powerful but at the same
time challenging tool for data analysis. In this book, we address this model head-on and then
guide you through various functions and applications of the MDX language.

Who This Book Is For

This book has been written based on our own experiences as well as those of numerous clients
and students. From these, we believe there are a few prerequisites to effectively learning the
MDX language.

First, you must have basic familiarity with the concepts of dimensional modeling and data
warehousing. If you do not have this knowledge, the overall purpose of Analysis Services and
the MDX language will be lost.

Second, you must have basic familiarity with Analysis Services. You do not necessarily have
to be a cube designer, but it does help to have worked with Analysis Services enough to be
comfortable with its objects and terminology. If you are relatively new to Analysis Services,
we recommend that you review Microsoft SQL Server 2008 Analysis Services Step by Step by
Scott Cameron (Microsoft Press, 2009) before proceeding with this book.

Finally, you must be able put aside the traditional notions of data access you may have
become familiar with. Some of the folks whom we’ve seen struggle the most with MDX
have been some of the most talented users of more traditional languages such as SQL. MDX
requires you to think about data very differently.

What This Book Is About

This book is about the core concepts and basic applications of MDX; it is not an exhaustive
text. Instead, it is intended as a primer for those relatively new to the language. Through the
discussions and exercises presented in each chapter you will be introduced to core concepts
and applications. This will provide you with a solid foundation for continued learning in
real-world scenarios.

This book is divided into three sections, each building on the one before it. We strongly
encourage you to read these sections in sequence to ensure that you fully grasp later
concepts and techniques.

Part I, “MDX Fundamentals,” teaches you the fundamentals of the MDX language and the
primary query development tool you use throughout this book.

Chapter 1, “Welcome to MDX,” presents MDX as a means to deliver business value. This chapter
is critical to establishing the concepts and vocabulary we employ throughout this book.

Chapter 2, “Using the MDX Query Editor,” introduces you to the practical aspects of
constructing and executing an MDX query using the MDX Query Editor.

Chapter 3, “Understanding Tuples,” presents the concept of tuples. Understanding tuples is
key to the successful use of the MDX language.

Chapter 4, “Working with Sets,” expands the concept of tuples to include sets. With knowledge
of tuples and sets, the MDX SELECT statement is explored.

Chapter 5, “Working with Expressions,” introduces MDX expressions. Using calculated members,
you explore expressions as a means for deriving values through Analysis Services.

Part II, “MDX Functions,” builds upon the foundation established in Part I to explore the more
frequently used MDX functions.

Chapter 6, “Building Complex Sets,” guides you through the assembly of complex sets using
a variety of MDX functions. Building just the right set is critical to retrieving the data you
need from your cubes.

Chapter 7, “Performing Aggregation,” explains the appropriate use of the MDX aggregation
functions. Thoughtful application of these functions provides access to insightful metrics.

Chapter 8, “Navigating Hierarchies,” explores the positioning of members in hierarchies and
how this can be exploited using the navigation functions.

Chapter 9, “Working with Time,” introduces you to the time-based MDX functions, through
which critical business metrics can be derived.

Part III, “MDX Applications,” uses concepts and functions explored in Parts I and II to
implement three basic applications of the MDX language.

Chapter 10, “Enhancing the Cube,” explores the enhancement of the MDX script through
which calculated members and named sets can be incorporated into the definition of a cube.

Chapter 11, “Implementing Dynamic Security,” presents a few approaches to implementing
identity-driven, dynamic dimension data and cell-level security in your cube.

Chapter 12, “Building Reports,” guides you through the process of developing MDX-driven
reports in Reporting Services, Microsoft’s enterprise reporting solution.

Conventions and Features in This Book

This book uses conventions designed to make information easily accessible. Before you start,
read the following list, which explains conventions and helpful features within the book.


■ Each chapter contains multiple exercises demonstrating concepts and functionality.
Each is presented as a series of numbered steps (1, 2, and so on) which you should
follow in sequence to complete the exercise.
■ Notes labeled “Note” provide additional information or alternative methods for
completing a step successfully.
■ Notes labeled “Important” alert you to information you need to be aware of before
■ Most exercises demonstrate concepts of the MDX language through the use of an MDX
SELECT statement. As steps progress, the SELECT statement introduced in previous
steps may be altered. These changes appear in bold.

Other Features

■ Sidebars are used throughout the book to provide important information related to
an exercise or a topic. Sidebars might contain background information, supplemental
content, or design tips or alternatives. Sidebars are also used to introduce topics
supporting exercises.
■ Each chapter ends with a Quick Reference section. The Quick Reference section
contains quick reminders of how to perform the tasks you learned in the chapter.

System Requirements

You’ll need a computer with the following hardware and software to complete the exercises
in this book:

■ Microsoft Windows Vista Home Premium edition, Windows Vista Business edition,
Windows Vista Enterprise edition, or Windows Vista Ultimate edition
■ Microsoft SQL Server 2008 Developer edition or Microsoft SQL Server 2008 Evaluation
edition with Analysis Services, Database Engine Services (including Full-Text Search),
Business Intelligence Development Studio, Client Tools Connectivity, and Management
Tools installed
■ CD-ROM or DVD-ROM drive to read the companion CD
■ 150 MB free space for sample databases and companion content

In addition to these requirements, you should be able to log on directly to this computer
with administrative rights. In addition to operation-level administrative rights, you should
have full administrative rights in the SQL Server Database Engine and Analysis Services
instances. Without these rights, you will not be able to install the sample databases or
complete exercises in some chapters.


Note that once you’ve completed each lesson in this step-by-step guide, you can also hone your skills by using the practice exercises from the companion CD. Plus, you can review and download code samples illustrating the author’s own, professional techniques—direct from the companion Web site.

Happy learning!

Share this post :
  • For restoring database problem, please check your AS service account to make sure it has permision on c:\tmp

  • The book also doesn't mention the need to give file permissions to the database engine and analysis services service accounts so that they can read the sample files.

  • I am also having the problem with loading the sample relational database. please provide me the resolution.

    my mailid is

  • Regarding AS restore 'access is denied', I suggest you modify the advanced server property AllowedBrowsingFolders to include the directory where you've stored the .abf.

    Alternately, just copy the .abf to your normal AS backup folder.


  • Don't know if you did resolve it, but I just ran into the same problem. As a work around try putting the file directly under c:\<>.abf - worked for me!

  • I ran into the following error restoring the database:

    Errors in the metadata manager.

    An error occurred when loading the MdxStepByStep datasource, from the file,

    '\\?\C:\Program Files\Microsoft SQL Server\MSAS10.MTS70448\OLAP\Data\MDX Step-by-Step.0.db\Adventure Works DW.0.ds.xml'.

    The following system error occurred:  Key not valid for use in specified state. .

  • Yup, exactly the same problem.  The corrections page only tells half the story.

    The default installation folder is a total red herring.

    Copy the scripts and mdf to a new folder you create, edit the attach script and run it from there.

    Since the book is a Vista-era item and I'm running on Win7(x86), perhaps it's just a new problem which has been thrown up.

  • I am unable to install the Sample database. Just found out it is an issue for alomost everyone. Need help!

  • i got problem with mdx data base Errors related to feature availability and configuration: The 'Measure expressions' feature is not included in the '64 Bit Standard Edition' SKU.

     can give  solution

  • When executing the restore_db.xmla file's contents I get the following:

    Executing the query ...

    The following system error occurred:  Invalid procedure call or argument

    Backup and restore errors: File 'C:\Microsoft Press\MDX SBS\Setup\Analysis Services\Mdx Step-by-Step.abf' specified in Restore command is damaged or is not an AS backup file.

    Execution complete

Page 2 of 2 (25 items) 12
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post