A few end of 2010 SQL BI thoughts

Many thanks to Derek for allowing me to join him on the SQL Magazine BI blog in 2010! I had a blast writing about Microsoft BI solutions, mobile BI and SQL Server BI for SQL Server DBAs. The postings and series that I worked on here were meant as primers with additional links to material that you can use to broaden your understanding of these areas. With that, I went back to review the different topics that were covered and so wanted to pen a final 2010 blog posting on some knowledge gaps and updates on those subjects here. Happy New Year! And best wishes for a successful and healthy 2011! Please use the comments section to let us know what you would like see as a focus area for this SQL Magazine BI Blog in 2011 …

Mobile BI

There were a number of conflicting reports that I read from marketing analysts and manufacturers of mobile devices from Apple, Blackberry, Android and the new Microsoft Windows 7 Phone. The disconnect in the area of business intelligence on mobile devices seems to me to be around supply vs. demand. It is impossible not to see the continued growth and adoption of mobile computing in terms of mobile phones and slates/pads in both consumer scenarios as well as in business enterprise use cases. That being said, it is not clear that there is demand from businesses to have immediate BI and analytical functions available on a mobile phone, such that it would drive a steep adoption curve of mobile BI. I hear customers ask about mobile BI nearly every day. But at this point in the adoption curve, the feeling I have is that it is curiosity and nice-to-have.

Derek did several write-ups, reviews and demos that you can search on SQL Mag of sites like PushBI and RoamBI to hook up your data sets to mobile apps that can run on iPhone. PushBI (http://www.pushbi.com/) has Windows 7 Phone examples up on their site with video demonstrations now. The adoption of mobile BI in 2011 will continue to increase. Whether it is a continued incremental growth or a sharp spike will likely depend on user demand and IT approval of appropriate enterprise-ready features on devices in terms of security and command & control of the environment.

SQL Server 2008 R2 Enterprise Edition BI

Without a doubt, the biggest, best and most popular addition in all of SQL Server 2008 R2 was in business intelligence and is PowerPivot. Named one of eWeek’s top products of 2010 (see here: http://www.eweek.com/c/a/IT-Infrastructure/eWEEK-Labs-Names-the-Top-Products-of-2010-203236/), PowerPivot is an Excel-based analytics tool that builds cubes in memory, allows on-the-fly data integration and hooks-up to SharePoint with SQL Server 2008 R2 to enable scheduled refreshes, updates and publishing. I touched on it a few times in 2010, but Derek and his work @ BI Voyage have gone into much more depth, so be sure to search his postings. Look for PowerPivot technologies like Vertipaq, to creep into other areas of SQL Server BI going forward on the product roadmap, enabling in-memory analysis throughout the server and BIDS environments. An honorable mention goes to the new BI site types in SharePoint 2010 and Report Builder 3.0 which is much more feature-rich than early version of Report Builder. I have been making extensive use of the built-in Bing Maps capability in Report Builder as well as using the ATOM feed output of reports to use as a source for the most awesome Silverlight BI tool, PivotViewer: http://www.microsoft.com/silverlight/pivotviewer/. We have write-ups on using that control on this blog as well.

SQL Server Data Warehouse Editions: PDW & FastTrack

I’m thinking that in the new year, we should talk more about Microsoft SQL Server Parallel Data Warehouse (PDW) and SQL Server FastTrack. I just finished-up the series on how DBAs can manage and monitor the general SQL Server BI environments (SSIS, SSAS, SSRS) using the assumption that you need to scale-out and will have SAN administrators that can help you with monitoring the I/O throughput and that the SharePoint admins will monitor and manage the BI sites. But I stayed away from the specific Microsoft SQL Server products that include hardware. PDW is the newest SKU from Microsoft and is the culmination of the past few years of the DataAllegro team that Microsoft acquired, transitioning their IP to SQL Server. There are a number of pilots that are wrapping up and case studies being written, so I can report on some of that to you in 2011. PDW is the MPP (massively parallel processing) edition of SQL Server with HP hardware in pre-configured racks that Microsoft & HP will ship to you ready to wire into your data center. It is a scale-out data warehouse appliance and includes new monitoring & management tools as well as extensions to T-SQL specific to its MPP model. FastTrack is also a data warehouse SQL Server offering with hardware. It is more of a lower-cost alternative that uses commodity hardware from HP or Dell (your choice) and includes a dedicated SAN. The key to FastTrack is that you can maximize throughput and consumption rates using concepts that are part of the FastTrack reference architecture intended to minimize fragmentation and maximize contiguous data layouts by leveraging a balanced system that maximizes large bulk reads for data warehouse workloads. As opposed to PDW, the hardware is basically a bill of materials that is more configurable than PDW and is shipped to you and then must be assembled in your data center. Both of these offerings will become more visible in 2011, so we can go into more depth on these products in this blog.

Microsoft Database in the Cloud: SQL Azure

From the big on-premises DW boxes now to the lightweight cloud. SQL Azure has a big future as Microsoft invests heavily in cloud computing and the overall Windows Azure platform. For BI solutions, SQL Azure, is available to use today as a data source to store your data. Go to http://sql.azure.com for a trial or use your MSDN subscriptions. There are not currently any BI-specific offerings available in Azure. But the vision of BI in the Cloud is part of Microsoft’s vision that was touched on in the PDC & PASS conferences in 2010. This will start with Azure Reporting Services (http://www.microsoft.com/en-us/sqlazure/reporting.aspx) which is close to a public CTP at this time. That being said, you can use BIDS, PowerPivot, Excel, etc. to connect to SQL Azure by using the Azure connection string to connect to your cloud database to build on-premises BI solutions from Azure. Another area that is expanding that is a part of the BI in the Cloud story and is available now is called Windows Azure DataMarket. That is a marketplace full of syndicated data sources that you can use to integrate into your analytical applications where you can use census data, industry-wide data, etc. And it is all published in the cloud through Windows Azure.

SQL Server 11 / Denali

Finally, there were several new public announcements from Microsoft in 2010 from the PDC and PASS conferences around SQL Server v-next, SQL Server version 11, code name “Denali”. Since we focus on BI in this blog, here are a few of my favorites so far that address the areas that we’ve covered which I think you should give some thought and research into as you start to gear-up for the next version of SQL Server BI:

1. Data lineage and impact analysis from Integration Services (http://msdn.microsoft.com/en-us/library/bb522534(v=sql.110).aspx). I really like this one because in the MEC project that both Derek & I worked on a few years back, we had built a custom MMC snap-in to help solve this problem. I have not had a chance yet to try this in Denali yet, but it looks like it will be available from SSMS, which is nice. But it does not seem to go into PerformancePoint dashboards or SharePoint sites, which we used to enable with MEC’s impact analysis. Still, this is a very welcome addition to the SQL Server BI stack.

2. Data Quality Services will be integrated into SQL Server from the Zoomix acquisition that Microsoft made. SSIS in 2008 started adding data profiling capabilities. But when I talk to customers about using SQL Server on a large enterprise DW and data quality scale for integration, this sort of capability is sorely needed: http://msdn.microsoft.com/en-us/library/ff877917(v=sql.110).aspx.

3. This is more for developers, but is very important to reporting and cubes in Denali and SQL 11. A new “BI Semantic Model” or BISM (http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx) has been added to complement the UDM that cube developers build today in SSAS. I like this idea because it is more intuitive for BI developers. That being said, we’ll report back to you on whether or not we agree and provide best practices on using BISM over UDM.

4. Project Crescent is an early look into the new Silverlight-based report building tool: http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx

I am very excited about getting more time in 2011 on Denali and will work with Derek to report back some of these findings to you all in the SQL Magazine BI Blog. BTW, note that some of these are very exciting features in Denali and may want to go and try them out. Be aware that not all of these will be available in the current CTP. You may need to wait for the next CTP drop.