Microsoft SQL Denali: New PowerPivot for Excel Features

Many businesses have been leveraging the power of Microsoft PowerPivot for Excel 2010 since its release almost one year ago. PowerPivot for Excel allows business-level users to pull data from structured sources like SQL, Oracle, and DB2 in order to compare with industry trends. PowerPivot removes the onus of defining relationships from the user and instead has that logic built into it.

While PowerPivot was already a powerful business intelligence tool, the newest release, which will be packaged in Microsoft’s next version of SQL Server – code name “Denali” – will provide several new features that are sure to improve the popularity of Microsoft’s do-it-yourself BI tool.

The most frequently requested feature in PowerPivot for Excel was a diagram view for users. PowerPivot’s old data-driven layout made sense for Excel users, but lacked a dynamic visual element. The new diagram view in Denali provides users with a visual representation of the relationships between tables and features intuitive zoom and mini-map features for even the largest sets of data. Users can see all relationships, objects, and meta data with which they’re working, and drag and drop fields between tables to recreate relationships.

While some features, such as renaming data/meta data and deleting/hiding objects can be performed easily in both views, others are more tailored to the new Diagram View.

The creation of Perspectives, for instance, introduces a new feature that allows data to be easily tailored to specific user groups. While all of the data is stored in one large semantic model, Perspectives allows view definition, meaning a sales team will see sales tables, the financial team will see tables that pertain to them, while executives may have access to all data, etc. Perspectives cut out extraneous information for end users, allowing them to view only the data that is relevant to their own needs and goals.

Another feature that is possible only within the Diagram View is the creation of user-defined hierarchies. A modeler can pre-define patterns of navigation using this feature. In Diagram View, a user can re-arrange columns within a table in the form of a list view. For example, in a “Products” table, a user can drag and drop desired columns (e.g. Category, Subcategory, Name, Style, Class, Type, etc.) as child nodes under a parent column.

These features are just a couple of the many new ones that you’ll see in the new Denali rollout of PowerPivot for Excel. Some of the other features will include the ability to create key performance indicators (KPIs), the ability to import multiple relationships, and a Measure Grid that allows users to create and edit KPIs and view measures in a grid format.

There will be books written on the new features of PowerPivot in the Denali release and how they can strengthen your company’s business intelligence, so the best way to learn them in more detail is to simply download the Denali Community Technology Preview (CTP) from Microsoft and explore the exciting new changes for yourself.