Microsoft Office Tutorials and References
In Depth Information
By changing the way data is organized in rows and columns, you can easily produce different
and interesting reports, with an intuitive, fast interface that helps you navigate the information.
Figure 1-3 shows what a standard PivotTable looks like. Users all around the world have been
using this tool for many years with great success, analyzing their Excel data in many different
ways and producing reports according to their needs.
One of the best characteristics of the PivotTable tool is its ease of use. Excel analyzes the
source table, detects numeric values, and provides us with the ability to display their total
slicing data over all other columns. Clearly, totals are aggregated using the SUM function
because this is what is normally needed. If we want a different aggregation function, we
can choose it by using the various PivotTable options.
As easy as it is to use, PivotTables have some limitations:
■ PivotTables can analyze only information coming from a single table stored in an Excel
worksheet. If you have different worksheets, containing different information, there is
no easy way to correlate information coming from them.
■ It is not always easy to get the source data into a format that is suitable for analysis. In
the previous example, you saw a table that is extracted from a SQL query run against the
AdventureWorks database and that you build to analyze data. The skills needed to build
such a query are somewhat technical because you need to know the SQL syntax and the
underlying database structure. Situations like this often force you to ask your IT
department to develop such queries before you even start the analysis process.
■ Because only one table can be analyzed at a time, you can often end up building the
queries needed for a specific analysis, and, if for any reason you want to perform a
different analysis, you need to build different queries. If, for example, you have a query that
returns sales figures at the month level, you cannot use it to perform further analysis
at the “day of week” level; to do that, you need a new query. This requirement, in turn,
might prompt you to contact IT again, which can become expensive if the IT department
charges for the amount of work it does.
When PivotTables are not enough, as is the case for medium-size companies, it is very
common to start a complete BI project with products like SQL Server Analysis Services, which
provide the same pivoting features on complex data structures known as OLAP cubes. OLAP
cubes are difficult to build but provide the best way to freely analyze complicated company
data. We talk briefly about OLAP cubes later, in Chapter 4, “Data Models;” at this point, it is
enough to point out that they are the definitive solution to BI requirements but they are
expensive and still require a strong effort from the IT department.