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.

SSRS Against an Oracle Data Source

My continuing coverage of integrating Oracle and SQL Server … Today’s post is a bit of a cheat: I’m just going to point you back to a blog post that I did when I was with Oracle where I showed you how to leverage SQL Server Reporting Services (SSRS) against an Oracle data warehouse that was loaded from the Oracle Primavera enterprise project portfolio management product (EPPM): http://blogs.oracle.com/EPPM/2010/05/new_p6_reporting_database_r2.html.

The reason that I felt compelled to post that example when I was with Oracle is that I would constantly run into customers who were using 1 of 2 products for ad-hoc reporting: Crystal Reports or SSRS. And usually SSRS and Report Builder where there because customers had existing investments in SQL Server. Of course, Crystal Reports was there because (a) it’s been around forever and (b) most of the customers that I worked with were actually SAP customers, not Oracle Apps customers. Which means that they received Crystal Reports licenses.

Anyway, long story short, that link above is a decent example of how I was able to pull in Oracle database data directly into an SSRS report. Very easy and straight-forward. Also, if you are going to use the SSMA migration assistant from Microsoft to migrate your database from Oracle to SQL Server, you will need to recreate your Oracle Reports in SSRS. There are a few companies out there that will work with you as consultants to migrate those reports for you.