Use Report Subscriptions for Multiple BI Consumers

This is part 4 of our series on creating cool dashboards, focusing on utilizing Microsoft’s Silverlight technology as a way to create compelling ways for your users to interact with BI data. Previously, we touched on downloading & starting to become familiar with PivotViewer and Derek showed you mobile dashboards for Microsoft BI with tools like PushBI, RoamBI and DS Panel. A theme that I hope you are observing throughout this conversation is that we are showing how you can accomplish your goal of creating cool dashboards without needing to dive deep into programming. To keep with that theme, I am going to talk today about utilizing Report Builder 3.0’s capabilities to schedule reports that can output ATOM data feeds, which can be easily consumed by Silverlight custom controls or PivotViewer as well as PowerPivot. Generating data feeds from Reporting Services gives you the ability to access SQL Server data sources such as relational tables or MDX and then re-use those reports as a way to feed that report data into other sources such as Silverlight. It is a convenient mechanism to use to generate BI content throughout your Microsoft ecosystem without needing to duplicate effort and without needing to write a lot of code.

By establishing a data feed subscription in Report Builder, for example, you can place an ATOM data feed version of your BI reports that were built using a tool from Reporting Services, into a location that can be consumed from PowerPivot, PivotViewer or your Silverlight user controls.

When you’re using PowerPivot, you will use the Data Feed data source as in the screenshot below. In PivotViewer, syndicated data sources can be utilized via . Setting up the scheduling and delivery is straightforward with little to no programming involved and the screenshots below also include the data feed output that you can use directly in Report Manager when executing a report. There are some caveats with scheduling reports that are part of the security requirements for Reporting Services. Be aware of those, such as requiring that the credentials are stored as part of the report data connection so that it can run unattended.

Once you have the data in PowerPivot as a data feed, you can schedule the refresh updates on that data source and use the data directly in your pivots, reports and scorecards in Excel. To use this data a source in PivotViewer, you can use the PivotViewer Excel add-in to generate a Pivot Collection in Excel, which can come directly from the SQL Server data source or from PowerPivot, using PowerPivot as the source with the data feed already consumed. As stated in earlier postings from this blog series, you publish your collection from Excel and then point to that published collection in a Silverlight app, for example: Pivot.LoadCollection(http://content.getpivot.com/collections/mkromer/myfeed.cxml, string.Empty);

As far as plugging a data feed into a Silverlight control binding, this MSDN article steps you through using the XmlReader with Syndication Feeds here. Note that this article references Silverlight 4, which I personally found much easier for data binding than earlier versions, such as Silverlight 1.0. Also note that the MSDN article makes several assumptions that you now how to figure out which references to add to your project and the namespaces you need to use. All in all, I felt that most intro-level programmers can make your way through those steps. Just understand that throughout our tutorial, binding data directly to Silverlight controls as opposed to using PowerPivot, PivotViewer or Report Builder will be the trickiest part. But I have found that using the data feed output from Report Builder is the easiest way to leverage all of these capabilities together in a comprehensive Microsoft BI visualization strategy for your enterprise dashboards.