All the Pieces: Microsoft Cloud BI Pieces, Part 4

It is now time for part 4 of my 5-part series where I am walking you through the different Microsoft product and solution pieces to build a Microsoft Cloud BI solution. There are a few parts that I’ve called out thus far that are “hybrid”, i.e. not yet fully cloud-based. But today’s installment is going to focus on the presentation layer and we’re going to deploy these dashboards solely in the cloud, via Microsoft’s Azure platform. First, we’re going to use the new CTP (beta) of Azure Reporting Services to host a simple AdventureWorks dashboard that I built using Report Builder 3.0 in part 3 of this series. And, yes, that is currently a client tool that you need to have a local copy of to make this work.

So we’re still quasi-complete in Cloud BI, still somewhat hybrid. But I’ll use our local copy of Visual Studio 2010 to build a simple ASP.NET application that will include the ReportViewer control hosting that Azure-based report, deployed in Windows Azure. Then another ASP.NET app will include a very simple, rudimentary example of a Silverlight control to display data from our SQL Azure database. Both of these presentation methods of Cloud BI use the same tools that you use today to build the classic SQL Server based dashboards and reports – Report Builder, Reporting Services, SQL Server, Visual Studio, Silverlight and ASP.NET. Instead, I’m just using SQL Azure, Windows Azure and deploying into the cloud instead of on a local server. Already being familiar with these tools and languages (T-SQL, MDX, .NET and C#), make the transition into cloud with Microsoft’s Azure platform quick & quite easy.

Let’s start with the ReportViewer control in Visual Studio with SQL Azure. Again, not much different than what you can do today where you host an ASP.NET application that has the ReportViewer control embedded in a form and you point that control to your report. The report is usually best to have hosted in SQL Server 2008 R2’s Reporting Services (SSRS). But since we’re talking cloud here, we’ll need to host the report in Azure Reporting Services using SQL Azure as the database source.

When working with the local emulators for Azure, which I am doing here for these demos, make sure that you are running Visual Studio 2010 with elevated privileges, running as admin. I create a new Windows Azure ASP.NET application from the templates, using C# and now I can edit the app and the forms. I’ve added 2 Report Viewer ASP controls as you can see below. If you’ve done this before in Visual Studio to SQL Server and SSRS, then you are familiar with both the screenshot below as well as the ASP code snippet. What it is interesting about this, to me, is that you aren’t going to have to learn anything new to migrate to the Azure cloud-based SSRS for Cloud BI.

ProcessingMode="Remote" SizeToReportContent="True"
    Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
    WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="770px">   
                    ReportServerUrl=https://zzzzzzzzz.ctp.reporting.database.windows.net/ReportServer

When I run this, I get my report from the Azure Reporting Services server, using SQL Azure as the data source and funny looking dashboard, which I’m sure that anyone can make look better than I can:

There are complete examples, demos and hands-on labs that you can run through from the Microsoft DPE team in the SQL Server 2008 R2 Developer’s Training Kit here, just make sure that you are downloading the April update with the newest updates. You can use that code to reproduce what I did here in a step-by-step color-by-numbers fashion.

Lastly, I’m going to give you an example of using the Silverlight data grid control to use data from SQL Azure. To do this, I am going to first create a WCF RIA service that will talk to the SQL Azure database and present the data to my Silverlight control for the presentation in the web page. The service that I am using comes from an excellent example of Silverlight data binding that I have modified for Azure over the past year or so. It is available here and it is by Mahesh Sabnis, so many kudos and thanks again to Mahesh.

This is not a Windows Azure tutorial, this is a Microsoft Cloud BI series based on SQL Server. So I’m going to skip some of the complexities of creating the RIA Services to provide data binding for the Silverlight Chart component. You can find more on the Silverlight Charting Toolkit from this video by Jesse Liberty here.

The Silverlight form will be a regular Windows Azure application and my simple design will look something like this with the charting controls on the MainPage.xaml:

Now, take the WCF service class, which will bridge the Silverlight control to the backend database, and In the connection object in the class definition, you can point your connection to the SQL Azure cloud database:

Conn = new SqlConnection("Data Source=xxxxxxxxx.database.windows.net;Initial Catalog=mark;User Id=xxxxxxx;Password=xxxxxxxx;Pooling=true;Min Pool Size=5;Max Pool Size=100;Connect Timeout=15;");

Now that I have a Windows Azure Silverlight app built as a shell with a WCF service and the MainPage.xaml. I can wire up the service reference and connect it to the chart control to the DataContext. Also, the Silverlight chart series values will need a KeyValuePair mapped to a result array from my RIA service reference:

    MyRef.clsSales[] colSalesDetails = e.Result;
    //KeyValuePair array is the data source used for all chart controls
    KeyValuePair[] arrSalesDetails = new KeyValuePair[colSalesDetails.Length];

    //Store Data from Result to the KeyValePair array
    int count = 0;
    foreach (var item in colSalesDetails)
    {
         arrSalesDetails[count] = new KeyValuePair(item.CompanyName, item.Sales);
         count++;
    }

    //Display data in Column Chart
    chartCompanywise.DataContext = arrSalesDetails;

    PieSeries pieSalesChart = chartCompanywise.Series[0] as PieSeries;

    pieSalesChart.ItemsSource = arrSalesDetails;

And what does this look like when I run it? Well, I’ve got a couple of simple charts that are charting data in my Cloud BI dashboard from SQL Azure using Silverlight. Note, though, that this is not data from Analysis Services. This is straight SQL data queried from SQL Azure:

Not too bad and not too difficult. I have 1 more part to go and that will be part 5, where I will focus on the future of Microsoft Cloud BI and take a look at some new, recent announcements, trials and betas that Microsoft has made available. Thanks! Mark

Comments

Plain text