In Microsoft SQL Server 2008 R2 we introduced PowerPivot: A self-service Business Intelligence experience that enables information workers to create their own self-service BI solutions and facilitating sharing and collaboration within the familiar environment of Excel and SharePoint. With the release of Microsoft SQL Server code-named “Denali” we bring the simplicity and power of the PowerPivot technologies to corporate BI, greatly expanding the reach of the Microsoft BI semantic model.

As of CTP3 of Denali we are delivering a wealth of new modeling features to the Microsoft BI semantic model, which will surface in both in PowerPivot and the Tabular Project type within the BI Development Studio.

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

  • Diagram View: The 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: The ability to create user defined hierarchies will enable the person building the model to design more intuitive navigation patters for the users consuming of the model.
  • Perspectives: With the introduction of 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 to the 3 features discussed above you will find a significant number of other new features to explore if you go download the CTP3 of PowerPivot for Excel. Other features include:

  • 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.

Other features delivered with previous CTPs include: KPI’s, ability to mark a table as a date table, multiple DAX functions, change of data type for calculated columns, persistence of measure formatting, descriptions, alphabetical display of fields in the Field List.

Download SQL Server Code Name “Denali” CTP3 from: www.microsoft.com/denali
Download SQL Server "Denali" PowerPivot for Excel at: http://www.microsoft.com/download/en/details.aspx?id=26721

Enjoy your modeling using CTP3!

Julie Strauss
Senior Program Manager
Analysis Services Team