Microsoft Data Analyzer

Yüklə 50,01 Kb.
ölçüsü50,01 Kb.

Microsoft® Data Analyzer

roduct Guide

Published: October 2001

Table of Contents


Introducing Microsoft Data Analyzer 1

Overview 1

Data Analysis for the Desktop 2

Data Analyzer Views 2

Creating Views 3

Display Types 4

Analysis Capabilities 7

Navigation 7

Filtering 7

Sorting 7

Template Measures and the

Template Measure Editor 8

Business Center 10

Find Similar 12

Publishing and Reporting Features 13

Send View by E-Mail 13

Export to PowerPoint 13

Export as Web Page 14

Export to Excel 14

Architecture and Deployment 15

Client-Server Deployment 15

Internet Deployment 16

Local Cubes 16

Extensibility and Custom Solutions 17

XML Support 17

Custom Solutions 17

Benefits 18

Introducing Microsoft Data Analyzer

Welcome to the Microsoft® Data Analyzer product guide. Microsoft Data Analyzer is an easy-to-use data analysis tool that makes sophisticated business intelligence accessible to all users, regardless of their technical expertise. Microsoft Data Analyzer enables organizations to get the maximum value from the wealth of business data stored in existing enterprise systems and collected on an on-going basis through e-commerce and other channels. Data Analyzer unlocks the hidden meanings buried in this mountain of data and puts this knowledge directly in the hands of those who need it most: decision-makers at all levels of the organization.


Data Analyzer’s innovative graphical analysis interface reveals trends, opportunities, and potential issues at a glance. By providing a complete overview on one screen, Data Analyzer helps users quickly understand their business data. It enables any business decision-maker to unlock hidden knowledge in enterprise data, expanding the analysis capabilities of desktop productivity tools.

Data Analyzer enables users to quickly view data, publish and share data, and enhances data analysis productivity. Microsoft Data Analyzer is also designed to take advantage of the powerful capabilities of Microsoft® SQL Server™ 2000 Analysis Services.

Microsoft Data Analyzer offers the following features:

  • Quickly Analyze Business Data. Unique visualization capabilities and graphical views enable people to rapidly identify opportunities and trends, find business anomalies, and review multiple sets of data in one interface for better decision-making.

  • Improve Data Analysis Productivity. Options for displaying multiple measures, such as gross profit, unit sales, or quantity, or for displaying relationships between unlimited business dimensions, such as customer, region, or product, are available in a single, easy-to-use interface. Data Analyzer includes full support for all new dimension types available in Microsoft® SQL Server™™ Analysis Services, including parent-child dimensional hierarchies.

  • Easily Publish and Share Business Data. Data Analyzer Publishing and Reporting Capabilities make it easy to share data with others, access data-analysis tools via the Web, and publish data graphically using other Office applications, such as Microsoft® Excel 2002 and Microsoft® PowerPoint® 2002.

  • Find business data anomalies and trends automatically. Guided analysis tools provide standardized questions for analyzing data; built-in template measures can easily identify key performance indicators; and filters can quickly select and filter certain criteria.

  • Leverage existing applications and computing environment. With Data Analyzer, users can easily access data analysis tools over a network, over the Web, or in remote/offline scenarios. In addition to client-server access, Data Analyzer supports remote connection to OLAP (Online Analytical Processing) cubes, as well as analysis of local cubes.

  • Integrate with enterprise business intelligence tools. Data Analyzer extends the business intelligence capabilities of Microsoft Office XP by adding rich visualization and analysis capabilities. Data Analyzer’s intuitive user interface and predefined queries complement the powerful analysis features provided by Excel 2002, Office Web Components, and digital dashboards. Designed to work with SQL Server™ 2000 Analysis Services, Data Analyzer is a valuable component of any organization’s business intelligence strategy and a key player in the Microsoft business intelligence platform.

Data Analysis for the Desktop

Successful companies already recognize the value of converting vast quantities of data collected by their e-commerce, customer relationship management (CRM), enterprise resource planning (ERP), and other systems into actionable information. Most organizations already employ some form of data warehousing, data mining, or OLAP analysis to unlock the value of their data; however, the tools and skills required to do so are traditionally the realm of dedicated analysts.

By providing easy-to-use tools for sophisticated data analysis, Microsoft Data Analyzer reduces the organization’s dependency on analysts by enabling any business decision-maker to extract knowledge from enterprise data. This dramatically increases the speed with which raw data becomes actionable information.

Companies that rely on Microsoft SQL Server™ and SQL Server™ Analysis Services to collect and store business data in their enterprise systems can take immediate advantage of Data Analyzer’s guided analysis features by giving more users access to this information and giving existing analysts a new tool. Data Analyzer enables organizations to utilize the advanced features of SQL Server™ 2000 Analysis Services, including

  • Full support for drill-through analysis

  • Support for SQL Server™ Analysis Services dimensional data types, including ragged dimensions and parent-child dimensional hierarchies

  • Support for the Analysis Services security model

  • Support for remote access to OLAP cubes via the Internet

For users and decision-makers who lack a background in the technical aspects of data analysis, Data Analyzer provides a simple way to view data. The intuitive visual interface reduces information overload, while a set of predefined queries enable users to understand their business and explore new opportunities. Guided analysis features are particularly powerful for these users.

For experienced OLAP users, Data Analyzer provides a powerful new data analysis tool that enables rapid analysis without sacrificing sophisticated query capabilities. Data Analyzer allows analysts to move quickly from graphic views of data to Excel PivotTable® dynamic views. The ability to build custom template measures using multi-dimension expressions (MDX) provides the power and flexibility to perform focused analysis.

Data Analyzer also offers opportunity for Microsoft Partners to develop custom data analysis solutions. Data Analyzer technology complements the Microsoft business intelligence platform as well as many of the industry’s third-party offerings. Data Analyzer is fully extensible, exposing its functionality through the Data Analyzer Object Model and enabling a wide variety of custom solutions and integration with existing enterprise systems.

Data Analyzer Views

Data Analyzer views present intuitive, visual representations of complex, multi-dimensional data. The flexible, dynamic views display the relationships between two or more dimensions of business data, such as time, product, and region. For example, a user can view and analyze sales and profit data across geographic regions for selected products over the last 12 months. A view connects to an OLAP database and displays the selected dimensions and measures, either in a grid (table) or as a bar or pie chart.

A view consists of a connection to an OLAP data source, a user-selected set of dimensions and measures, and a display type. Users define the properties for the view using the New View Wizard. The wizard creates a specific view type; however, users can quickly change the display type, add or remove dimensions and measures, and drill up or down by using the tool bar buttons or menu commands.

Figure 1. A typical Microsoft Data Analyzer view showing three Dimension panes.

Creating Views

Data Analyzer enables users to quickly generate views of their data using a wizard. The New View wizard prompts the user to select or add a connection to an existing OLAP cube—either an OLAP server (accessible over a network or HTTP), or an OLAP cube file (.cub) stored locally on the user’s computer.

After selecting a data source, the user can follow the wizard step-by-step, selecting from lists of available dimensions and measures, or simply click Finish to allow Data Analyzer to generate views based on the properties of the cube. If the user continues with the wizard, the Define View – Dimensions dialog box (Figure 2) lists all the defined dimensions for the selected cube, and enables the user to select one or more dimensions to analyze in the view. The dimensions displayed in the list correspond with fields displayed in field list of an Excel PivotTable® or PivotChart® bound to the same OLAP cube.

Figure 2. The Dimensions dialog of the New View wizard lists the dimensions that were defined in the database when the OLAP cube was created.

The Define View – Measures dialog box is used to select the measures (precalculated totals) that should be displayed in the view for each dimension. The measures available for selection include the measures defined when the cube was created as well as “template measures” calculated by Data Analyzer. Bar and Pie chart views display two measures (one represented by color and one by the length of the bar or size of the slice). Grid view can display multiple measures in columns.

Display Types

Data Analyzer offers three display types for viewing data in an OLAP cube:

  • Bars view displays either a horizontal or vertical bar chart of the data.

  • Pie chart view displays data in a familiar pie chart.

  • Grid view displays the data in a tabular format.

Bars view is the standard view for displaying data in Data Analyzer. Each graph bar represents a single dimension member, with the length and color representing two measures. The Color Scale allows easy recognition of patterns and exceptions by non-expert users, with low values displayed in the red spectrum, midrange values in the orange-yellow spectrum, and high values in green.

When defining the view, users define the Length property by selecting a measure to be displayed as a horizontal or vertical bar in the bar chart. Length is typically assigned to a quantitative measure. For example, if Profit is selected, the length of each bar in the chart represents the profit for that particular dimension. By comparing the lengths of each bar, users can see at a glance the difference in profit for the selected dimension. This is the same as viewing an Office Chart in Bar or Column view, and comparing the lengths of the members visually.

Users define the Color property by selecting a second, qualitative measure to be displayed as a color for each bar. Data Analyzer displays this measure in a color range from green to red. By default, high values are colored in green and low values are colored in red, while the values in between are colored in shades of yellow or orange. Typically, red represents values that might be of concern, such as low sales, high cost, or negative growth, while green represents the opposite: high sales, low cost, or outstanding growth. Users can customize the Color Scale of a view in many ways; for example, by setting green to represent low or midpoint values; by specifying a numeric range for the Color Scale; or by applying color only to filtered values.

Figure 3 depicts a typical dimension pane showing the standard Bars view. In this example, based on profitability data for a fictitious airline company, colors represent number of passengers, while length represents gross profit. The view uses aircraft type as a dimension, so the data is summarized by aircraft type.

Figure 3. A typical Dimension pane showing a Bar Chart.

In this view, the jumbo aircraft type was the most profitable for the company since its bar was the longest. However, the orange-yellow color indicates that it was among the lowest in number of passengers carried. Compare that with the super jumbo aircraft type: its bar is the third longest, which indicates that it was the third most profitable aircraft type; however, its color is green, which indicates that it was the highest in number of passengers carried.

The following examples show the same data set displayed in Pie Chart view and Grid view. In the pie chart, the length measure corresponds to the size of the slice, while color values remain the same. In Grid view, Number of Passengers and Profitability are shown numerically alongside a third measure, Total Revenue, which is displayed only in Grid view.

Figure 4. These Dimension panes show the airline data in Pie Chart and Grid View.

Analysis Capabilities

Data Analyzer enables users to perform sophisticated analysis from an intuitive graphical interface. Point-and-click navigation, filtering, and sorting enable users to fully explore their data by drilling up, drilling down, and searching for members based on similarities or specified criteria. For users seeking answers to complex business questions, Data Analyzer offers two options: the Business Center provides sophisticated queries in the form of predefined questions expressed in simple sentences, while the Template Measure Editor allows users to quickly build multi-dimensional expressions (MDX expressions) for calculating custom measures.


Data Analyzer’s graphical interface makes it easy for users to navigate through complex data sets. Users can quickly drill up or down on an entire dimension or a single member; go to a specific level; or go to the default member (defined in the cube on a per-user basis). Users navigate by clicking on bars or items of interest, and selecting from context-sensitive pop-up menus.


Microsoft Data Analyzer allows users to quickly filter the members in a dimension, displaying more information about a particular member or group of members. Filters are applied graphically: simply click on a bar to filter on that member, or right-click to select other filter options. Data can be filtered by individual dimension members, by multiple dimension members, or by applying specific criteria to a measure.

The Reverse Filter command enables users to answer specific questions, akin to simple what-if scenarios, for example, to determine how revenues would be affected by discontinuing a region or product line. The command filters the members by the opposite of the current filter. For example, if the current filter excludes certain members, the Reverse Filter command changes the filter to exclude the original members and then include the members that were originally excluded.

In addition to filtering by individual members of a dimension, Data Analyzer allows users to filter on criteria. Options available for setting criteria to a filter include:

  • Select a specific member level to use in the filter, or choose from the properties of all members in the dimension.

  • Indicate the range of values that will be used for the filter, by measure value or member name.

  • If filtering by measure value, specify from a list of operators and comparisons.


Microsoft Data Analyzer enables users to sort the members in a dimension by using different measures or by using a default sort order. For example, users could sort members by name, by unit sales, or by profitability. Users can access the sorting features from the Dimension Properties dialog box or by using one of the sorting buttons on the Dimension toolbar.

Several options enable users to customize the sorting of their data:

  • Natural Sort preserves the sort order that existed in the database. This option disables the Sort By and Preserve Hierarchy options.

  • The Sort By list contains all measures defined in the OLAP cube, plus template measures created by Microsoft Data Analyzer. Additionally, users can select any other measure, even if it is not being displayed in the view. Note that this list also contains the Name option, which allows you to sort the dimensions by their names, instead of a measure.

  • The Sort Order option is used to select whether the member should be sorted in ascending or descending order. By default, the measures are sorted in Descending order, so that the longest bars are at the top of the chart in horizontal view.

  • The Preserve Hierarchy option preserves the existing hierarchy of the data. For example, when sorting cities in two or more states by name without the Preserve Hierarchy option, all cities are grouped together, regardless of state, and sorted alphabetically. With Preserve Hierarchy selected, the same sort displays the cities grouped and sorted by state.

Template Measures and the
Template Measure Editor

When defining a view in Data Analyzer, users select measures from a predefined list. The available measures include the measures defined in the cube when it was originally constructed on the OLAP server, as well as several predefined measures that Data Analyzer calculates.

The measures calculated by Data Analyzer are called “template measures.” By default, Microsoft Data Analyzer includes six template measures. Users cannot modify the six template measures; however, they can use the Template Measure Editor to create new template measures that perform specific calculations.

The predefined template measures are as follows:

  • Average Children's Length. Calculates the average length of this member's child bars.

  • Number of Children. Calculates the number of child members for this particular member.

  • Percent of total length of members in filter. Calculates the current member's percentage of the members in the current filter.

  • Change from Last Year. Calculates the percentage of change from the previous year

  • Change in Year to Date. Calculates the current member's percentage change in year to date, using a single selected member of the time dimension as a reference point.

  • Change from Previous Period. Calculates the current member's percentage change from the previous period (month, quarter, year, and so on).

The last three template measures require that a single member of the Time dimension be selected as a reference point. Changing the selection in time changes the filter for all dimensions. If no time value is selected, or if two or more time values are selected, the measures cannot be calculated.

The Template Measure Editor provides an interface for writing expressions to define custom measures. Users can reuse existing template measures or start from scratch, building or editing an expression using MDX functions. Expressions can reference global or cube-specific measures.

Figure 5. Template Measure Editor

Business Center

Another strength of Data Analyzer is the capability to guide users through the exploration of their data, unlocking the meaning behind the numbers. The Data Analyzer Business Center is a powerful guided-analysis tool that allows users to easily identify trends and answer common business questions. Business Center enables users to construct sophisticated, multi-dimensional queries using predefined questions phrased in simple English. For example, a typical Business Center question might ask, "How did year-to-date unit sales compare to the same period last year?"

The Business Center feature is available at three levels (View level, Dimension level, and Member level), and behaves differently based on the level. At the View level, the Business Center presents a set of predefined questions about the data. At the Dimension or Member level, it presents a single, customizable question that enables users to change the dimension or measure being analyzed.

Figure 6. At the View level, Business Center enables users to select from a list of predefined questions about their data.

When the user selects a question, an explanatory dialog box appears, explaining how the view will work, what types of dimensions are now available, and what the color and length measures represent. It gives detailed information on how users can answer certain business questions by filtering on particular members, and what colorless members mean.

Figure 7. At the dimension or member level, Business Center presents a single, customizable query that helps users find similar members or identify trends and patterns

At the Member level, the question displayed in the dialog box is generated based on the selected member. The question displayed will vary depending on the selected member, and the current dimensions and measures in the view. Typically, the question will offer the ability to see what other members of the current dimension are similar to the selected dimension member. Items that appear as hyperlinks allow the users to select dimension members to filter on.

Find Similar

When an analysis turns up a member of particular interest or an anomaly, Data Analyzer’s Find Similar feature enables users to locate additional members that exhibit similar exceptional behavior. Accessible through the Business Center, the Find Similar feature identifies members that have similar characteristics, and presents them side by side for comparison.

For example, Figure 8 shows the results of a Find Similar operation on the Frozen Foods member of the Product dimension. Moving the slider at the bottom of the dialog box from left to right displays the similar products in decreasing order of similarity to “Frozen Foods.”

Figure 8. The Find Similar dialog box ranks members of a dimension according to similarity to the selected member.

Publishing and Reporting Features

Data Analyzer includes features that make it easy for users to publish and share vital business data with others. Data Analyzer’s intuitive graphic representations of data communicate the facts at a glance, and users can publish these views with a single click of the mouse.

Export features include the most popular formats for communicating business information: e­ mail, PowerPoint® slides, HTML pages, and Excel workbooks or PivotTables®.

Send View by E-Mail

The Send to Mail Recipient command enables a user to share a view with other Data Analyzer users who have access to the same cube. The command creates a mail message and attaches the view as a .max file. Recipients simply open the file to launch Data Analyzer and display the view.

Export to PowerPoint

The Export to PowerPoint command exports the current view to PowerPoint® and opens that application. The export command creates a single PowerPoint® slide that includes the view as well as a title that describes the dimensions and measures displayed. The slide can be created as a standalone presentation or appended to an existing presentation, enabling users to quickly create briefing books containing multiple views.

Figure 9. PowerPoint® presentation created from the view example

Export as Web Page

The HTML Report command saves a view as a single HTML page that can be published to the Web or to a corporate intranet. The page includes the current view as well as a descriptive title. Web page styles are defined as XSL style sheets. Data Analyzer includes two styles; administrators can create additional style sheets that conform to corporate templates or branding.

Figure 10. Web page created from the view example.

Export to Excel

Users who are familiar with PivotTable® analysis or are comfortable using the analysis tools of Excel can export data from Data Analyzer views in tabular form to an Excel workbook or PivotTable®. The Export as PivotTable command launches a wizard that prompts the user to specify dimensions for columns and rows, and select the measures to include. Experienced Excel users will save time by using Data Analyzer to quickly navigate through a large cube or to select areas of interest before creating a PivotTable® to explore in more depth.

Architecture and Deployment

Data Analyzer allows flexible deployment scenarios to meet the needs of most organizations. As shown in Figure 11, Data Analyzer works against data stored in OLAP cubes created by SQL Server™ 2000 Analysis Services. Data Analyzer uses Microsoft PivotTable® Services to connect to a SQL Server™ 2000 Analysis Server on a network, to a local cube (.cub) file on the client computer, or to an Internet-based cube exposed through HTTP.

SQL Server™ 2000 Analysis Services provide companies with the most complete, integrated, and Web-enabled analysis services. The OLAP component includes a middle-tier server that enables users to perform sophisticated analyses on large volumes of data with exceptional data-retrieval performance times. Data sources can include any OLE DB provider, such as SQL Server™, Oracle, DB2, other relational databases, and flat files.

Figure 11. Logical diagram showing how Data Analyzer communicates with a data source.

Client-Server Deployment

To connect to Analysis Services on a network, PivotTable® Services requires a TCP/IP connection to a Microsoft® Windows NT®, Windows® 2000, or Windows® XP computer running SQL Server™ 2000 Analysis Services. The server host must be able to authenticate the client via domain authentication. This requires that the host and client be on the same Windows domain or on two domains with trusting relationships.

Typical client-server scenarios can be found in virtually any large company; for example, a Finance department may have deployed an OLAP Server for analysis of enterprise financial data. Deploying Data Analyzer to desktops throughout the company would allow managers and decision-makers to conduct their own ad hoc analysis of this data, providing solid statistical basis for decisions, reducing the burden on the financial analysts, and speeding up the company’s reaction times to changing market conditions.

Internet Deployment

Analysis Services cubes can also be accessed over the Internet via HTTP connection. This scenario requires Web access to a Windows NT, Windows 2000, or Windows XP host running SQL Server™ 2000 Enterprise Edition Analysis Services. In this case, domain authentication is performed on the server side rather than on the client, so the client computer does not have to share a domain with the server.

Secure access via the Web enables a range of extranet scenarios. For example, a commercial data provider could allow customers or subscribers who use Data Analyzer to connect to their database over the Web, by distributing saved views via e-mail. By setting user permissions in SQL Server™, the provider ensures that users have access only to data for which they are authorized. This scenario provides customers with real-time access to data, potentially creating new services or profit centers for the provider.

Local Cubes

Local cubes are particularly useful in small organizations or in support of a mobile/offline workforce. Local cubes can be exported from a standard OLAP cube by an OLAP administrator, or a user can export a subset of the data or a set of query results from Excel using the OLAP Cube Wizard or Offline Cube Wizard.

Local access provides mobile professionals with access to business data anytime, anywhere, enabling fast, sophisticated analysis in the office, at a client site, at home, or on the road.

Extensibility and Custom Solutions

The Data Analyzer Object Model exposes the application’s user interface and functionality, enabling a wide range of extensibility or custom third-party solutions. Developers can drive any aspect of the Data Analyzer user interface programmatically. Thus, they can include any view in a Web page or digital dashboard with full (or partial) functionality, including full or limited ability to filter, sort, and drill up or down.

Developers can customize Data Analyzer by using the documented application programming interface (API). They can also automate Data Analyzer features with Microsoft Visual Basic®, Microsoft Visual Basic® for Applications (VBA), Microsoft Visual Basic® Scripting Edition, and Microsoft Visual C++®.

XML Support

Extensibility and integration with custom solutions are aided by extensive use of XML in Data Analyzer. Data Analyzer files, views, and template measures are saved as XML-formatted files. Business Center questions are also formatted in XML. Although detailed schemas are not included with in the API, developers can easily modify the XML files, or generate XML programmatically to create custom views or template measures and Business Center questions for a specific line of business. By modifying the XML that defines the view—for example, by embedding MDX expressions in place of specific members—developers can create dynamic views that can be incorporated into Web-based solutions.

Custom Solutions

Data analyzer offers numerous other opportunities for customization as well as integration with Microsoft Office and third-party enterprise systems:

  • Integration with digital dashboards. The Data Analyzer includes an ActiveX® control that can be embedded in a Web page or digital dashboard. By including a dynamic, predefined view on a home page or corporate portal, companies can ensure that users are kept aware of pertinent business data in real time—without waiting for distribution of published reports.

  • Embedded views. Data Analyzer views can be embedded in any Microsoft Office document using the Insert Object command. Views can be created on the fly or by opening a saved view (.max file). This enables analysts to create dynamic reports that present concise graphical information and invite readers to explore business data on their own.

  • Automated reporting. By programming Data Analyzer to export views as HTML pages and automating the publishing of these pages to the Web or corporate intranet (or by e-mail), organizations can update shareholders, managers, and/or employees at regular intervals, with little or no administrative overhead.

  • Custom presentation. Data Analyzer functionality can be used to add a user-friendly interface to virtually any system that performs data mining or reporting against an OLAP data source. By incorporating the Data Analyzer controls in the front-end applications of enterprise solutions, such as ERP, CRM, and financial systems, developers can provide a highly intuitive, visual interface, making business data accessible to a much wider audience.


Microsoft Data Analyzer enables organizations to get the maximum value from the wealth of business data stored in existing enterprise systems and collected on an on-going basis through e-commerce and other channels. This results in benefits to knowledge workers and decision makers, to teams, and to the entire enterprise.

For individual users, Data Analyzer unlocks knowledge that was previously inaccessible. Users can quickly view the data stored in enterprise systems and easily conduct sophisticated analysis or queries. This leads to faster, more informed decision making at all levels of the organization.

Teams and workgroups benefit from Data Analyzer’s integrated publishing tools. The capability to quickly output views as PowerPoint® slides, to send by e-mail, or to publish to the Web fit easily with any team’s communication style and ensure that business information is shared and disseminated as soon as it is available. By automating publishing of standard views, or incorporating Data Analyzer views into digital dashboards or intranet portals, organizations can keep entire teams up to date and in sync with important trends or data.

Organizations that deploy Data Analyzer will benefit immediately from broad, data-driven decision making across the enterprise. Business decisions will be executed faster and with greater insight, leading to enhanced organizational agility. Data Analyzer’s intuitive interface and ease of use reduce or eliminate training costs associated with many business intelligence initiatives. Perhaps most importantly, Data Analyzer allows organizations to capitalize on the knowledge now locked in their enterprise applications and data stores, improving the return on investment in these systems.

Yüklə 50,01 Kb.

Dostları ilə paylaş:

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur © 2024
rəhbərliyinə müraciət

    Ana səhifə