Forrester Research has just released Microsoft’s positioning as a leader in the “The Forrester Wave™: Self-Service Business Intelligence Platforms, Q2 2012″ report. SQL 2012 has a lot to do with this. Forrester highlights Microsoft’s approach to delivering self-service BI through the familiar tools of Excel and SharePoint. According to the report, ” Microsoft has built on its broad, omnipresent, SQL Server-based BI functionality, which already includes Integration Services, Reporting Services, and Analysis Services. It now offers a very respectable PowerPivot and Power View in-memory data analysis and discovery engine. Think of this as Excel on steroids.”
So talking about SQL Server 2012, there are quite a few new features that are worth knowing about, particularly from a development perspective.
#1 Power View
Power View is a great new ad-hoc reporting tool built for 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 date change over time.
#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 catalogue 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 lot 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 & Environments
Parameters and environments allow developers to pass variables into packages now without using configuration files or tables. Developers don’t have to manage a group of files or tables seperate 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. 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 PowerPivot 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 to PowerPivot and start slicing and dicing data. The diagram view makes it easy to visualise 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 SSIS developers, they know the undo/redo feature added to SSIS makes life 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.