What Does the New SQL 2012 BI Environment Look Like?

This is sort of a continuation of my recent tool by tool exploration of the SQL Server BI ecosystem in SQL Server 2012 that I’ve been writing about for SQL Server Pro Magazine here and here.

For years, I’ve carried around with me many different high-level data flow diagrams of what and end-to-end BI solution using the Microsoft stack would look like. Come to think of it, I was able to use essentially the same diagram in SQL Server 2005 and SQL Server 2008. Some of the rendering tools changed like Proclarity, PerformancePoint and SharePoint added more BI features. But there was always SSRS, SSIS and SSAS, so I would use something like this below:

When SQL Server 2008 R2 came to market and introduced PowerPivot, I still stuck with this general architecture because PP was still on the uptake / heavy-lift portion of the curve and the majority of production-ready BI solutions were using SSAS for the semantic modeling and cube building.

SQL Server 2012 has changed the game enough such that I’ve started a new data flow diagram in Visio, albeit not as detailed or fancy as the one that I show above.  A big reason for that is (1) I just created this new diagram this week! And (2) it has to evolve over time. As SQL Server 2012 BI solutions using Tabular Model databases and techniques becomes more mature and builds up a larger set of best practices and lessons-learned, then I will update these diagrams and share them here on my blog as well as over at SQL Server Pro Mag.

Now that SQL Server 2012 fully embeds and supports columnar compression through the Vertipaq engine in SSAS, you can build semantic models with Visual Studio or with PowerPivot. To use the Power View visualizations such as I am depicting in this diagram, you will need to have a BI Semantic Model, so I’m now shifting to this guidance in many cases. Using PowerPivot for data modeling, IMO, is very advantageous because it expands the data analyst community to Excel users and data experts and allows for easy trail-and-error style of data modeling whereby Excel becomes the design surface to test your models through Pivot reports.


New MSSQLDUDE Videos: SQL Inventory with MAPS and WP7 Cloud BI

If you would like a short 10-minute intro on how to get started using the Microsoft Assessment and Planning Toolkit (MAPS) to inventory, discovery and record your SQL Server footprint, versions, utilization, users, licenses, etc., then you can have a look at the video that I just posted on our Microsoft YouTube channel for SQL Server data warehouse & BI group here in the East Coast U.S. field organization: http://www.youtube.com/watch?v=VdHs8Ee1iZI. BTW, the MAPS tool is a FREE download from Microsoft.com, see it here. This is version 6.5 of this tool and if you have any version prior to it, I suggest that you upgrade to it and run another SQL Server assessment on your network. This version of MAPS now has the awesome feature of virtualization discovery, proposal and recommendations built-in. This makes MAPS the premier SQL Server tool when consolidation your SQL Server environment.

And if you have a Windows Phone 7 and would like to see what a WP7 Mobile Cloud BI App would look like, take a look at this video that I just posted for our Microsoft field national DW & BI team: http://www.youtube.com/watch?feature=player_embedded&v=P1akSjiZasU#!. This describes the updated V2 improved version of the WP7 App for Microsoft Cloud BI on a mobile device which I described in my slide presentation here.

These are some of the BI data visualizations that you get out of the box with this WP7 app on your phone through the native app:


Catching-up: Updated Goings-on in October

Sorry that I’ve been so busy lately, I have not yet had time to complete part 1 of my System Center series for SQL Server DBAs. I promise to have the SCOM section out this weekend.

I do want to point you all to 2 updating “goings-on” in my world:

  1. We are running a free 4 hour SQL Server 2008 R2 performance monitoring and tuning seminar sponsored by Sebastian Meine of SQLITY.NET on October 20 in our Malvern Microsoft Technology Center (MTC) here in the Philly burbs. You can register for that free event here.
  2. And I finally got around to complete a new SQL Server Magazine BI Blog post today focused on the SQL Server Denali CTP3 release of SSIS, demonstrating the new version control features here.
  3. I am finalizing my demos and presentations for Philly Code Camp on October 15. You can register here, it is FREE and lots & lots of training on .NET technologies and we have BI & SQL Server tracks. This is the 3rd update to my series on leveraging Silverlight in Microsoft BI to make cool visualizations and dashboards. Code Camp is one of my favorite events of all-time, I always have a great time at these events. I also will update my presentation and demos and post those next week, just prior to the event.


Microsoft Cloud BI part II

Head over to the SQL Server Magazine BI Blog where Derek Comingore and I blog about Microsoft BI here. Today, I added part 2 of my series on created Microsoft Cloud BI solutions using SQL Azure, PowerPivot and Reporting Services. Part 1 is also available from that blog site where I created a SQL Azure data mart in the cloud from my on-premises SQL Server AdventureWorks database.

If you have interest in Silverlight BI for mobile or other devices, I’m going to get to that in part 4 and will use some of the techniques that you can find here on MSSQLDUDE blog like those that I talked about at Code Camp and SQL Saturday.

Thanks!! Mark

SQL Saturday Philly: Silverlight BI

Thanks for to who were able to attend my talk this past Saturday at SQL Saturday Philadelphia on Microsoft BI Silverlight Dashboards. I had a great time and hope that you did, too.

If I missed anything here in this blog or post-session follow-up, please use this thread to ask your questions and we can discuss openly here. Or you can always reach out to me directly.

You can grab the presentation that I used here. And if you would like copies of the Silverlight code for the 1.0 MEC controls from the Microsoft Enteprise Cube project, I made a few of them available here previously. I am working on also making available the Silverlight map and pie chart, too. So keep checking back here to my MSSQLDUDE blog for those updates.

BTW, I promised a link to Silverlight support on the MAC, so here it is

And whenever I give this talk, I like to point you to Bart Czernicki’s book Silverlight BI. My opinion is that for using Silverlight with Microsoft BI, it is the definitive guide out there.

Best wishes, Mark

NYC SQL Saturday BI Visualization Follow-up Info

Thanks for joining my session @ NYC SQL Saturday on Microsoft BI cool visualizations!

Let me start you off with a list of important links that I mentioned today and how to get access to the demos and cool Silverlight BI dashboard controls that I demo’d.


This is the super-cool Silverlight-based control from Live Labs in Microsoft that is now available as a CTP for Sharepoint. Here is the link for the online live demo with Netflix data. This is the PivotViewer blog and here is the PivotViewer download for SharePoint. I was demoing the version from laptop using the JIT PivotViewer which you get here.


Remember, most of what I demo’d in terms of spatial, GIS and maps are all available natively in SQL Server 2008 / R2, SSRS and Report Builder. The Silverlight advanced map that uses Bing Maps API is available on Codeplex in a project called Data Connector. Go there to download and try out the sample. It is very easy to set-up and get working against your database.


The Silverlight 1.0/2.0 graphs that I showed at the beginning were from my project @ Microsoft prior to SQL Server 2008 and I will post the code for those controls in a subsequent follow-up posting here on my MSSQLDUDE blog. But the current up-to-date Silverlight style graphs that I demo’d with transitions and smooth graphics were from Visifire. Go to their site here to get the XAML and try it out. You can use the free version that I showed with the disclaimer on the web part. But I don’t want to recommend one Silverlight control vendor over another. There are plenty out there that you should examine before purhcasing. Thanks to Russell Christopher, a fellow Microsoft Technology Specialist, for turning me on to Visifre, though. Their demo is very cool. And if you would like to get your hands on some spatial data and location data like lat/long try out geocoder, CodePlex SQL Spatial tools for loading shape files and viewing maps here and Sharp GIS has some good tools and samples to work with.


Let us not forget the old standby tools that are the bread-and-butter of the Microsoft BI stack for us SQL Server pros. The capabilities in the enterprise edition of these tools is outstanding as you saw at the SQL Saturday this week. The out-of-the-box mapping capabilities with Bing Maps is phenomenal and you can make very, very good dashboards like these below with the base tools.


We had so much fun talking and demoing these technologies and so liitle time on Saturday that we never made it to mobile BI! Below is a screenshot of a mobile BI screen on Windows Phone. You can use tools like that one from PushBI or another good online tool is RoamBI. A parnter of ours that I work with alot is BI Voyage. Check out Derek Comingore’s demo of RoamBI which allows you to quickly build an iPhone based Microsoft BI solution using your data from their website.

Thanks again and here is a link to the presentation that I used. Best, Mark

Silverlight Map Controls for Microsoft BI

Just up with latest post on SQL Mag BI blog to complete the series on Silverlight for Microsoft BI here. It’s a final part of the series that Derek and I worked on to show Silverlight, mobile apps and other cool ways to visualize your Microsoft BI solutions and make them more compelling. Check it out, enjoy and send me your feedback & comments! Thanks, Mark