All the Pieces: Microsoft Cloud BI Pieces, Part 1

I’m going to try to expand a bit on the posting that I did last month describing different definitions of “Cloud BI” and what that means in Microsoft-speak.

Starting here in part 1, I am going to walk through a complete example of building a Cloud BI solution using only Microsoft technologies from SQL Server, Azure and Office. By then end of the series, I will probably not be able to put a complete solution “in the Cloud”. If I want to build an analysis cube around my data, I can store the data in SQL Azure in the cloud, but my cube will have to be on-premises in SSAS or PowerPivot. But I can build a Cloud-based application and dashboard in Azure that is using data from SQL Azure, in the cloud, and I can now create cloud-based reports using Azure Reporting Services (CTP).

Let’s start with the assumption that we’ve built a data mart in the Cloud using SQL Azure. We have transactional data from our stores in Adventure Works stored in a centralized data-center based SQL Server 2008 R2 database. This is the classic starting point of a business intelligence solution: move the data from transactional systems into a data warehouse or data mart for analysis and reporting.

For SQL Azure, you can get your data and schemas to the cloud with several different tools. The newest CTP of the Data Sync framework will now move SQL databases from prem to cloud, back & forth, cloud data centers, etc. The classis Codeplex project called SQL Azure Migration Wizard moves data & schemas from on-prem SQL Server to SQL Azure. It’s a great free tool, very simple, very engineer-based. Works great, using BCP and takes care of lot of the SQL Azure-specific house-keeping things like creating clustered indexes for all tables that you are transferring to Azure that do not have clustered indexes. SQL Azure requires1 for each table. I give this entire discussion much more attention in my MSSQLDUDE blog here.

What I am going to use is SSIS because if you are doing serious ETL work, you are not going to rely heavily on a simple data movement tool like BCP or a migration wizard. You need to transform, modify, integrate, etc. The funny thing is, I always argue that SSIS may be more used by database administrators for SQL Server maintenance jobs and simple data movement that it is used as BI tool for ETL to load a data warehouse. I have no empirical data to back up this claim. I just hesitate to blanket SSIS as a “BI tool”.

Anyway, I digress. SSIS works just fine & dandy using SQL Azure or SQL Server together in a data flow as a source or a target. As you can see below, I’ve created connection managers in ADO.NET for my SQL Server on-prem and used OLEDB for my cloud-based SQL Azure connection:

And when we run it, we successfully load, transform and move that data into a data mart table on my SQL Azure database:

Nothing fancy or tricky about this. Just remember that SQL Azure connection are SQL Server authentication, not Windows authentication. To me, this is one of the clear benefits and advantages that Microsoft has built into the Azure platform. Azure is truly a platform as a service (PaaS) offering that does not require much, if any, retraining. If you are already using SSIS, SSRS, SQL Server and .NET, then you will quickly and easily transition to the Azure world.

When we get to the presentation layer in SSRS & .NET apps, you will see that we can transition and re-publish work that you’ve already accomplished against the classic SQL Server into Azure.

Now that we have loaded data into SQL Azure for our BI solution, part 2 will focus on preparing that data for analysis, reporting and publishing.

Go to PART 2: Microsoft Cloud BI Pieces.