Module 1: Introduction To Business Intelligence Architecture

Module 1: Introduction to Business Intelligence Architecture Presentation: 75 minutes

Table of Contents Capture the business and technical requirements for a business intelligence solution architecture. ........................................................................................................................................................ 4 Introduction................................................................................................................................. 4 Lesson 1: Overview of Business Intelligence ......................................................................................... 5 Describe the purpose and nature of a BI solution. .......................................................................... 5 Discussion: Considerations for Defining Business Intelligence ......................................................... 6 Concept: Define Business Intelligence and Business Intelligence solutions. ................................. 6 Introduction................................................................................................................................. 6 Discussion questions ................................................................................................................... 7 Business Intelligence Solution Users .................................................................................................. 8 Fact: Identify the users of a BI solution. ......................................................................................... 8 Introduction................................................................................................................................. 8 Business users ............................................................................................................................. 9 Applications ................................................................................................................................ 9 Overview of Common Business Intelligence Terms......................................................................... 10 Fact: Identify key BI terminology................................................................................................. 10 Introduction............................................................................................................................... 10 ETL ........................................................................................................................................... 11 Data warehouse ......................................................................................................................... 11 Data mart................................................................................................................................... 11 OLAP ........................................................................................................................................ 11 Data mining............................................................................................................................... 11 Dashboards................................................................................................................................ 12 Scorecards ................................................................................................................................. 12 Reporting................................................................................................................................... 12 Traditional Business Intelligence Solutions...................................................................................... 13 Fact: Describe the traditional approach to BI................................................................................ 13 Introduction............................................................................................................................... 13

Module 1: Introduction to Business Intelligence Architecture

2

Facilities and capabilities .......................................................................................................... 13 Contemporary Business Intelligence Solutions................................................................................. 17 Concept: Describe the contemporary approach to BI. .................................................................. 17 Introduction............................................................................................................................... 17 Additional facilities and capabilities ......................................................................................... 17 Common Business Intelligence Philosophies .................................................................................. 19 Fact: Describe common BI philosophies. ..................................................................................... 19 Introduction............................................................................................................................... 19 Top-down design....................................................................................................................... 20 Bottom-up design...................................................................................................................... 20 Microsoft Business Intelligence Vision ............................................................................................ 21 Fact: Describe Microsoft’s BI vision. ........................................................................................... 21 Introduction............................................................................................................................... 21 Increased access to BI ............................................................................................................... 21 Unified Dimensional Model...................................................................................................... 22 Open server architecture ........................................................................................................... 22 Lesson 2: Overview of Business Intelligence Architecture .................................................................. 23 Identify the main components of a comprehensive BI architecture. ............................................. 23 Introduction............................................................................................................................... 23 Components and Processes in a Business Intelligence Architecture................................................. 24 Concept: Describe the components and processes in a BI architecture. ....................................... 24 Introduction............................................................................................................................... 24 Back end.................................................................................................................................... 25 Front end ................................................................................................................................... 25 Front-End Architecture ..................................................................................................................... 28 Fact: Describe the front-end components and processes of a BI solution architecture. ................ 28 Introduction............................................................................................................................... 28 Client applications and interfaces ............................................................................................. 29 Back-End Architecture...................................................................................................................... 30 Fact: Describe the back-end components and processes of a BI solution architecture. ................ 30 Microsoft Technologies for Business Intelligence............................................................................ 32 Fact: Describe Microsoft technologies for a BI solution. ............................................................. 32

Module 1: Introduction to Business Intelligence Architecture

3

Introduction............................................................................................................................... 32 Microsoft SQL Server 2005 ..................................................................................................... 32 Microsoft Office Excel and Office Web Components .............................................................. 33 Microsoft Office SharePoint Server.......................................................................................... 33 Microsoft Office Business Scorecard Manager ........................................................................ 33 Other Microsoft Technologies .......................................................................................................... 34 Fact: Describe the other Microsoft tools and technologies that you can use in a BI solution....... 34 Introduction............................................................................................................................... 34 Internet Security and Acceleration Server 2004........................................................................ 35 Microsoft Operations Manager 2005 ........................................................................................ 35 BizTalk Server 2006 ................................................................................................................. 35 Host Integration Server 2004 .................................................................................................... 35 Lab: Identifying Business Intelligence Solution Requirements ............................................................ 36 Time estimated: 60 minutes ...................................................................................................... 36 Introduction............................................................................................................................... 36 Preparation ................................................................................................................................ 37 Exercise 1: Determining Business Requirements ............................................................................. 38 Introduction............................................................................................................................... 38 Scenario..................................................................................................................................... 38 Determining business requirements .......................................................................................... 38 Questions................................................................................................................................... 39 Exercise 2: Designing a High Level Architecture ............................................................................ 41 Introduction............................................................................................................................... 41 Designing a high level architecture........................................................................................... 41 Questions................................................................................................................................... 41 Turning off the virtual machine ................................................................................................ 41

Module 1: Introduction to Business Intelligence Architecture

4

Module objective After completing this module, you will be able to: Capture the business and technical requirements for a business intelligence solution architecture. Instructor note This module contains references to external sources of further information. You can find links to all referenced resources on the student CD. Because these external sites are beyond Microsoft control, you should test all links prior to teaching this module.

Introduction When designing a business intelligence (BI) solution, the first step is to understand the business requirements and goals, the user requirements, and the technical requirements. You can then define and agree the overall scope and architecture of the proposed BI solution before going on to develop the detailed design for each area of the solution. In this module, you will learn about the capabilities that a modern BI solution can offer. You will then learn about the components of the back end and front end of a BI solution, so that you can design and develop appropriate BI solutions for organizations.

Module 1: Introduction to Business Intelligence Architecture

5

Lesson 1: Overview of Business Intelligence

Lesson objective After completing this lesson, you will be able to: Describe the purpose and nature of a BI solution. Introduction Increasingly, organizations recognize that BI solutions are essential if they are to remain competitive. In the past, BI solutions primarily provided reports for managers and analysis facilities for a small group of well-trained analysts using specialized vendor or industry-specific solutions. Modern BI solutions additionally target all information workers, giving them consolidated, up-to-date information so that they can make the best decisions. In this lesson, you will identify the potential users of a BI solution in an organization and review common BI terms and concepts. You will also examine how traditional BI solutions have evolved and what the options are today. Lastly, you will look at the key drivers behind Microsoft’s vision and products for BI.

Module 1: Introduction to Business Intelligence Architecture

6

Discussion: Considerations for Defining Business Intelligence

Concept: Define Business Intelligence and Business Intelligence solutions. Instructor note Make sure that students appreciate that business intelligence (BI) is more than just online analytical processing (OLAP). Point out that the three main areas in a BI solution are integration, analysis, and reporting. The discussion covers BI functionality in general rather than specific products. However, you should be aware that students might mention specific vendors’ products such as Hyperion, Essbase, Cognos, AlphaBlox and others. Use this discussion to understand the level of knowledge that students have, so that you can better address the needs of the students and the class as a whole. Most students should be familiar with data warehouses, OLAP solutions, reporting solutions, and ETL, but some students might not be familiar with data mining. If students are not familiar with data mining, spend some time describing the main functionality provided by a data mining solution and the scenarios in which data mining is commonly used. For more information about data mining, see Data Mining Concepts in SQL Server Books Online. This discussion should take approximately 10 minutes.

Introduction In this discussion, you will share your knowledge and understanding of BI solutions. The intention is to achieve a broad consensus of what constitutes a BI solution and what major components it includes.

Module 1: Introduction to Business Intelligence Architecture

Discussion questions Q What is BI? A Answers will vary, but should focus on the analysis of business data across the organization to understand current business performance and make informed business decisions. The information provided by the BI solution should be targeted, and should have the appropriate level of detail and presentation format for the target user group. Q What is a BI solution? A Answers will vary, but may include: •

Components, such as:

•

Business data sources

•

Data warehouses and data marts

•

OLAP cubes

•

Analysis-focused client applications

•

Reports

•

Processes, such as:

•

Extraction, transformation, and loading (ETL)

•

Data scrubbing

•

Cube processing

•

Data mining

7

Module 1: Introduction to Business Intelligence Architecture

8

Business Intelligence Solution Users

Fact: Identify the users of a BI solution. Instructor note Ask students about the BI systems that they have worked with or built in the past, and, specifically, who were the users and how did their requirements differ.

Introduction Historically, BI solutions have been industry-specific, and expensive, and were often of benefit to only the largest organizations. However, with new, more cost-effective BI-related technology, the benefits of BI solutions are no longer limited to a particular type or size of business. BI solutions are effective for regional, national, and international organizations in any commercial market sector (not just retail), as well as non-commercial organizations, government agencies, intelligence organizations, and the armed forces. Regardless of the organization, a BI solution should focus on business users, not technical IT users, for example: • Executives and management •

Analysts and planners

•

Information workers

•

Business applications

Module 1: Introduction to Business Intelligence Architecture

9

Business users BI is no longer the domain only of managers and highly trained business analysts and specialists. In the increasingly competitive business environment, for an organization to remain competitive, an effective BI solution should assist business information workers, at all levels in the organization, to make better decisions more quickly. A BI solution provides a consistent, consolidated, and accurate insight into all aspects of an organization—its sales, customers, manufacturing, suppliers, distribution, competitors, marketing, human resources, and finance—and other corporate information. This insight drives core improvements in efficiency, which enables the organization to improve productivity, provide better customer service and support, enable more accurate planning, and increase the business agility of the organization. It is important that business users know how the organization’s BI solution and its associated tools are applicable to their specific job roles.

Applications In addition to being used by employees in an organization, BI functionality can be used directly by line of business applications. Examples include automated data mining solutions, such as the prediction engines of retail Web sites, or systems to identify fraudulent credit card transactions.

Module 1: Introduction to Business Intelligence Architecture

10

Overview of Common Business Intelligence Terms

Fact: Identify key BI terminology. Instructor note Determine the terms with which students are already familiar. Ask students to discuss the differences between the terms and why the terms are important. Be prepared to discuss other terms that may arise. Ensure that students understand the principles and construction of OLAP cubes: dimensions, measures, aggregations, slice and dice, and drillup and drilldown. Some of the points in this discussion may overlap with those in the discussion at the start of this lesson.

Introduction Organizations typically use two broad types of business systems: •

Operational systems, also known as online transaction processing (OLTP) systems, which run the business, for example, an order-entry system or stock-control system.

•

Decision-support systems that extract useful information from operational data to enable well-informed business decisions to be made.

Some businesses use a single business system for both purposes. Generally, this approach is not optimal for the following reasons:

Module 1: Introduction to Business Intelligence Architecture

11

•

OLTP data is normalized in operational systems is normalized to reduce duplication, and requires complex queries to generate analytical data.

•

OLTP is optimized for insert and update operations as well as queries, and is not normally heavily indexed. This lack of indexes reduces the potential performance of queries to retrieve analytical data.

The objective of a BI solution for an organization is to provide a broad enterprise-wide decision support capability and range of tools for users that is easy to use and that provides well-organized, relevant information to the various user groups. This information must usually be based on the data in the operational OLTP data sources, but is generally extracted into the BI solution and optimized for decision support

ETL The data held in operational systems is application specific, so the structure and format of the data is not directly comparable or compatible with the structure of the BI data warehouse. The main role of the extraction, transformation, and loading (ETL) component in a data warehouse system is to extract the data from these separate systems, cleanse and harmonize it, and then incorporate it into the central database of the data warehouse. Data warehouse A data warehouse is a centralized repository of consistent data. Each operational system in an organization contains data that describes a specific view or aspect of the business. A data warehouse is a relational database that provides an overall integrated view of the business by consolidating and aligning the data from operational systems and other non-operational data sources. A data warehouse holds a separate copy of the data so that data analysis, data mining, and reporting activities do not interfere with core operational functions. In most cases, the data in the data warehouse is denormalized to improve query performance. Because operational data is constantly changing and growing, the data warehouse requires regular updates so that the business information remains current for its users.

Data mart A data mart is a subject-oriented or departmental repository, for example, focused around marketing or accounting. Some organizations might us departmental data marts instead of a central data warehouse, or in some cases the data marts might be aggregated together to forma the central data warehouse (on conversely, the data marts might consist of subsets of the organization’s analytical data extracted from the data warehouse. OLAP The most common kind of analysis is multidimensional data analysis, which is also referred to as online analytical processing (OLAP). In a multidimensional analysis solution, you arrange data in an OLAP cube structure in aggregations of business measures, intersected by various dimensions of the business. For example, you could create a multidimensional analysis solution that enables users to view sales totals by product, order date, customer, and store. Data mining Data mining solutions use mathematical algorithms to analyze data and generate trends and predictions based on statistics and patterns in the data. A data mining solution consists of one or more data mining model in which a particular data mining algorithm has been applied to a representative set of business data to identify trends, clusters, or statistical relationships between key data items. The data model can then be used to predict business measures for similar data. For example, you could use a data mining model to analyze past purchases made by customers, and then use the model to suggest products that an individual customer is likely to buy, based on purchases made by customers with a similar demographic profile.

Module 1: Introduction to Business Intelligence Architecture

12

Dashboards Dashboards, in a BI context, provide a way to view the current performance metrics of an organization—the key performance indicators (KPIs). A dashboard is hosted in a Web portal to provide simple access through a browser. A dashboard usually provides a level of drilldown. For example, if the profit metric of an organization shows that profits are lower than those of the previous year, by using drilldown, you can immediately identify the underperforming areas. Scorecards Scorecards, like dashboards, identify how an organization is performing. The difference is that the strategic performance goals of an organization are disseminated, top-down, through the organization, to managers and their staff. In this way, if all individuals and managers meet their allocated performance goals, the organization meets its overall performance goals. Individuals and managers monitor their performance through the scorecard interface. Reporting Many business users in an organization do not require the analysis capability of OLAP, but they do want reports. Reports can range in sophistication from standard fixed reports to reports that offer a level of drillthrough, as well as customization through report parameters. In addition to authoring facilities, a reporting system typically enables business users to subscribe to automatically receive the most up-to-date version of these reports.

Module 1: Introduction to Business Intelligence Architecture

13

Traditional Business Intelligence Solutions

Fact: Describe the traditional approach to BI. Instructor note Point out that the traditional approach to BI has many limitations; it provides data that is often out of date and does not support modern business requirements well.

Introduction Traditional BI solutions offer core BI functionality; this includes data analysis and basic reporting. However, these traditional solutions lack the additional sophistication that is increasingly required by organizations. Additionally, they also often have long development cycles and can be difficult to implement or to change after deployment Facilities and capabilities A traditional BI solution includes the following core BI components: •

Relational data warehouse that stores the BI data.

•

ETL subsystem that extracts the data from the various operational systems.

•

OLAP cubes to analyze business data through drilldown and slice and dice.

•

Reports to present information that is contained in the data warehouse or cubes.

When traditional BI solutions were designed, real-time access to decision-support data was not important. Typically, the propagation delay introduced between the operational systems and the warehouse, and then between the data warehouse and the cubes in the multidimensional database can

Module 1: Introduction to Business Intelligence Architecture

14

be considerable. However, today, an increasing number of organizations want the option to incorporate near-real-time access data into their BI solutions. Traditional BI solutions often do not offer data mining or performance-management applications, and provide only basic reporting facilities.

Module 1: Introduction to Business Intelligence Architecture

15

Module 1: Introduction to Business Intelligence Architecture

16

Module 1: Introduction to Business Intelligence Architecture

17

Contemporary Business Intelligence Solutions

Concept: Describe the contemporary approach to BI. Instructor note Encourage students to discuss the implications of the differences between a contemporary approach to BI and a traditional approach. Ask students to consider how contemporary requirements influence IT processes and planning. Also, encourage them to discuss the range of ways in which BI can be used in any kind of business and to identify ones that they are familiar with.

Introduction Traditional BI solutions primarily target a limited set of executives, managers, and analysts, who make strategic or tactical decisions. Real-time (or low-latency) access to BI data offers all information workers the option to use operational BI to make decisions based on up to date information. All employees need to make decisions, and the availability of timely and targeted information enables employees to improve productivity and have more confidence in their business decisions. Additional facilities and capabilities A contemporary BI solution offers a superset of the features provided by traditional solutions. Enhancements include: •

Low-latency. Contemporary solutions can use a number of techniques to ensure that the data in the BI system is kept up to date with the operational systems. This can be achieved by building the OLAP cubes directly from the OLTP data sources instead of an intermediary data

Module 1: Introduction to Business Intelligence Architecture

18

warehouse, or by using notifications to keep cached cube data synchronized with the underlying database. •

Key performance indicators (KPIs). KPIs provide an easy way to compare business metrics with stated goals at a glance.

•

Integration with existing user applications. User interface (UI) components, such as scorecards and dashboards, can embed critical business performance information into standard UIs such as Web portals and desktop applications.

•

Flexible reporting. Contemporary solutions provide increased flexibility in report formatting and delivery, and often enable users to create their own reports, rather than use predefined reports.

•

Data mining. Data mining uses a number of statistical and mathematical algorithms to identify patterns and trends in data, from which to make predictions and business forecasts.

Module 1: Introduction to Business Intelligence Architecture

19

Common Business Intelligence Philosophies

Fact: Describe common BI philosophies. Instructor note Ask students which type of philosophy (if any) they have used. Encourage students to discuss the implications of the different approaches to BI for the design and implementation of solutions. You should be familiar with the Inmon’s Corporate Information Factory (CIF), and be prepared to explain its components if necessary, and Kimball’s data warehouse approach. For more information, see The Corporate Information Factory by Bill Inmon (Wiley, 2001) and The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling by Ralph Kimball and Margy Ross (Wiley, 2002.)

Introduction There are two overall design philosophies for developing an organization-wide BI solution: •

The top-down, or holistic approach, requires a full set of the organization’s BI requirements to be captured. These requirements identify the data that is needed from the back-end operational sources to enable a full design of the data warehouse.

•

The bottom-up approach enables a BI solution to be incrementally developed and deployed, as a series of data marts, when additional business requirements are identified and incorporated.

Module 1: Introduction to Business Intelligence Architecture

20

Top-down design Because the top-down approach considers all BI requirements at the start of the design process, the design can deliver a fully integrated, organization-wide BI solution. There will still be design changes to the data warehouse and back-end systems as the organization evolves, for example, as new external data sources are identified to enhance or enrich existing data, or the organization expands into new business areas and adds new operational systems. The disadvantage of the top-down approach is that there might be a long lead time. Associated with the lead time, there is an initial investment that is necessary before the BI solution can be deployed. Bill Inmon, in his Corporate Information Factory (CIF) approach to BI development adopts the topdown approach.

For more information For more information about the Corporate Information Factory approach to BI development, see The Corporate Information Factory by Bill Inmon (Wiley, 2001.) Bottom-up design The bottom-up approach enables an initial BI solution to be deployed quickly and then incrementally enhanced and extended as required until it covers the organization’s full BI requirement. The disadvantage is that each update must be consolidated into the existing BI solution. For this approach to work effectively, it is important to centrally control and coordinate development to prevent inconsistencies. Initially, compared with the top-down approach, the bottom-up design costs less, although the BI solution is initially less extensive. However, this cost advantage is lost as you add more business processes and their BI requirements. The bottom-up approach is the one sanctioned by Ralph Kimball in his data warehouse approach to BI development.

For more information For more information about the Data Warehouse approach to BI development, see The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling by Ralph Kimball and Margy Ross (Wiley, 2002.)

Module 1: Introduction to Business Intelligence Architecture

21

Microsoft Business Intelligence Vision

Fact: Describe Microsoft’s BI vision. Instructor note Focus on the key ideals of the Microsoft vision for BI: consistent analytical data across the enterprise, and all users in the organization empowered by the BI solution. The specific products and platform components are discussed later in this module.

Introduction At the center of the Microsoft BI vision are its goals to: •

Make BI available to the all organizations and users that can benefit from it.

•

Facilitate superior solutions by unifying the relational and multidimensional models.

•

Encourage extensibility through an open server architecture that embraces open standards such as XML Web services.

Increased access to BI In the past, powerful BI solutions have only been available to large organizations with sufficient budget and technical expertise, and have only been of use to specialist analysis users. Microsoft’s BI platform widens the audience for BI solutions in the following ways: •

Pricing and licensing models are attractive to organizations of all sizes.

Module 1: Introduction to Business Intelligence Architecture •

Organizations that use Microsoft® SQL Server™ already have the core BI server-side components and infrastructure that is necessary to implement a BI solution.

•

BI functionality will continue to be integrated into Microsoft Office technology, making it easier for users to integrate BI functionality into the productivity tools they use every day.

22

Unified Dimensional Model The multidimensional model, unlike the relational model, organizes data in a way that focuses on the business rather than the system. However, the relational model is still an efficient and easily managed solution for storing data. The Unified Dimensional Model (UDM), introduced in Microsoft SQL Server™ 2005, combines the advantages of the multidimensional model with key advantages from the relational model. The effect is that a cube based on the UDM has superior features and flexibility. Some of the characteristics of the relational model that are incorporated into the UDM are: •

Attribute-based dimensions are used to make every column in a dimension table a hierarchy by which measures can be aggregated.

•

Data warehouse schema support is no longer limited to star or snowflake schemas.

•

Improved real-time (or low-latency) OLAP support for operational business intelligence is achieved by automatically detecting changes in source data and either updating the cube data directly or through a proactive cache that balances latency and performance characteristics.

Open server architecture SQL Server exposes its OLAP, data mining, and reporting components programmatically through Web services. For example, OLAP and data mining functionality is exposed through the XML for Analysis (XMLA) standards-based protocol. Over 20 leading BI software vendors, including Microsoft, Hyperion, and SAS, support XMLA. Standards encourage third-party vendors to build applications for the Microsoft BI platform. Also, with Web services you can develop thin client applications with a zero footprint. This makes the Microsoft BI platform readily accessible from any operating system and by using any language.

Module 1: Introduction to Business Intelligence Architecture

23

Lesson 2: Overview of Business Intelligence Architecture

Lesson objective After completing this lesson, you will be able to: Identify the main components of a comprehensive BI architecture. Introduction Microsoft offers all the core BI components, as well as the infrastructure technologies, required to build comprehensive BI solutions that can meet the requirements of organizations of all sizes. Independent software vendors also offer a range of products designed to complement and enhance a Microsoft BI solution. In this lesson, you will examine both the back-end and the front-end components that make up a typical BI solution, and you will look at how the components interact. You will then learn how these components map to Microsoft BI technologies, so that you can design and develop BI solutions based around Microsoft products.

Module 1: Introduction to Business Intelligence Architecture

24

Components and Processes in a Business Intelligence Architecture

Concept: Describe the components and processes in a BI architecture. Instructor note Use an analogy, such as a restaurant, to explain the difference between the front-end and back-end aspects of the BI solution. The experience of diners in the restaurant is different from that of employees in the kitchen, but both a kitchen and a dining area are required. Point out that the purpose of all the activity in the kitchen is to meet the needs of the diners, in the same way that the back-end components and processes of a BI solution provide the front-end services that the business users require. The key point is that the back end is invisible to the users—they do not really care what goes on there. The front end is everything that is exposed to the user. From the perspective of the users, the front end is the BI solution.

Introduction The diagram provides a broad overview of the components that typically make up an organization’s BI architecture. The specific design and implementation details will depend on the requirements of the business and the users. For example, if access to specific information is required in real time, the implementation must provide this. Even if real-time access is not an initial requirement, it may be sensible to ensure that the design and implementation can support it if required in the future. Most raw data comes from the operational systems, but is often augmented or enriched from external data sources, for example, mailing lists and credit and competitor information that is purchased from third-party vendors.

Module 1: Introduction to Business Intelligence Architecture

Back end The back-end components of a BI solution are: •

An ETL service to process data from data sources and then write it into the data warehouse store.

•

The data warehouse relational database store that holds the consolidated version of the organization’s data and associated metadata.

Front end The front-end components of a BI solution are: •

The data delivery services, such as data analysis, reporting, and performance management. These services typically use the data warehouse as their data source.

•

The client interface of a delivery service. In addition to PC clients and Web browsers, other client devices, such as PDAs or Web service client applications can be supported.

25

Module 1: Introduction to Business Intelligence Architecture

26

Module 1: Introduction to Business Intelligence Architecture

27

Module 1: Introduction to Business Intelligence Architecture

28

Front-End Architecture

Fact: Describe the front-end components and processes of a BI solution architecture. Introduction In addition to the tools and applications that are used by business users, tools are also required by administrators to manage the BI solution components and by developers to create the business solution elements, such as OLAP cubes and business reports. OLAP service At the heart of a BI solution, an OLAP service (or engine) represents source data in a multidimensional model. The resulting OLAP cubes present data in a way that is far more intuitive to a user than that of a relational schema model. With the right tools, users can thoroughly analyze the cube information. Another key advantage of an OLAP cube is that queries against aggregated data execute much faster than queries against relational database tables. The cube drillup and drilldown operations use preaggregated values, so aggregations for cell values do not need to be calculated. The obvious data source on which to build OLAP cubes is the data warehouse relational data store. However, an OLAP cube could also use a data mart or even directly connect to an OLTP database.

Data mining service The data mining service detects data patterns to predict future outcomes. Depending on the type of prediction that is being sought, an appropriate algorithm is used with a typical set of data to train a

Module 1: Introduction to Business Intelligence Architecture

29

data mining model. Each member of the training set contains the input values and the output value. Once trained, you can use the model with similar data, but this time the model predicts the output value. For example, you could use a data mining model to identify the customers that are most likely to respond to a product promotion. Based on the results of the data mining model, the organization can optimize its mailing list.

Reporting service The typical reporting service enables report specialists to design and publish reports that users can then access, typically through a portal. The service may also offer a subscription facility so that users can subscribe to a report. For example, users might subscribe to receive a report every day at a set time. Typical delivery options include receiving a report by email or depositing it in a shared folder. A reporting service may offer reports in various formats, for example, HTML, Excel, and PDF.

Client applications and interfaces The interfaces that are used by business users to interact with the BI solution include: •

Analysis applications that enable thorough interactive analysis of data by fully exploiting the powerful slice, dice, drillup, and drilldown capabilities of OLAP cubes. Microsoft Office Excel® is a commonly used client tool for analyzing OLAP cubes.

•

Reports that present a set of data from an OLAP cube or relational database in an easy-tounderstand format. For example, a report might present Internet sales detail in a table and summary information in a chart, and the user can enter parameters to select the product categories and financial quarter to include. The reporting service generates the reports centrally, and users access the reports through a browser directly from a report server or from within the Web portal of the organization.

•

Dashboards and scorecards that users can use to monitor performance. As with reports, you typically access dashboards and scorecards through a Web portal.

•

Data mining applications that use algorithms to identify trends and make predictions.

•

Custom applications. For example, a custom data mining application might suggest additional products that a user might want to purchase on an e-commerce Web site.

Module 1: Introduction to Business Intelligence Architecture

30

Back-End Architecture

Fact: Describe the back-end components and processes of a BI solution architecture. Instructor note Encourage students to discuss how each of the back-end components contributes to the provision of BI services and data processing support. Ask students what experiences they have with each of these components.

Introduction At the center of the BI back end is the data warehouse. The warehouse is a relational database that can store a large amount of data that is optimized for read access. The logic that is defined and incorporated into the ETL processes determines how to populate the data warehouse to provide a consistent enterprise-wide data store. For all server components, the technical considerations must include security, availability, performance, and scalability.

Extraction process The extraction process copies the records from the various data sources to a staging area for processing. In addition to identifying the data that is required, you must ensure that the extraction is done in such a way, and at such times, that it does not affect the running of the operational systems.

Transformation process

Module 1: Introduction to Business Intelligence Architecture

31

The transformation process applies the business rules that are defined for the data. The operations include: •

Filtering: to remove records.

•

Summarizing: to total a set of records.

•

Merging: for example, to merge customer records from different sources that contain some non-overlapping information.

•

Transposing: for example, to change descriptions (or values) provided by various input data sources into the standard descriptions used in the data warehouse.

•

Performing calculations: such as mathematical operations to produce derived values, for example, converting monetary values in records from one currency to another.

Cleansing process The cleansing process ensures that the data input for the data warehouse meets the predefined quality. For example, customer address records that have missing or incorrect fields may not meet the required criteria. The cleansing process attempts to correct quality violations; records that cannot be corrected are rejected. Once cleansed, the data is loaded into the data warehouse.

Module 1: Introduction to Business Intelligence Architecture

32

Microsoft Technologies for Business Intelligence

Fact: Describe Microsoft technologies for a BI solution. Instructor note Encourage students to discuss how each of the components contributes to the provision of BI services and data processing support. Ask students what experiences they have with each of the components. There is a lot of information in this topic, therefore it is important that you are able to summarize the functionality and benefits of each component. For more information about the Microsoft Business Intelligence platform, see the Microsoft Business Intelligence Product Guide on the Microsoft Web site.

Introduction The Microsoft BI server platform is based on SQL Server. Microsoft also offers end-user tools for analysis, creating scorecards, and reporting. In addition, various tools are available from independent software vendors. Microsoft SQL Server 2005 Microsoft’s BI server platform uses the following SQL Server services: •

Relational engine: used by the data warehouse store.

•

SQL Server Integration Services (SSIS): redesigned in SQL Server 2005, this provides extensive ETL functionality.

Module 1: Introduction to Business Intelligence Architecture

33

•

SQL Server Analysis Services (SSAS): provides comprehensive OLAP cubes and data mining functions.

•

SQL Server Reporting Services (SSRS): provides a full report design, deployment, and delivery service. In SQL Server 2005 there is a facility for report designers to create report models that business users can use as the basis from which to create and publish ad hoc reports that include intuitive drillthrough capability.

Microsoft Office Excel and Office Web Components The powerful and familiar data analysis capabilities provided by PivotTable® and PivotChart® reports in Excel enable business users to analyze (slice and dice, drill up, and drill down) and graphically display data from Analysis Services OLAP cubes. You can publish PivotTables and PivotCharts as interactive Web pages and access them through Internet Explorer®. Internet Explorer transparently uses Office Web Components (OWC). OWC is included with Microsoft Office. To use OWC, Office does not need to be installed on the client machine; a license is required only for interactive access. Through OWC, you can add full Excel functionality to a custom Windows client application. Alternatively, because OWC exposes a complete object-programming model, developers can just incorporate selected Excel functions within a program. Additionally, the new server-side Excel capability in Office 2007 enables centralized access to spreadsheets from any browser.

Microsoft Office SharePoint Server You can use Microsoft Office SharePoint Server to create a central portal that users can use to access reports and other BI data. Microsoft Office SharePoint Server 2007 allows users to collaborate on Excel spreadsheets containing BI data, and integrate data from a number of business data stores by compiling a business data catalog. SharePoint Server 2007 also enables users to create dashboards that display KPIs. Microsoft Office Business Scorecard Manager Microsoft Office Business Scorecard Manager 2005 is a comprehensive server-based business performance scorecard and dashboard management application with which business users can build, manage, and use their scorecards. Business Scorecard Manager can integrate information from a variety of sources, including databases and enterprise resource planning (ERP) solutions. By using Analysis Services as a source, this adds powerful analysis capability. KPIs are displayed with associated contextual reports, charts, and analysis tools, within a Microsoft SharePoint® portal. For more information For more information about Business Scorecard Manager 2005, see “Microsoft Office Business Scorecard Manager 2005” on the Microsoft Office Online Web site.

Module 1: Introduction to Business Intelligence Architecture

34

Other Microsoft Technologies

Fact: Describe the other Microsoft tools and technologies that you can use in a BI solution. Instructor note Determine to what degree students are familiar with the technologies on the slide and their particular capabilities. Recommend ways of filling any knowledge gaps, such as suggested books and Web site references. Ask students to identify how, in their organizations, they could use these technologies to improve their solution. You should be familiar with these technologies to a reasonable degree. There is a lot of information in this topic, therefore it is important that you are able to position and summarize the purpose and benefits of each component.

Introduction In addition to SQL Server 2005 and SharePoint technologies, there are a range of other server technologies that are used to support a Microsoft BI solution. Windows Server 2003 Because of the potentially huge amount of data and large number of users that may use SQL Server and its BI components, it is important to consider the following scalability guidelines for the Windows Server 2003 operating system on which SQL Server is installed:

Module 1: Introduction to Business Intelligence Architecture

35

•

Use more powerful server hardware in conjunction with the appropriate edition of Microsoft Windows® 2003. The edition of Windows 2003 determines the type of processor (32-bit or 64-bit), the maximum number of processors (up to 32), and the maximum memory (up to 64 gigabytes) that the server can support. Windows Server 2003 64 bit editions access large amounts of RAM more efficiently, without the need for address windowing extensions (AWE), which can positively impact a BI solutions (for example by reducing cube processing times.)

•

Distribute SQL Server BI platform components, and component elements, across servers. For example, through remote cube partitioning, you can partition cubes across multiple servers.

•

Use Network Load Balancing (NLB) to scale out and distribute incoming requests across a number of identical servers, for example, to distribute analysis and reporting requests across a group of Analysis Service instances and report servers. Because a request can go to any server, all servers must hold identical data. Changes made to any server must be available, or replicate to, the other servers.

You should also consider SQL Server availability. Availability solutions use either NLB or a Windows server cluster. All servers in a Windows server cluster share the same data through a share storage system. Because there is a single copy of the data, no synchronization is required between servers. The hardware requirement that is associated with the shared storage means that cluster servers must be certified for use in a Windows server cluster.

For more information For more information about Networking Load Balancing and server clusters, see Technical Overview of Clustering in Windows Server 2003 on the Microsoft Web site. Internet Security and Acceleration Server 2004 Microsoft Internet Security and Acceleration Server 2004 is an advanced stateful packet-layer and application-layer inspection firewall. It also includes a virtual private network (VPN) component that offers secure access to intranet information for remote users and Web sites, and a Web-caching component to improve performance for users when accessing external Web sites. In a BI solution, Internet Security and Acceleration Server can be used to implement secure subnets in the network topology of the BI solution, and to provide security for communication between BI components across the Internet. Microsoft Operations Manager 2005 Microsoft Operations Manager (MOM) 2005 is an event-management and performance-management tool for Windows Server–based systems. In addition to recording events, MOM 2005 can proactively resolve issues. Reports can be exported to Reporting Services and published. In a BI solution, MOM can be used to monitor the system health of the BI infrastructure, and to help maintain an effective operational environment for all BI services. BizTalk Server 2006 Microsoft BizTalk® Server 2006 provides the coordination, or orchestration, layer that connects isolated business processes and services within and between organizations to deliver integrated endto-end business solutions. In a BI solution, BizTalk Server can be used to connect the BI solution to line of business systems and processes. Host Integration Server 2004 Microsoft Host Integration Server 2004 integrates IBM host applications, data sources, messaging, and security systems with the Microsoft Windows Server System platform. In a BI solution, Host Integration Server can be used to integrate data from legacy data stores into the ETL process.

Module 1: Introduction to Business Intelligence Architecture

36

Lab: Identifying Business Intelligence Solution Requirements

Time estimated: 60 minutes Instructor note This lab has two exercises. Students should complete this lab in groups of two or three, although they can also complete it individually. You should review the answers at the end of each exercise with the students. There are no right or wrong answers; students can make different assumptions. In Exercise 1, the students will examine several interviews and an overview of the existing business process flow that is used by the sales and marketing division of Adventure Works. Based on this information, students will create a vision and scope document for the proposed business intelligence (BI) solution. Students should spend approximately 30 minutes on this exercise. In Exercise 2, the students will use the vision and scope document in conjunction with information about the various data sources that are available within the organization to create a high level BI solution architecture. This exercise requires students to use Microsoft Office Visio®. If students are unfamiliar with Visio, point out that a basic guide to the product is provided in the D:\Labfiles\Starter folder. Students can use any of the available shapes in Visio to design their control flow solutions. Students should spend approximately 30 minutes on this exercise.

Introduction In this lab, you will define the vision and scope statements to position a proposed business intelligence (BI) solution for Adventure Works. You will base the vision and scope primarily on interviews that have already taken place with stakeholders and a description of the existing business process flow used within the sales and marketing area. You will discuss your results with the group.

Module 1: Introduction to Business Intelligence Architecture

37

Preparation Ensure that the virtual machine 2794-MIA-SQL-01 is running. Also, ensure that you have logged on to the computer. To log on to the computer, use the following credentials: •

User name: Student

•

Password: Pa$$w0rd

Tip For best results, adjust the screen resolution of the virtual machine to at least 1024 x 768 pixels and switch the virtual machine to full screen mode.

Module 1: Introduction to Business Intelligence Architecture

38

Exercise 1: Determining Business Requirements

Introduction In this exercise, you will review several interview transcripts, some sample reports, and an overview of the existing business process flow used by the sales and marketing division of Adventure Works, to develop a vision and scope document for a proposed BI solution. Scenario Adventure Works manufactures and sells metal and composite bicycles, as well as components and accessories, to North American, European and Asian commercial markets, both over the Internet and through retail outlets. Although the company has been tracking sales for a number of years by directly accessing its online transaction processing (OLTP) database, this approach is now recognized to have severe limitations. The goal of the senior management is that information workers at every level of the organization should be able to use, and capitalize on, all the data sources within the organization, including the OLTP data. By using this consolidated corporate data, information workers should be able to perform interactive analysis through well understood and readily available client applications, such as Microsoft® Office Excel®. This data will also be the basis for all business reporting. You are the consultant system architect for the newly formed BI design team at Adventure Works. Your initial task is to develop a vision and scope document to encapsulate the proposed BI initiative. An important source of information for your task is transcripts of interviews with the following stakeholders: •

VP Sales

•

Sales Manager—Retail Division

•

Sales Manager—Internet Division

•

Marketing Director

•

Production Control Manager

•

IT Manager

Determining business requirements Summary 1. Review the interview transcripts, the associated reports, and the description of the sales business process flow. 2. Identify the business and technical requirements for the solution, based on the information provided.

Specifications •

The file Interviews.doc is in the D:\Labfiles\Starter folder. This file contains interviews with the major stakeholders in the project.

•

The file SaleBusinessProcessFlow.doc is in the D:\Labfiles\Starter folder. This file contains an overview of the sales and marketing business process.

•

The file SampleReports.xls and ExecutiveReportSample.xls are in the D:\Labfiles\Starter folder. The first file contains two sample worksheet reports used by reseller sales managers, and the second file contains a single report used by executives.

3. Add the appropriate content to the vision and scope template document. 4. Discuss your conclusions with other students.

Module 1: Introduction to Business Intelligence Architecture •

39

The file VisionAndScope.doc is in the D:\Labfiles\Starter folder. This file contains a template for your answers.

Questions Q In addition to interviewing employees and reviewing existing systems, what other techniques could you use to gather business requirements for a BI solution? A Answers will vary, but might include shadowing users as they perform their daily tasks, or spending some time working in various departments to understand the business and its processes. Other approaches include questionnaires and focus group meetings. Q What would be the benefits of doing your own interview rather than reading the answers to an interview that someone else has performed? A Answers will vary. Obvious benefits include the ability to ask follow-up questions or clarify statements. Additionally, you can design your own interview questions and plan the interview process to suit your own particular scenario.

Module 1: Introduction to Business Intelligence Architecture

40

Module 1: Introduction to Business Intelligence Architecture

41

Exercise 2: Designing a High Level Architecture Introduction In this exercise, you will produce a solution architecture diagram to complement the vision and scope document that you created in Exercise 1. Designing a high level architecture Summary

Specifications

1. Review the vision and scope document created in Exercise 1, and examine the list of available data sources provided.

•

The file DataSources.doc is in the D:\Labfiles\Starter folder. This file lists the available data sources.

2. If necessary, review the interview transcripts, the associated reports, and the description of the sales business process flow provided in Exercise 1.

•

For more information about using Visio, refer to Getting Started with Visio.doc in the D:\Labfiles\Starter folder.

3. Identify the components of the high level BI solution architecture to satisfy all identified requirements. 4. Create a diagram for your proposed BI solution architecture in Microsoft Office Visio®. 5. Discuss your solution with the other students.

Questions Q Why is a high-level pass important in planning a BI solution? A Answers will vary, but usually cover the following areas: •

A degree of abstraction is useful so that the architect does not get overly absorbed in the fine detail. It is important to have a clear view of the entire BI project to facilitate the best choices of technologies and solution implementations.

•

High level analysis can often use similar information from other BI projects as models, and this helps to reduce planning and development time.

Q Why do you need to distinguish between front-of-house and back-of-house component requirements? A They have significantly different roles to play in providing the solution. Users and stakeholders are different, and the technologies that are involved are different in many cases.

Turning off the virtual machine When you have completed the lab, turn off the virtual machine and discard the undo disks.