Professional Microsoft SQL server 2012 analysis services with MDX and DAX

1180 Pages·2012·105.775 MB·English

by  Sivakumar Harinath, et al

PROFESSIONAL
MICROSOFT® SQL SERVER® 2012
ANALYSIS SERVICES WITH MDX AND DAX
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxv
(cid:2) PART I INTRODUCTION
CHAPTER 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
(cid:2) PART II DESIGNING MULTIDIMENSIONAL BISM
CHAPTER 2 A First Look at Multidimensional BISM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
CHAPTER 3 Understanding MDX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
CHAPTER 4 Data Sources and Data Source Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
CHAPTER 5 Dimension Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
CHAPTER 6 Cube Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
CHAPTER 7 Administration and Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209
(cid:2) PART III ADVANCED TOPICS IN BISM
CHAPTER 8 Advanced Dimension Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269
CHAPTER 9 Advanced Cube Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
CHAPTER 10 Designing Mulitdimensional BISM for Performance . . . . . . . . . . . . . . . 419
CHAPTER 11 Optimizing Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
CHAPTER 12 Data Mining, Multidimensional BISM, and
Data Mining Add-ins for Offi ce 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513
CHAPTER 13 SQL Integration Services and SQL Server
Reporting Services with Multidimensional BISM Models . . . . . . . . . . .597
CHAPTER 14 Securing Multidimensional BISM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
(cid:2) PART IV POWERPIVOT AND TABULAR BISM
CHAPTER 15 Self-Service Business Intelligence and Introduction to
PowerPivot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 719
CHAPTER 16 A First Look at Tabular BISM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .741
CHAPTER 17 Enhancing Your Tabular BISM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773
CHAPTER 18 Introduction to DAX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813
CHAPTER 19 Advanced Topics in DAX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .867
CHAPTER 20 Analyzing Multidimensional and Tabular BISMs In Excel . . . . . . . . . . .907
CHAPTER 21 PowerPivot for SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .969
(cid:2) PART V ADVANCED TOPICS WITH TABULAR
BISM AND INTEGRATION WITH POWER VIEW
CHAPTER 22 Introduction and Confi guration of Power View . . . . . . . . . . . . . . . . . . .999
CHAPTER 23 Visual Analytics with Power View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1031
INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1077
PROFESSIONAL
Microsoft® SQL Server® 2012
Analysis Services with MDX and DAX

PROFESSIONAL
Microsoft® SQL Server® 2012
Analysis Services with MDX and DAX
Sivakumar Harinath
Ronald Pihlgren
Denny Guang-Yeu Lee
John Sirmon
Robert M. Bruckner
Professional Microsoft® SQL Server® 2012 Analysis Services with MDX and DAX
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2012 by Sivakumar Harinath, Ronald Pihlgren, Denny Guang-Yeu Lee, John Sirmon, and Robert M.
Bruckner
Published by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-118-10110-0
ISBN: 978-1-118-22343-7 (ebk)
ISBN: 978-1-118-23708-3 (ebk)
ISBN: 978-1-118-26209-2 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108
of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers,
MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the
Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax (201)
748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with
respect to the accuracy or completeness of the contents of this work and specifi cally disclaim all warranties, including
without limitation warranties of fi tness for a particular purpose. No warranty may be created or extended by sales or pro-
motional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold
with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services.
If professional assistance is required, the services of a competent professional person should be sought. Neither the pub-
lisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to
in this work as a citation and/or a potential source of further information does not mean that the author or the publisher
endorses the information the organization or Web site may provide or recommendations it may make. Further, readers
should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was
written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at [phone], outside the United States at [phone] or fax [phone].
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with stan-
dard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such
as a CD or DVD that is not included in the version you purchased, you may download this material at
http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2012933620
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are
trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other coun-
tries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft
Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated
with any product or vendor mentioned in this book.
Thanks to my wife Shreepriya and my kids Praveen
and Divya for their love and patience.
—Sivakumar Harinath
Thanks to my wife Nujsaran for her continual
support, understanding, and love. Thanks also to Siva
for the opportunity.
—Ron Pihlgren
To the patience and love from Samantha, Isabella, and
Hua-Ping.
—Denny Guang-Yeu Lee
Dedicated to my parents.
—Robert M. Bruckner