Familiar. Collaborative. Managed.
Today we are excited to feature a guest post from Mike Davis, MCITP, who is the Managing Project Lead at Pragmatic Works. He is an author of several Business intelligence books. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups, Code Camps, SQL Server Launch events, and SQL Saturday events. Mike is also an active member at his local user group (JSSUG) in Jacksonville, FL.
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.
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. More information on Power View can be found on the team blog: http://blogs.msdn.com/b/sqlrsteamblog/archive/2011/11/17/what-s-new-in-power-view.aspx
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. You can learn more on this at the SSIS team blog: http://blogs.msdn.com/b/mattm/
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. For more on this topic visit the SSIS team blog : http://blogs.msdn.com/b/mattm/
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. You can read more about Tabular models on MSDN here: http://msdn.microsoft.com/en-us/library/hh212945.aspx
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. Read more on this and more SSIS new features on the SSIS team blog: http://blogs.msdn.com/b/mattm/
What are your top five features in SQL Server 2012? SQL Server 2012 is packed with so many new features it is hard to choose from them all. Certainly Master Data Services and Data Quality Services should rank near the top for me as well. Learn more about the new features and enhancements in SQL 2012 on the MSDN site at: http://msdn.microsoft.com/en-us/library/bb500435.aspx
As an experienced SSIS developer, #5 is my #1 :D
But Tabular and Power View make a very close #2.
As an experienced SSIS developer, #5 is my #1 too :D, wish we could compare DTSX in TFS visually!
Certainly SSIS Undo/Redo is a must to have feature. I see #2 has a great potential, it is a great way to tightly integrate all code components (T-SQL, SSIS, SQL Jobs) related to a business solution.
I like the Flat File enhancements too, for the developers working with Flat Files it will be close competitior to #1 slot:-)
If it would be a survey, I would put these features in a 5, 3, 4, 2, 1 order (descending importance):
#5: Undo/Redo is essential in any editor.
#3: Environments make the life of an SSIS developer easier.
#4: The Tabular Model is fine. It has its pros and cons compared to the MD Model. I'm still a fan of the MD model as it can have more complex designs. However the underlying data source can be reused for a TM solution, I have concerns regarding the pain of implementing complex calculations.
#2: create_execution, start_execution and the rest of the SSISDB features are OK, as we only had workarounds to call SSIS from T-SQL. However I think in the majority of the cases, the ETL process can be scheduled in a comfortable way and does not need T-SQL coding at all.
#1: The Power View is impressive! When I saw it first on a MS demo, I thought yes, this is an easy-to-use, professional reporting tool (at last). But I would put it to the last place because (1) its functionality is expected to be available in Excel 2013 as well, so I would not invest into Sharepoint if not necessary. (2) Its data source cannot be a MD model - this is not a good point as there are many MD solutions exist already, and there are many SSAS 2008 developers everywhere (including me, sorry :)), so Power View cannot be deployed instantly, just after some work on creating a TM.
Thanks, very useful information.