New book: Microsoft SQL Server 2008 MDX Step by Step

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 :

Comments (25)
  1. While there may be a shortage of cash, jobs, and common sense in the world right now, there’s certainly

  2. Steve says:

    You sample databae will not install. The KB suggestion to delete the log file does not work. I will return this worthless book to amazon

  3. Steve, if you write, our Support team will help resolve the issue.  And I’m sure the authors will help too.

  4. Yulia says:

    I had the same problem with not able to attach SQL DB. However, it seems to be an issue with rights. Delete log file, add yourself to bulkadmin and you will be fine. It is a great book.

    # re: New book: Microsoft SQL Server 2008 MDX Step by Step

    You sample databae will not install. The KB suggestion to delete the log file does not work. I will return this worthless book to amazon

  5. Pete says:

    Thanks for the information about adding yourself to the Bulk Admin role and deleting the installed log file.  That worked for me.

  6. Yes, thanks to Yulia.  And to you too, Pete, for verifying the approach.

  7. Charles Howard says:

    I can’t attach the supplied database. My login is a member of all the roles, including BulkAdmin. How can I tell if I have full administrative rights to the SQL Server Database Engine and the Analysis Services instances, as you note in your intro?

    Please note that the links in your intro don’t work: is busted.

    A search at fails either by title or ISBN.

    And there is no download link I can find.

  8. Charles, thanks for getting in touch and sorry for the trouble. I’m told this issue is addressed in the comments and corrections article for the book at  The relevant entry reads:

    "When installing the sample database by running ‘Microsoft PressMDX SBSSetupSQL Serverattach_db.sql’, you may receive an error. To avoid the error, delete the ‘Microsoft PressMDX SBSSetupSQL ServerMdxStepByStep_log.LDF’ file and run the attach_db.sql script again."

  9. Brad says:

    I can attach the SQL database ok but cannot install the Analysis services database. I get the following error.

    Executing the query …

    The following system error occurred:  Access is denied. .

    Backup and restore errors: File ‘C:Microsoft PressMDX SBSSetupAnalysis ServicesMDX Step-by-Step.abf’ specified in Restore command is damaged or is not an AS backup file.

    Execution complete

  10. Brad, please write mspinput [at] about this issue and our Support team will help you.

  11. Craig says:

    No point contacting mspinput … I did and here is the reply that I got …

    I have been unable to reproduce the problem you are running into.  I have verified that, with the exception of the .ldf file issue mentione in the Knowledge Base article, the sample files function correctly and will install fine.  Unfortunately, SQL Server configuration or permissions issues are outside the scope of our support, so I can only refer you to the SQL Server support page at

    Totally useless waste of time.

  12. Sven Bossenmaier says:

    You can import the files when you copy all of the following files into a temporary directory (C:tmp for exampe)

    – MDX Step-by-Step (folder)

    – drop_db.xmla

    – MDX Step-by-Step.abf

    – MDX Step-by-Step.sln

    – restore_db.xmla

    Then you have to alter the command in the xmla file to:

    <Restore xmlns=""&gt;

     <File>C:tmpMdx Step-by-Step.abf</File>

     <DatabaseName>MDX Step-by-Step</DatabaseName>



    This is how i could solve it 🙂

    Hope it works for you, too.

  13. Vancouver Jerry says:

    I tried all of the suggestions above and got nothing but errs. I even tried installing on different machines and got the same errs. Interestingly I could not find the Bulk Admin group to add my account to.

  14. Don says:

    Trying to Process MDX Step By Step solution, I get the following error:

    Errors and Warnings from Response

    Parser: The query contains the Associated_Measure_Group parameter, which is not declared.

    Any help getting around this would be greatly appreciated.


  15. Test says:

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

  16. Megan says:

    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.

  17. ETL vs ELTL says:

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

    my mailid is

  18. Donna Kelly says:

    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.


  19. Nikhil Agrawal says:

    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!

  20. Greg says:

    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 FilesMicrosoft SQL ServerMSAS10.MTS70448OLAPDataMDX Step-by-Step.0.dbAdventure Works DW.0.ds.xml'.

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

  21. Jay says:

    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.

  22. Shawn says:

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

  23. laxman says:

    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

  24. Page xx, Restore the Analysis Services database says:

    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 PressMDX SBSSetupAnalysis ServicesMdx Step-by-Step.abf' specified in Restore command is damaged or is not an AS backup file.

    Execution complete

Comments are closed.

Skip to main content