Centralizing and Sharing Business Intelligence
Using SharePoint and Microsoft Consolidated Business Intelligence to create a Personalized, Company-Wide Business Intelligence Portal and an Enterprise Report Catalog
Technical White Paper
Published: October 2009
Products & Technologies
Microsoft IT maintains a complex reporting environment with a substantial number of server-based business intelligence systems. The multiple analysis systems, data repositories, intelligence gathering methods and reporting methods that Microsoft business units employ introduce business and technical challenges. Among these challenges are multiple business intelligence silos, non-uniform reports and complicated permission schemes for data sharing.
To help Microsoft business units make better and faster decisions, Microsoft IT developed a custom solution, the Microsoft CBI portal based on Microsoft products and technologies. The Microsoft CBI portal enables Microsoft business units to exploit the potential of the Microsoft business intelligence solutions more fully, more securely, and in closer collaboration with partners and vendors.
In early 2007, Chief Information Officer (CIO) charged the IT leadership team at Microsoft to adopt an internal portal solution based on Microsoft® Office SharePoint® Server 2007 for centralizing and sharing business intelligence tools and information across the entire organization. This is the Microsoft Consolidated Business Intelligence (Microsoft CBI) BI portal, developed and maintained by the Business Intelligence Engineering (BIE) within the Microsoft Information Technology (Microsoft IT) group. This centrally hosted, customizable portal integrates with a large number of Microsoft business intelligence tools and products - Microsoft Office SharePoint (ProClarity Analytics, Microsoft Office Excel® Services, Microsoft Office Performance Point Server), Microsoft SQL Server™ Reporting Services, and SQL Server Analysis Services including an internal tool called the Microsoft CBI report catalog,
The Microsoft CBI portal gives Microsoft employees and decision makers a single, personalized location with one enterprise report catalog to access standard and ad hoc reports, scorecards, and other business intelligence components. These components were previously scattered across various locations and were difficult to manage, introducing security challenges. The Microsoft CBI portal enables business units within Microsoft to streamline reporting and decision-making processes as well as share business intelligence across the company in a more secure and efficient way. Additionally, it enables close collaboration with business partners and vendors that work with Microsoft on strategic and business-critical projects worldwide.
Note: The Microsoft CBI solution is not an externally available product for customers. It is an example of how Microsoft IT uses various Microsoft business intelligence products to deliver an enterprise business intelligence capability.
This technical white paper features the Microsoft CBI portal as the context to discuss how Microsoft IT uses Microsoft Office SharePoint Server and Microsoft business intelligence products to help business units within Microsoft to make better and faster decisions. Although Microsoft CBI is an internal Microsoft solution, the underlying technologies and products are publicly available to customers who seek to achieve similar results. These results include improving business insight, accelerating shared business processes, and connecting people with business-critical information in one central location through a standardized user interface that is customizable according to personal preferences.
This paper contains information for technical decision makers who are considering or planning to implement business intelligence portals based on Microsoft Office SharePoint Server 2007. This paper assumes that the audience is already familiar with the concepts of Microsoft Windows® Server® 2003, the Active Directory® directory service, and the Microsoft suite of business intelligence products. A high-level understanding of the features and technologies included in Microsoft Office SharePoint Server 2007 can also be helpful. Detailed product information is available on the Microsoft Office SharePoint Server home page at http://office.microsoft.com/en-us/sharepointserver/FX100492001033.aspx.
Note: For security reasons, the sample names of organizations and other internal resources mentioned in this paper do not represent real resource names used within Microsoft and are for illustration purposes only.
A Gartner survey of 1,400 CIOs conducted in 2006 revealed that IT organizations are increasingly focusing on business intelligence to deliver value-driven services and solutions to their companies. Business intelligence revolves around the practice of capturing and analyzing business data to monitor business drivers, gain greater insight into performance and budgeting, and accelerate better-informed strategic and tactical decisions at all organizational levels. Connecting employees and decision makers with the right information that is relevant to their specific needs is a key prerogative for IT organizations that want to contribute to business performance. However, delivering effective solutions that provide convenient security-enhanced access to metrics, key performance indicators, scorecards, and other business data has proven difficult.
Like other IT organizations, Microsoft IT maintains a complex reporting environment with a substantial number of server-based business intelligence systems. These systems assemble and process information from a variety of data sources, such as the enterprise resource planning (ERP) system, the customer relationship management (CRM) solution, and a variety of departmental data warehouses. To display the information, Microsoft employees and decision makers use Office Excel 2007, SQL Server Reporting Services, ProClarity, Office Performance Point Server 2007, and other Microsoft Office system applications. Excel has long been a favorite tool of Microsoft managers for analyzing multidimensional data and gaining business insight.
Important business intelligence systems that Microsoft IT maintains in the corporate production environment include:
- Office SharePoint Server Enables business units to deploy interactive business intelligence portals. The Microsoft CBI portal is based on Office SharePoint Server 2007. Individual business units also use Office SharePoint Server to deploy departmental report centers. Upgrading to Microsoft SharePoint Server 2010 is within Microsoft IT BI Engineering team�s current roadmap.
- Microsoft CBI report catalog An internal Microsoft solution that offers business units an enterprise-wide directory of business reports that exist in the corporate production environment. The Microsoft CBI report catalog includes more than 2,000 reports and relies on Web service components to give Microsoft employees access to report-generating applications.
- ProClarity Analytics A data analysis solution that expands the capabilities of SQL Server based business intelligence tools through query and analysis features, dashboards, scorecards, and data visualization tools.
- SQL Server Analysis Services Provides a unified and integrated view of business data for traditional reporting, online analytical processing (OLAP), key performance indicator (KPI) scorecards, and data mining.
- SQL Server Reporting Services Enables business units to create, manage, and deliver both traditional, paper-oriented reports and interactive, Web-based reports.
- Office PerformancePoint™ Server Provides scorecards, dashboards, and management reports to monitor, understand, and act on challenges and issues that affect the performance of business units.
Figure 1 illustrates the distributed and diverse nature of the business intelligence landscape at Microsoft.
Figure 1. Business Intelligence Landscape at Microsoft
Microsoft started BIE in early 2006 to help the business units throughout the company gain maximum benefit from the collection of business intelligence systems and reporting tools within the corporate production environment. One of the first steps that BIE performed was to analyze the existing business intelligence landscape at Microsoft in order to identify existing solutions that could help the BIE team. Two important solutions that BIE decided to use were the Microsoft CBI report catalog and the Microsoft CBI portal.
The Admin IT team within Microsoft IT developed the report catalog to give Microsoft employees a central repository to locate global reports quickly and conveniently. The Microsoft CBI portal, on the other hand, was a solution that the Services IT team within Microsoft IT developed based on the report catalog. The Microsoft CBI portal extended the reach of the Microsoft CBI report catalog across the Microsoft IT organization by providing a central integration point for global reports, scorecards, key performance indicators, multidimensional analytics, and other metrics.
Services IT deployed the first version of Microsoft CBI in 2006. Shortly thereafter, reorganization took place to integrate the Admin IT and Services IT Platform teams into the BIE team to combine resources and coordinate development efforts. The original customer of Microsoft CBI was Services IT, yet due to the success of Microsoft CBI, BIE now provided support to all business units within Microsoft that wanted to use these business intelligence solutions.
Business Benefits of Office SharePoint Server 2007 and Microsoft CBI
The most important factors that drive the success of Microsoft CBI at Microsoft are business benefits, support of upper management, and proper business-specific prototypes, demonstrations, and presentations. Examples of achieved business benefits are reduced costs associated with maintaining and supporting business intelligence solutions across the company; increased consistency, reliability, and scalability; and convenient sharing of business reports through a central intranet location. Support of upper management, on the other hand, helps to emphasize the strategic significance of the Microsoft CBI initiative to all business units.
Technical factors might prevent business units from moving to the Microsoft CBI portal, such as inconsistent data sources, inconsistent capabilities, and missing data warehouses. Substantial cleanup of data and infrastructure optimizations might be necessary before a business unit can successfully complete the onboarding process. In these cases, management support helps to ensure that the business units recognize the importance of the Microsoft CBI initiative and implement the required organizational changes. To further help business units recognize the benefits and advantages of the Microsoft CBI portal; BIE invested the time and effort into prototyping and product presentations. Showing the right demonstrations to the right people is crucial to get proper funding at each step, one business unit at a time.
Based on Office SharePoint Server 2007, the Microsoft CBI portal provides business units with the following benefits and advantages:
- Centralized access to business-critical information Individual business intelligence solutions make finding the right information at the right time difficult. With Microsoft CBI, all reporting solutions, including the latest spreadsheets, reports, and KPIs, are readily available in a central place. Microsoft executives, managers, employees, partners, and vendors with intranet or extranet access can go to one URL to assemble and display business information from disparate sources for all metrics, reports, and other business analysis tasks.
- Reduced number of business intelligence applications Centralization of reporting solutions based on Office SharePoint Server 2007 and Microsoft CBI is the basis for Microsoft to coordinate development processes, share business intelligence effectively across organizational boundaries, eliminate duplicated efforts, and achieve a high level of consistency across all reporting solutions.
- Reduced development, maintenance, and support costs The Microsoft CBI portal uses components from the stack of Microsoft business intelligence products, reducing the need for business units to develop shadow applications. With a single code base, Microsoft CBI significantly reduces implementation cycles and the need to maintain and support isolated business intelligence environments.
- Streamlined everyday business activities Improving reporting processes is a key to streamlining everyday business activities. Business units that still rely on manual processes to communicate business information within the department, across the company, and to upper management can use the Microsoft CBI portal to replace these processes with more-efficient, automated reporting solutions that are available 24 hours a day, seven days a week. Centralized reporting solutions not only reduce overhead and costs, but also increase the consistency, reliability, and scalability of the reporting environment and deliver business intelligence more efficiently. Business units then can make better-informed decisions and more proactively respond to important events.
- Ability to connect people with information The user interface of Microsoft CBI is easy to navigate, and it supports personalization. For example, users can organize favorite reports, perform ad hoc analysis, and customize and reuse report views. Office SharePoint Server 2007 also includes an Enterprise Search Center to find business documents and data quickly in order to accelerate decision-making processes based on the latest information and facts.
- Broad sharing of business data while maintaining information security The Microsoft CBI portal provides online access to data and analytics while helping to secure information through access-based security and authentication. There is no need to log on to multiple applications. The Microsoft CBI portal identifies each user based on the Windows account and shows only those reports that the user can access.
- Enhanced partner relationships The Microsoft CBI portal enables Microsoft business units to strengthen their relationships with business partners and vendors by sharing business intelligence solutions and information to drive better joint decisions. Partners and vendors with an extranet account can access the Microsoft CBI portal over encrypted Hypertext Transfer Protocol (HTTP) connections.
Centralizing Access to Business-Critical Information
Centralizing access to business-critical information is an ongoing effort at Microsoft. In general, business units are accustomed to developing their own isolated reporting tools, which are rarely designed to integrate with solutions of other groups. Incompatible databases and custom data warehouses exist across the entire corporate production environment, often insufficiently maintained and outdated. Inevitably, the lack of consistent data and common report definitions results in multiple versions of the same information, impeding strategic decision-making processes and increasing business risks.
Microsoft IT developed the original reporting solution to give the finance, sales, marketing, and human resources departments a central location to publish, share, and access business reports, views, and metrics based on predefined business rules. The original solution exploited Microsoft Office Excel 2003 functionality and required the installation of custom add-ins on each client workstation. A basic Web interface was also available to provide access to reports based on SQL Server Reporting Services.
What started as a basic Web interface for Microsoft CBI, BIE expanded to include the entire suite of Microsoft business intelligence solutions. The foundational technology is Office SharePoint Server 2007, which enabled BIE to establish a hosted, integrated, security-enhanced, and configurable business intelligence portal. The portal provides a continuum of reporting and business intelligence capabilities where business units can expose the right information to the right users in the right format.
Figure 2 shows the business intelligence solutions that BIE consolidated onto Microsoft CBI.
Figure 2. Centralizing business intelligence and reporting tools
It is important to note that Microsoft CBI does not host the data of the various business units. BIE maintains centrally the server software, reporting solutions, and associated metadata and catalog databases for all business units that use the Microsoft CBI portal. The Microsoft CBI portal consolidates the user interface and business logic to provide a single point of access to all business intelligence and reporting tools without forcing BIE into a data-provider role. BIE is creating Enterprise Data Warehouse and customers can use this Data Warehouse to be the data source for Microsoft CBI reports.
Reducing the Number of Business Intelligence Applications
In the absence of a consolidated business intelligence infrastructure, business units must establish and maintain their own reporting environments. A typical approach is to install the required server software on workstation-level computers. Another, more formal approach is to send the Infrastructure Management team within Microsoft IT a request to purchase a utility server by using the designated budget of the business unit and have it installed in a data center. In this case, the Data Center Operations team maintains the hardware and operating system, yet the reporting environment is still the responsibility of the business unit. As more business units begin to rely on business intelligence tools, application silos accumulate, the footprint of underutilized servers in the data centers increases, the complexity of the environment grows, and the overhead associated with maintaining and supporting redundant reporting environments multiplies.
Consolidating the business intelligence infrastructure helps Microsoft IT to avoid unnecessary investments in information technology. The business units still maintain their data warehouses and develop reports, yet it is no longer necessary to host and maintain the business logic on departmental utility servers inside or outside data centers. Business units do not need to wait up to 60 days until their utility servers are purchased and installed.
Furthermore, in addition to standard and ad hoc departmental solutions, business units can use enterprise-wide management reports that consolidate data from multiple departments. This facilitates executive-level reporting and strategic reviews. It is also straightforward for business units to share intelligence with partners and vendors in a security-enhanced manner. The Microsoft CBI solution places all reports and analysis tools in an integrated portal that is readily available and personalized for each user.
Development, Maintenance, and Support Costs
The Microsoft CBI portal is a strategic solution for Microsoft IT to control the complexity of the global reporting environment and maximize the return on investment in server systems. Moreover, BIE actively helps business units to lower development, maintenance, and support costs. For example, reusing existing business intelligence solutions by sharing reports on the Microsoft CBI portal is an effective way to reduce individual development costs.
It is important to emphasize that Microsoft CBI is not just a technical solution to a business problem. The Microsoft CBI subject matter experts (SMEs) give business units training and support to help users exploit the capabilities and features of Microsoft business intelligence products more fully, target the right users with the right reports, and shorten development cycles. For example, a power user familiar with Office Excel reporting capabilities might find assistance and support very helpful when working with dashboards in Office PerformancePoint Server. With assistance and support from BIE, power users who create reports within their business units can unlock the full spectrum of distinct capabilities that the stack of Microsoft business intelligence solutions entails.
Streamlining Everyday Business Activities
Prior to the BIE initiative, business units had to find answers to their reporting needs individually. Reporting solutions varied widely in sophistication and complexity. These solutions often relied on manual processes, which are time-consuming, unreliable and unscalable.
The America Operations Centre (AOC) Commercial Business Intelligence team is a good example. The AOC Commercial Business Intelligence team supports the North America and South America operations centers, specifically the commercial licensing business, which represents US $12 billion in yearly revenues. The mission of the AOC Commercial Business Intelligence team is to empower clients, through business intelligence and data mining activities, to make informed decisions and drive strategic initiatives.
Before onboarding to Microsoft CBI, the AOC Commercial Business Intelligence team used Office Excel files posted on internal SharePoint sites, and in some cases Office Web Components on custom Microsoft ASP.NET solutions, to share business information. Exporting the data from the AOC Commercial Business Intelligence data warehouse was a manual and labor-intensive process, which proved unstable and unreliable. Additionally, these reports provided the data in raw format. Making the information valuable and useful for reviews took a substantial amount of time.
Onboarding to Microsoft CBI enabled the AOC Commercial Business Intelligence team to centralize and automate its existing business reports and develop new reports based on Office Performance Point Server 2007, and SQL Server 2008 Reporting Services. Today, the AOC Commercial Business Intelligence team provides an entire spectrum of reporting solutions to support the commercial licensing business, including Operations Excellence Reporting (daily Flash reports, scorecards, month-end close reporting), Readiness Reporting (ad hoc reporting for detailed analysis), and Revenue Generation/BI Reporting.
Now that the AOC Commercial Business Intelligence team uses Microsoft CBI, efficiency and productivity have increased because finding the required information to drive strategic initiatives and improve business processes takes less time. A preliminary survey showed that the automated reporting solutions save 60�90 employee hours per month across all AOC Commercial Business Intelligence clients. Furthermore, the team anticipates that new reporting solutions will help save field sales teams an additional 50 hours per month. There are also direct financial gains associated with the new reporting solutions. The new solutions enable the field sales teams in the United States to capture an additional $60 million in revenue opportunity.
Connecting People with Information
Centrally maintaining and sharing reports, metrics, scorecards, and KPIs is one aspect of communicating business information across the company. Another is to share helpful documentation and training guides, answers to frequently asked questions, announcements about upcoming events, contact information, and useful links to other content that exists elsewhere in the corporate production environment. To cover these additional communication needs, BIE provides business units with document libraries and custom application pages on the Microsoft CBI portal.
Power users within each business unit can then modify the application page according to departmental needs by using standard Office SharePoint Server Web Parts, without the involvement of BIE. For example, a business unit can add announcements, provide help and contact information, and include a short description of the reports and their purpose.
Figure three displays the main user interface of the Microsoft CBI portal, which BIE implemented based on a custom Office SharePoint Server master page. An important design objective was to keep the user interface straightforward for convenient navigation. Accordingly, there are only two primary navigation controls, Communities and Reports, which provide access to the application pages (Communities tab) and published business reports (Reports tab).
Figure 3. The Microsoft CBI home page
When a user visits the Microsoft CBI portal, the user interface shows the Communities tab, and it lists on separate tabs all custom application pages that the current user has permissions to read. This is particularly useful for managers and other users associated with multiple business units because their Communities tab provides instant access to announcements and information from all of these groups.
Note: One important reason for BIE to choose Office SharePoint Server 2007 as the underlying platform for Microsoft CBI was that Office SharePoint Server standardizes the user interface that business units can use to create document libraries and customize their application pages. Office SharePoint Server provides the necessary level of security, and BIE does not need to develop additional customization tools.
Sharing Business Data While Maintaining the Security of Information
One of the most effective strategies to increase security is to place all resources in a heavily fortified location designed to limit the attack surface, concentrate defense mechanisms, and enforce security best practices and auditing. Providing security for business information is difficult if shadow applications and application silos are widely distributed across the corporate production environment. The reason is simply that there are too many diverse ways to share information, too many security vulnerabilities, and too few security experts to protect each business intelligence island. Reducing the number of shadow applications and application silos by concentrating business intelligence on a centralized, security-enhanced, and reliably maintained platform is a key to sharing business data while helping to keep information secure.
Enhancing Partner Relationships
Microsoft business units share information with more than 30,000 business partners and vendors every day. In fact, currently, approximately 30 percent of Microsoft CBI users are partners and vendors. For example, the Microsoft customer service works closely with vendors that operate the Microsoft contact centers around the world. Customer care agents within the contact centers use Microsoft software to access customer account information and other data as necessary to respond to customer requests or resolve business problems, regardless of the sales channel that the customers use. These contact centers continuously need access to business reports that show their current performance levels.
Microsoft IT maintains a global extranet environment, separate from the corporate network, to give external entities access to resources and facilitate collaboration. Microsoft staff can access the extranet from the corporate network, but partners and vendors with extranet accounts cannot access internal resources in the corporate production environment. This separation is necessary for security reasons, yet it also hinders business units from sharing reports, metrics, key performance indicators, and scorecards.
The Microsoft CBI infrastructure spans both the corporate network and the extranet, for efficient sharing of business intelligence with partners and vendors. For example, Microsoft CBI enabled the customer service team to streamline business activities and lower organizational overhead by reducing manual reporting processes. The customer service team created data cubes by using SQL Server Analysis Services and ProClarity reports based on these cubes for OLAP, and then deployed the resulting reporting solutions through Microsoft CBI. Now that the customer service team uses Microsoft CBI, contact centers enjoy convenient and security-enhanced access to business reports. At the same time, the customer service team lowered costs, increased the reliability of the reporting process, and increased scalability.
Architecture of the Microsoft CBI Portal
The BIE team designed the Microsoft CBI solution as a user interface portal. This means that the Microsoft CBI portal provides the front end for accessing business intelligence solutions maintained outside Office SharePoint Server, such as in ProClarity Analytics, SQL Server 2008 Reporting Services, and Office PerformancePoint Server.
User Interface Portal Design
Figure 4. The Microsoft CBI user interface portal
Report Publishing Architecture
One of the advantages of the Microsoft CBI portal design is that BIE did not need to develop separate tools to create and publish reports. Analysts and power users within each individual business unit can continue to use Report Builder, Dashboard designer, Office Excel and ProClarity Desktop to develop reporting solutions, as indicated in Figure 5. The only additional requirement is to publish the reports in the Microsoft CBI report catalog so that users can select them on the Reports tab in the Microsoft CBI user interface. For historical reasons, Microsoft IT hosts the administrative components of the Microsoft CBI report catalog on separate servers on the corporate network. All other server components run on the same application server farm.
Figure 5. The Microsoft CBI report publishing architecture
Core Infrastructure Design
Figure 6 illustrates the design of the Microsoft CBI infrastructure. The main servers that host the portal and the Microsoft business intelligence solutions reside on the extranet to support internal users within the corporate production environment in addition to partners and vendors with extranet accounts. Internal users can access these front-end systems by specifying in the Microsoft CBI URL in Windows Internet Explorer®. External users must use an externally facing Microsoft CBI Web address.
Figure 6. The Microsoft CBI core infrastructure design
In addition to the main servers on the extranet, a second farm of application servers exists on the corporate network. These servers are available for internal use only because extranet users do not have access. Essentially, the internal application servers run the same Microsoft business intelligence stack as do the main servers on the extranet. The only exception is the Microsoft CBI report catalog. Deploying the catalog client on the internal application servers was not necessary, because the catalog pane in the user interface always uses the main servers on the extranet.
To provide fault tolerance and load balancing in the core Microsoft CBI infrastructure, BIE uses Windows Network Load Balancing for the front-end application servers. The database systems on the back end rely on SQL Server high-availability features, such as failover clustering or database mirroring. BIE uses failover clustering for the catalog and metadata databases.
The technologies that business units use to ensure high availability for the actual business data is not under control of BIE and varies widely. It depends on the importance of the data and the choice of the business unit. For example, one team uses failover clustering to ensure high availability for customer systems deployed on the extranet.
Global Infrastructure Design
The core infrastructure design reflects the architecture that BIE uses in the Puget Sound area to support Microsoft headquarters in Redmond, WA and the business units in North and South America. Additional data centers with Microsoft CBI application server farms are located in Dublin, Singapore, and Tokyo. These regional deployments follow the core design, yet with fewer servers and without the Microsoft CBI report catalog.
The entire infrastructure relies on a single instance of Microsoft CBI metadata. BIE deployed Microsoft CBI metadata centrally to avoid administrative overhead and latency issues associated with SQL Server data replication in a decentralized environment. The corporate network operates reliably and provides sufficient bandwidth to achieve acceptable response times in all geographic locations of the company, making a centralized Microsoft CBI metadata deployment possible.
Figure 7 shows how BIE deployed Microsoft CBI at a global scale. Although all users work with the same instance of the Microsoft CBI report catalog, the reports can reside on an application server in any location.
Figure 7. The Microsoft CBI global infrastructure design
Deploying the application servers regionally provided BIE with the following advantages:
- Better reporting performance Server-based reporting solutions can place significant load on the application server. By deploying reporting solutions on regional application servers, BIE effectively distributes the load while providing faster access to the reports for users in the same geographic region.
- Ability of business units to maintain their reports locally Maintaining reports on application servers that are close to the business units facilitates development processes as it avoids latencies and other issues that can occur when reports are saved over wide area network (WAN) connections.
- Ability of application servers to access data warehouses locally A number of factors influence the performance of a reporting solution, including WAN latency issues. By deploying the application servers close to the data warehouses of the business units, BIE can ensure that the performance of the reporting solutions does not diminish when the business units complete the onboarding to Microsoft CBI.
Prototype, Sandbox, and User Acceptance Testing Environments
A sound strategy for business intelligence consolidation must address the needs of end users and power users alike. End users need convenient access to reports, whereas analysts and power users, who create the reports, have additional requirements. To verify functionality, performance, and reliability, analysts and power users must perform integration testing and user acceptance testing prior to the final step of publishing new reports on the company-wide Microsoft CBI portal. To accommodate these testing needs, BIE integrated the following types of lab environments into the Microsoft CBI infrastructure:
- Prototype environment This environment enables business units to prototype reports during the initial evaluation of Microsoft CBI, which is part of the onboarding process. This environment essentially consists of a single server that hosts all necessary software components and databases. Business units can publish their own existing reports or sample reports provided by BIE to see how reporting solutions work on the Microsoft CBI portal.
- Sandbox environment The purpose of the sandbox environment is to facilitate integration testing. This environment consists of a single computer running SQL Server 2008 to simulate the architecture of the production environment closely. Business units and test teams can use the sandbox environment to test the initial integration of reporting solutions with Microsoft CBI, including functionality and reliability.
- User Acceptance Testing environment This environment is for final user acceptance testing before going live with new reports on the Microsoft CBI portal. At this point, the reporting solution has passed integration testing, yet the actual users of the reports still need to confirm that the solution meets their acceptance criteria. The staging systems mirror the production environment in both the corporate network and the extranet, to provide access to the reporting solutions for corporate users and extranet users.
Information Work Scenarios
Perhaps the most remarkable feature of the Microsoft CBI solution is its straightforwardness in meeting the needs of power users, end users, and managers. Power users work with familiar tools to create and publish reports that are then immediately available across the company to all end users who have access permissions. End users can simply view reports or optionally customize reports. Managers can share reports with employees, partners, and vendors without having to depend on BIE.
To illustrate the ease of use, note that the procedure to display reports, metrics, key performance indicators, and scorecards in Microsoft CBI requires only four steps:
- Go to Microsoft CBI.
- Switch to the Reports tab.
- Select the desired reports and scorecards in the catalog pane.
- Click Load Reports button.
Note: In this procedure, the user can eliminate steps 3 and 4 by specifying default reports, which appear automatically in the reports pane. It might seem insignificant, but having the most important business information readily available with just one mouse click is a key feature for busy managers at Microsoft.
BIE defined the Power Users role on the Microsoft CBI portal to give business units more control over their resources. Power users are corporate users who develop reports and perform other tasks on the Microsoft CBI portal, such as customizing Office SharePoint Server application pages for business units.
The process of creating and publishing reporting solutions is similar for all business intelligence tools:
- Independent of Microsoft CBI, the power user works with Report Builder, Report Designer, Dashboard designer, Office Excel and ProClarity Desktop to create the report by using the desired data source and saves the report definition on the local computer.
- The power user publishes the report to an appropriate application server in the Microsoft CBI infrastructure. Now that the report is available on the application server, the power user can view the report in Windows Internet Explorer by specifying the report URL. Viewing the report is a quick way to verify that the previous publishing process ended successfully.
- There is one more step before end users can see the report in the Microsoft CBI report catalog: registering the report URL by using the Microsoft CBI Catalog Admin client, as illustrated in Figure 8. Power users can specify report attributes that enable searching and categorization for end users, such as report owner, geographic region, and report type. Power users can specify access permissions for a folder or an individual report.
Following the click on the Publish command, the report is immediately available on the Microsoft CBI portal. A quick check in Internet Explorer gives the power user the confidence that this process finished successfully. Figure 8 shows an example.
Figure 8. Publishing a ProClarity report on the Microsoft CBI portal
End users see all their reports in the Microsoft CBI report catalog. They can browse through the folder list or search for reports based on categories and metadata, such as report type, department name, and geographical information. For example, an executive manager can search for scorecards across all business units of a specific geographical region and have these scorecards displayed under each other. Of course, it is also possible to open each report separately. In either case, displaying reports from any location is easy because all reports are centrally available in Microsoft CBI.
Figure nine shows two reports in the same browser window. However, the end user can also select different types of reports because the reports pane is not application specific and works simultaneously across all business intelligence solutions available on the Microsoft CBI portal. The end user selects the desired reports in the catalog pane, clicks Load Reports, and the reports pane shows the business information side by side in the same browser window for convenient analysis and comparison. The user can also minimize and maximize individual reports.
Figure 9. Specifying multiple default reports in Microsoft CBI
The Microsoft CBI report catalog also supports customization, so that users do not need to search for their favorite reports each time they go to Microsoft CBI.
The most important Microsoft CBI user interface personalization features are:
- Default reports As illustrated earlier in Figure 9, the user can right-click any report and then click Set as Default Report. Default reports have the suffix (Default) to indicate that the reports pane loads these reports automatically when the user displays the Reports tab.
- Favorites Because more than 2,000 reports are available, Microsoft CBI gives users a feature to narrow down the listed reports. One common way is to add preferred reports to a Favorites folder. To accomplish this step in the Microsoft CBI report catalog, the users only needs to right-click the desired report and then click Add to Favorites. The report then appears as an individual item in the Favorites folder.
- Saved filters End users with a large number of reports might find additional filtering capabilities helpful. On the Advanced Filter tab, the user can define and save filter settings similar to a template. Then, the user can apply the filter on the General tab to list only those reports in the folders that meet the filtering criteria. In addition, the user can have multiple predefined filters and specify one as the default view on the General tab. This feature is particularly useful for managers that work with varying types of reports during different periods, such as financial reports at the end of the fiscal year.
Ad Hoc Reporting
The customization features that the Microsoft CBI user interface displays in the reports pane depend on the business intelligence software that renders the report.
End users can also add external reports to their favorites. Another option that end users can use to perform ad hoc reporting is the export of report views to Office Excel.
Sharing of Reports
Another important feature of the Microsoft CBI report catalog enables Microsoft CBI users to share individual reports directly with other Microsoft CBI users. The only prerequisite is that the all users have the required access permissions to the Microsoft CBI portal.
Figure 11 illustrates how report sharing works on the Microsoft CBI portal.
Figure 11. Sharing business reports on the Microsoft CBI platform
In the example depicted in Figure 11, the manager performs the following steps to share a report with three employees:
- The manager creates a group under a special folder called Groups and grants the employees access permissions.
- The manager right-clicks the desired report and then clicks Add to Groups to add the report to the group in order to share it with these employees.
- The employees can see the report in the catalog pane and select it to analyze business information.
Onboarding to the Microsoft CBI Portal
For BIE, Microsoft CBI onboarding refers to the process of transitioning business units from isolated business intelligence islands to the Microsoft CBI infrastructure. In this transition, business units must receive a clear path to move to the Microsoft CBI Portal, including clearly defined roles and responsibilities. With every Microsoft CBI release, the Microsoft CBI team reviews the onboarding process to ensure that it remains in line with business requirements and executive direction.
Onboarding Roles and Responsibilities
Several teams must work together to migrate the reporting solutions of a business unit to the Microsoft CBI portal. On the side of the business unit, the Business IT team develops and tests reporting solutions. On the side of BIE, the Microsoft CBI Platform team and the Microsoft CBI Production Support team share responsibilities. Responsibilities of the Microsoft CBI SME include managing all aspects of the onboarding process, evangelizing the process, training customers, and prototyping for customers. The Microsoft CBI SME works very closely with the Business IT team and reports progress to a product manager in the business unit, who assumes the responsibility of defining business requirements and provides feedback during integration and user acceptance testing.
Figure 12 illustrates the Microsoft CBI onboarding phases. The process starts with a business unit that maintains its own departmental data warehouses, application servers, and reporting solutions. The process ends with the Microsoft CBI team hosting the reporting solutions on the Microsoft CBI application servers. Only the business data remains within the environment of the business unit.
Figure 12. The Microsoft CBI onboarding process
The preferred approach to transition a business unit from an isolated business intelligence environment to the global Microsoft CBI infrastructure includes the following phases:
- Getting started When the CIO charged Microsoft IT to adopt the Microsoft
CBI solution across the entire IT organization, an executive memo informed all Business
IT units within Microsoft IT about the strategic nature and importance of the Microsoft
CBI solution. This memo also included where to find more information about Microsoft
CBI. For example, business units can attend presentations and demonstrations that
Microsoft CBI SMEs provide. Business units can also go directly to the Microsoft
CBI site for hands-on experience and to access online documentation.
To initiate the onboarding process, the business unit must fill out an onboarding questionnaire based on a Microsoft Office InfoPath® 2007 form. This questionnaire gives the Microsoft CBI team details about the current business intelligence tools and data, the estimated number of power users and end users, the primary contacts who will work with the Microsoft CBI team during the onboarding process, and the desired onboarding timeline. The Microsoft CBI team maintains this information in an onboarding database to track status and manage capacities. Among other things, the gathered information helps the Microsoft CBI team to estimate onboarding requirements, including hosting and support aspects. Business units and the Microsoft CBI team collaborate to determine how existing solutions and data warehouses can best be integrated with Microsoft CBI to accommodate the requirements of the business unit and provide necessary support.
- Prototyping The prototyping phase begins when a business unit agrees to onboard to Microsoft CBI. The goal of this phase is to assess technical requirements and help the business unit plan for the future. Following the successful completion of prototypes, the business unit must fill out an onboarding document that describes in detail the required configuration needed to deploy the business unit's reports based on its data sources within the Microsoft CBI infrastructure. System accounts and security settings for data warehouses are examples of such configuration information. The business unit must also specify the user accounts or security groups of end users and power users who need access permissions on the Microsoft CBI portal.
- Integration testing The Microsoft CBI sandbox environment is an integrated
test environment, maintained and supported by the Microsoft CBI team. Business units
can use their own environment for developing and testing purposes. After development
and test teams approve the reports and data as part of the Microsoft IT software
development life cycle (SDLC) process, the business unit can start integration testing
in the Microsoft CBI sandbox environment. More specifically, the business unit uses
the Microsoft CBI sandbox environment to verify that the reports work on the Microsoft
CBI portal. If there are any issues, the Microsoft CBI SME involves the developers
and testers from the Microsoft CBI team to find a solution. The Microsoft CBI team
also provides regular training sessions, Help documentation, and sample scripts
to the core set of power users who develop and publish reports on the Microsoft
After each onboarding phase, the Microsoft CBI team tracks the onboarding status to prepare for the next round. At this point, the most important information to obtain from the business unit is confirmation that the integration tests finished successfully and according to business requirements.
- User acceptance testing Following successful system integration, the business
unit and the Microsoft CBI team are ready to start the user acceptance phase. For
this purpose, the Microsoft CBI team provides a staging environment, hosted in the
data center and supported by the Data Center Operations and Microsoft CBI Production
Support teams. This is a much higher level of support than is available in the sandbox
environment. This is an important aspect because user acceptance testing involves
the power users creating the reports and the end users confirming that the reports
meet their requirements.
During the process of user acceptance testing, end users must confirm that their reports work as expected on the Microsoft CBI portal. The Microsoft CBI team keeps track of the formal approval in the onboarding document.
- Production rollout The production rollout essentially follows the same process used to deploy the solutions in the staging environment. Based on the procedures, verified during integration and user acceptance testing, the Microsoft CBI Production Support team ensures that the Microsoft CBI application servers can access the business data sources over an encrypted connection, configures access for extranet users, and grants general Microsoft CBI access permissions to power users and end users. The business unit can subsequently refine these permission settings. The business unit can then deploy its approved reporting solutions and communicate to the end users that the reports are live on the Microsoft CBI portal. Again, the Microsoft CBI team keeps track of progress and coordinates further services with the Microsoft CBI Production Support team.
Centralizing business intelligence across a large and multinational company, such as Microsoft, is a complex organizational and technological challenge. It requires a reliable business intelligence infrastructure, scalable business solutions, executive support, clear communication, tremendous coordination, and sound project planning. Although Microsoft is a unique environment, other IT organizations that want to implement solutions similar to Microsoft CBI might find the following general best practices useful:
- Get executive support Changing the business intelligence landscape of a company can be a challenge for individuals and business units alike because it disrupts established business processes in favor of new procedures and technologies. One of the most important issues to realize is that this effort is primarily about process improvement and operational innovation and not about technology. Even on grounds of compelling business benefits, transforming business processes at a company-wide scale cannot succeed without executive involvement, approval, and active support.
- Provide demonstrations, presentations, and guidance from the beginning Another important element is compelling product demonstrations and presentations that clearly show the benefits and advantages of the business intelligence solution. It is important to use visually attractive prototypes and presentations based on realistic business scenarios. For example, the Microsoft CBI team spent almost three months with three developers on this effort. The initial prototype helped the Microsoft CBI team get the necessary funding. The prototype work continues with every new feature and release to win more business units for Microsoft CBI and maintain the support of upper management. The Microsoft CBI team also created several small training and demonstration videos, which in conjunction with Microsoft Office Live Meeting sessions and on-site presentations help Microsoft CBI SMEs promote Microsoft CBI across the enterprise. Prototype work, demonstrations, presentations, training, and continuous support are keys to ensure a very high level of customer satisfaction.
- Become a process leader When business processes change, business units need a clear understanding of the motivation behind the changes and precise directions. The onboarding process must happen in a predictable way even if certain aspects do not go as planned during system integration and user acceptance phases. Developing change management processes based on established frameworks, such as the Microsoft Solutions Framework, is vital to move business units in a logical order of events to the new environment. It is also important to define key roles and responsibilities, implement clear communication paths, and report progress to business units frequently. It is necessary to document the onboarding processes and to keep these documents up to date.
- Involve the customer and maintain project documentation To complement the onboarding processes, it is important to integrate members of the business units actively into the design process as stakeholders and members of steering committees, document expectations, business requirements, and priorities, and to obtain support for changes. The Microsoft IT SDLC involves business units at all key stages during the project life cycle. Accordingly, the Microsoft CBI team collaborates with the business units to define the right scope based on prioritized requirements, reviews the design and obtains regular feedback to ensure that the delivered solutions are relevant and useful, and maintains requirement documents and technical specifications that clarify expectations and details about Microsoft CBI functionality.
- Set realistic goals This is a basic concept, but it is a tricky task in a global project that delivers results to managers and executives in all positions and locations of the company. Starting with a small and basic solution that delivers immediate results is often better than developing a deluxe version that tries to solve all the problems at once. Adding features and capabilities based on user feedback over time and avoiding excessive customizations are keys to success. It is important to start with a solution that meets general business needs and implement a feedback process to convert customization requests into feature requests that are useful for all users.
- Focus on security and scalability from the beginning This should be a best practice in any software development project, and it is vital for a solution that provides access to sensitive business information. Designing for security means that the solution works based on the principle of least privilege, validates all user input, provides fault tolerance, and responds in a managed way to system exceptions and other critical states. Designing for scalability means that the solution can accommodate any number of users through additional server hardware if necessary.
- Plan for sufficient project resources The company-wide deployment of a centralized business intelligence environment poses software integration challenges. Individual software packages do not integrate seamlessly. Bringing these products together onto a unified business intelligence platform takes effort, knowledge, and skills. Providing the required resources in terms of time, money, and people is a key to success.
- Provide training and support Business units need a substantial amount of assistance during the onboarding process. Providing training, technical documentation, and support to power users and end users is critical to the success of the onboarding process. Users need to understand the advantages of the new environment and the benefits of learning how to use new business intelligence tools. The better the training, Help system, and technical documentation included in the solution, the lower the support requirements and an operational cost after a business unit has accomplished the onboarding process.
- Help business units target the right users with the right types of reports The capabilities that the full stack of Microsoft business intelligence products provides might initially overwhelm business units that complete the onboarding process. Different business intelligence tools cover different needs. Business units must receive expert advice regarding the typical use of reporting solutions.
Centralizing business intelligence in a company-wide infrastructure based on Office SharePoint Server, SQL Server Reporting Services, and SQL Server Analysis Services can help IT organizations to deliver value-driven services and to contribute to business performance. Business benefits range from lower operational costs, streamlined business processes through higher productivity of mission-critical employees and decision makers, and closer collaboration with partners and vendors.
Microsoft Office SharePoint Server provides the foundation to implement a reliable, scalable, and security-enhanced infrastructure that provides centralized access to business data from disparate sources. This foundation facilitates broad sharing of business information while helping to protect sensitive data. An Office SharePoint Server portal can also provide access to additional information. For example, departmental document libraries and custom application pages can share helpful documentation, announcements about upcoming events, contact information, and useful links to other content.
Microsoft IT's decision to use Office SharePoint Server for business intelligence consolidation was strategic. Office SharePoint Server enabled Microsoft IT to establish a hosted, integrated, security-enhanced, and configurable Microsoft CBI infrastructure. This infrastructure provides a continuum of reporting and business intelligence capabilities where business units can give the right information to the right users in the right format. The Microsoft CBI solution expands the reach of the Microsoft CBI report catalog and an internal line-of-business application, and it includes the entire suite of Microsoft business intelligence software.
The core of the Microsoft CBI infrastructure is a user interface portal that provides seamless access to reporting tools and data across the company. BIE maintains this portal in addition to the application servers, reporting solutions, and associated metadata and catalog databases for all business units that use the Microsoft CBI portal. The individual business units remain in control of their databases and data warehouses. To support partners and vendors in addition to corporate users, BIE deployed the application servers on the Microsoft extranet and on the corporate network.
To help business units transition from isolated business intelligence silos to the Microsoft CBI platform, BIE provides a comprehensive lab environment. This environment consists of demonstration systems, sandbox environments, and staging environments that mirror the configuration of the production environment closely. Business units can use these test systems to evaluate Microsoft CBI according to their business requirements. They can also perform integration testing and user acceptance testing according to the Microsoft IT SDLC. To help power users and end users unlock the full potential of Microsoft business intelligence solutions on the Microsoft CBI platform, BIE provides assistance, training, and production support.
One of the advantages of the Microsoft CBI portal is that analysts and power users within each individual business unit can continue to use Dashboard designer, Report Builder, and Office Excel to develop reports. Meanwhile, end users can share standard and ad hoc reports, scorecards, and other business intelligence tools in a single, personalized location with other users without having to involve IT staff nor complex administrative tools. On the Microsoft CBI platform, managers and employees can find their favorite business reports with a single mouse click and display multiple reports side by side for convenient comparison. The Microsoft CBI portal connects employees and decision makers with the right information that is relevant to their specific needs. It enables the company to transform business insight into decisions and actions more rapidly and maintain its competitiveness.
For More Information
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information through the World Wide Web, go to:
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
© 2007 Microsoft Corporation. All rights reserved.
Microsoft, Active Directory, Excel, InfoPath, Internet Explorer, PerformancePoint, SharePoint, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
All other trademarks are property of their respective owners.