Search This Blog

Friday, July 25, 2014

Power View report migration from SharePoint 2010 to 2013

With the latest changes to the Microsoft Power BI and ability to create Power View reports within an Excel file (starting from Excel 2013) perhaps there will be no big need to migrate old stand alone Power View reports which were based on Power Pivot data models created in Excel 2010. You can just recreate you Power View analytics in Excel 2013 and off you go: users can see and interact with new reports either using desktop or Office 365 versions of Excel; and not to worry about SharePoint deployment platform.

However, if you really want to bring your old Power View reports from SharePoint 2010 to SharePoint 2013, it is still possible. During the time working on this task and I couldn't find any automotive tools that would enable me to migrate both the Power Pivot model file along with its related Power View report; so all the techniques described in this post are pure file upgrading and manipulation (so if you find some tools for this I would really appreciate).

Basically, in the old SharePoint 2010 and Excel 2010 world you could create a Power View report with two artifacts: (1) Power Pivot data model in Excel 2010 file and (2) Power View report which is based on that model.

First thing I did was upgrading the Power Pivot data model from Excel 2010 to Excel 2013 by just opening my old data model file in Excel 2013 and consented to upgrade the data model (here is the official information for this: Upgrade Power Pivot Data Models to Excel 2013). It took less then a minute to do the upgrade in my case, it make take longer depending on a size of your model. And then I deployed this upgraded Power Pivot data model file to the SharePoint 2013.

I could have done the same thing with the Power View .rdlx file by just copying it to the new SharePoint location; however the report still was connected to the old Power Pivot model file; and this needed to be fixed.

1) I've changed the file extension of my Power View .rdlx report to .zip and opened it as an archive file:

2) Then I went to the [Reports] folder where my PowerView report definition resided in the [report.rdl] file:

3) And then I just edited the data source connecting string and directed it to the upgraded Power Pivot data model file which had already been saved in the new SharePoint 2013 instance:

4) Then I saved my [report.rdl] file in that opened .zip archive; changed the .zip back to .rdlx and saved it in my new SharePoint 2013 Power View gallery.

There were a few gotchas that I needed to adjust in my upgraded Powev Pivot data model after this. What happened that during the data model upgrade all my custom column renaming and sorting were lost and this resulted in breaking some of the relationships within the data model and thus my adjusted Power View report didn't show all the charts at they were in the old SharePoint 2010 environment. But with a few adjustments to the data model, the new Power View report looked exactly like the original one with only difference that now it was in SharePoint 2013 environment.

It worked, and it worked well for me :-) 

Happy data adventures!

Tuesday, July 22, 2014

SSRS report subscription: Fire Event from SSIS package

My previous two posts about SSRS reports being deployed to a SharePoint site along with their subscription didn't stop me from exploring the whole things of SSRS reports subscriptions; regardless of their deployment environment either native or SharePoint mode.

After exploring different aspects of the subject (Subscriptions and Delivery) you'll become familiar with setting up delivery types, extensions and other metadata for your subscriptions (data-driven subscription brings even more flexibility :-). The only thing that puzzled me was how I could test each of the subscriptions: schedule, schedule and nothing but the schedule; and you even don't have a UI option to initiate or trigger subscriptions.

However, MS Reporting Service has a way to create an event that will trigger an SSRS report subscriptions no mater what those schedule definitions are. You can simply call a report server stored procedure dbo.AddEvent and provide two parameters: @EventType='TimedSubscription', @EventData=@Subsciption_ID (primary key from the dbo.Subscriptions table). And whatever action is defined in your subscription, it'll get performed!

There is another way to create the 'TimedSubscription' event within a report server, it's a FireEvent method of the ReportingServices web services that you can you call from anywhere. So let's be crazy enough and test this method from an SSIS Script Component that will trigger SSRS report subscriptions which are based in a SharePoint mode report server. Here is how it worked for me:

1) I created a proxy class from the ReportingServices web service (file location and SharePoint site will be different on your side)
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\wsdl.exe /out:ReportService2010.cs

2) Then I compiled ReportService2010.cs into DLL:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe csc /target:library ReportService2010.cs 

3) Signed this DLL file (Adding strong name for the library)

Details for this step are taken from here:

   a) Generate a KeyFile  
       sn -k keyPair.snk

   b) Get the MSIL for the assembly   
       ildasm ReportService2010.dll /  

   c) Build a new assembly from the MSIL output and your KeyFile 
       C:\Windows\Microsoft.NET\Framework\v2.0.50727\ilasm.exe /dll /key=keyPair.snk

   d) Install the DLL in to the GAC 
       gacutil -i ReportService2010.dll

5) Created a Script Component task in my SSIS Data Flow task

6) Diverted sourcing column with the Subscription IDs data to this component

7) Added reference to the ReportingService DLL in the script component 

8) Placed a code to execute FireEvent method of the ReportingService web service

and executed my SSIS package! :-)

In a few moments I was able to see the results: emails were sent out and report files had been created; and I didn't even have to wait for schedules.

I hope that those 8 steps were detailed enough, otherwise let me know if you need more details on this.

Happy data adventures!

Tuesday, July 8, 2014

SSRS reports in SharePoint mode, Saga #2: PowerShell generates email subscriptions

Life is full of opportunities, it only takes to notice and use them. About 3 months ago I wrote a post about deploying reports using a PowerShell script (PowerShell, help me to publish my SSRS reports to SharePoint!) and at my new project I proposed to apply the very same technique of deploying reports to different environments. There was only one new thing to this, each of the SSRS reports at this new project had several data driven subscriptions (emails, file shares) and to recreate them manually, it would take a big junk of your life time.

So, that was my new opportunity that I didn't want to miss and decided to enhance my original PowerShell script even more, why not, let's utilize PowerShell potential and bring SSRS and SharePoint even closer.

Having this said, here is the complete PowerShell script (PS_Subscriptions) that does:
- Deploy SSRS datasources, datasets, reports,
- Maps all datasources and datasets for reports when it's necessary,
- and Creates report data subscription.

Main knowledge on how to automate SSRS subscription came from CreateDataDrivenSubscription method of the ReportingService assembly; also be sure to check this Microsoft knowledge base article ( that shows some of the corrections that were applied to the original examples of the CreateDataDrivenSubscription method.

Let's start from the main changes to the original script. I created two new functions that removes old subscriptions and creates new ones (CreateDataDrivenSubscription method doesn't overwrite old subscription with the same name, it just creates same name subscription with different subscription IDs):

Also, don't forget to specify a necessary delivery extension for SSRS reports, because there are 3 of them available (Report Server Email, Report Server DocumentLibrary, Report Server FileShare):

Main steps to create a new SSRS report subscription can be described with these steps:
1) Set delivery extension setting
2) Create the data source for the delivery query with data query and fields
3) Define subscription schedule
4) Set parameter values
5) Execute CreateDataDrivenSubscription method

It's been a great experience to apply PowerShell techniques in creating SSRS report subscriptions; and I've spent a few days writing this code; however I'm sure that more experienced PS developers could accomplish this task in a few hours. Nevertheless, I've learned a few lessons and would like to share them with you.

Lessons learned:

a) If you create a new datasource, then use DataSourceDefinition; otherwise if it's a shared data source use DataSourceReference.
b) Don't use parameters labels (as they're shown in SharePoint), but use real parameters names:

c) Lack of matchdata schema explanation; look at your ReporServer backend table for the XML tag examples in order to understand a schedule creation.

d) Delivery Extension parameter values are case sensitive; for Email and Sharepoint file location they're different: for example, "IncludeLink" for Email and "RENDER_FORMAT" for SharePoint file.

e) Weird error about method not being supported; check all the variables, parameters format and value and test your script again.

I've been using PowerGUI tool ( to develop and test all of my PowerShell scripts, perhaps you can suggest other PS GUI environments. But in the meantime, I continue wishing you happy data adventures!