A New report-Authoring Experience
In the field list's Financial Reporting measure group area, look at the Amount
measure. Notice that Amount shows up in the PivotTable. If you've applied SP2
to the SSAS instance, notice also that the server-side formatting (in this case,
currency) is recognized. Amount also appears at the bottom of the field list
in the Values area. Next, in the Account dimension, check the Accounts hierarchy;
the hierarchy will appear in the PivotTable (as rows) and in the field list's
Row Labels area. Finally, expand the Date dimension's Fiscal folder and check
the Date.Fiscal hierarchy; it will appear in the PivotTable (as columns) and
in field list's Column Labels area. If you followed me so far, your PivotTable
and field list should look like those in Figure
3. (Note that I've rearranged my field list by using the Fields Section
and Areas Section Stacked view so that the areas are displayed side by side
with the field section.)
Next, try to add and rearrange fields on the report. Unlike the old PivotTable
drag-and-drop experience in Excel 2003, you now add or remove fields by simply
checking or unchecking them. Likewise, to rearrange a field in a report (e.g.,
move a field from a row to a column), you simply drag and drop a field between
the areas in the field list. Although I'm experienced with PivotTables in Excel
2003, I've very quickly come to prefer this new report-authoring approach. But
if you decide you like the "old way" of doing things, you can right-click a
PivotTable, select the PivotTable Options menu item, then check the Classic
PivotTable layout option in the Display tab. While you're in this Options window,
you might also want to select the Show calculated members from OLAP server
check box to ensure that server-defined calculated members are visible.
Another change in PivotTable reports has to do with the default row layout
when navigating a dimensional hierarchy (or when displaying two or more dimensions
on a row). For example, if you expand the Balance Sheet dimension member (to
expand a dimension, click the + sign next to the dimension name in the PivotTable
report), notice how the two child dimensions (Assets and Liabilities and
Owners Equity) are indented—but still remain in the same column.
This feature lets you navigate hierarchies without additional columns being
allocated—nice for maintaining screen real estate and/or when you've
defined other Excel calculations to the right of your PivotTable.
OLAP Formulas
At the beginning of the article, I mentioned that one drawback of PivotTables
is they're "locked." This is still the case with Excel 2007; you can't insert
rows or columns into a PivotTable. But you can now convert a PivotTable to a
set of OLAP formulas— which will then let you take full advantage of
Excel's free-form capabilities. (To convert the PivotTable, from the Ribbon's
Options Tab, select the OLAP tools command, then select Convert to
Formulas.) Figure 4 shows a PivotTable
that I've converted. Notice that the selected cell, B8, is defined using the
CUBEVALUE function. There are seven new CUBE functions in Excel 2007 that let
you fetch dimension members, measure values and member properties, and perform
other related tasks. Notice also the Gross Margin% row (row 9), doesn't come
from the cube; rather, I inserted this row and used standard Excel calculations
to reference the OLAP-based Gross Sales and Gross Margin values in each respective
column.
Here are three other important points about OLAP formulas:
- Notice in Figure 4 that the
row filter (Date.Fiscal Year) still has a filter symbol in its value cell.
When you convert a PivotTable to OLAP formulas, you have the option of retaining
the row filters. This option gives you the best of both worlds in that you
can customize your worksheet (e.g., insert new columns and rows) while still
having the ability to filter the OLAP data by different dimension members.
- You don't have to start with a PivotTable to use OLAP formulas. Rather,
you can use these formulas on a cell-by-cell basis.
- Converting a PivotTable to a set of OLAP formulas is a one-way operation;
you can't revert back to a PivotTable.
More Great Features... and a Missing One
Several other PivotTable features that are new in Excel 2007 improve upon Excel's
OLAP integration. They include
- Member-property support. Member properties (which are now simply
referred to as attributes in SSAS 2005) are visible when hovering over a dimension
member. Attributes let you see additional information about a dimension member
(e.g., a phone number for a customer). You can optionally choose to have attributes
displayed in a PivotTable report alongside the dimension member.
- KPI support. Excel 2007 can display the value, goal, status, and
trend for a KPI—including the graphical indicator defined for a KPI's
status and trend.
- Support for actions. Drill-through is now considered a cube action,
and Excel 2007 does a nice job of returning drill-through results into a new,
formatted worksheet.
- Enhanced filtering. With SSAS 2005 SP2 applied, you can filter dimension
members according to date, label, and measure values.
Notwithstanding all the new PivotTable features, one that's missing is the
ability to create calculated fields (what you'd think of calculated members
with respect to an OLAP cube). On the Options tab in the Excel Ribbon, there's
a command called Formulas (located in the Options tab's Tools section); for
OLAP reports, the Formulas command is disabled. Although OLAP Formulas provide
a workaround, I'm still hoping calculated fields for OLAP will be enabled in
a future service pack or release.
OLAP and More
Excel, in my opinion, will continue to be the desktop tool of choice for analyzing,
organizing, and making sense of data. The OLAP-specific enhancements in Excel
2007 greatly improve its usefulness as a means to analyze and manipulate data
from OLAP cubes. In addition to the features I've covered here, Excel 2007 also
offers an enhanced PivotChart experience—backed by a new graphics engine
to provide better charting and visualization capabilities. For more examples,
details, and discussions concerning Excel 2007 and SSAS, check out the Microsoft
Excel 2007 blog at http://blogs.msdn.com/excel/archive/tags/analysis+services/default.aspx