BI - Read me

Business Intelligence Architecture and Design Guide



Summary: Welcome to Microsoft Patterns & Practices Business intelligence architecture and design guide. This guide gives you prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform.

Microsoft Confidential. © 2006 Microsoft Corporation. All rights reserved. These materials are confidential to and maintained as a trade secret by Microsoft Corporation. Information in these materials is restricted to Microsoft authorized recipients only. Any use, distribution or public discussion of, and any feedback to, these materials is subject to the terms of the attached license. By providing any feedback on these materials to Microsoft, you agree to the terms of that license.

Microsoft Corporation Technical Documentation License Agreement (Standard)
READ THIS! THIS IS A LEGAL AGREEMENT BETWEEN MICROSOFT CORPORATION ("MICROSOFT") AND THE RECIPIENT OF THESE MATERIALS, WHETHER AN INDIVIDUAL OR AN ENTITY ("YOU"). IF YOU HAVE ACCESSED THIS AGREEMENT IN THE PROCESS OF DOWNLOADING MATERIALS ("MATERIALS") FROM A MICROSOFT WEB SITE, BY CLICKING "I ACCEPT", DOWNLOADING, USING OR PROVIDING FEEDBACK ON THE MATERIALS, YOU AGREE TO THESE TERMS. IF THIS AGREEMENT IS ATTACHED TO MATERIALS, BY ACCESSING, USING OR PROVIDING FEEDBACK ON THE ATTACHED MATERIALS, YOU AGREE TO THESE TERMS.

1. For good and valuable consideration, the receipt and sufficiency of which are acknowledged, You and Microsoft agree as follows:

(a) If You are an authorized representative of the corporation or other entity designated below ("Company"), and such Company has executed a Microsoft Corporation Non-Disclosure Agreement that is not limited to a specific subject matter or event ("Microsoft NDA"), You represent that You have authority to act on behalf of Company and agree that the Confidential Information, as defined in the Microsoft NDA, is subject to the terms and conditions of the Microsoft NDA and that Company will treat the Confidential Information accordingly;

(b) If You are an individual, and have executed a Microsoft NDA, You agree that the Confidential Information, as defined in the Microsoft NDA, is subject to the terms and conditions of the Microsoft NDA and that You will treat the Confidential Information accordingly; or

(c)If a Microsoft NDA has not been executed, You (if You are an individual), or Company (if You are an authorized representative of Company), as applicable, agrees: (a) to refrain from disclosing or distributing the Confidential Information to any third party for five (5) years from the date of disclosure of the Confidential Information by Microsoft to Company/You; (b) to refrain from reproducing or summarizing the Confidential Information; and (c) to take reasonable security precautions, at least as great as the precautions it takes to protect its own confidential information, but no less than reasonable care, to keep confidential the Confidential Information. You/Company, however, may disclose Confidential Information in accordance with a judicial or other governmental order, provided You/Company either (i) gives Microsoft reasonable notice prior to such disclosure and to allow Microsoft a reasonable opportunity to seek a protective order or equivalent, or (ii) obtains written assurance from the applicable judicial or governmental entity that it will afford the Confidential Information the highest level of protection afforded under applicable law or regulation. Confidential Information shall not include any information, however designated, that: (i) is or subsequently becomes publicly available without Your/Company’s breach of any obligation owed to Microsoft; (ii) became known to You/Company prior to Microsoft’s disclosure of such information to You/Company pursuant to the terms of this Agreement; (iii) became known to You/Company from a source other than Microsoft other than by the breach of an obligation of confidentiality owed to Microsoft; or (iv) is independently developed by You/Company. For purposes of this paragraph, "Confidential Information" means nonpublic information that Microsoft designates as being confidential or which, under the circumstances surrounding disclosure ought to be treated as confidential by Recipient. "Confidential Information" includes, without limitation, information in tangible or intangible form relating to and/or including released or unreleased Microsoft software or hardware products, the marketing or promotion of any Microsoft product, Microsoft's business policies or practices, and information received from others that Microsoft is obligated to treat as confidential.

2. You may review these Materials only (a) as a reference to assist You in planning and designing Your product, service or technology ("Product") to interface with a Microsoft Product as described in these Materials; and (b) to provide feedback on these Materials to Microsoft. All other rights are retained by Microsoft; this agreement does not give You rights under any Microsoft patents. You may not (i) duplicate any part of these Materials, (ii) remove this agreement or any notices from these Materials, or (iii) give any part of these Materials, or assign or otherwise provide Your rights under this agreement, to anyone else.

3. These Materials may contain preliminary information or inaccuracies, and may not correctly represent any associated Microsoft Product as commercially released. All Materials are provided entirely "AS IS." To the extent permitted by law, MICROSOFT MAKES NO WARRANTY OF ANY KIND, DISCLAIMS ALL EXPRESS, IMPLIED AND STATUTORY WARRANTIES, AND ASSUMES NO LIABILITY TO YOU FOR ANY DAMAGES OF ANY TYPE IN CONNECTION WITH THESE MATERIALS OR ANY INTELLECTUAL PROPERTY IN THEM.

4. If You are an entity and (a) merge into another entity or (b) a controlling ownership interest in You changes, Your right to use these Materials automatically terminates and You must destroy them.

5. You have no obligation to give Microsoft any suggestions, comments or other feedback ("Feedback") relating to these Materials. However, any Feedback you voluntarily provide may be used in Microsoft Products and related specifications or other documentation (collectively, "Microsoft Offerings") which in turn may be relied upon by other third parties to develop their own Products. Accordingly, if You do give Microsoft Feedback on any version of these Materials or the Microsoft Offerings to which they apply, You agree: (a) Microsoft may freely use, reproduce, license, distribute, and otherwise commercialize Your Feedback in any Microsoft Offering; (b) You also grant third parties, without charge, only those patent rights necessary to enable other Products to use or interface with any specific parts of a Microsoft Product that incorporate Your Feedback; and (c) You will not give Microsoft any Feedback (i) that You have reason to believe is subject to any patent, copyright or other intellectual property claim or right of any third party; or (ii) subject to license terms which seek to require any Microsoft Offering incorporating or derived from such Feedback, or other Microsoft intellectual property, to be licensed to or otherwise shared with any third party.

6. Microsoft has no obligation to maintain confidentiality of any Microsoft Offering, but otherwise the confidentiality of Your Feedback, including Your identity as the source of such Feedback, is governed by Your NDA.

7. This agreement is governed by the laws of the State of Washington. Any dispute involving it must be brought in the federal or state superior courts located in King County, Washington, and You waive any defenses allowing the dispute to be litigated elsewhere. If there is litigation, the losing party must pay the other party’s reasonable attorneys’ fees, costs and other expenses. If any part of this agreement is unenforceable, it will be considered modified to the extent necessary to make it enforceable, and the remainder shall continue in effect. This agreement is the entire agreement between You and Microsoft concerning these Materials; it may be changed only by a written document signed by both You and Microsoft.









Who Should Read This Guide
This guide is intended for software architects and developers who are developing Business Intelligence applications on the Microsoft .NET Framework using SQL Server 2005 - Integration Services, Analysis Services, Reporting Services and Microsoft Office.
What You Must Know
To most effectively use this guidance, you should already have experience using SQL Server 2005, .NET development techniques and technologies. You should be familiar with general distributed application architecture challenges, and, if you have already implemented business intelligence solutions, you should know your own application architecture and deployment pattern.

Where we are
We are in the early stage of Business intelligence architecture guide. This guide address architectural and design aspects of Business intelligence. We anticipate gaps in this guide as we are doing release at the very early stage. This guide is evolving guide and we will incorporate the valuable feedback from the readers.

Out of Scope
We will not be providing any business solutions nor discuss any domain specific business problems. Concepts discussed in this guide are well suited for Microsoft BI solution framework. This guide will not dwell deep into all the Microsoft product feature sets rather focus on core functionalities.
What Is in This Guide
This guide consists of the following chapters, each of which deals with a specific issue relevant to business intelligence. Each chapter is designed to be read, in whole or in part, according to your needs.

In Release 4.0 (Final) we will be covering the following chapters.
• BI Framework
• Data Extraction
• Data Staging
• Data Transformations and Data Quality
• Data Loading
• Dimensional Modeling
• Data Partitions
• Online Analytical Processing (OLAP)
• Data Mining

• Introduction. This chapter examines the challenges you might face when designing and building business intelligence applications. It then discusses some of the high-level architectural challenges, solutions and provides guidance to help you determine and build an architecture right for your application. This guide will introduce you a BI frame to help you organize and prioritize challenges. Use the design guidelines in this chapter to learn practices, principles, patterns, and anti-patterns that will help you to make informed choices.
• Data Extraction. This chapter examines the various extraction models and techniques. It also covers the various options available for doing extraction in Microsoft Product Stack. Address the most frequent challenges faced during extraction like delta detection, high volume etc.
• Data Staging. This chapter examines the thoughts behind stage or not to stage and types of staging
• Data Transformations and Data Quality: This chapter examines the various data quality problems and proposes the solutions. Common transformations techniques and guidelines are detailed in this chapter.
• Data Loading. This chapter examines the challenges faced in loading and provides options to address it. This chapter provides loading guidelines.
• Dimensional Modeling: This chapter details the dimensional modeling techniques on design guidelines for facts, dimensions. It explains the common guidelines and solutions for the general design problems.
• Data Partitions: This chapter details the techniques that can be adopted for designing the partitions. The basic advantage of partitions is to reduce the loading time and so the availability is increased, and boosting the query performance
• Online Analytical Processing (OLAP): This chapter details the concept and implementation details of OLAP. It has the guidelines that can be considered for various custom measures, storage structures, aggregations and querying the multi dimensional data.
• Data Mining: This chapter details the basic concept of Data Mining, need of data mining, techniques, guidelines and customer scenarios for choosing the right data mining model

Collaborators and Reviewers
• Special thanks to our reviewers:
• Special thanks to our external reviewer:
• Thanks to our editors and release team:
• Thanks to our test team:
• Thanks to our product management:
Feedback on the Guide
We have made every effort to ensure the accuracy of this guide. We are particularly interested in feedback regarding the following:
• Technical issues specific to recommendations
• Usefulness and usability issues
• Writing and editing issues

Email your valuable feedback to biadg@microsoft.com. We sincerely appreciate your valuable feedback.