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 is a manager of specialized services with Hitachi Consulting’sMicrosoft Database Technologies team. As a member of this team,he designs and implements business intelligence solutions for clients ina variety of industries using the products in the Microsoft SQL Serversuite. Bryan has degrees from Texas A&M and Duke Universities, holdsa number of Microsoft certifications, and has more than 10 yearsof experience developing solutions supporting data analysis. Bryanlives in the Dallas area with his (amazing) wife, Haruka, and their two(equally amazing) children, Aki and Umi.
C. Ryan Clay is a senior architect with Hitachi Consulting, specializingin business intelligence, data management, portal and collaboration,and SAP integration/interoperability solutions employing Microsofttechnologies. Ryan has implemented Microsoft Business Intelligencesolutions using Analysis Services and MDX for a variety of Fortune500 clients in the retail, construction, finance, and consumer goodsindustries. Ryan holds degrees in computer science as well as a numberof Microsoft certifications and is active in the Microsoft communitythrough speaking engagements and presentations at regional andnational 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. Manyorganizations, both large and small, have adopted it to provide secure, high-performanceaccess to complex analytics.
MDX is the language used by Analysis Services for data access. Proficiency with this languageis essential to the realization of your Analysis Services databases’ full potential. The innovativeand elegant model underlying the MDX language makes it a very powerful but at the sametime challenging tool for data analysis. In this book, we address this model head-on and thenguide you through various functions and applications of the MDX language.
This book has been written based on our own experiences as well as those of numerous clientsand students. From these, we believe there are a few prerequisites to effectively learning theMDX language.
First, you must have basic familiarity with the concepts of dimensional modeling and datawarehousing. If you do not have this knowledge, the overall purpose of Analysis Services andthe MDX language will be lost.
Second, you must have basic familiarity with Analysis Services. You do not necessarily haveto be a cube designer, but it does help to have worked with Analysis Services enough to becomfortable 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 byScott Cameron (Microsoft Press, 2009) before proceeding with this book.
Finally, you must be able put aside the traditional notions of data access you may havebecome familiar with. Some of the folks whom we’ve seen struggle the most with MDXhave been some of the most talented users of more traditional languages such as SQL. MDXrequires you to think about data very differently.
This book is about the core concepts and basic applications of MDX; it is not an exhaustivetext. Instead, it is intended as a primer for those relatively new to the language. Through thediscussions and exercises presented in each chapter you will be introduced to core conceptsand applications. This will provide you with a solid foundation for continued learning inreal-world scenarios.
This book is divided into three sections, each building on the one before it. We stronglyencourage you to read these sections in sequence to ensure that you fully grasp laterconcepts and techniques.
Part I, “MDX Fundamentals,” teaches you the fundamentals of the MDX language and theprimary query development tool you use throughout this book.
Chapter 1, “Welcome to MDX,” presents MDX as a means to deliver business value. This chapteris 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 ofconstructing and executing an MDX query using the MDX Query Editor.
Chapter 3, “Understanding Tuples,” presents the concept of tuples. Understanding tuples iskey to the successful use of the MDX language.
Chapter 4, “Working with Sets,” expands the concept of tuples to include sets. With knowledgeof 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 morefrequently used MDX functions.
Chapter 6, “Building Complex Sets,” guides you through the assembly of complex sets usinga variety of MDX functions. Building just the right set is critical to retrieving the data youneed from your cubes.
Chapter 7, “Performing Aggregation,” explains the appropriate use of the MDX aggregationfunctions. Thoughtful application of these functions provides access to insightful metrics.
Chapter 8, “Navigating Hierarchies,” explores the positioning of members in hierarchies andhow this can be exploited using the navigation functions.
Chapter 9, “Working with Time,” introduces you to the time-based MDX functions, throughwhich critical business metrics can be derived.
Part III, “MDX Applications,” uses concepts and functions explored in Parts I and II toimplement three basic applications of the MDX language.
Chapter 10, “Enhancing the Cube,��� explores the enhancement of the MDX script throughwhich calculated members and named sets can be incorporated into the definition of a cube.
Chapter 11, “Implementing Dynamic Security,” presents a few approaches to implementingidentity-driven, dynamic dimension data and cell-level security in your cube.
Chapter 12, “Building Reports,” guides you through the process of developing MDX-drivenreports in Reporting Services, Microsoft’s enterprise reporting solution.
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 shouldfollow in sequence to complete the exercise.■ Notes labeled “Note” provide additional information or alternative methods forcompleting a step successfully.■ Notes labeled “Important” alert you to information you need to be aware of beforecontinuing.■ Most exercises demonstrate concepts of the MDX language through the use of an MDXSELECT statement. As steps progress, the SELECT statement introduced in previoussteps may be altered. These changes appear in bold.
■ Sidebars are used throughout the book to provide important information related toan exercise or a topic. Sidebars might contain background information, supplementalcontent, or design tips or alternatives. Sidebars are also used to introduce topicssupporting exercises.■ Each chapter ends with a Quick Reference section. The Quick Reference sectioncontains quick reminders of how to perform the tasks you learned in the chapter.
You’ll need a computer with the following hardware and software to complete the exercisesin 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 Evaluationedition with Analysis Services, Database Engine Services (including Full-Text Search),Business Intelligence Development Studio, Client Tools Connectivity, and ManagementTools 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 computerwith administrative rights. In addition to operation-level administrative rights, you shouldhave full administrative rights in the SQL Server Database Engine and Analysis Servicesinstances. Without these rights, you will not be able to install the sample databases orcomplete 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.
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 firstname.lastname@example.org
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 email@example.com
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.