SAP TechEd && d-code 2014 Las Vegas – Pre Conference Jet Lag Day

Well I made it to Las Vegas…    My travel from the UK was pretty uneventful, taxi, plane, taxi.  My travel day was a long one actually if felt like 3 days all rolled up into one.  In the morning I was out in the Ashdown Forest with the family all morning, then the flight, and then supper in Vegas.  I went from wearing tweeds to a t-shirt in 1 day…..  phewwww

2014-10-18 09.57.56

2014-10-19 06.50.49

Sunday was an acclimatisation day, not for altitude but for the sensory explosion that is Las Vegas (and to try and beat the jetlag).  Sunday started early with a run along the “strip”, it’s  amazing what you see at 06:30 in Vegas.  Anything from young couples embracing, some doing the walk of shame, people staggering the worse for drink and I was even “High Fived” by a “lady of the night” (must have been a slow night). This run was the culmination of 8 weeks effort as I haven’t really exercised to any regularity for probably 20 years.

At SAP TechEd for the last few years there has been a 5k fun run / walk organised for mid week starting at 06:30.  When I registered for the conference it appears as a extra thing you can do so I signed up as an incentive to start exercising.  Using the Couch to 5K programme I started from not being able to run for more than 30 seconds to running 5k and I even entered a 10K and finished (way down the field, but finishing was the aim).

The actual SAP TechEd fun run is on Wednesday so wait for the update to see how I get on, but I’m chuffed with acheiving my goal of a 5K run along the strip in Vegas


Throughout the day familiar faces from SAP SCN and Twitter have started arriving at the venue so I’m really hoping to bump into some this evening. Maybe some selfies to follow….

My tech bag is all packed and hoodie ready as I’m told the conference centre can be cool. Is you see me please stop me and say Hi !!

2014-10-19 18.24.41-1




Integrating SAP Lumira and ESRI mapping to deliver Location Intelligence.

I recently recreated what is considered one of the 5 most influential visualisations of all time using SAP Lumira, The Causes of Mortality in the Army in the East by Florence Nightingale

To continue this theme I was inspired to try out another one of the top 5 that requires visualising data at a street level based on Longitude and Latitude coordinates. SAP Lumira has allowed plotting data on basic map outlines using Longitude and Latitude coordinates for some time but not on at street level like we are used to using in say Google Maps.

However, in the most recent version of SAP Lumira 1.17 this has changed as SAP has announced a partnership with ESIR the enables integration of their AecGIS online service within SAP Lumira.

Press Release: SAP and Esri Team Up for Geo-Spatial Analytic Innovations at #EsriUC with SAP Lumira and ArcGIS – See more at:


Another one of the Top 5 most influential visualisations of all time:

John Snows Map of the 1854 Broad Street cholera outbreak.


Just to get things straight from the outset, I am talking about John Snow the Physician not John Snow from House Stark in the Game of Thrones

J Snow Jon-Snow-Kit-Harington_510



John Snow’s original Cholera Map


On the 8th September 1854, on the instruction of Dr John Snow, the handle to the water pump on Broad Street (now Broadwick Street) in Soho London was removed. Snow identified that the water from this pump was the source of an outbreak of cholera that ravaged Soho, killing over 500 over the previous weeks.

Snow’s work was ground breaking for its use of data visualisation where he plotted the locations of all the deaths in the cholera epidemic on a map and identified that they clustered around water pumps and particularly around the pump in Broad Street.  More Information:

Recreation using SAP Lumira and ESRI ArGIS

snow - lumira


How To

After a bit of searching I found a dataset that gave the Longitude/Latitude reference of each house surveyed by Snow along with the count of deceased by Cholera. Additionally, there was a similar data file giving the points of each water pump in the locality.

I thought the challenge in this endeavour would be integrating SAP Lumira with ESRI, this was in fact the easiest part.  All that is needed was to sign up for trial access of ESRI ArcGIS and enter the resulting credentials in the SAP Lumira Preferences pane. (Links below)

However, I must admit to doing a fair amount of manipulation in Microsoft Excel to get the combined Longitude/Latitude field into two separate data columns that Lumira will acquire formatted as a Number. SAP Lumira could have split out the combined field into two easily but when creating a geographical hierarchy based on Longitude/Latitude these fields need to be a number data type and sadly the inbuilt convert function had issues.

Step 1

Acquire the dataset into Lumira and create a geographic hierarchy on the location name using the Longitude/Latitude.

Step 2

Sign up for a free 30 day trail of ESRI ArGIS using the link in the Lumira preferences panel and enter the resulting user credentials

lumira - geo preferences

Step 3

Build a visualisation in the using the new  Geographic map.

geo map 2

Step 4

Configure the Layer. Note “Count” is used twice, in SIZE sect the measure, in COLOR use the dimension

geo config



The finished article:

snow - lumira

In closing

Be aware there is no Lumira cloud integration for ESRI mapping at this time.  Also, I’m not an ESRI customer so I am unaware of ESRI licensing costs to use ArcGIS.

And one more thing..

If you visit London why not be a data visualisation tourist and visit the John Snow Pub on Broadwick Street and see the water pump

the pump





  • Raw Data File attached.



Reworking Florence Nightingales “Diagram of the Causes of Mortality in the Army in the East” with SAP Lumira

Recently I visited an exhibition at the British Library titled “Beautiful Science: Picturing Data, Inspiring Insight”.  This exhibition “explored how our understanding of ourselves and our planet has evolved alongside our ability to represent, graph and map the mass data of the time”. One exhibit stood out for me: The causes of mortality in the army in the east by Florence Nightingale. 2

Florence Nightingale, (1820 – 1910) was a celebrated English social reformer and statistician, and the founder of modern nursing. She came to prominence while serving as a nurse during the Crimean War, where she tended to wounded soldiers. She was known as “The Lady with the Lamp” after her habit of making rounds at night. (Source Wikipedia)


I have looked at this visualisation numerous times in the past but it struck me that Nightingale’s diagram represents the opposite risk to that you see normally. An image may be so visually interesting, so iconic, that we assume its conclusions without examining its data. I have definitely been guilty of looking at this visualisation as a piece of art but not listening to the story the data was trying to tell.

How should we read this data visualisation?

The Economist suggests how to interpret the diagram: As with today’s pie charts, the area of each wedge is proportional to the figure it stands for, but it is the radius of each slice (the distance from the common centre to the outer edge) rather than the angle that is altered to achieve this. The areas of the blue, red, & black wedges are each measured from the centre as the common vertex. The blue wedges measured from the centre of the circle represent area for area the deaths from Preventable or Mitigable Zymotic diseases, the red wedges measured from the centre the deaths from wounds, & the black wedges measured from the centre the deaths from all other causes. Put simply we have three data series plotting deaths by a custom grouping of cause over time.

What is the story being told?

If you study this visualisation long enough you will hopefully see that over a 2 year period that the number of deaths from preventable deaths grew to a peak in January 1855, but then reduced dramatically over the following 15 months. I found deciphering the visualisation to understand the story honestly quite difficult.  In today’s world there is a desire for the story a data visualisation is telling to jump out of the page and hit the reader right between the eyes.  Additionally I feel that creating statistical diagrams in that period of time was only possible by a small number of academics and trained individuals. But, again in today’s world we want to empower many people to Access, Analyse and Share data of all kinds.

The History

On 21 October 1854, Florence Nightingale and the staff of 38 women volunteer nurses that she trained were sent to the Ottoman Empire. They were deployed in the Crimea, where the main British camp was based. Nightingale arrived early in November and her team found that poor care for wounded soldiers was being delivered by overworked medical staff in the face of official indifference. Medicines were in short supply, hygiene was being neglected, and mass infections were common, many of them fatal. There was no equipment to process food for the patients.  After Nightingale sent a plea to The Times for a government solution to the poor condition of the facilities, the British Government commissioned Isambard Kingdom Brunel to design a prefabricated hospital that could be built in England and shipped to the Dardanelles. The result was Renkioi Hospital, a civilian facility which had a death rate less than 1/10th that of Scutari. (Source Wikipedia) I have often said any data visualisation must have a “mission statement”, a reason to live and a story to give. In the case of this visualisation I understand that it was developed with the specific aim of driving the important military hospital reforms through government to change lives The first edition of the Dictionary of National Biography (1911) asserted that Nightingale reduced the death rate from 42% to 2% either by making improvements in hygiene herself or by calling for the Sanitary Commission (Source Wikipedia)

Is there another way?

The dataset for this diagram is publically available and is less that 100 data points. So using SAP Lumira I have given below a few different ways of visualising the data that should make it easier for to gain the intended insight 4   5

I was hoping to show a new feature in SAP Lumira version 1-16 to animate the data over time which dramatically displays the swing in the number of deaths between 1854 – 55.  Unfortunately, SAP Lumira version 1-16 is not GA at the point of sharing this blog but I intend to update this blog appropriately in the future.


Is SAP Design Studio like teenage sex?


You may well have seen this analogy before for the topic of “Big Data”, could the same be said for SAP Design Studio?

I may be barking up the wrong tree but a year or so after its launch SAP Design Studio appears to be making little traction in the dashboard space for SAP in the UK market.  (I base statement on my contact with companies through my day job and engaging with business users through the UK&I SAP User Group and Data Visualisation Special Interest Group.)

To be honest the majority of companies I have spoken to thus far come from the classic BOBJ perspective. Is the real adoption rate different for companies who use SAP BW as their primary datasource instead of the classic BOBJ user community using the universe connectivity?  

Is this observation the same worldwide?

Why aren’t companies adopting SAP Design Studio?

We have seen in the Dashboard statement of direction from SAP that there will be unification between SAP Dashboards (aka Xcelsius) and SAP Design Studio in the future and migration tools to bring Dashboards into Design Studio.  So why aren’t companies adopting SAP Design Studio now at the start the journey rather than holding off?

My musings are:

  • People are holding off as the data visualisation and dashboard space is moving very fast, just look what SAP Lumira has achieved in a similar time frame to Design studio.
  • People aren’t even evaluating Design Studio as unless you are hooked into a SAP User group (UKISUG, ASUG) there is little marketing or education as to the future for dashboards and they just don’t know about its importance.
  • People are evaluating Design Studio and identify it requires a developer with specialist skills with they do not have internally and would need to  engage with a central IT function or consultancy providers
  • People are evaluating Design Studio but it is seen as too complicated to use with its reliance on Java code and Custom Style Sheets (css).
  • People are evaluating Design Studio but it is seen as still too juvenile to adopt as core functionality needed is missing.
  • Users would rather have something on their desk than can build and deliver themselves rather than endure a dashboard project delivered by the IT department
  • You have to ask yourself why  products like Decision Point from Antivia are coming to market? Is SAP Design Studio seen as too difficult for the existing developers in the SAP dashboard space by software companies and they want to offer a alternative solution?

When oh When will Design studio be as easy to use as SAP Dashboards (Xcelsius)?  Is that the right question?  Is that the plan by SAP ? I’m not so sure. 

I would draw a parallel with Dashboards and Design Studio with Crystal Reports and Web Intelligence.

ds Crystal Reports didn’t get easier to develop content in when Web Intelligence was bought to market, it still today does what it does very well and is a core analytic tool for SAP.

However, in my opinion moving from Dashboards (Xcelsius) to Design Studio could be seen as the reverse, the more complex tool is being released to replace the simpler tool.

The diagram below shows my understanding of the content creators for the SAP Analytic tools in BI4.

Screenshot 2014-03-19 10.29.55

There are still lot’s of technologies in the hands of the Business User but we see here that the future technology recommended  to author “Dashboards”  ( “What is a dashboard really” has moved from being one used by Business Users to now an IT function.

Real life feedback

Reaching out to end users who have tried Design Studio gave some interesting feedback. Thanks Alex for the comments below as someone who has tried Design Studio against BW and the Universe.

“What left me very disappointed was the amount of code required to make things happen when stuff like this seems so simple in WEBI for example. Creating a Navigation URL took 6 lines of code! Then what annoyed me even more was the fact that the link only worked if you clicked on the last column of the crosstab?”

“My Boss loved the output I hated the input! Nevertheless I pursued and tried to create more applications with his requirements, I then hit an even bigger stumbling block, line chart, Data retrieval failed. Upon investigation of this by turning on the trace and watching the logs it was limiting the amount of data it could retrieve. I found the answer myself in the documentation saying it is  (universe connectivity) limited to 5000 rows or 50000 cells. I am not trying to show this much data I was trying to show a count of Incidents by Year and Month”

What about business user created dashboard style visualisations?

At this time I see multiple technologies used by business users to deliver a “Dashboard” including:

  • MS Excel
  • Dashboards (Xcelsius)
  • Exploration Views in Explorer
  • Web Intelligence
  • Web Intelligence delivered to the mobile device

What will business users use to create dashboard style visualisations in the mid term?

Below is clear advice from SAP on what technology they encourage to be used.  


My personal hunch is that business users will still use the core technologies they are used to in the short to medium term and only branch out Design Studio if it gets easier to use.

Is there another option?

Its again early days but I think some companies may well start investigating SAP Lumira storyboards published to the newly released native HANA platform application SAP Lumira Server.   Yes you will need HANA and integration to the BOE Platform is planned but only as a side car.

SAP Lumira Server 1.15 is now Generally Available (GA)!

SAP Lumira Server Frequently Ask Questions

Something to ponder ……

Microsoft Power Query Connectivity to SAP BusinessObjects Universes – How to get started

It has recently been announced that ” Microsoft and SAP are jointly delivering business intelligence (BI) interoperability in Microsoft Excel, Microsoft Power BI for Office 365, and SAP BusinessObjects BI.”    Microsoft Power Query for Excel will seamlessly connect to SAP BusinessObjects BI Universes, ee Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI for more details

I had a spare hour this morning so wanted to give this connectivity a try out and gain a quick understanding of what this really means to users.

The System Requirements are:

  • Microsoft Power Query preview add-in installed in MSOffice Excel 2010 or Excel 2013, alternatively
  • SAP Business Objects Platform version 4.1 with SP2
  • SAP BI 4.1.2 REST Web Service installed in BI4.1 SP2

I went through the steps that the videos suggested on Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI, and yep it does take a while as the video suggests.  But...   I believe it’s not necessary to register for the trial and provision Power BI for Office 365 as we will be working in a local addin install in Excel on your laptop.

 Install Microsoft Power Query

1.  Download exe from Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI

2.  Run the appropriate 32bit or 64bit installer

3.  After installing Power Query, there will be a Power Query ribbon in Excel.


 Connecting to a universe

To connect to an SAP BusinessObjects BI Universe, select From Other Sources in the Power Query ribbon. Navigate to the bottom and select the From SAP BusinessObjects BI Universe data source.

When prompted, enter the URL for the SAP BI 4.1 system you’d like to connect to (e.g.: http://<host>:<port>/biprws).

CARE:  This URL took me a while to get right, you would assume it’s the Host and Port Number you use to access BI Launchpad, alas not.  After a number of failed attempts and a large mug of tea for inspiration I had a moment of inspiration.  The system requirements stated SAP BI 4.1.2 REST Web Service installed in BI4.1 SP2 are needed, this lead to a bit of research, and these are done automatically, which is great.

You can use the Windows installer to add RESTful web services to your custom BI platform deployment. RESTful web services requires an instance of the Web Application Container Server (WACS), which is installed with RESTful web services if it does not already exist. RESTful web services was introduced in BI platform 4.0 to Feature Pack 3. • If your BI platform 4.0 FP3 is a new installation, RESTful Web Services is automatically included in the installation. If you choose custom install, RESTful Web Services is selected in the feature tree by default.  If you are upgrading from 4.0 SP2 to 4.0 FP3, after completing the upgrade, use the Programs and Features Windows Control Panel, Uninstall/Change feature to add the RESTful web service.

But, the REST Web Service installed in BI4.1 SP2 are installed by default using port 6405, so the URL needed to log in is:


 Select the Universe from the other data source droplist



Enter the URL discussed earlier with port 6405


Next Enter your credentials for the service. Use the options in the dialog to view all possible credential types. Choose the credential type supported by your service and enter the necessary credentials. Once connected, you may see some progress information in the dialog.

 After successfully connecting to the service, you will see a list of the available SAP BusinessObjects BI Universes in the Navigator pane. You can drill into the items in the Navigator and select SAP objects to transform.  In my demo system I have a UNX universe accessing data on UK Road Traffic accidents since 2005, this UNX is now visible in the Navigator panel and I can select objects to display in Excel.


Click on LOAD DATA and you now have Data from your trusted universe inside Excel very simply


 My conclusion

This was a quick technical how to get started with Microsoft Power Query and connecting to SAP BusinessObjects Universes.

But, just stop and think …… Live Office from SAP has been around for more years than I care to mention, I believe it came out of SAP Labs originally and is the current primary way to access universe data within the MSOffice suite of products.  Live Office offers integration of both new queries and report parts from Crystal and Web Intelligence into Excel, Word, PowerPoint and Outlook, this new integration with Power Query is understandably limited to Excel but is also limited to new queries only. Some way to go to have parity of functionality if that is the vision from SAP and Microsoft.

I am left with this question in my mind ..  Could this be a replacement to Live Office going forward?   I personally think not, but I see this as a great complementary use of technologies.

What do you think ?

Validating the BBC’s statement that “England has had the wettest January since records began in 1766”


Working at home with the rain pounding on the window and iTunes shuffle brings up Storm by Vanessa Mae, random, coincidental or inspirational?  Well for me it brought together an article I have read about in the UK news about the weather we are experiencing this winter and a tweet by @steverumsby this morning.

bbc news


So the hunt was on to find UK rainfall statistics for as many years as I can.   Thankfully the UK Met Office has a fairly open view to data and historical climate data was easy to find.

The data download looks fine to identify spot numbers but I needed a way to easily visualise it in a way to validate the statement in the BBC article that “England has had the wettest January since records began in 1766”

data 1

Firstly I did a bit of “Quick Analysis” using MSOffice 2013

Quick Analysis

Click to enlarge

HHmmm   The red dot which identifies the High point is not on the last record, 2014.  So is the BBC wrong ?

Alas no, the dataset is for the whole of the UK and not just for England.  Try again….

data 2

Click to enlarge

Yep the high point for Jan is in 2014, so in this data set Jan 2014 has the highest rain fall since 1910.

Now  to analyse this further as this data brought on loads of other ideas and hunches to validate on the wider UK dataset. I wanted to use SAP Lumira but the shape of the data as a crosstab (pivot) table wouldn’t work well in SAP Lumira so I needed to flatten it into a columnar format.  Easy you’d think, alas no, well not for me anyway.

I resorted to google and found a small piece of VBA to do a large portion of the work.

The VBA on transpose crosstab table to list:

Sub ConvertTableToList() 
Const TEST_COLUMN As String = “A” 
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = iLastCol To 3 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 2).Value = .Cells(i, j).Value
.Cells(i, j).Value = “”
Next j
Next i
End With
Application.ScreenUpdating = True
End Sub

With a little more effort in Excel I now had a good data table to be used.

lumira data

After importing the data into SAP Lumira, I simply plotted the Year and Monthly rainfall figures.

Data Quality

Spot the data quality issue?  

Whilst working in excel I had labelled the year 1923 against the records for 1923 and 1924.  A great example of why visualising data draws out things you’d miss in a 1250 row table. After fixing the data in Excel it was easy to refresh the visualisation in SAP Lumira.

Adding on the predictive calculations really didn’t give much hope for lower rainfall for the next 10 years…


You can download the SAP Lumira File here:

Recreating the author Andy Kirks data visualisations using SAP Lumira

Last night I was fortunate enough to attend a class presented by the author Andy Kirk which was a super speedy whistle stop tour through lots of content from his recent book “Data Visualisation – a successful design process”. It was an unplanned event for me as I responded to a tweet saying there was 1 ticket left and as the event was taking place just 10 mins walk from my companies London office i jumped at the chance.



It was a great class not only for those to whom the craft of Data Visualisation was new to them but also had some great nuggets for those wanting to optimize their design approach.

As part of the class there were practical activities and one of note was to explore a data set on obesity trends from theWorld Health Authority.  Delegates were asked to look at the dataset in excel and identify data variables and ranges, ways they might transform the data and crucially identify some possible data questions and interrogations.  Andy then shared his answers and his data visualisations with the class.   This activity got me thinking ….


Could I use SAP Lumira to undertake these tasks and recreate the data visualisations ?

Andy kindly shared the dataset with all the delegates and on my train journey home I started to play.  I acquired the data set into SAP Lumira which was only 11,580 rows and 14 columns.  Not a large dataset by any means but packed with insights to be drawn out.   I then set out to reproduce each of the visualisations discussed in the class using SAP Lumira.


Visualisation 1


Andy Kirk




SAP Limura

Not too difficult but I had to reduce the number of data items shown to the top 50 to allow me so see and therefore select (highlight) the USA ans UK



Visualisation 2


Andy Kirk




SAP Lumira





Visualisation 3


Andy Kirk





SAP Lumira


Now this was the toughest one in the set as SAP Lumira doesn’t have this chart component type in it’s library (an opportunity for an enterprising developer using the SDK maybe).  So I worked through a number of different ideas but sadly had to pivot the data outside of SAP Lumira in Excel and add multiple data sets to structure the data in a shape to drive the visualisations I had in mind. This method worked easily and I will repeat this approach in the future.




Not as easy to read as the original




You know I just love Pie charts !!  This kind of works but the scale doesn’t expose the differences well enough.



My thinking turned a corner …   In Andy’s visualisation you are comparing the gap between the BMI in each gender by region, you could call this the variancebetween the gender scores.  So I set to work on showing the variance between the gender as an absolute number and then plotting how it changes by Region and between 1980 and 2009.


You can easily see the changes in the variance for example in Africa between 1980 and 2009 has widened by nearly 1 full point.  This approach worked but it masked the gender split.


Then again my thinking moved on to using a Radar Chart which I think is the clearest and closest to the original representation by Andy Kirk.






Visualisations 4 & 5


Andy Kirk





SAP Lumira





5 viz.JPG

These seams to be a “bug” in Asc and Desc sorts in SP13 but the visualisation are pretty much there.



Visualisations 6 & 7


Andy Kirk




SAP Lumira






Visualisation 8


Andy Kirk




SAP Lumira


Now this didn’t look tough until you think about the volume of data points plotted.  193 countries x 30 years = 5790 in a 6 zone trellis chart.  Sadly SAP Lumira couldn’t render the chart.




BUT …..   If you can build one for one region then there is further possibility:




Use the new COMPOSE feature to build a storyboard with all 5 regions displayed.






It took me about 3 hours to prepare, explore the data and build all the visualisations and I’m really happy with the results.  With more time refining the titles, colours etc. I think SAP lumira could really step up to the mark in delivering high quality Data Visualisations.




Content reproduced with the kind permission of Andy Kirk, visualisation blogger, designer, consultant, author, teacher, trainer and speaker