Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11)

Posted by denglishbi on July 19, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

Original source post Superior Consulting Services | July 18, 2011 at 10:36 am | Categories: Technology | URL: http://wp.me/p1kpnO-16

With the recent release of the Community Technology Preview (CTP) 3 of SQL Server ‘Denali’ we wanted to point out and highlight some of the key items that are related to Business Intelligence and provide very compelling reasons for evaluating and considering the next release of the product. 

sqldenali_logoSo here we go with the top 10 BI reasons to upgrade to SQL Server ‘Denali’:

10. Integrations Services (SSIS) Enhancements – lots of usability enhancements have been made such as ability to undo and redo, a new SSIS Toolbox, grouping capabilities in the data flow, simplified data viewer configurations, new data flow source and destination assistants (wizards), and improved data flow designer experience with mapping and resolving errors.  Other modifications were made so it is easier to compare and merge packages, and you have the ability to view object dependencies in SQL Server Management Studio.

9. Master Data Services (MDS) Enhancements – improvements in the user interface (UI), performance improvements with the UI, ability to incorporate data quality services into the workflow process, and a new Excel add-in client that will provide the ability to edit Master Data and publish it back to MDS and even create a model in Excel.

8. New Feature: Data Quality Services (DQS) – this is a new product offering that has been added that allows you to create a knowledge base repository of data related items that can then be used to incorporate and use for data cleansing. This will allow you to profile, improve the quality, and monitor the on-going health of your data. This new feature can be added into your Integration Services and Master Data Services processes.

7. New Feature: Database Columnstore Indexes – the VertiPaq engine first introduced with PowerPivot has now been brought to the database engine and is available to use to create indexes on columns in the relational store. This will provide a wonderful opportunity for users leveraging ROLAP technology to great improve the performance on the query processing. This index groups and stores the data for each column versus the row.  This can alleviate having to use pre-aggregated data and summary tables.

6. New Feature: Reporting Services (SSRS) Shared SharePoint Service – the SSRS integration with SharePoint will now be setup as a Shared SharePoint service just like Excel Services and PerformancePoint Services.  Everything will be managed completely through SharePoint and there will no longer be a separate SSRS Windows Service running.

5. New Feature: Reporting Services (SSRS) Data Alerts – new self-service data-driven alerting capabilities have been added to allow end-users the ability to setup and define alerts using existing Reporting Services reports. These alerts can be setup based on defined criteria and scheduled as needed to provide a mechanism to notify the user without them having to manually execute the report on their own.  This new feature is only provided in the SharePoint Integrated mode of Reporting Services.

4. PowerPivot Enhancements – with version 2 of PowerPivot there have been some major additions which include features like a new diagram view of the model, hierarchy and KPI support, ability to sort a column based on another column, multiple relationships (like role playing dimensions kind of – can access through DAX), image support, add values to rows and columns (very cool – flexibility provided in the PivotTables), reporting properties (will be leveraged by ‘Crescent’), ability to format measures, field list sorted alphabetically (instead of how they were added to the model), and most importantly…show details (drillthrough support – this will be fabulous when using with PerformancePoint).

3. DAX Enhancements – new functions have been added to help simplify and provide additional capabilities like DISTINCTCOUNT, Rank, Standard Deviation, TOPN, table functions, LOOKUPVALUE, parent-child relationship functions, SWITCH, and more.

2. New Feature: Analysis Services Tabular – the PowerPivot experience in Excel has also been added into the Business Intelligence Development Studio (BIDS) introducing a new Tabular project option.  This will allow you to take the PowerPivot model and convert it to a Tabular project and deploy the solution at the server level for a corporate/enterprise level solution that will scale and handle large volumes of data. With the Tabular models you will have the same options as in PowerPivot along with additional capabilities like partitioning, incremental updates, and role based security.

1. New Feature: Project ’Crescent’ – this is a brand new browser based Silverlight report authoring tool.  This new tool will replace the Report Builder 1.0 application that leveraged Report Models and will be able to leverage the Tabular BI Semantic Models (BISM) that you will be able to create with PowerPivot for SharePoint and Analysis Services Tabular projects. This will be a highly interactive reporting experience that will allow you to access, explore, and visualize your data and have fun doing so in the process.  This tool will enhance the existing Microsoft BI toolset and provide a very friendly self-service reporting solution for end-users to leverage.  The new feature is only provided in the SharePoint Integrated mode of Reporting Services.

If you have any questions or would like to meet with SCS to discuss how Business Intelligence can help your business please give us a call or visit or website to learn more about how we can help at www.teamscs.com.

For additional information in regards to SQL Server ‘Denali’ check out the following list of resources:


7 Responses to “Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11)”

  1. Wow, the top 10 features seem to be a fantastic improvements over the current functionality. Just one query on “Tabular Projects”..will it have full interactivity on SharePoint like drag and drop ? this was a very big limitation while using Pivot tables in excel services. Hope this has been resolved.

    Sanjay Shah

    • denglishbi said

      There has been no changes to add any type of drag and drop AS browser support like you have with OWC (Office Web Components which has been removed now in the product – cube browser).

      • Hi Dan,
        1 Thanks for your reply.
        2 in the Tabular model, can i create one model containing all my masters only where in i define their hiearchies etc (similar to doing the dimensional modelling).
        3 then from another tabular model will i be able to connect to the first tabular model and just pick up the masters which i need ?
        4 or will i have to create the masters in each BISM ?


      • denglishbi said

        Each database would need to contain this logic, no different then multidimensional as well with the exception in multidimensional we can have multiple cubes that can reuse the conformed dimensions. That concept does not exist in the tabular forms.

      • Thanks Dan.
        Then as a workaround would it be better to define all the masters data and its hierarchies etc in an MDM database ? When i take the data from an MDM database will the metadata of the hiearchies flow into my tabular model ?

        kind regards


      • denglishbi said

        That would be nice wouldn’t it? I am not current aware of what can consume the metadata in MDS as far as the hierarchy definitions.

  2. Thanks Dan.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: