SQL Server 2012 Business Intelligence
SQL Server 2012 is out now and there are a lot of great new features in the world of Business Intelligence (BI) included in this new release and this article covers my top 5 new BI features in SQL Server 2012. From Reporting, Loading Data, and Analysis there are a lot of new features to choose from in 2012. It is hard to choose from all of the new features, so this list was based on how useful each feature is in terms of BI development and analyzing data.
#1 Power View
Power View is a great new ad-hoc reporting tool built for the end users. It requires SQL Server 2012 and SharePoint 2010 and has one of the coolest UI displays compared to the previous tools. Power View gives end users an intuitive ad-hoc reporting tool they can use to easily create and interact with data from PowerPivot workbooks or tabular models deployed to SQL Server 2012 Analysis Services. It runs in a browser and uses Silverlight from within SharePoint Server 2010. One of the coolest graphing tools in Power View is the time line. You can build a chart and place a time line along the bottom and click the play button and watch the data change over time. I for one can’t wait to build reports with this tool and see my users build them too.
#2 SSIS Configure and Execute with T-SQL
SSIS has improved significantly in SQL Server 2012. The greatest new feature is the ability to run and control your SSIS packages using T-SQL. With the addition of the new SSIS catalog and the project deployment, your packages are housed in a database now and there is an entire set of stored procedures and functions for SSIS administration. This opens up a Pandora’s Box of possibilities! Imagine writing complex stored procedures that call multiple SSIS packages, or using a cursor to execute a package for each row on a table. You can select data from a table and pass it to a package using parameters. This is much improved from the command line prompt method in the previous versions of SQL Server. It gives developers and DBAs the ability to incorporate SSIS more into their database and their development.
#3 SSIS Parameters and Environments
Speaking of Parameters, it happens to be number three on my top 5 BI list. Parameters and environments allow developers to pass variables into packages now without using configuration files or tables. That’s right, no longer do developers have to manage a group of files or tables separate from their packages. They can execute packages with T-SQL and pass in the parameter values using the T-SQL. They can also save multiple parameter values in the new environments. Environments can be thought of as a parameter bucket that holds a set of parameter values. Packages that need a different set of values can have all the parameters changed with just a simple change of selecting a different environment. A good example of this would be a package that needs one set of values during the week and a different set on the weekend or at month end. As you read in number two, you can execute the package with T-SQL, so you can place logic in your T-SQL, like a Case When statement, to select the proper environment. You can also use them for Development versus Production settings.
#4 SSAS Tabular Models
Developers now have the ability to create tabular models in SQL Server Data Tools (SSDT, Formerly BIDS). Instead of having to use Power Pivot in Excel to develop a tabular model, developers can now use a tool they are more familiar with, Visual Studio. This gives developers the ability to create and deploy tabular models using SSDT. These are available to the end user to connect with power pivot and start slicing a dicing data. The diagram view makes it easy to visualize the data and build hierarchies. Before SQL Server 2012, PowerPivot was the only way to create a tabular like model in SQL Server. This new model puts the developers in a familiar environment and allows them to create models for the users to easily consume.
#5 SSIS Undo/Redo
I know this seems like a small improvement, but for any SSIS developers out there, they know the undo/redo feature added to SSIS makes like so much easier. Imagine writing a word doc and not having undo. Oops, you accidently deleted an entire paragraph, now write it again! In SSIS, that was the norm, until SQL Server 2012. Now if you delete some task or make changes you need to reverse, CTRL + Z is here to save the day.
