Manage Data More Effectively | MSBI Tutorial pdf

Manage Data More Effectively

Office Excel 2007 offers sophisticated calculation capabilities and powerful analytic tools that help you explore complex data relationships quickly and easily.

New Results-Oriented User Interface
Office Excel 2007 offers a new user interface that presents the appropriate tools at the right time so you can produce your best work more quickly. With the new user interface, all important features are organized and presented in context. For example, intuitive Table Galleries show you how a table will look before you make your final selection.

                                 Fig: New Office Excel 2007 visualization.

Increased Spreadsheet Row and Column Capacity
To facilitate importing and working with large amounts of data, Office Excel 2007 offers a larger spreadsheet capacity—1 million rows by 16,000 columns than previous releases.
As a result, you rarely need multiple spreadsheets to contain your data.

                                  Fig: Increased row and column capacity.

Faster Performance
Office Excel 2007 provides the fastest Microsoft Office spreadsheet calculation to date. Using multi-threaded recalculations, Office Excel 2007 spots formulas that can be calculated concurrently and then runs those formulas on multiple processors simultaneously. The number of processors depends on the number of available processors on a computer.

Improved Formula Authoring
Improved formula authoring in Office Excel 2007 means you can use an expanded set of functions, including new cube functions, to query data contained within SSAS. A contextbased Formulae AutoComplete enables you to write the proper formulae syntax the first time, every time. A resizable formulae bar helps prevent long, complex formulae from spilling over onto your spreadsheet.
In addition, Office Excel 2007 offers structured referencing for citing named ranges and tables within formulae and functions. The Name Manager feature helps you organize, update, and manage multiple name ranges from a central location.

                           Fig: Drop-down Formula creation.

Enhanced Sorting and Filtering
New and improved sorting and filtering options within Office Excel 2007 help you better organize and work with data contained in tables. Sort your information by date, selection, or even color if you choose. You can manage up to 64 sorting levels from a single central location. In addition, an AutoFilter feature simplifies complex filtering, such as applying custom filters to more than two items.

                                        Fig : Enhanced filtering and sorting.

Enhanced PivotTable Views and PivotChart Views
Office Excel 2007 offers new tools for exploring and analyzing with PivotTable views and PivotChart views.
With the PivotTable Field List, you can use a clear, side-by-side view to select the fields you want to include in a list. You can also drag fields in different orders to alter the hierarchy of your information. In addition, a Chart Gallery can help you quickly modify the look of a PivotTable view.
Enhanced filtering tools enable you to date and label filters, select multiple items, and reveal filter entry points. You can also sort by various fields, such as color or compact axis, and then view your results.
In Office Excel 2007, you can take advantage of these features while working within a browser so you can interact with a PivotTable view smoothly whether you are working with Office Excel 2007 on a desktop or in a Web browser.

                                    Fig : Creating a PivotTable view.
PivotTable view enhancements make them more readable and presentable. For example, compact row axes display multiple fields in one column. New style options available from the Table Gallery make it easy for you choose a style that is consistent with the look of your table or other aspects of your spreadsheet.

Simplified Conditional Formatting with Rich Data Visualizations
Office Excel 2007 offers simplified conditional formatting that can be represented in rich data visualization schemes. Discover important trends and highlight exceptions in your data. Identify and illustrate your results with colored gradients, data bars, and icons. You can then reorganize the data with sorting options like Sort by Smallest to Largest or Cell Color.

                     Fig : Unified Dimensional Model exposing data as a PivotTable.

Support for SQL Server 2005 Analysis Services
Office Excel 2007 integrates smoothly with SQL Server 2005 Analysis Services to help you easily extract information from your data warehouse. As a result, end users can better understand the context for the data they are analyzing. SSAS provides a metadata model, called a Unified Dimensional Model (UDM), for defining business logic, calculations, and metrics. The UDM serves as the central metadata repository for all reports, spreadsheets, OLAP browsers, and KPIs. By being connected to the UDM in SSAS instead of the data in a spreadsheet, you can quickly find what you need by using a field list. PivotTable and PivotChart views in Office Excel 2007 expose three types of fields measures, KPIs, and dimensions for slicing data (time, geography, product, and so on). All three groups have different icons. Different components of a KPI
(Value, Goal, Status, and Trend) can be added to the Values area of a PivotTable view so you can track the latest values of your key business metrics.
Office Excel 2007 also supports perspectives, subset views of a cube (defined in SSAS) that only contain information needed for a given purpose. In Office Excel 2007, a perspective looks like any other data source.

                         Fig : Integration with SQL Server 2005 Analysis Services.

Improved Charting Tools

Dramatically improved charting tools within Office Excel 2007 help you easily summarize your analysis in professional-looking charts. Format axes, titles, and other chart labels with a few simple clicks. Add visual enhancements like 3-D effects, shadows, and glow. It’s that easy.

                                                     Fig : Charting tools.
The improved charting capabilities in Office Excel 2007 are shared across the 2007 Microsoft Office system, meaning that when you copy tables and objects between programs, the items maintain their appearance. As a result, working simultaneously with different programs becomes more consistent and less frustrating.

Data Connection Library
The Data Connection Library in Office Excel 2007 simplifies how you connect to external data sources. The library centralizes storage of Business Data Catalog connections to external data, making it easy to discover new sources of data. You can use Data Connection Library to access external data and import information directly into your spreadsheet without having to provide password authentication.

                                   Fig : Data Connection Library.
The Data Connection Library also simplifies the maintenance of data connections. If a connection changes, it needs only to be updated in the library instead of in the individual spreadsheets that are linked to the original data source.