SQL Server Analysis Services 2012 Cube Development Cookbook is a friendly companion to individuals implementing and managing business intelligence applications using Microsoft's flagship product, SQL Server Analysis Services. The book provides step-by-step recipes for developing Analysis Services objects. Readers will learn how to develop business intelligence solutions using the Analysis Services multidimensional model as well as the Tabular Model. In addition to development recipes, the book also includes recipes for administering, securing, monitoring, troubleshooting, and scaling Analysis Services solutions. The book discusses methods beyond the initial cube design, exploring cube maintenance with partitions and designing effective aggregations, as well as options for synchronizing analytics solutions. Filled with tips and recommended best practices based on years of experience, the book is designed to quickly get the reader from beginner level to the point of comfortably architecting solutions.
Chapter 1, Introduction to Multidimensional Data Model Design, discusses the value of business intelligence as well as challenges faced when undertaking a business intelligence project and how to overcome them. You will learn high-level differences between Analysis Services tabular and multidimensional models and when each is appropriate. Finally, you will learn the differences between star and snowflake schemas for dimensional modeling.
Chapter 2, Defining Analysis Services Dimensions, starts off by explaining how to define data sources and data source views. You will learn how to extend data source views beyond the model available in the relational database. The chapter's main focus is on teaching you how to build and customize most commonly encountered dimensions within the Analysis Services multidimensional model.
Chapter 3, Creating Analysis Services Cubes, explains how to define measure groups and measures while setting appropriate properties. You will learn how to relate database dimensions to each measure group and implement basic as well as advanced measure aggregation functions, including distinct count and semi-additive measures. The chapter also discusses cube file structures and teaches the most effective strategies for measure group partitioning and building aggregations. Finally, you will learn various methods of deploying your multidimensional projects to your Analysis Services instances.
Chapter 4, Extending and Customizing Cubes, builds on the foundation of cube development laid in Chapter 3, Creating Analysis Services Cubes. You will learn how to make your solutions more useful to the business by adding calculated measures, named sets, actions, key performance indicators, perspectives, translations, and measure expressions to your cubes.
Chapter 5, Optimizing Dimension and Cube Processing, teaches basic and advanced options for processing multidimensional objects through XMLA, SQL Server client tools, as well as using SQL Server Integration Services (SSIS). This chapter also demonstrates how you should monitor and tune processing performance.
Chapter 6, MDX, explains how to write the most commonly encountered Multidimensional Expressions (MDX) queries and calculations. You will learn how to return data on query axes, limit and sort query output, define calculations and named sets, and navigate dimension hierarchies. The chapter also explains the basic functions used within MDX scripts and introduces the framework for monitoring and tuning MDX queries.
Chapter 7, Analysis Services Security, teaches how to manage multidimensional model security at the Analysis Services instance, database, cube, dimension, and cell levels. In addition to basic role-based security, the chapter also includes a recipe for implementing dynamic security.
Chapter 8, Administering and Monitoring Analysis Services, starts off by providing an overview of Analysis Services configuration options. Next, you learn how to create and drop databases, monitor and troubleshoot Analysis Services instances, and check the size of each object. Additionally, you will learn various methods for scaling out your multidimensional business intelligence solutions using backup and restore, synchronization, and the detach and attach options.
Chapter 9, Using Tabular Models, teaches you how to define data sources and models and load data into Tabular Models. You will learn how to define hierarchies, define calculated measures, and extend the Tabular model using key performance indicators. The chapter also explains recommended strategies for processing, partitioning, and securing Tabular Models.
Chapter 10, DAX Calculations and Queries, introduces the reader to Data Analysis Expressions (DAX) fundamentals as it applies to defining calculations within the Tabular Model and querying the model. You will learn how to write calculated columns and measures in the Tabular Model designer and DAX queries within SQL Server Management Studio.
Chapter 11, Performance Tuning and Troubleshooting Tabular Models, discusses the Analysis Services Tabular Model's usability limits. The reader will learn about diagnosing issues, optimizing performance, and memory use using various tools for troubleshooting suboptimal performance, which include Windows Resource Monitor and SQL Server Profiler.
Appendix, Miscellaneous Analysis Services Topics, discusses various Analysis Services topics that don't lend themselves to effective presentation in a recipe format. The chapter teaches you about various dimension properties, as well as outlining considerations for multidimensional design when developers are not permitted to create objects in the relational data sources.
You will need Microsoft SQL Server 2012 Analysis Services multidimensional as well as tabular instances to which you have administrative access in order to follow along with the examples presented in this book. All examples are based on the Adventure Works sample relational database and Adventure Works Analysis Services database, which can be downloaded from www.codeplex.com. Each chapter referencing these samples includes a link to sample downloads.
The book is for individuals intending to build, maintain, use, and administer business intelligence solutions exploiting Microsoft SQL Server Analysis Services multidimensional as well as tabular databases. The intended audience includes seasoned professionals who have worked with databases and are embarking on more complex business intelligence solutions as well as for people who have built Analysis Services solutions in the past but would like to hone their skills further by administering, tuning, and scaling databases.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: " Temporary files can be found under the <TempDir> folder as specified in the msmdsrv.ini configuration file."
A block of code is set as follows:
<ErrorConfiguration
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyErrorLogFile>C:\key_errors.txt</KeyErrorLogFile>
<KeyNotFound>ReportAndStop</KeyNotFound>
<KeyDuplicate>ReportAndContinue</KeyDuplicate><NullKeyConvertedToUnknown>ReportAndContinue</NullKeyConvertedToUnknown>
<NullKeyNotAllowed>ReportAndStop</NullKeyNotAllowed>
</ErrorConfiguration>New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: " Now if you query the cube as a role member and choose All Products as the product category, you will see a different number than what you would see as the total of Bikes and Accessories."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.