What’s new in Microsoft SQL Server Business Intelligence?
Posted on Fri, Jan 20, 2012
It used to be that Business Intelligence with SQL Server meant a discussion of Reporting Services, Integration Services (SSIS) and Analysis Services. With the release of SQL Server 2008 R2, we added Master Data Services (MDS) and PowerPivot, and a host of new features, mostly to Reporting Services. With the upcoming release of SQL Server 2012, we are adding Data Quality Services(DQS), and Power View (if you are running Reporting Services in SharePoint integrated mode). There are also major changes to Integration Services (a new deployment model) and to Analysis Services - the ability to store data in tabular mode, not multi-dimensional mode.
Integration Services has long had a model of deploying packages. If you wanted to deploy scripts, or deploy a set of packages, you had to write your own script to move those files from development to production. Now, with SQL Server 2012, you will, by default, be deploying projects. You can still use the legacy method of deploying packages if you want to. Since you now have projects, you also have project parameters. A discussion of what project parameters can do versus package parameters is here: Project parameters vs. Package Parameters.
As part of Microsoft’s retooling of deployment, they’ve also added the ability to specify environments, and environment variables which can be used during package execution. You can specify a development environment, create variables used in development and also create, during development, the variables and values that will be used in your production environment. Just as with the older configuration variables, these can be accessed and chosen or changed at runtime.
With the addition of Data Quality Services and Master Data Services, attention is now being focused on what happens in enterprise data management before you start getting the data into a data warehouse. Master Data Services was added with SQL Server 2008 R2. Donald Farmer gave a good presentation on MDS at SQLBITS.
MDS allows users to create business rules that can be applied to data from disparate sources, so that they all follow the same rules. Note I said that it allows users to manage the data. This is Microsoft’s foray into master data management. And, as part of their increased focus on involving the user, MDS is about data owners deciding what the business rules should be, not someone in IT, or in development. The limitation of MDS is that MDS only really works for data that represents key business entities (e.g. “customer”, “product”, etc.). For other data, that isn’t an entity, you could still have input errors, or duplicate entries. For these cases, you will have users applying their knowledge of the business transactions through use of the Data Quality Services client application, or, alternatively, through use of Excel. DQS allows you to say that the geographical location is US, not America, not USA, nor United States. All of the non-acceptable entries would be identified as synonyms of US, with US as the leading synonym. As more data is analysed, the application learns and saves that knowledge in a knowledge base. This knowledge base can also be used to feed MDS, for the design of business rules.
To learn more about SSIS 2012, and about MDS and DQS, see the new beta version of the MS-10777AB course: Implementing a Data Warehouse with SQL Server 2012. This course is beta courseware, with the SQL Server running SQL 2012 RC0. When SQL 2012 releases later in the first half of this year, the course will be updated with the final release bits of SQL 2012.
Your tool for developing SSIS packages is no longer called Business Intelligence Development Studio, but is now called SQL Data Tools. Another change is that you will get two Toolboxes: one just for SSIS, and another generic one still called Toolbox. As before, you can add other development tools to your toolbox.
Remembering that SQL 2008 R2 had many changes to Reporting Services, and that Analysis Services has had few changes in recent releases it is no surprise that there are major changes to SQL 2012 Analysis Services. Microsoft has realized that it takes a lot of design and development work to create a data warehouse. Wouldn’t it be nice if users, without assistance from IT, could slice and dice data in their favourite tool, Excel? PowerPivot has been created as an addin for Excel, so that users could import data from a variety of sources on separate worksheets in a workbook, tie that data together by identifying common column values (DBA’s call those Primary key-foreign key relationships), and then slice and dice that data.
That tabular data can also now be stored in Analysis Services. To do that, when you install SSAS, you now have three choices: Multidimensional, Tabular, and PowerPivot for SharePoint. When you choose multidimensional, you create an online analytical processing (OLAP) model that includes cubes, fact tables and dimensions. Tabular mode supports creating a model using tables and relationships. PowerPivot for SharePoint integrates SSAS with SharePoint 2010, so that users can centrally store and process their PowerPivot for Excel workbooks, using a web browser.
While the bulk of the changes were made to Integration Services and Analysis Services, Reporting Services has seen the addition of Power View, a new way for users to create reports which are visually interesting and informative. Thus there are now three ways to create reports, Report Designer, Report Builder 3.0 and Power View. Again, you can see that Microsoft has put the emphasis on making data and the use of that data, into the hands of users and information workers.
For a closer look at the new features of Reporting Services and Analysis Services, please check out the new course: MS-10778AB Implementing Reports and Data Models with Microsoft SQL Server 2012. Again, as with MS-10777AB, mentioned above, this course is a beta course, using SQL Server 2012 RC0. It will also be updated when the product releases.
What are your thoughts?
Saskia Schott, Systems Engineer, QuickStart Intelligence