Increasing Productivity by Empowering Business Users with Self-Serve BIKen Withee December 2009
Summary: Enabling end-user self-serve business intelligence (BI) is a critical step in modern business. The latest wave of Microsoft products enable self-serve BI by using tools and technologies such as Office SharePoint, PowerPivot, Business Connectivity Services (BCS), Office Excel, and Report Builder.
IntroductionCreating a self-serve environment is a critical evolutionary step in any software environment. Imagine if the IT department had to be involved in sending every e-mail message. The thought is almost laughable. When an e-mail system has been put in place, it is maintained by IT, but users are free to send, receive, and manage their e-mail through self-serve tools such as Office Outlook, Thunderbird, Lotus Notes, Eudora, and Pine. Business intelligence has become increasingly important—many would say critical—in modern business. Getting the right information to the right person at the right time is the promise of BI, but this is often easier said than done. Providing a self-serve mechanism for business users is the next step in the BI story. Providing self-serve BI allows for an exponential increase in the usefulness of BI by removing the large hurdle that is involved in the back-and-forth interaction of business users and the technical team. In essence, business users can answer their own questions as they arise, without having to pause to involve the IT team with every detail. This article explores the latest Microsoft tools and technologies that enable self-serve BI. In particular, it outlines the latest wave of SQL Server, Office SharePoint, and other Office products and how they can be used to provide self-serve BI to business users.
PowerPivotWhen it comes to BI, there is a constant battle between business users and IT. Business users know the functional components of the business; they understand fully what they want to analyze and the questions that they want to answer. The IT team understands the structure of the data, the models, the cubes, data flow from the operational systems, the data warehouse, and the control mechanisms for the data. Business users often feel that IT is always saying “No” when they make a request for a data cube, report, chart, graph, or even raw data. The members of the IT team often feel that users are making unreasonable requests, with timelines that equate to boiling the ocean by first thing tomorrow morning. Self-serve BI attempts to solve this problem by providing a mechanism that will satisfy the analytical needs of users and provide the governance and control that IT requires. In its latest release of Office Excel 2010 and Office SharePoint Server 2010, Microsoft attempts to provide the self-serve analytical needs of business users in a feature that is known as PowerPivot. PowerPivot and Office Excel 2010PowerPivot is an add-in to Office Excel 2010 that, from the point of view of business users, simply allows them to pull massive amounts of data into Office Excel and then analyze it in a familiar environment. The premise is that users are already familiar with Office Excel and prefer to work in Office Excel, but are forced to go through IT when they are dealing with large and complex data. The IT team then goes through the process of modeling the data and building OLAP cubes that then can be used by the business users to perform their analysis. This back-and-forth nature is the root of a great deal of frustration for both sides. PowerPivot attempts to remove this interaction by providing features in Office Excel that allow business users to pull in and analyze very large amounts of data without having to interact with IT. When users have completed an analysis, they can upload the Office Excel document to an Office SharePoint library from which it can be shared with the rest of the organization. Because these PowerPivot documents live on the Office SharePoint server, IT maintains governance and control over the entire process. PowerPivot ArchitectureFrom an IT point of view, the predictable thing about business users is that they just want things to work; they are focused on their business role and see technology only as a tool that helps them perform their tasks. Under the covers, PowerPivot (formerly known as codename “Gemini”) is an incredibly complex piece of technology in which Microsoft has invested heavily to bring to market. Above the covers, however (and what IT presents to the business user), PowerPivot is simply Office Excel. Many business users will not even care that a new technology has been introduced; they will just know that the new version of Office Excel can solve their problems with analysis of massive and complex data sets. The technology that provides Office Excel the ability to analyze millions upon millions of rows of data in line with what business users are expecting can be found in memory. In particular, the data is loaded into memory in what is known as in-memory column-based storage and processing. In essence, users are building their own in-memory data cube for analysis with Office Excel. In order to get the amounts of data capabilities that are provided by PowerPivot, the in-memory data structure is highly compressed and read-only. The engine that is responsible for compressing and managing this in-memory data structure is called VertiPaq. PowerPivot and Office SharePoint Server 2010PowerPivot for Office SharePoint accomplishes two important tasks. The first is that it provides a home for the PowerPivot documents that users create in an environment that IT controls. The second is that it provides users throughout the organization the ability to view and interact with PowerPivot documents by using nothing more than their thin-client Web browser. For business users, consuming a PowerPivot document is as simple as going to their Intranet site and clicking a document. The document then renders in their browser, and they can interact with the document and perform their analysis. In fact, the consumers do not even need to have Office Excel installed on their local computers to interact with the PowerPivot document. The result is that business users focus on their business analysis and are oblivious to the technology that enables the interaction. PowerPivot Control and GovernanceOne of the biggest challenges in IT involves spreadsheets that become critical to business users without the knowledge by IT of their existence. Office SharePoint provides functionality that gives IT the ability to track PowerPivot usage patterns. As PowerPivot (Office Excel) documents bubble up in importance, IT can monitor and identify the source data and business functionality. Having visibility into which PowerPivot documents are most frequently used by business users is critical in developing management and disaster-recovery plans. For example, if an extensively used PowerPivot document is pulling data from an operational system that was thought to have minimal importance, IT has achieved visibility into what is truly important to business users. IT is then better able to accommodate the needs of their users going forward.
Business Connectivity Services (BCS)The major promise of BI is getting the right information to the right person at the right time. When people think of BI information, they usually think of numeric data and analytics. However, information takes many forms, including nonnumeric information that lives in a plethora of applications and databases. Some of the most popular systems that require integration include line-of-business (LOB) systems—often called enterprise resource planning (ERP)—such as SAP, Oracle, Dynamics, Lawson, Siebel, and Sage, to name just a few. Access to these systems often is cumbersome and time consuming. A typical interaction involves using specialized applications and screens to access or update the information that lives in the ERP system. BCS is a technology that is included in Office SharePoint Server 2010 and provides integration and interaction (read/write) with the information that is contained in external systems, as shown in Figure 1.
Figure 1. BCS allows Office SharePoint 2010 read/write interaction with external systems.
Integrating a front-end user-facing portal with external systems provides a single self-serve access point, without the need to install specialized software on end-user desktops. For example, imagine a customer-support representative taking an order from a customer. There might be one application for entering the order, another for taking notes on the call, and yet another for researching questions that the customer has about products. If the customer notifies the support representative of an address change, the representative must access also the system that stores the customer information and make the update. Business users have no need or desire to understand where their data and information lives; they just want to interact with the right information at the right time, in as easy a manner as possible. The BCS technology provides a mechanism for the consolidation of access points to external systems into one convenient portal location. Consolidation greatly reduces the complexity of end-user job functions by providing a single destination to perform business tasks and find business information. Reducing the complexity for users also reduces the number of disparate requests that IT must service. Instead of IT having to support connections, security, audits, and one-off projects for multiple systems, IT only must set up the connection in the portal once and then support the single portal framework. In addition, moving everything to a single framework restores control over the external systems to IT by moving users into a single environment. BCS ArchitectureBCS is an evolution of the Business Data Catalog (BDC) from Office SharePoint 2007. BCS is baked into the Office SharePoint 2010 platform and the Office 2010 clients. BCS uses three primary components that enable the connection to external systems. These include Business Data Connectivity, an External Content Type Repository, and External Lists. In addition the BCS client is included also in the Office 2010 applications. The External Content Type Repository and External Lists allow solution architects not only to describe the external data model, but also to define how the data should behave within Office SharePoint and Office. BCS connections are XML-based and include functionality to connect to SOA-based services. When a connection file has been set up, it can be used throughout the Office SharePoint environment by end users.
Report BuilderReport Builder is an application that is designed to provide end users the ability to create and publish their own SQL Server Reporting Services (SSRS) reports. Report Builder was designed for the end user with the comfortable look and feel of other Microsoft Office products. In particular, Report Builder includes the Office Ribbon at the top of the report-design surface, as shown in Figure 2.
Figure 2. Report Builder is designed to provide business users with an easy-to-use report-development environment. (Click on the picture for a larger image)
The underlying report-engine code base is shared with the Business Intelligence Development Studio (BIDS) report-design environment. This single underlying code base was designed to provide functionality for end users to create reports by using Report Builder and for developers to design reports by using BIDS with functional parity, due to the shared underlying code base. Report Builder uses ClickOnce technology for deployment. ClickOnce allows users to click the link in either Report Manager or Office SharePoint and download the application to their desktop computers. ClickOnce alleviates the need for a mass install by the IT department. When Report Builder must be upgraded or updated, the new bits are automatically downloaded to the user desktop without the need for manual updates. SQL Server Reporting ServicesSQL Server Reporting Services (SSRS) is the reporting component of the SQL Server product. The SSRS architecture consists of a Windows Service that is designed to render reports and a couple of SQL Server databases that are designed to store content, configuration, metadata, and temporary rendering information. SSRS reports consist of an XML-based format that is called Report Definition Language (RDL). SSRS reports—or RDL files, in other words— can be created by using either BIDS (Visual Studio) or Report Builder. An SSRS database can be installed in either stand-alone mode or integrated mode. When it is installed in stand-alone mode, a Web application that is known as Report Manager is responsible for storing, managing, and providing the reporting environment to end users. When it is installed in integrated mode, Office SharePoint takes over, and Report Manager is no longer used. Although SSRS is a component of the SQL Server product, it is not restricted to pulling data from only a SQL Server database. Using Report Builder, end users can pull data from a number of different connection types, including OLE DB, ODBC, Analysis Services, Oracle, XML, Report Models, SAP Netweaver BI, Hyperion Essbase, and TERADATA. Self-Serve ReportingBusiness users launch Report Builder by clicking a link in either Report Manager or Office SharePoint. As soon as Report Builder is launched, business users create connections to data sources and build reports. The reports can then be saved into either Report Manager or an Office SharePoint Document Library. Other users can then connect to Report Manager or the Office SharePoint site to view the available reports. The IT team can maintain control by providing “approved” reports, monitoring usage, and limiting access to the servers that contain the source data. When SSRS is installed in integrated mode, reports can take advantage of the Office SharePoint Enterprise Content Management (ECM) features, such as versioning, security, check-in/check-out, and workflow. In addition, the responsibilities of IT are reduced, because only a single content-management system must be maintained and managed. In the Office SharePoint environment, an SSRS report is nothing more than a content type, such as an Office Word document or Office Excel spreadsheet.
Office Excel and Excel ServicesOffice Excel has to be one of the most prolific and ubiquitous data-analysis applications in the world. Nearly every organization uses Office Excel in one capacity or another. Some businesses use Office Excel almost exclusively to run and manage their data needs. One of the most beloved data-analysis features of Office Excel is the PivotTable. A PivotTable provides an easy-to-use drag-and-drop interface for slicing, dicing, grouping, and aggregating data. Beginning with Office Excel 2007, end users have the ability to connect to and utilize the back-end SQL Server Analysis Services (SSAS) server from the comfort of Office Excel on their desktops. As a result, end users can browse and analyze OLAP cubes and tap into the powerful data-mining capabilities that SSAS provides. Using Office Excel to Browse SSAS CubesWhen connecting to and analyzing an SSAS OLAP cube, the business- user experience is nearly identical to analyzing a local Office Excel pivot table. A user makes the connection by selecting the From Analysis Services option on the Get From Other Sources menu of the Data tab, as shown in Figure 3.
Figure 3. Making the connection to an SSAS OLAP cube is accomplished on the Data tab of Office Excel. (Click on the picture for a larger image)
When the connection has been made, users can browse the cube and perform an analysis, just as they would a local pivot table, as shown in Figure 4.
Figure 4. Browsing an SSAS OLAP cube as a PivotTable in Office Excel. (Click on the picture for a larger image)
Office Excel and SSAS Data MiningA Data Mining add-in for Office Excel is available to provide access to the data-mining algorithms that are contained within SSAS. Installing the add-in provides a Data Mining tab in Office Excel with which users can access the algorithms that are contained within the SSAS Data Mining engine. The Data Mining tab in Office Excel is shown in Figure 5.
Figure 5. The Data Mining tab provides end users the ability to interact with the data-mining capabilities of SSAS. (Click on the picture for a larger image)
The SQL Server Data Mining Add-In provides the following functionality:
Office SharePoint 2010 and Excel ServicesOffice Excel has to be one of the most popular and prominent data-analysis applications. Business users create Office Excel spreadsheets that perform everything from ad-hoc analysis to fully featured profit-and-loss calculators. When these applications are under the radar, they cannot be backed up or supported by IT. The result is that business users become frustrated with IT for not being able to support them, and IT becomes frustrated with users who are working outside the provided system. Business users feel that IT is not giving them the correct tools, so they go and create their own by using Office Excel. The IT team members feel that the business users are going around IT and have no right to complain when the stuff that they created on their own breaks. To complicate matters further, users often e-mail Office Excel documents to other users, who e-mail those documents again. This creates multiple mutations of critical business functionality, as shown in Figure 6.
Figure 6. Office Excel document is e-mailed to users, who modify and e-mail again—creating multiple mutations of the document.
Excel Services in Office SharePoint 2010 attempts to solve the issues with Office Excel by providing a home for Office Excel documents in the Office SharePoint environment that is controlled and governed by IT. When Office Excel documents are saved in an Office SharePoint document library, there is one version of the document, and users can connect and use the document without spawning multiple mutations, as shown in Figure 7. The document can also take advantage of the ECM features of Office SharePoint, including versioning, security, check-in/check-out, and workflow. In addition, IT is able to gain oversight, visibility, and control over the Office Excel applications with which users are performing their business tasks.
Figure 7. Office Excel document is saved to Office SharePoint and accessed by users throughout the organization by using only a thin client (Web browser).
Office Excel documents in Office SharePoint can use connection files that are managed by the IT department. For example, IT can create connection files to the source systems and then simply point end users to these approved connection files. This alleviates the need for IT to service numerous requests for connection information for every Office Excel file that is created for a business problem. One of the most powerful features of Office SharePoint is called Excel Services. Excel Services is the ability to render Office Excel documents in a thin client (Web browser). An important Office Excel document can be saved to a document library, and the entire organization can then view and interact with the Office Excel document without having to leave their browser. The consumers of the document just navigate to their company intranet and click the Office Excel document. This functionality is particularly powerful when thinking about rolling out Office Excel 2010 to provide PowerPivot functionality. Only a handful of business users actually produce content, with the rest just consuming it. Using Excel Services, the only users who will need to have Office Excel 2010 are the producers of content. The consumers can interact with the PowerPivot documents without ever having to leave their browser or install the latest version of Office Excel.
ConclusionReport Builder is an end-user report-development tool that provides end users the ability to create their own SSRS reports without the need for an SSRS expert. The Report Builder application uses the same underlying code base as the Business Intelligence Developer Studio (BIDS), which is designed for professional developers. Allowing end users to build their own reports takes a tremendous amount of resource load off of the technical team—allowing them to focus on the underlying data warehouse, instead of the tedious report-design process. Office Excel is one of the most ubiquitous data-analysis programs that are used in the world today. Microsoft has recognized that people are already comfortable with Office Excel and often do not want to change to another application for data analysis. Office Excel can be used as a client for the back-end SSAS server. In particular, users can connect Office Excel to OLAP cubes that are hosted on SSAS and slice and dice data in the same fashion in which they would use a local PivotTable. In addition, Office Excel can be used as a client for the data-mining functionality of the SSAS server. The power of the data-mining algorithms can be leveraged with data that is contained in a data warehouse or data that is local in Office Excel spreadsheets. In both situations, Office Excel acts as a client for the SSAS server, which provides end users with the power of SQL Server and the comfort of Office Excel. One of the biggest pain points in an OLAP environment is the amount of effort that it takes to organize and develop data cubes. Business users have to coordinate with BI developers to identify the correct data, relationships, and aggregates. Requirements are constantly shifting, and, by the time a cube has been developed, the requirement has changed and must be reworked. Providing end users the ability to create their own data cubes in an easy-to-use environment is extremely important to the evolution of BI. PowerPivot provides the ability for users to create in-memory cubes right on their desktops in the familiar Office Excel environment. The cubes can then be uploaded to an Office SharePoint site and accessed by users throughout the organization. Office SharePoint 2010 includes BCS, which provides read/ write integration between Office SharePoint and external systems. Consolidating functionality into the Office SharePoint environment reduces complexity for end users and provides a one-stop shop for all content, including BI, reporting, analysis, communication, and collaboration. In addition, IT can consolidate focus from multiple access systems into a single portal system. A self-serve environment is a key inflection point in any technological solution. Placing the power of a solution in the hands of end users unleashes an exponential power that can only be realized through a self-serve environment. Surfacing BI information into a collaborative environment such as Office SharePoint enables a new form of BI that is called human business intelligence (HBI). HBI merges the traditional analytical capabilities of a BI solution with the knowledge of the people throughout the organization. The latest wave of Microsoft products are interwoven to provide a single cohesive self-serve environment for end-user content creation. This places the power of content creation and analysis in the hands of the end users. Without the intensive back-and-forth BI-development process that currently exists, users are free to expand their knowledge exponentially and on their own time.
Resources
About the AuthorKen Withee ( KWithee@hitachiconsulting.com) is a consultant with Hitachi Consulting and specializes in Microsoft technologies in Seattle, WA. He is author of Microsoft Business Intelligence for Dummies (Hoboken, NJ: For Dummies; Chichester: Wiley Press, 2009) and, along with Paul Turley, Thiago Silva, and Bryan C. Smith, coauthor of Professional Microsoft SQL Server 2008 Reporting Services (Indianapolis, IN: Wiley Publishing, Inc., 2008). Follow up on this topic
|
Issue 22 Index
|
.jpg)
.png "BCS allows Office SharePoint 2010 read/write interaction with external systems.")
.png "Report Builder is designed to provide business users with an easy-to-use report-development environment.")
.png "Making the connection to an SSAS OLAP cube is accomplished on the Data tab of Office Excel.")
.png "Browsing an SSAS OLAP cube as a PivotTable in Office Excel.")
.png "The Data Mining tab provides end users the ability to interact with the data-mining capabilities of SSAS.")
.png "Office Excel document is e-mailed to users, who modify and e-mail again—creating multiple mutations of the document.")
.png "Office Excel document is saved to Office SharePoint and accessed by users throughout the organization by using only a thin client (Web browser).")
