Friday, October 14, 2011

SQL PASS 2011 Summit in Seattle, WA

I wanted to share my top ten list on the topics I felt were significant and received a lot of attention at SQL PASS 2011 Summit. It was reported that this year’s SQL PASS Summit was the largest everyseattle-public-market with 5000+ reported participants. Personally, it is always fun to chat with other SQL Server folks and the SQL Server celebrities (i.e. authors, bloggers, the SQL Server product development team, and the SQLCAT team).

Without any other formalities, here is my top ten list of significant topics at the  SQL PASS 2011 Summit:

Number 10  BI Semantic Model was something the Microsoft BI stack often got discounted for not having when companies did product evaluations against Business Objects, Cognos, and MicroStrategy or in reviews by Gartner Group. The BI Semantic Model (BISM) will expose the complex data structures in a simpler way that use business friendly terms which will become the foundation for self-service and ad hoc reporting for non IT staff.

Number 9   Hadoop connectors and Linux ODBC drivers will be available. These type of connectors will surround these technologies to provide a gateway to the Microsoft BI stack. The Linux ODBC drivers will be useful when doing migrations from databases on Linux to SQL Server.

Number 8   Powershell is the scripting language for administration for Windows, SQL Server, and SharePoint. There was a lot of buzz on learning Powershell to do setup and administration. Many of the setup tools actually use Powershell in the backend and provide the script before it is executed. This is a nice feature as the script can be saved to a file for reuse latter or be a good way to learn Powershell.

Number 7   Change Data Capture extended to Oracle was announced to be in SQL Server 2012.

Number 6   Data Mining - Noticeably missing in action was any update on the Data Mining Excel 2010 Add-On in the SQL Server 2012 release. Currently, the Data Mining Excel Add-On is only officially supported for Excel 2007.  I was told by a SQL MVP that the SSAS team was focused on delivering Power Pivot v2 and Power View, but that updating the Data Mining Add-On for Excel 2010 is unofficially planned within the next year. There was a lot of buzz about using DMX queries to visualize data mining output in SSRS and using DMX queries to bring insights into SSIS or online web applications. This is a big positive. The message to me was that the pieces are in place to embed data mining insights to end users. I also had the chance to get familiar with a company called Predixion (aka the old Microsoft Data Mining team).  They had an interesting cloud based analytics tool that used Excel 2010. It looked promising and to be tailored for small to medium sized companies.

Number 5  SQL Server Reporting Service (SSRS) will support data driven alerts created by end users. This will be a first version so formatting is somewhat limited, but the idea will be that a user can create their own alerts to be emailed to them when data conditions exist that require immediate action. Additionally SSRS will  be available as a service application in SharePoint. This improves performance to get integrated mode on par with native mode and simplifies setup. SSRS will still be available in native mode, but integrated mode will be required for the new alerting feature.

Number 4  There was a lot of buzz about “AlwaysOn”. This is high availability (HA) feature that uses something know as Available Groups (AGs) which are a combination of the best of SQL Server Clustering (same IP) and database mirroring while not having a requirement for share storage. AGs support off site replication. This should make HA easier to deploy and reduce hardware dependencies.

Number 3   Improvements in information retrieval of unstructured documents (PDF, Word, and Excel) in the SQL Server 2012 database engine. There are performance improvements in Full Text Search, but the two more significant new features called Semantic Search and File Table. Semantic Search gives the ability to find documents that are similar to a given document. File Table is a new object is SQL Server 2012 and uses FileStream functionality to query the files in a given directory. The File Table object is update immediately as soon as files are copied to the directory the File Table is configured to. Semantic Search is very exciting to me.

Number 2   Column Store index is database structure that stores data using Vertipaq which allows data to be stored in a more compressed format to reduce physical IOs. Historically column store technology was a high end feature available in products like Teradata and Sybase IQ. The column store index will significantly improve performance of queries that scan many rows, such as queries that scan large fact tables. It was reported that queries taking minutes can now take seconds.

Number 1   Power View (formerly know as Project Crescent) is the ad hoc reporting application presented through a browser via Silverlight. Microsoft made a huge investment in this technology as it is seen as Microsoft's answer for mobile BI. Power View will allow users to create reports using the BI Semantic Model. The reports are very interactive and support self-service ad hoc reporting. It also supports animating charts on a time dimensions to visually show trend information changing over time. Power View is very visual and very eye catching.  All in all, Power View was the buzz of PASS 2011. It carries a huge WOW factor that end users will be please with. With any good thing there is a catch. The catch here is that implementing Power View in most environments is not a trivial task. It involves multiple technologies, security topologies, and communication protocols. The challenge will be to understand SharePoint 2010, PowerPivot, SQL Server Reporting Services, Excel Services, SQL Server Analysis Services, BI Semantic Model, Sliverlight, and the various security architectures (classic Windows authentication and claims authentication) to successfully implement this solution in medium to larger environments.