What's new in SQL Server 2012 - Part 2: Business Intelligence
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.
This course is intended for Business Intelligence developers and
administrators who consider an upgrade to SQL Server 2012.
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
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
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
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
Extended Events were already available in the relational SQL Server, but
are now also available in SSAS, and allow capturing about 40 types of
DAX is the query language used by tabular models. DAX was already
available in PowerPivot, but has been extended with more than 30 new
PowerShell cmdlets for Analysis Management Objects (AMO) and PowerPivot
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
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
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.