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.


April Catch-up on Recent Goings-on

Time for an end-of-the-month catch-up on the goings-on of the life of a Microsoft SQL Server TSP!

My latest blog entries for the SQL Server Magazine BI Blog are on Enterprise Information Management (EIM) tools in SQL Server 2012 and on using the new SSAS Tabular Model database paradigm using Report Builder as your reporting tool:



I was honored to have my entry on SQL Azure Reporting Services in the latest edition of SQL Server Magazine (now SQL Server Pro) and now online only (subscription required):


I completed my Windows Azure Web application for event registration using Windows Phone 7 and Silverlight UIs and successfully deployed it at Microsoft’s Women Executive retreat in Philadelphia last week. I am making a Codeplex project for the code and will publish the App in the Windows Marketplace soon. Look for a write-up on that on MSDN and this blog shortly.

If you are in the Philly area May or June, I will be presenting the SQL Server 2012 overview for the local .NET Code Camp at the Penn State Abington campus on May 12 and our SQL Saturday on June 9. There I will present DW management in SQL Server and Cloud BI. Both of these events are on a Saturday and include an entire day of free training immersed in .NET and SQL Server, respectively.

Well, back to authoring SQL Server & BI demos, presentations and planning my weekly customer visits for next week … That is a MSFT SQL Server TSP’s life after all!

Mid-July Updates

I just completed 2 weeks of prep and delivery of my new SQL Server 2008 R2 Security Audit workshop, so I’m catching up on the blogs and other publishing matters this week and next week. In the meantime, I’ve posted a new blog on SQL Server Magazine’s BI blog on using Change Tracking and CDC to reduce ETL load times here and a few updates on Twitter around announcements including a free Philly SQL Server User Group evening session on SQL Server PDW in our Malvern office on August 10: http://twitter.com/mssqldude.

I am going to start a SQL Server security auditing series, probably split across 3 parts, starting this week that includes most of the material from my workshop, so look for that and enjoy!

All the best, Mark

Welcome to June!

I just realized that I haven’t posted yet in June and it’s already June 9!

It’s about 100 degrees here in the Philly burbs today and I’ll get things kicked-off this summer with an update on some of my other published musing on the Web around Microsoft SQL Server, BI and data warehouses:

This is my June edition of 5 Qs with SearchSQLServer

Here is part 4 of 5 of my SQL Server Magazine BI Blog series on Microsoft Cloud BI

Stay cool! Talk to you soon … Br, Mark