intTypePromotion=1

MasteringSQL Server 2005 Reporting Services Infrastructure Design

Chia sẻ: Phung Tuyet | Ngày: | Loại File: PDF | Số trang:507

0
71
lượt xem
7
download

MasteringSQL Server 2005 Reporting Services Infrastructure Design

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Reporting Services is a powerful tool for SQL Server 2005 database administrators, developers, and other IT professionals, enabling them to make sense of the immense amount of data generated by enterprises of all sizesShows readers how to create, manage, and distribute information as well as how to design, produce, and distribute reports that meet the needs of the stakeholder

Chủ đề:
Lưu

Nội dung Text: MasteringSQL Server 2005 Reporting Services Infrastructure Design

  1. Mastering TM SQL Server 2005 Reporting Services Infrastructure Design Joseph Jorden Wiley Publishing, Inc.
  2. Mastering TM SQL Server 2005 Reporting Services Infrastructure Design
  3. Mastering TM SQL Server 2005 Reporting Services Infrastructure Design Joseph Jorden Wiley Publishing, Inc.
  4. Acquisitions Editor: Tom Cirtin Development Editor: Dick Margulis Technical Editor: Evangelos Petroutsos Production Editors: Sarah Groff-Palermo and Michael Gordon Copy Editor: Liz Welch Production Manager: Tim Tate Vice President and Executive Group Publisher: Richard Swadley Vice President and Executive Publisher: Joseph B. Wikert Vice President and Publisher: Neil Edde Book Designers: Judy Fung and Bill Gibson Proofreader: Ian Golder Indexer: Ted Laux Anniversary Logo Design: Richard Pacifico Cover Designer: Ryan Sneed Cover Image:  Pete Gardner/Digital Vision/Getty Images Copyright  2007 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-470-11459-9 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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional 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 publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website 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 Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites 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 or to obtain technical support, please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993, or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. Library of Congress Cataloging-in-Publication Data is available from the publisher. TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book. 10 9 8 7 6 5 4 3 2 1
  5. DearReader Thank you for choosing Mastering SQL Server 2005 Reporting Services Infrastructure Design. This book is part of a family of premium quality Sybex books, all written by outstanding authors who combine practical experience with a gift for teaching. Sybex was founded in 1976. More than thirty years later, we’re still committed to producing consistently exceptional books. With each of our titles we’re working hard to set a new standard for the industry. From the paper we print on, to the authors we work with, our goal is to bring you the best books available. I hope you see all that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an e-mail at nedde.com, or if you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex. Best regards, Neil Edde Vice President and Publisher Sybex, an Imprint of Wiley
  6. With love to my wife, Rachelle
  7. Acknowledgments Over the past decade or so, I’ve worked on a number of books and articles. None of them has been easy, but they have all been rewarding. This book is no exception; it has taken a lot of work from a lot of people who deserve a lot of praise. First, I want to thank Tom Cirtin at Sybex, for introduc- ing me to this project to begin with, and Dick Margulis, who kept everything running smoothly after the project got under way. Special thanks to Evangelos Petroutsos, who did double-duty as the technical editor and the contributor of three chapters in the book. I would also like to thank the production editors, Sarah Groff-Palermo and Michael Gordon, copy editor Liz Welch, and proofreader Ian Golder. Writing books takes time from personal as well as work-related activities, and I have had a number of friends help me through this book. I have to start by thanking my mother, Mary Jorden, along with the rest of my immediate family: Buddy and Shelly Jorden, and Janet, Colin, and Leian McBroom. Thanks to all of you. Also, thanks to some special people who have unwittingly taken on the task of keeping me sane through all of this (well, as close as I can come, anyway). Thanks to Jyles McDonald for the early mornings. To Dave Moody back at Jelly Belly; IOU, LE. Tristan and Amanda Williams, thanks for making me feel good about myself at pool. Most important, special thanks to my wife, Rachelle Jorden, who once again sacrificed a lot of time so that I could write another book. And finally, thanks to all of you out there for reading this book; may it serve you well.
  8. Contents at a Glance Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Designing the Reporting Infrastructure . . . . . . . . . . . . . . . . . . . . . . 1 Part 1 • • Chapter 1 Principles of a Reporting Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . 3 • Chapter 2 Determining Business Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 • Chapter 3 Planning and Developing Role-Based Security . . . . . . . . . . . . . . . . . . . . 35 • Chapter 4 Managing User Expectations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 • Chapter 5 Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 • Chapter 6 Business Intelligence and Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Using Reporting Services in Your Infrastructure . . . . . . . . . . . . . . 113 Part 2 • • Chapter 7 Deploying Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 • Chapter 8 Ad Hoc Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 • Chapter 9 Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 • Chapter 10 Maintenance and Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Advanced Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 Part 3 • • Chapter 11 Managing Reports Using Program Code . . . . . . . . . . . . . . . . . . . . . . . 217 • Chapter 12 Extending Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 • Chapter 13 Report Definition Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 • Chapter 14 Report Server Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 • Chapter 15 Data Sources and Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 • Chapter 16 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
  9. Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Designing the Reporting Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 • Part 1 Principles of a Reporting Infrastructure . . . . . . . . . . . . . . . . . . . 3 • Chapter 1 Reporting Infrastructure Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 What Is a Reporting Infrastructure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Do You Need a Reporting Infrastructure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Why Use Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 What Reporting Services Can Do for You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Inside Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Licensing Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Determining Business Requirements . . . . . . . . . . . . . . . . . . . . 15 • Chapter 2 Determining Current Business Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Understanding the Current Business Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Analyzing Business Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Analyzing the Company Model and Geographic Scope . . . . . . . . . . . . . . . . . . . . . 20 Analyzing Organizational Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Identifying New Business Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Gathering Accurate User Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Identifying Key Project Stakeholders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Determining Business Priorities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Determining Risk Tolerance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Creating a Scope Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Identifying Internal and External Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Creating Data-Flow Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Assessing Training Needs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Determining Regulatory Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Defining Report Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Planning for the Future . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Accurate Prognostication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Planning and Developing Role-Based Security . . . . . . . . . . . . . . 35 • Chapter 3 Planning Role-Based Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Gathering Security Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Setting Security Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
  10. CONTENTS xii Analyzing Your Security Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Dealing with Conflicting Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Analyzing the Costs, Benefits, and Risks of Requirements . . . . . . . . . . . . . . . . . . . 39 Implementing Role-Based Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Understanding Securable Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Understanding Tasks and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Understanding Role Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Creating Role Assignments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Exploring Minimum Access Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Managing User Expectations . . . . . . . . . . . . . . . . . . . . . . . . . . 61 • Chapter 4 Understanding Your Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Communicating with Your Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Communicate Early and Often . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Be Honest and Forthright . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Educate Your Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Prototyping and Storyboarding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Training Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Listen to Your Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Using Service-Level Agreements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Preparing the SLA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Writing Documentation for Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 • Chapter 5 Understanding Change-Management Theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Unfreezing/Changing/Refreezing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 ADKAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Putting Change Management into Practice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Understanding Management’s Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Necessary Skills for You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Change-Management Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Managing Ongoing Change . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Business Intelligence and Reporting . . . . . . . . . . . . . . . . . . . . 85 • Chapter 6 Business Intelligence in SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Online Analytical Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Data Mining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Installing and Configuring Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Installing Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Creating and Configuring a Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Creating a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Creating a Report Based on a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
  11. CONTENTS xiii Creating a Report Based on a Data-Mining Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Creating a Data-Mining Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Creating a Report Based on a Data-Mining Model . . . . . . . . . . . . . . . . . . . . . . . . . 106 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Using Reporting Services in Your Infrastructure . . . . . . . . . . . . . . . . . . . . 113 • Part 2 Deploying Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . 115 • Chapter 7 Planning a Reporting Services Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Standard Deployments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Scaled-Out Deployments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Playing Nice with SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Automating Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Automating the Installation and Configuration Steps . . . . . . . . . . . . . . . . . . . . . . 129 Postdeployment Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Custom Authorization Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Advanced E-mail Delivery Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Internet Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 A Deployment Checklist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Ad Hoc Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 • Chapter 8 Preparing the Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Preparing the Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Preparing the Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Working with Report Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Using Model Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Using Report Manager and Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Using Report Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Creating and Designing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 • Chapter 9 Understanding Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 Configuring the Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Configuring a File Share . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Configuring E-mail Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Configuring the Default Delivery Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Creating Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Data Driven . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 SharePoint Integrated . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Managing Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Preloading the Report Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
  12. CONTENTS xiv Maintenance and Administration . . . . . . . . . . . . . . . . . . . . 185 • Chapter 10 Managing the Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Administering the Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Performing Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Managing Encryption Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Managing Processing and Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 Report Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 My Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Managing Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Managing Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Managing a Running Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 Working with Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 Tips for Common Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Advanced Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 • Part 3 Managing Reports Using Program Code . . . . . . . . . . . . . . . . 217 • Chapter 11 Accessing Reports from within Your Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Accessing Reports with a URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Viewing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Using the rs:Command Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220 The rs Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 Using the rc Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Passing Report Parameters in a URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 The Report Server Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Accessing the Report Server from within .NET Applications . . . . . . . . . . . . . . . . 226 Selected Management Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231 Selected Execution Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 Specifying Device Information Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Extending Reporting Services . . . . . . . . . . . . . . . . . . . . . . . 245 • Chapter 12 Understanding Extension Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Building a Security Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 The IAuthenticationExtension Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 The IAuthorizationExtension Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 Deploying a Custom Security Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 Building a Data Processing Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258 The IDbCommand Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 The IDbConnection Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 The IDataReader Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 Deploying a Data Processing Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 Deploying a Data Processing Extension to Report Designer . . . . . . . . . . . . . . . . . 264 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
  13. CONTENTS xv Report Definition Language . . . . . . . . . . . . . . . . . . . . . . . . . 267 • Chapter 13 What’s in an RDL File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 The Structure of an RDL File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 Creating a Report Definition File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272 Deploying the New Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 Specifying Report Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 Creating Reports in Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Embedding Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 Exploring the RDL Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 The Report Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 The EmbeddedImage Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 The ReportParameters Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 The ReportParameter Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 The ParameterValue Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 The DataSet Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 The Fields Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 The Field Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The Query Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The QueryParameters Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The QueryParameter Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The DataSources Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The DataSource Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 The Body Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292 The PageHeader Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292 The PageFooter Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292 The Table Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 The TableColumns Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 The TableColumn Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 The Header Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 The Footer Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 The TableGroups Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The TableGroup Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The Details Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The TableRows Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The TableRow Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The TableCells Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 The TableCell Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 The Textbox Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 The Image Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 The Style Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 Report Server Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 • Chapter 14 Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 Types of Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
  14. CONTENTS xvi Configuring Report Server for SSL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301 Changing Service Accounts and Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Securing Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307 Securing Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308 Securing My Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Preventing SQL Injection Attacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Data Sources and Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . 315 • Chapter 15 Defining Report Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Creating a Shared Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317 Using Data Source Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 Defining Report Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 SQL Server Relational Database Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 Analysis Services Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 SQL Server Integration Services Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327 XML Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 Oracle Relational Database Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 SAP Relational Database and NetWeaver BI System Data Sets . . . . . . . . . . . . . . . 330 Hyperion Essbase Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 Report Model Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 • Chapter 16 Using the Performance Console . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 Modifying Configuration Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 ReportingServicesService . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 RSReportDesigner . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 RSWebApplication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 RSReportServer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348 Tips and Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 Setting a Measurement Baseline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 Data Archiving and Trend Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 Manually Configuring Available Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 The Bottom Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 • Appendix A SharePoint Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . 391 • Appendix B Report Server Web Service Reference . . . . . . . . . . . . . . . . . 417 • Appendix C Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
  15. Introduction Reporting Services was introduced with SQL Server 2000, and it quickly gained popularity because it was easy to work with and it came free with SQL Server 2000. A lot has changed since its initial release over seven years ago. Several new features make Reporting Services easier to use and more functional: Reporting Services now allows users to create their own reports using the Report Builder application. Developers can create reports, report models, and other SQL Server–related projects using the new Business Intelligence Development Studio, which is based on the familiar Visual Studio interface. There are several new tools for administrators to use in configuring the report server, such as SQL Server Reporting Services Configuration Tool, the SQL Server Surface Area Configuration Tool, and SQL Server Management Studio. With all of the changes, a lot has stayed the same. For instance, Report Manager is still the pri- mary interface for end users and administrators alike, and it looks almost the same in this version. The Report Server is still separated into two separate services: the Windows service and the Web service. The Windows service still provides encryption and scheduling services, and the Web service still handles ad hoc requests and serves up the API to developers. And here’s the really good news: everything you’ve written in the previous version of Reporting Services will still work in the new version. All of your reports will still render, your subscriptions will still run, your custom extensions will still function — everything will still work after the upgrade. There is a lot more to Reporting Services than just this basic list, though, and you will be exposed to the various functions and procedures in this book. Whether you’ve worked with Reporting Services previously or this is your first time, you are going to be impressed with what this new version can do. How this Book is Organized We’ve designed this book to be a reference for the user new to Reporting Services or the experienced user who might want to see what’s new in this version. The emphasis is not just on getting up and running, but on making sure that you do it right the first time. We don’t try to cover every nook and cranny of the system, because that would take a much larger book. We have, instead, provided you with the essential information you will need to get oriented with the product and get it running in your environment.

CÓ THỂ BẠN MUỐN DOWNLOAD

 

Đồng bộ tài khoản