Understanding Microsoft Data Analysis Software and Features

With today’s complex computer software, the people who decide what information technology a business or an organization will use frequently look for software suppliers with complete, end-to-end solutions for their business needs. In the following sections, I’ll describe in more detail the Microsoft software applications you can use for storing and analyzing data and the situations in which you would use a specific product. First, let’s review some of the requirements and methods of data storage.

Data Storage

Before you can fully understand how to use the Microsoft data analysis software applications, you should understand the differences between file-based data storage applications and server-based data storage applications.

File-based data storage applications such as Excel and Access provide graphical data analysis features in addition to lightweight data storage capabilities. Excel is better suited to storing nonrelational data lists (groups of data records that stand on their own), while Access is designed to store relational data tables (groups of data records that rely on other data records to describe business facts) and can also store a larger number of records than a single Excel worksheet. Excel can store only about 65,000 records per worksheet, while Access can handle up to 2 gigabytes of data per database file.

Server-based data storage applications such as Microsoft SQL Server 2000 provide additional database management features such as enhanced data recovery, faster data retrieval, distribution of data analysis requests among groups of networked computers, and the ability to store and manage multiple databases at the same time. SQL Server 2000 can store about 2 terabytes of data per database that it manages.

You may want to use a nonrelational file-based data storage application such as Excel when

• You need to store only a relatively small amount of data (a few hundred megabytes or less).

• You want to store nonrelational data.

• You are not necessarily concerned about recovering your data should a disaster such as a power failure, a computer hardware failure, a computer virus, or electronic file corruption occur.

• Only a few business computer users (a couple of dozen at most) will be entering data into the data source or accessing the data from the data source at any given time.

On the other hand, you should use a relational file-based storage application (such as Access) or a server-based storage application (such as SQL Server 2000) when any of these conditions apply:

• You want to store a moderate to a relatively large amount of data.

• You want to store relational data.

• You are concerned about recovering your data if a disaster occurs.

• A moderate to relatively large number of users (a couple of dozen or more) are entering data into the data source or accessing the existing data from the data source at any given time.

Microsoft data analysis tools such as the Office Web Components and Data Analyzer do not store data at all; they connect to sources of data and then display data in a number of different ways, depending on the data analysis task you’re trying to accomplish.

Over the next several pages, I’ll introduce you to these data storage and data analysis applications and their features.

Microsoft Excel

Microsoft Excel is used by millions of computer users around the world. Chances are that you, your employees or coworkers, or the people who will join your organization in the future are familiar with Excel. There’s an even greater chance that most computers in your organization already have Excel installed. It makes sense, then, for you and others in your organization to build on your basic skills and use Excel to store and analyze data.

Excel can be used not only to store data on worksheets, but also to analyze and share that data with others. Many organizations that once relied on expensive mainframe computers or outsourced their data analysis tasks can now assign many of these tasks to the average business computer user. Excel has a wide range of data analysis functions, a large variety of chart types, and specialized add-ins that encompass data analysis capabilities that used to be understood only by corporate financial analysts or executive financial specialists.

In addition to its analysis of simple lists of facts and figures, Excel also integrates with a pair of potent data analysis tools: PivotTable reports and PivotChart reports. The reports these tools generate offer summarized, interactive, highly graphical analyses of large amounts of data. PivotTable reports and PivotChart reports allow users to switch from one view of their summarized data to another by rotating rows and columns, in many cases with just a few quick mouse and keyboard actions. Better yet, the skills learned in using PivotTable reports and PivotChart reports to analyze nonrelational data can be applied to relational and hierarchical data as well.

Excel also allows you to work with many sources of data that are available on the Web. Excel can query Web data and import it into a worksheet that can be stored for analysis and reference. Excel can also be set up to query a Web data source at specified time intervals and import any data that has changed. You can use Excel with Office Web Components to expose the business data stored in worksheets to the Internet or an intranet.

Excel 2002 has built-in support for Extensible Markup Language (XML) data, a data format that transcends the binary formats used by specific software applications. (For more information about XML, see the section “Working with XML Data” in Chapter 2.) And, of course, Excel, as part of Microsoft Office, can be used with applications such as Word, Access, PowerPoint, and Outlook to create complete business data analysis solutions.

Microsoft Access

Although Access does not have the same number of analytical and statistical functions and add-ins as Excel, Access is uniquely qualified to handle relational data.

Relational data refers to groups of discrete data records that cannot fully describe business facts on their own. For example, a group of records might describe information about customers who purchase goods, but the details about the goods purchased are contained in another group of records. The groups of records are related to each other by a unique identifier called a key. In this case, the key could be a unique customer ID; each record in the group of goods purchased records is related to the customer information records by the unique customer ID. By using these keys, Access has the built-in ability to record, relate, look up, and analyze data by cross-referencing several groups of data at once.

Access also has these features:

• Built-in data views such as Datasheet view, PivotTable view, and PivotChart view. You can switch between these views with just a few mouse clicks.

• The ability to filter data by selection or by form, which provides more filtering options than the built-in filtering features of Excel.

• The ability to create Web pages (known as data access pages) that enable you to publish highly customizable graphical user interfaces for your data, which can then be viewed through a Web browser.

• The ability to script automated data access and analysis routines, known as macros, without needing to learn a programming language such as Microsoft Visual Basic for Applications.

• Several relational data wizards that can help you build simple data entry and data analysis applications.

Microsoft Office Web Components

Microsoft Office Web Components are used to build Web-based data analysis tools that have the familiar look of Excel spreadsheets, PivotTable reports, and PivotChart reports. The components can be embedded in Web pages or Web- based applications that you can then use to publish interactive data over an intranet, analyze data in a Web browser, or retrieve data from an intranet for local storage in an Excel spreadsheet.

There are three Office Web Components:

• The Spreadsheet Component includes Excel features such as loading XML data files, worksheet functions, multiple worksheets, and wrapped text. You can also publish worksheets from Excel to the Web, hosting the information in a Spreadsheet Component.

• The Chart Component includes features such as three-dimensional chart types, graphing multiple charts within the same Chart Component, conditional formatting, and custom annotations inside a chart.

• The PivotTable Component looks and behaves much like its Excel counterpart, and it supports features such as filtering and live data updating.

The Office Web Components can access not only nonrelational data, but also relational and multidimensional data. This provides you with access to almost all of your data without having to learn a suite of unfamiliar software application features.

You will learn more about the Microsoft Office Web Components in Chapter 6.

Microsoft SQL Server 2000

Microsoft SQL Server 2000 is a group of high-performance, server-based data software applications. These applications are very fast, can store lots of data, can recover from disasters and downtime quickly, and expose their features to a wide variety of data analysis applications. Because many of the SQL Server 2000 applications can be installed only on network computer servers, SQL Server 2000 takes advantage of server features such as enhanced security, ease of access from multiple computers, and centralized application management and control.

SQL Server 2000 features include

• The ability to access SQL Server 2000 databases over the Web.

• English query, which enables you to frame business questions in plain English phrases instead of in a complicated computer language.

• Distributed views, which allows an organization to balance data analysis tasks over several computers running SQL Server 2000 in a computer network.

• Enhanced indexing, which organizes data so that it can be located much more quickly (similar to looking up telephone numbers in a directory or speed dialing.)

• Automatic database management tools that take a lot of the time and guesswork out of performing database backups, fine-tuning database indexes, and alerting database administrators when problems occur.

• Data Transformation Services, which allows for a wide variety of data from different formats to be imported and translated into a format that SQL Server 2000 can understand.

For hosting business data analysis solutions on individual computers that are not network servers or that communicate only with a small workgroup of computers, Microsoft SQL Server 2000 Desktop Engine (often referred to as MSDE 2000) provides a low-cost, lower-resource alternative. For more information about MSDE, see the next section.

For analyzing larger amounts of data, Microsoft SQL Server 2000 Analysis Services is designed to summarize groups of data fields prior to a request for that information. Rather than you asking for a specific data summarization and waiting while the data analysis application calculates the results, SQL Server 2000 Analysis Services most likely has the result figured out already. This provides very fast performance, even with millions of data records.

Although the SQL Server 2000 family of applications supports business data analysis, the user interfaces these applications include for performing data analysis can be somewhat difficult to learn. Because applications such as Excel and Access perform almost all of the data analysis tasks that the features built- in to SQL Server 2000 can, this book will not cover SQL Server 2000 data analysis features. For more information about SQL Server 2000 data analysis features, go to the Microsoft SQL Server Web site at http://www.microsoft.com/sql.

Microsoft SQL Server 2000 Desktop Engine

The Microsoft SQL Server 2000 Desktop Engine is a lightweight version of Microsoft SQL Server 2000. Its system requirements are less stringent, and it has fewer features than SQL Server 2000.

MSDE 2000 is ideal for individual computer users who need SQL Server 2000 features—for example, faster application performance and better disaster recovery options than are available in Access—but who do not want to learn a new suite of database management tools.

Because MSDE 2000 is a subset of SQL Server 2000, MSDE 2000 supports only as many as five simultaneous database actions at a time. It also supports databases only up to 2 gigabytes in size. However, this capacity is more than sufficient for most business data analysis solutions running on individual computers or on a computer serving a small number of connected workgroup computers.

Solution developers can also include MSDE 2000 in the applications they distribute. MSDE 2000 provides an ideal offline data storage application for many solutions.

MSDE 2000 can be installed from any media that includes Microsoft Access 2002 or any Microsoft Office XP software application suite that includes Microsoft Access 2002. Check the MSDE 2000 end-user license agreement (EULA) for additional installation and distribution requirements.

For more information on MSDE 2000, go to the Microsoft SQL Server 2000 Desktop Engine Web site at http://www.microsoft.com/sql/techinfo/development/ 2000/MSDE2000.asp.

Microsoft SQL Server 2000 Analysis Services

Microsoft SQL Server 2000 Analysis Services is designed to perform data analysis tasks on large amounts of data in less time. Microsoft Office data analysis applications support SQL Server 2000 Analysis Services, so you or members of your organization can get answers to data analysis questions faster and without a lot of additional training.

Analysis Services provides access to data by creating multidimensional data storage units called cubes. Analysis Services calculates summarizations from cube data ahead of time, which means that getting results from data managed by Analysis Services takes less time than making such a calculation without a cube already prepared.

You will learn more about multidimensional data in Chapter 7 through 9. For more information on Analysis Services, go to the Microsoft Analysis Services Web site at http://www.microsoft.com/sql/evaluation/bi.

Microsoft Data Analyzer

Microsoft Data Analyzer, part of Microsoft Office, can display interactive bar charts and pie charts along with conditional color formatting, quickly compare similar data distributions among hierarchical slices of data, synchronize data views across multiple data slices, drill up and down through data cubes to locate specific data summarizations, and more.

Data Analyzer can export the data that it is analyzing to an Excel spreadsheet or an Excel PivotTable report for further in-depth analysis. Data Analyzer can also export a data view to a PowerPoint slide for integration into a presentation.

Data Analyzer is designed to work only with hierarchical data that is stored in a Microsoft SQL Server 2000 Analysis Services cube or in an offline cube (*.cub) file. Fortunately, Excel lets you grab data from many other data sources and turn it into an offline cube file format that Data Analyzer can use. Offline cube files are also helpful in situations such as when you are using portable computer on an airplane, when you are analyzing data in a hotel room without a connection to you network, or when you want to display a view of your data to a customer when you’re in the field and you have no connection to live data via a wireless computer network.

You will learn more about Data Analyzer in Chapter 9.