Introducing PowerPivot for Excel Features with SQL Server Code Name "Denali" CTP3

The CTP3 of Microsoft SQL Server code-named "Denali" bring the simplicity and power of the PowerPivot technologies to corporate BI, greatly expanding the reach of the Microsoft BI semantic model.The video below gives you a brief overview of 3 key features introduced with the CTP3: The new Diagram View, user defined hierarchies and perspectives.Diagram View […]

The CTP3 of Microsoft SQL Server code-named "Denali" bring the simplicity and power of the PowerPivot technologies to corporate BI, greatly expanding the reach of the Microsoft BI semantic model.

The video below gives you a brief overview of 3 key features introduced with the CTP3: The new Diagram View, user defined hierarchies and perspectives.

  • Diagram View is - as the name suggests - a diagrammatic view of the model. IN addition to enabling you to view tables in a visually organized way and to easily add and change relationships and hierarchies it's important to note that the diagram is a fully-fledged designer, which allows for greater interactivity and navigation across the entire model
  • Hierarchies will enable the person building the model to design more intuitive navigation patters for the users consuming of the model.
  • Perspectives, the user creating the model can easily define different subsets of the model providing a more simplified view for the end user. Perspectives are typically defined for particular user groups or business scenario and make it easier to navigate large data sets.

In addition, you'll find a significant number of other new features including:

  • Support for Multiple Relationships: In PowerPivot V1 only one relationship would be allowed to exist between a give set of tables. In CTP3 we have added the ability to import - and later create - multiple relationships between tables. Only one relationship will be active but inactive relationships can be used when defining measures which will allow for full usage of all relationships.
  • Calculation Area: With the introduction of the Calculation Area we are bringing measure authoring into the PowerPivot modeling window. The Calculation Area is a free form data grid located at the bottom of each table enabling the user to easily create view, create, edit, and manage measures and Key Performance Indicators (KPIs) in context of the data within each table.
  • Advanced Tab: An advanced tab has been added, making all advanced features available in a separate ribbon tab. The features include the ability to create or edit perspectives, summarize a numeric column by an aggregation function, and set reporting properties. The advanced tab is not enabled by default, but can be enabled on a per user basis from the File button within the PowerPivot window.
  • Reporting Properties: In the Reporting Properties area of the advanced tab the modeler can set various properties reporting allowing the modeler to drive an optimized default experience for client tools, such as Project Crescent.
  • New DAX Functions: A variety of new DAX functions have been added such as USERELATIONSHIP, DISTINCTCOUNT and TOPN just to mention a few.
  • Sort by Other Column: Sort by Other Column will allow for sorting one column by anther column within the same table. As an example the month name column can be sorted by the moth number column, which will essentially assign each month its number in order to sort the column in a natural way when the month field is added to a report or PivotTable.
  • Add Values to Rows and Columns: As of CTP3 the user can add values to rows and columns, which was functionality that could previously only be achieved using the Excel Field List.

You can download SQL Server Code Name "Denali" CTP3 from: this link, and to download SQL Server "Denali" PowerPivot for Excel, visit the Microsoft Download Center.

Here is the video demonstration:

[Via: SQL Server Team blog]