What's new in SQL Server 2012 - Part 2: Business Intelligence

days
Training code

Upcoming sessions

Learning Goals

The new SQL Server 2012 contains many improvements as well as complete new features in the business intelligence functionality. This course presents you with the new features and improved functionality and the many hands-on labs allow you to familiarize yourself with all the new functionality.

Target audience

This course is intended for Business Intelligence developers and administrators who consider an upgrade to SQL Server 2012.

Prerequisites

The course requires participants to have significant hands-on experience with SSIS, SSAS and SSRS on SQL Server 2008 or SQL Server 2008 R2, as well as a good knowledge of T-SQL. Certain parts of this course relate to SharePoint; for this the participants should have at least some familiarity with SharePoint

Course Outline

What’s new in SSIS

  • Data correction transformation uses Data Quality Services for testing Data Quality Rules
  • Change Data Capture source, transformation and task simplifies retrieving only modified records from the source database
  • ODBC source and destination for improved performance when accessing e.g. SQL Azure or Hadoop
  • Expression task makes it easier to modify the values of variables
  • Support for .Net 4.0 scripts
  • New project deployment model, which makes it easier to manage packages in SQL Server, using build-in stored procedures, views and reports.
  • Also .Net object model and PowerShell can be used to deploy and manage SSIS packages
  • Variables and shared connection managers makes it easier to share connections and other settings amongst multiple packages
  • GUI changes: BIDS has been updated to provide a more productive development experience: Undo support, revised toolbox, better support for reconnecting objects, …
  • Memory improvements on the Merge and Merge Join transformation
  • .dtsx improvements: The structure of the new dtsx files makes it easier to compare and merge dtsx files, making it easier in source control tools.

What’s new in SSAS

  • Tabular Model databases provide another way to analyze data. Instead of the traditional OLAP modeling in dimensions and hierarchies, tabular mode sticks to tables, but uses the Vertipaq engine for efficient querying. This causes many changes to Analysis Services, including a complete new GUI to create and edit tabular models, partition them, using memory paging to create tabular models larger than RAM, setting up tabular security roles and row level security, as well as hierarchies.
  • DirectQuery is roughly the Rolap equivalent of tabular models, where users query the underlying table directly via SSAS, without using the SSAS cache.
  • Extended Events were already available in the relational SQL Server, but are now also available in SSAS, and allow capturing about 40 types of events.
  • DAX is the query language used by tabular models. DAX was already available in PowerPivot, but has been extended with more than 30 new functions.
  • PowerShell cmdlets for Analysis Management Objects (AMO) and PowerPivot for SharePoint
  • Performance and storage improvements: Scalable String storage allows storing more than 4 GB of strings in dimensions and distinct count aggregates, tables with more than 2 billion rows are supported.

What’s new in SSRS

  • PowerView is an interactive ad-hoc reporting tool for data stored in tabular models in Analysis Services. It is accessed from within SharePoint 2010
  • Improved SharePoint integration with support for claims-based authentication, cross-farm support
  • Data alerts inform end-users of certain changes in the data of the reports of their interest. A data alert designer is provided to create these alert rules.
  • Office 2007 rendering types allow users to render reports as docx or xlsx files.

What is Data Quality Services?

  • Data Quality Services is a new SQL Server service which helps in data cleaning tasks such as deduplication and domain validation. It is a combination of knowledge bases describing correct data and rules, as well as tools to validate the data. It links with SSIS via the data correction transformation, and links with Master Data Services.
Upcoming sessions at U2U in Brussels, Belgium:

To organize this course on-site, please contact info@u2u.be.

Course Calendar
Courses On Site
Course Agenda in PDF
Become a U2U trainer

Contact Me

Call me
Send U2U folder
Send more info