Mobile Business Intelligence Series: Part 1 – Mobilising Native SSRS

This is the first part in a series about implementing Mobile Business Intelligence. Interested readers may like to know that I’m presenting on this topic at a number of events, including SQLSaturday 162 in Cambridge, UK, and at SQLSaturday 170 in Munich, Germany. I also presented on this topic at SQLMidlands in July 2012, and also in SQLSaturday South Florida straight after TechEd North America 2012.

In this series, I will suggest different ways in which people can mobilise reports using a variety of technologies. If I am missing some from the list, please let me know! I should add that Copper Blue or myself are not affiliated or partnered with any of the organisations that  appear here. These are simply solutions to business problems that I’ve come across, and thought it might be useful to share here. 
One question is this: how can we mobilise standalone SSRS reports without SharePoint?  Here are some suggestions, which are dependent on your mobile device:

At the time of writing, Reporting Services isn’t viewable on an iPad in SharePoint 2010.  Issues that you might find include the following:

Native Mode Report Manager not fully functional
Some reported drop-down issues, for example, if you have more than 6 parameters. In this case, you might want to consider PowerPivot. I will look at this later on in the current series.
Vertical text does not render properly. In any case, are you sure that you need to use it?
Calendar control issues

How is it possible to view Reporting Services on an iPad? One option is to use MobiWeave, which displays SQL Server Reporting Services reports.  It’s possible to download an evaluation copy from the MobiWeave site. Here are the main features: 
  • Download and View Reporting Services Reports from multiple reporting servers
  • Supports SSRS in SQL Server 2005, 2008, 2008 R2, 2012
  • It also supports Azure SSRS in Native and Sharepoint modes with SSL
  • Parameters support – remember that this requires specific ways of interacting on the iPad
  • Interactive mode which supports Drill down and Drill through reports
  • Bookmarks and History will also work
As a Windows fan ( I can’t wait until I get my hands on a Surface!) I like Blue Granite Nitro. Blue Granite uses XML-formatted data to produce dashboards and reports. The XML data is generated using an application server, such as SQL Server Reporting Services. This tool is available from the Zune MarketPlace (how I love saying that!), and you can take a look here.

These solutions might be a good option for people who don’t have SharePoint but still want mobile business intelligence. 
Next up, we will look at the various options for mobile Business Intelligence in SharePoint 2010 and SharePoint 2013 Preview. I’m a SharePoint fan so it’s next up! I look forward to your comments.

SQLUniversity: PowerPivot, Tableau and Jedi Knights

This blog will show an overview of how I mobilised PowerPivot using Tableau. I’ve previously given this session at SQLBits, NEBytes Microsoft Technology User Group, and SQLServerDays in Belgium but thought it would also be useful to supply the files for you. The steps are very simple since I intended to show the end-to-end solution simply as a proof of concept, as follows:

  • creation of a PowerPivot which mashed up UK Census data with geographical data
  • creation of the report in Tableau
  • deployed to Tableau Public for consumption by mobile devices such as the iPad
The example was deliberately kept simple in order to prove the concept of PowerPivot being mobilised. 
The data sample involved mashing up two sources:
  • Jedi Knight census, data, which can be downloaded from here This is a basic file but the final PowerPivot can be downloaded from a link later on in this article
  • Geonames offer an excellent free download service, which you can access here
The Jedi Knight data, along with the geographical data, were joined using the outcode of the postcode data. If you need more definitions of the UK postcode system, I’ve previously blogged about this here.  
Essentially, a very simple RELATED formula was used in order to look up the latitude and longitude from the UKGeography table, and put it into the Jedi Knights data, and produce the necessary data in a simple Excel table. The formula looks like this:
Once these very simple formula were put in place, it was time to load the data into Tableau.
Tableau can take both PowerPivot and Excel data – which driver to use?  I used version 6 of Tableau. Whilst this version of Tableau does see the PowerPivot correctly as an Analysis Services cube, it does not always read the date as a ‘date’ type, but instead as an attribute. There is a forum posting on the Tableau website which tells you how to fix this issue, which involves changing the date so it appears as a measure, which means it can then be used for trends and so on. 
However, I wasn’t comfortable with this solution because I like dates to be in date format. I’ve also run into this issue at customer site, where the customer wanted to use SSAS as a source and Tableau as the presentation layer. They were data-warehouse savvy and didn’t like the ‘measures approach’ fix. 
On customer site, I got around it instead by using the Excel data source, and importing all of the PowerPivot columns into an Excel 2010 sheet. By doing it in this way, date formats were preserved. In this example, I didn’t have date format so it didn’t matter – but this is a useful tip for the future if you are using PowerPivot with Tableau. The final data, in an Excel PowerPivot, can be obtained in zip format here or if you can’t access it, please email me at jenstirrup [at] jenstirrup [dot] com.
Once the data was accessible by Tableau, I used the Tableau Desktop version to upload the data into Tableau’s memory. I did this so that I could eventually upload the Tableau workbook to Tableau Public. The instructions to save to Tableau Public are given here
Once the data was in Tableau Public, I just needed to access the data using the Safari browser on the iPad. In case you are interested, the demos are publically accessible and you can access the final result by clicking on the hyperlinks below.
I hope that’s been a useful overview of PowerPivot, and the ease of which it was mobilised. This blog forms a use case of how it might be useful to use PowerPivot, since I think that people sometimes need examples of how PowerPivot can benefit them. In this case, the clear benefit of PowerPivot is to provide an easy way of mashing up different data sources.
I look forward to your comments and thank you for sticking with me for the PowerPivot SQLUniversity discussions!

SQLUniversity: Introduction to PowerPivot and Mobile Business Intelligence

Here is the second post for SQL University, where we introduce the topic of mobilising PowerPivot data. As an introduction, I thought it might be useful to share the presentations that I did recently. Tomorrow’s blog will talk more about the details of mobilising PowerPivot data using Tableau, but I thought that these articles might provide some introductory material for people to use, and re-use, in order to expose the usefulness of PowerPivot as a data source. Here is a brief overview:

Essentially,the Ordnance Survey and Census data were mashed up together and linked together in PowerPivot. The PowerPivot was then used as a source to Tableau, which was used to visualise the data.  As an introduction to the process, I generated some presentations to use when I was talking around this solution. and I have provided these presentations here.
Here is a very introductory presentation on PowerPivot in Sharepoint. This presentation was designed to be given in around 5 minutes before being supported a series of demos, and the intended audience was people who hadn’t seen PowerPivot previous to the presentation.

The following presentation came from SQLBits, where I gave a discussion on mobilising business intelligence with PowerPivot. Tableau Software was used in order to display the data from the PowerPivot, and the next blog in the series will present more technical detail on how the solution was built.

Here is the presentation that I produced for SQL Server Days in Belgium in November 2011:

I hope that you enjoy these presentations, which I’ve provided for people to use and enjoy as they wish. Tomorrow’s SQLUniversity post will provide more detail on the mobilisation of PowerPivot with Tableau.

Mobile Business Intelligence – Try it out!

Thank you to everyone who attended my SQLBits ‘Mobile Business Intelligence in Action’ session recently. If you are interested to try out Mobile Business Intelligence on your iPad or mobile device, here are the links below:

Jedi Knight Actuals of UK Census 2001 Dashboard 
Jedi Knights Percentage of UK Census 2001 Dashboard
AdventureWorks Sales by Geography Dashboard
AdventureWorks Actuals Sales
AdventureWorks Analysis Dashboard

I haven’t tried this on every browser and every device, so I would be very interested in your feedback.
I look forward to hearing from you. Please leave a comment below, or email me at jenstirrup [at]

Challenge: optimising data presentation on a mobile device

How do we maximize the perception and understanding of data on a minimal screen on a mobile device? The current blog aims to discuss some interesting areas of human perception, such as illusions. These areas are then relate these to the issues involved in displaying data on a mobile device to ensure that the real meaning of the data is communicated clearly and quickly to those ‘on the move’.

As a strategy for displaying data on mobile devices, it is better to maximise the ratio between the ‘ink’ used for decoration, and ‘ink’ used for displaying data. If a lot of ink is use for decorative purposes rather than data display purposes, then the report consumer’s visual processing system is required to do more work in order to understand the message being displayed. It is important to maximize the data/ink ratio in order to simplify the message that is being given in the graph.

There are a number of different issues involved in ensuring that data is presented clearly and concisely in a way that the human brain can process in an optimal manner. One major issue is the area of illusions, in which the image presented to the eye can cause a distraction that can mean that the data is not communicated clearly on a small mobile screen.

One common illusion that can apply here is the Hermann grid illusion. This illusion was discovered by mathematician Hermann, when he was reading a book by Irish physicist John Tyndall. This illusion can make a black and white matrix appear as if it contains grey as well. Although the same light intensity is transmitted along the white spaces in the matrix, the intersections ca
n appear to be grey rather than white. An example follows:

It is known that the Hermann Grid illusion is not just restricted to black and white: it has been found in other colours as well. For example, a red and white matrix may appear to have reddish intersections. The dependency is that there must be a high contrast between the two colours involved in the grid.

This illusion occurs as a result of the cell activity of the retina in the eye in resolving the ‘gain’ or the average power of light intensity in the image. Although a television or computer screen only has one ‘brightness’ setting, the eye can vary the ‘brightness’ setting for different parts of the image. The fovea, of centre of the retina, is the region of highest visual acuity: it has a greater concentration of cells and thus has finer-grained control over the ‘brightness’ settings. So, if you look directly at a grid intersection, the intersection is resolved by the fovea and it appears white. On the other hand, the neighbouring intersections can appear grey. The peripheral areas of the retina do not have the same fine-grained control over the brightness setting. This means that the neighbouring intersections can appear gray as the ‘gain’ or average light intensity of the image is turned down.

The Hermann grid illusion will not work if there is a low contrast between the colours involved in the grid. As a sidenote, this is why Excel has light blue cells with a white background: there is not enough contrast between the light blue and the white colours to confuse the visual system, so the illusion does not appear.

Another illusion which has direct impact on the display of data on a mobile device is the Moiré effect. Moiré patterns are a focus of interest in visual systems, and more generally in physics as an area of investigation of the fundamental substance of the universe. A Moiré effect can be described as a visually disturbing experience, when the consumed image contains a series lines or dots that are superimposed at an angle to a parallel series of lines or dots. An example can be found below:

Here, the Moiré effect can be seen in the horizontal bars that have been produced as a result of a series of vertical lines which have been overlaid by an identical series of lines at an angle. Moiré effects can be used to create some beautiful patterns as well, such as Kolomyjeck’s Moire images.

Moiré effects are also thought to occur in nature as well. Recently, string theorists in physics have an area of study which focuses on Moiré patterns and the creation of the universe. M theory proponents have held that the universe was formed originally out of two membranes, which collided. The vibrations that occurred at the intersection points of the two membranes is posited to be responsible for all of the observed particles in the universe. The resulting pattern between these two membranes is like a moiré effect, and some work is being conducted to understand the different moiré patterns that could be held responsible for producing fundamentally the laws of physics. A pictorial representation of superstrings and M theory can be found below:

Thus, it is important to avoid the Hermann grid illusion and the Moiré effect in the design of data display, particularly for mobile devices. This directly affects the colours and lines used in the display.

With respect to portraying the data so that the meaning of the data is clearly presented, some recommendations are given below:

  • It is recommended that report creators avoid high-contrast grids. For example, a high-contrast grid may invoke the Hermann grid illusion in the report consumer, and this may distract the report consumer from the ultimate message of the data. In fact, if possible, mute grid-lines or even avoid them altogether if possible. Gridlines, whether vertical or horizontal will increase the ‘ink’ on the page; the main message here is to increase the meaning of the data, rather than increase the amount of ink which is not totally necessary.
  • solid colour should be used in bar charts, piecharts and so on; not bitmaps, hatches or grids. A ‘hatched’ graph can seem to ‘buzz’ in the eye of the viewer. This can produce a Moiré effect in the perception of the report user, which can be annoying and distract attention away from the true meaning of the data.
  • there is no need to ‘box’ the colours in a bar chart by drawing black lines around them. This may produce the Hermann grid illusion by producing ‘fuzziness’ at the intersecting black points. Also, this increases the data/ink ratio. It is argued here that the eye is very capable of fine-grained detail, as evidenced by the high visual acuity found in the fovea. Thus, the eye can distinguish clearly between blocks of colour. This is enough to get the clarity of meaning across in the report, as well as reduce load on the visual system by reducing complexity.
  • use 2D images: they are processed more quickly than 3D images. Although these graphs may look nice, they can sometimes be difficult to interpret due to the additional hatching that may be involved in the production of the 3D effect. In order to ensure that the data is understood in a way that does not require an overhead on the visual system, it is recommended that graphs are kept to 2D. Further, this can also maximize the data/ink ratio, which is recommended as an overall strategy.
  • data display is restricted to displaying data meaningfully, and that non-essential images should not be included. This may include company logos, for example. There is no need to include them since they do not add to the meaning of the data; instead, they also serve to reduce the data/ink ratio. Additionally, it is not possible to control elements of the company logo which may produce effects such as the Hermann grid illusion or the Moiré effect; so it is best to reduce them altogether.

To summarise, this blog has discussed some of the physiological and psychological elements of visual processing, and has applied these principles to displaying data on a mobile device. From these psychological and physiological discussions, some recommendations regarding data display have been given here. To add this post to Twitter, please click here