Reporting Services 2012 on iOS

A couple of months ago Microsoft announced support for iOS (ie iPhone, iPad and iPod Touch) in Reporting Services 2012 with the release of SQL Server 2012 Service Pack 1. During the Christmas break I thought I'd try it out.

The basic instructions are here - and they are pretty straightforward. The result is that Reporting Services reports render in Safari on iOS devices. A screenshot of an AdventureWorks sample report on an iPhone (in landscape mode) follows:

As you can see all of the interactivity is available as well as the ability to pinch-to-zoom in and out of a report and swipe-to-pan around it.

A couple of things to note:

1. Reports rendered in the Report Manager don't appear to work - the web page is blank. So if you're relying on this serve reports to users you're out of luck, its the web service report viewer only.

2. As the screenshot demonstrates even a relatively simple report doesn't format all that well on the small amount of screen-estate available on an iPhone. The Reporting Services implementation won't help much with this issue. This is slightly mitigated by the additional touch-enabled navigation options, but I think bespoke iPhone reports will be necessary.

3. Trying to find software to emulate iOS on Windows is hard! Most are on-line only which require a publicly accessible website. The iOS SDK appears to be OS X only. In the end I had to borrow an iPhone to demo it.

4. Remember if you're trying it out using Reporting Services on your Windows 7 machine to open up your Windows firewall!

SQL Bits X - the 'take-aways'

I've just about recovered from attending SQL Bits X in London and I thought I'd share the points that I took away from Thursday and Friday.

Overall the conference was well-organised and probably twice the size of the last one I attended in Brighton. On Thursday alone they had about 600, and to quote Chris Webb there were 'loads more' on Friday. Its still organised by volunteers and does not make a profit, and is comfortably a match for the other more established conferences (such as Connections, TechEd or MS's own BI Conferences). So many thanks to all those who made it happen.

One other note - what follows is my own personal take on each session, as much for my own benefit as anyone else's and any mistakes or omissions in relaying them here are entirely my own.

SSAS Deep-dive
Akshai Mirchandani
On Thursday I went to Akshai Mirchandani's SSAS deep-dive session, which slightly confusingly, was merged with Cathy Dumas' 'Putting tabular models into production' for the first hour. This actually worked quite well as they went through the pro's and cons of SQL 2012's Analysis Services Tabular vs 'old' Analysis Services cubes (now re-branded as 'Multi-dimensional'). The major take-away for me here, was the fact that MS has committed to add Multi-dimensional (ie cube) support to Power View (aka 'Project Crescent') 'before the next major release'. Not sure exactly what that means - whether we have to wait for a service pack, an 'R2' release or even a Reporting Services-type release. Which if you recall, originally came out in 2003 as a seperate install (before SQL 2005). In any case this could still be a couple of years away.

Additionally MS is committed to supporting both Tabular and Multi-dimensional into the foreseeable future. However, whilst Multi-dimensional will get incremental development, Tabular will get the most investment. As an aside my colleague spent the rest of his day in the Tabular session and identified some serious limitations in the current version around KPI's, which will limit the use to which we can put it. Anyway the controversy over the BI Semantic Model and Multi-dimensional versus Tabular has been covered at some length elsewhere and so I'll leave that particular hot potato.

The rest of Akshai's session was 'deep' as advertised (it was based on the three-day meastro course) and way out of the scope of this blog post, however the following points stood out for me;

• Full processing of cubes blocks queries - no surprise - but it is also blocked by queries. This could lead to a situation where processing can't complete because of long-running queries. In turn other queries that started after processing began would be waiting for the cube to complete processing (ie release its 'pending' lock). In order to mitigate this change the 'ForceCommitTimeout' property. This sets the amount of time the processing will wait for a query to finish before cancelling it.

• Always consider keying string attributes by using a integer, since there is a 12-byte overhead for even the smallest string. A smaller key means better querying and processing performance.
  

• Similarly use an integer value to key degenerate dimensions. It may even be worth experimenting with a separate database table for degenerate dimensions. Also try indexing the degenerate column in the database.
  

• Its usually worth explicitly setting the partition-slice (ie the 'Slice' property in a partition) explicitly. This is for various reasons, described here and here. Further, some customers use regions or time-zone slicers in addition to date in order to support rolling partition-processing by time-zone.

• Finally its worth remembering that when constructing a calculated measure its worth evaluating to NULL rather then 0. This improves performance by taking advantage of block computation algorithms

Introduction to Microsoft Codename "Data Explorer"
Miguel Llopis
The first session I attended on Friday was about 'Data Explorer'. This is a beta product from Azure Labs that is essentially a self-service ETL tool. Despite being a beta product it is fully-featured with a long list of potential data sources such as SQL databases, text files, Excel, OData and even tables in web pages - the example they used was a Wikipedia page. There is also a wide variety of transformations that can be applied as well as look-ups and merges of seperate data sets. The immediate use-case I thought of was as a way of profiling data sources prior to bringing into a data warehouse via more traditional ETL tools. I will definitely be checking it out when I get the chance.

Building a BI Portal for Tabular Models
Cathy Dumas
Despite the title this was more of a high-level look at the variety of SQL Server tools now available for reporting, rather than a practical lesson in creating portals for Tabular. This included the usual suspects such as SSRS, Excel Services, PerformancePoint and Power View. For me, the key points here were;

• Currently Reporting Services, of all things, has the best support for mobile - as long as you're using iOS (ie iPhone, iPad). The PASS presentation featuring Power View on mobile was in Cathy's words a 'fake'. Apparently it was merely indicative of functionality that will be made available in the future.

• Reporting Services can also be used to report on Tabular models by using DAX by pretending its a DMX query. Its unsupported, but works, and reminds me of similar hacks needed to report on cubes in the first release of SSRS.
  

• SharePoint is the future for BI reporting, for example the next version of PerformancePoint is likely to be much more integrated into SharePoint. If you consider yourself a BI professional, and you want to exclusively use MS products, its no longer enough just to learn SQL, you need to understand SharePoint too. This also means paying attention to SharePoint Cumulative Updates and the next version of SharePoint coming with the monster Office 15 release.
  

• Cathy concluded by suggesting that the best solution for dashboards at the moment was a mix of Excel and Reporting Services. She called out the latter's strength in geospatial for which Excel has no support. Conversly Excel has the advantage of allowing end-users to alter reports rather than costantly needing BI pro's help.
  

Load Testing Analysis Services
Bob Duffy
The penultimate session I attended was devoted to performance testing Analysis Services. The source code can be found here.

• The session principally used Visual Studio Test Edition, which was slightly disappointing for me as I don't think MITIE's MSDN subscription actually covers it!
  

• Despite the reliance on VS Test Edition, there were some good tips. One if which was in the analysis of trace files. Bob suggested using checksum to identify similar queries and take the average response of these rather than focusing on the slowest queries that might represent 'freak' results.

• What came through quite strongly in the case study Bob quoted was that caution needs to be exercised when using more CPU cores to process and query Analysis Services cubes. This is because before SQL Server 2012 SSAS didn't work at all well with NUMA. This can be so bad that performance can actually worsen. In the case study the ultimate solution for this was to run 4 virtual machines on each NUMA node, each hosting an SSAS instance.
  

Unit Testing SSIS Packages
John Welch
John presented his unit-testing framework/software for Integration Services. I'd actually already downloaded this from CodePlex a while ago.

• Its still the only real option for unit-testing SSIS, and John promised that the next release would incude the ability to test individual data-flow transformations which is sorely needed.
  

• One good tip was the use of database snapshots to roll-back changes made by unit tests.

..and finally SQLRelay!

At the end of the Friday we were all reminded that all-day SQL-related community events continue through April and May; details here. I'd thought I'd give a special mention to the Bristol event since its in my back yard. Hope to see you there!

Reporting Services Win/Lose Sparkline - Setting the Axis Line

Win/lose sparklines are a useful way of displaying performance over time, providing you can boil-down performance to a binary 'win or lose' result.

Sean Boon has a good example of doing this here but I struggled to get the Axis line to display in the right place. I thought I'd share what worked for me.

What I found was after setting the axis to visible, as per Seans blog post, that initially the Axis is drawn at the bottom like so:

This just looks daft and gives the incorrect or at least confusing impression of where '0' is.

The answer to this is buried well within the SSRS interface.

1. First select the sparkline chart. Then press 'F4' to see the Properties grid.

3. Find the ChartAreas property and select the tilde to the right:

4. Within the 'ChartArea Collection Editor' select the 'CategoryAxes' property and click the tilde to the right of that:

5. Within the 'ChartAxis Collection Editor' make sure that the 'CrossAt' property is set to '0':

6. Click OK to get back to the 'ChartArea Collectino Editor' and then click the tilde on the right of 'ValueAxes' and repeat step 5.

7. You should now find that the Axis for your win/lose sparkline goes through the middle of your chart where you'd expect. You'll probably also want to remove the tick marks from the Axis by ticking the 'Hide major tick marks' box in the horizontal axis properties.

Configure Reporting Services to use proxy for Bing maps

If you work somewhere that uses a proxy server to connect you and your servers to the internet you will need to configure Reporting Services to use said proxy in order to use the Bing maps layer. Otherwise you will see something along the lines of the following message in your report, instead of a map:

The remote name could not be resolved: 'dev.virtualearth.net'

The solution is easy;

Step one
Navigate to the webconfig file for the instance of Reporting Services you want to proxy out to the internet. Typically for 2008 R2 this will be:

<drive name>\Program Files\Microsoft SQL Server\MSRS10_50.<instance name>\Reporting Services\ReportServer\web.config

Step two
Add the following xml fragment to the file - I usually add it in-between the</runtime> tag and the </configuration> tag, at the end of the file:

<system.net>

        <defaultProxy enabled="true" useDefaultCredentials="true">

            <proxy bypassonlocal="True" proxyaddress="http://<proxy server>"  />

        </defaultProxy>
    </system.net>

Save the file.

This of course assumes your server is allowed to proxy out in the first place (which it will need for Bing maps integration). You can test this by logging onto the server and firing up a browser or by asking your friendly local sysadmin...

Repeating table headers in Reporting Services 2008

You may find it a bit of a challenge to get Reporting Services 2008 to repeat table headers across pages. Unfortunately the tablix properties 'Repeat Row Headers' and 'Repeat Column Headers' appear to do absolutely nothing despite what MSDN says. In fact there is a connect request related to it here. There is a bit of information on the web about this - but not definitive guide and I still found myself scratching my head a bit, so here's my attempt at explaining it:

Step 1:

Select the tablix in question and click the arrow on the left of Column Groups and the bottom of the Design tab, and select 'Advanced Mode':



Step 2:

Under 'Row Groups' you should now see grey bars before and after each tablix grouping marked '(Static)'. These represent the column headers for each group. Click on one of these:



Step 3:

Click the Properties Window (there's no right-click menu on the static groups). On the RepeatOnNewPage property click 'True':




Step 4:

Repeat step 3 for every static group that a repeating header is required. Bear in mind there are the static groups after the tablix - don't set these to repeat.

Then you should be done! Shame this can't simply be done on the tablix property - lets hope MS fix this in a Service Pack at some point.

Strange Reporting Services 2008 error

Fixed a rather odd SSRS error today. A report with multiple tablix controls wouldn't render as a PDF. Looking in the ReportServer error log I saw the following:

System.FormatException: Too many bytes in what should have been a 7 bit encoded Int32.

The issue was related to a divide-by-zero 'error' in one row in the result set from an Analysis Services cube. Analysis Services doesn't actually error in this situation but returns '1.#INF'. The HTML version of the report happily rendered 'Infinity' however the PDF renderer would appear to be using the BinaryReader.Read7BitEncodedInt Method of System.IO and this obviously chokes on the string since it expects a 7-bit integer. Hence the slightly obtuse error message in the Reporting Services log above. Removing '1.#INF' is as easy as doing the following:

WITH MEASURE c AS (IIF(Measures.a=0, NULL, Measures.b/Measures.a))

Mosha commented here that he didn't see the point in removing these values. I guess he hadn't had to render PDF's using Reporting Services at the time!

Update:

After attempting to reproduce the error it appears that Reporting Services normally, is quite happy to render PDF's correctly, even when there are '1.#INF'-type characters in the results. This is the case even when switching from an integer-only field. So the bug is obviously much subtler than it first appeared. I'll post about this again if I figure it out...

Deploying Reporting Services programmatically - shared data sources

Hello again! After a longish hiatus I'm back posting fairly useful stuff - so I don't lose/forget/have to do it again in another job.

The first useful snippet covers deploying Reporting Services reports using the rs utility on the command line. This will be necessary in situations where you can't deploy directly from BIDS - which to my mind isn't good practice when you are outside of the development environment. Although there are various examples of doing this there aren't many using VB.NET (which you have to use with rs) and none covering my requirements.

As you may know when using the rs utility to deploy (technically create) reports on a Report Server it does not maintain references to shared data sources even when they exist in the same relative position given in the Reporting Services project. This means you have the tricky job of adding those references programatically after using CreateReport to create the reports.

Tricky? Well it is if your reports have multiple different data sources. If you try adding a data source to a report that doesn't know about that data source (ie hasn't got a reference to it in the .rdl) you will get an error. Therefore you have to work out what data sources that report has a reference to and relate those references to the shared data sources with the same name elsewhere in Reporting Services. A complicating factor is that you can't (or at least I couldn't) simply map the data sources obtained from the report to the shared data source location. Which is way I ended up looping around the datasources retrieved from the report definition in the code below. Anyway it works and that's good enough for me...

In order to use the example create an empty .rss file in Notepad and copy and paste the following code and save it as UpdateDataSources.rss.

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item as CatalogItem
Dim items as CatalogItem()

Try
items=rs.ListChildren(ReportFolder, False)

For Each item in items

If item.Type = 2 'ie Report

Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)

For Each ds as DataSource in dataSources

Dim sharedDs(0) as DataSource
sharedDs(0)=GetDataSource(SharedDataSourceFolder, ds.Name)

rs.SetItemDataSources(item.Path, sharedDs)
Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")

Next

end if


Next

Console.WriteLine("Shared data source reference set for {0} reports.", ReportFolder)

Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try

End Sub

Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource


Dim reference As New DataSourceReference()
Dim ds As New DataSource

reference.Reference = sharedDataSourcePath & "/" & dataSourceName
ds.Item = CType(reference, DataSourceDefinitionOrReference)

ds.Name = dataSourceName

GetDataSource=ds


End Function

Use it with rs on the command-line as follows:

rs -i UpdateDataSources.rss -v ReportFolder=[Folder Location containing the reports to be updated] -v SharedDataSourceFolder=[Folder location containing the shared data sources].