Posts Categorized: Architecture

compare-icon

Syscompare – a tool for comparing repos across HANA instances

compare-icon.png

A couple of weeks ago I was moving code from 1 hana instance to another trying to keep them in sync. However, I thought there might be a better alternative for comparing the contents of the repos across my systems to ensure that the files matched. After doing some digging and not finding a solution, I decided to write a small tool to do just this, called Syscompare. It is a open source HANA app which uses the new File API and compares the files on each system and displays the differences.

You can read more about the application here, and find the files for the HANA app in Github.

 

Features:

- Compare repos across HANA instances

- Display file differences in the application

- Highlights missing files on each instance

Usage:

- Setup the 2 XSHttpDest files

- Specify the source and target systems (using the xshttpdest file names)

- Specify the repo to compare

Once the processing is complete the app will show a summary of differences:

Screen Shot 2015-06-16 at 10.18.20 PM.png

Screen Shot 2015-06-16 at 10.20.12 PM.png

Screen Shot 2015-06-16 at 10.20.51 PM.png

You can checkout the Github/Source code here: paschmann/Syscompare · GitHub

If you prefer to download the Delivery Unit – please click here and provide your email address (this way I can keep you up to date with changes): metric² | Real-time operational intelligence for SAP HANA

Interested in contributing to the project? Please feel free to fork or submit a pull request.


bg_top

metric² for iPhone and SAP HANA

 

metric² for iPhone lets you monitor your SAP HANA instances from your phone showing you alerts, core resources and important metrics. Wherever you are.

As mentioned in my GitHANA article, developing on the metric² open source project has really provided some interesting use cases for me around SAP HANA. While it might not be as critical as ERP, BW or custom solution, the metric² demo system is used fairly regularly by people wanting to test drive the functionality. I recently had some server troubles and my HANA instances was down without me knowing. This promoted me to develop a small mobile app to monitor and ensure that my instance was available and running optimally. This is when metric² for iPhone was conceived and I started developing the free app.

 

 

The app is currently availble for iPhone, and I have a iPad version getting ready to be submitted to the App store. From a technical perspective the app uses a small XS file called mobileapi.xsjs which will need to be put in a package on your XSEngine instance to serve up the data to the app. You can specify how often you would like the data to be refreshed and specify multiple systems which you may need to monitor. (I have included my demo HANA instance as an example within the app so you can try it out.)

http://metric2.com/img/alerts2.png

 

The app is perfect for anyone running a HANA instance, be dev, test or production. It provides a really easy way to view the status of your system from anywhere using your iPhone. The app also downloads updates in the background and will notify you if any high alerts are experienced on the selected system, this is perfect for any sys admin/dba who will be to anticipate critical outages and be ready for the support calls.

A few features of the app

- View CPU, Disk, Memory consumption
- View open alerts
- Insights into your HANA instance quickly and from anywhere
- Add multiple HANA instances for monitoring
- Clean and simple UI for basic admin functions
- Push notifications for high alerts, when the app is in running background

Click here to find the GitHub project (of the mobileapi.xsjs file) and click here to check out the product page. This includes install instructions.

Technical Details

Building a native iOS (read Obj. C or Swift) which is integrated with SAP HANA is not terribly challenging and you really have 2 options for pulling or pushing data. Via a XSJS type file (like this app) or via a xsOData type of interface. Both have their pro’s and con’s but are fundamentally very similar. Below is a snippet of some of the code from my xsjs file and looks/acts very similar to what a regular AJAX call would use from a native XS app.

One of the biggest challenges for production users, like any intranet based resource, will probably be gaining access to the URL (mobileapi.xsjs) from outside the corporate net and will probably require a network admin to grant you access or configure (or reuse) a reverse proxy or firewall.

Screen Shot 2014-08-22 at 1.08.56 PM.png
Screen Shot 2014-08-22 at 1.12.40 PM.png
XCode iOS Pull Data Code
Screen Shot 2014-08-22 at 12.52.08 PM.png
SAP HANA XSJS Code serving data to the iOS app

pastedImage_0

Hacking SAP HANA Web Sockets

Disclaimer: This is not a production or documented feature – its also more of a hijack than a hack 

 

I have been hoping for the inclusion of Websocket support on the HANA DB platform for a while now, and I was a little disappointed it was not packaged in the SPS08 release. My goal when building apps (or products) is to make use of the core platform its running on as much as possible, I firmly believe that when convincing an IT department, or company, to implement a product or app, the first question is: “How much infrastructure does this need?”. This can often be a deal breaker and why I am such a big proponent of the HANA’s DB + XS App Server integration – it consolidates the requirements into a single investment. Having a Websocket technology built directly in XS can be an additional selling point which developers are starting to expect these days.

A little while ago I wrote a blog post on building a dashboard using the awesome Node.js package from Holger Koser, however I have really been wanting to use Websockets in the metric² platform since the get go. Some comments here and here are prime examples of my long lasting hope of seeing the technology being included in the XS Engine platform sooner rather than later. I recently had a little nudge again from John Patterson to dig back into the topic and did manage to hack something together. The most interesting part of this was that once I had it working, I was left wanting just a little more …

Firstly a little bit about Websockets and why I feel they important to the app/web development world …

Real-time: In the age of having blazingly fast DB’s, we need a UI & server side (In our case XSJS files) integration layer which can display the data to user with as little over head as possible. Web Sockets supports this by providing a low-latency, near real-time connection between a client and the server.

Light Weight: Currently we need to do multiple AJAX calls to our backend server (to the XSJS files) to either perform some complex logic or DML against our database. This is slow and also fairly network intensive as each request (and response) requires additional handshakes and the packet size is considerably larger than just the intended content inside the package. In Web sockets, the requests and responses are really just the content themselves.

Duplexity: Web Sockets by nature are Full-duplex, implying that we can send and receive data at the same time for multiple requests.

Persistence: Web sockets provide a “open” connection between the server and client after the initial upgraded HTTP request handshake has been made. This lets us perform multiple server side requests using the same connection. This also lets the server initiate a response without needing a request from the client side.

The importance of these 4 factors to the web development world and to HANA XS specifically is that this is the missing link for us to take Web applications to the next level.

[Cross-domain support is another great feature!] In this example I was able to successfully have the HTML + JS file local to my PC and execute it against my HANA XSWS service (via a public URL).

– 07/25/2014 — Chris Paine made a good point on potential Cross-domain security issues (see below). Keep in mind that these files are secured just like any of the XS content files, e.g. as long as your folder has a authentication requirement it will persist to this XSWS file as well.

So onto the more interesting HANA specific parts …

 

I initially realized that HANA XS was using Web Sockets in SPS06, when for some reason the XS Engine debugger was not loading correctly due to my user missing some permissions. After searching through the XS code I came across the folder where the debugger was saved and it included a interesting file with the suffix of xsws i.e. XSWebService. After doing more digging I found that Websockets were being loaded in the WebDispatcher config file and I was confident I could find a way to start using it for app development.

After spending some time trying to create my own HANA package with these file extension types I realized that the name is somehow being filtered and only this file, and more specifically, in this location can be executed, otherwise the XS Engine passes back a 403 (forbidden) – I was a little disappointed but it didn’t discourage me … and I decided I would simply re-purpose my Debugger.xsws file for my own needs After a quick backup, I was ready to do some coding …

Essentially, a xsws file is just like any xsjs file, with the exception that it has some socket code returning the responses versus your regular xsjs file. You can do things like $.import for additional libraries as well as perform $.sql functions. Here is a small snippet from the code over on Github.

Debugger.xsws

  1. $.ws.onmessage = function (evt){
  2.     handleIncomingRequest(evt);
  3. }
  4. $.ws.onerror = function (evt) {
  5.     $.trace.debug(“error on connection: “ + evt.message);
  6.     throw new Error(evt.message);
  7. }
  8. $.ws.onclose = function (evt) {
  9.     $.trace.debug(“connection closed, disabling debugger”);
  10.     debugObject.enabled = false;
  11.     throw new Error(“Close status “ + evt.code + “:” + evt.reason);
  12. }

 

 

And this is some of our Client side code making calls the xsws service:

App.js

  1. // Create a new WebSocket. This works fine
  2.   var socket = new WebSocket(‘ws://<ENTER YOUR HANA SERVER HERE>/sap/hana/xs/debugger/api/Debugger.xsws’‘xsCrossfire’);
  3.   // Handle any errors that occur.
  4.   socket.onerror = function(error) {
  5.     console.log(‘WebSocket Error: ‘ + error);
  6.   };
  7. // Send the message through the WebSocket.
  8. socket.send(message);

As you can see – the code and requests are very simple and straight forward, in fact to me they are a little easier than jQuery + AJAX.

One caveat I did find was connection persistence in the event you have an error on the server side, the socket connection can break, in this case you would need a error handling state that attempted a reconnect before submitting any new requests.

A quick screenshot of the running test app i developed and how the server is sending persistence frames pings/pongs to validate the open connection along with the client request and server response.

If you are interested in trying this out on your test or dev instance I have posted the code on Github. Follow these simple instructions to get up and running …

1.) Using the Web IDE, open SAP -> HANA -> XS -> Debugger

2.) Make a backup of the Debugger.xsws file or simply comment out the contents.

3.) Paste the code into the file from Github

4.) Create the Websocket.html file and paste the contents of the Github file

4.) Create the app.js file and paste the contents of the Github file

5.) Open the Websocket.html file and enter any SQL statement

(Be sure you have the debugger security role)

As you can see from the files, the required methods for web sockets are really at a minimum and barely get in your way at all.

Conclusion

At the start of the article I mentioned I was left wanting a little more … this was mainly because since I have been wanting Web Sockets for such a long time, I realized that using it , alone its not really enough. In order for us to really take XS Engine to the next level, we also need to consider a “Publish/Subscribe” feature and a server side timer feature.

The Pub-Sub approach would essentially allow us to push data from the server side, on a specific occurrence of an event, much like a DB trigger. If a new row gets inserted, push the new inserted record to the registered subscriber.

The server side timer feature would allow us to create a server side timer (similar to a xsjob) which would persist and execute the server side function every x secs/mins/hours.

Overall I am pretty impressed with the opportunities Web sockets will bring to the XS Engine. I am hoping they will be included in the next release.


pastedImage_24

Why Demo Jam ROCKS

It was Tuesday night sometime back in 2009 at my first TechEd where I was sitting in the audience and really wishing I was standing up on stage, presenting something inspiring and innovative which would encourage the votes of the demanding audience of Demojam. Well, it took 4 years to creep over personal hurdles, family time and to gain an ounce of courage which persuaded me to post an entry in 2013, this blog gives some insight into what I did, what I would have done differently, and also why Demojam really needs people like YOU!A couple of months ago (May through July) I went through the openSAP HANA course and was really impressed, not only by the DB, but more the HANA XS Engine as an Web/App server. Being a “learn by doing” kinda person I struggled to get the most out of the course content since it really didn’t apply to anything I was currently working on, until I realized the opportunity… As I described in my demojam presentation, I drank the HANA coolaid Personally, it was not so much of the big data aspect that intrigued me, but rather this concept of the DB and the app server really being a single entity from a platform, as well as infrastructure perspective. Simple. I spent a couple weeks learning a lot more, since I had something to apply it against and started developing an app called Metric²(pronounced squared).Image.png“I realized that while there are a lot of applications which are using HANA, there were very few for HANA.”My goal with Metric² was to build a web based realtime platform for showing how your HANA instance was performing, and subsequently, how your business was actually benefiting from having this type system and helping your business run better. The concept was to try and correlate performance with user impact.

OK , get to the demo jam part …

Around the end of August, Metric² was doing what I had intended and I decided to submit the app for Demojam, although, my original anticipation was to build something far more purpose built and “crowd” pleasing. However, I was really hoping that the free perspective, something useful – realtime dashboards – and a good use case – audience involvement – would hopefully win the votes and hearts of a couple techies in crowd. I created a submission video and a couple days after submissions closed, I got a welcoming, but equally nerve wracking acceptance email. Graham Robinson was actually the one who sparked the idea of a Demojam submission with his comment on a small Mac app I developed for HANA called xSync. I both cursed and thanked him for about 2 months straight when I was wondering what I had gotten myself into

After getting accepted, I had a couple interesting challenges, my 1st priority and goal was to deliver the app at Teched, which meant between the acceptance email (around the 20th of September) and October 20th (TechEd) I needed a fully function product. I also needed to put together, and have a solid presentation nailed down. I subsequently neglected the last point and as always, focused more on adding features and functionality to the app. I had conceptual ideas about what I was going to present, and set out to do something a little different by getting the audience involved by using the actual system I built to show its purpose and functionality, but also to make them feel a part of the demo.

Shortly after being accepted you also have the opportunity to have a online session with Tobias Queck and Sebastian Steinhauer, 2 really great guys and coaches who listen to your demojam pitch and provide some really solid feedback on what their thoughts and suggestions are. After speaking with them, it really calmed my nerves and gave me a certain reassurance that I was going to be OK. A great and well needed confidence boost, especially for a first timer.

Well the 30 days between the above mentioned dates just flew by, and before I knew it, it was TechEd time. The first two days of TechEd for me, included the awesome Innojam event – I thoroughly enjoyed myself, but ended up spending considerably more time helping out than I anticipated and did not get as much coding done as possible. Yes, I was still writing code 2 days before the presentation. Adding features, optimizing things and getting the website finished. Innojam wrapped up on Monday evening, I skipped the keynote to do additional coding and practicing my pitch, the Tuesday of the event unfortunately came pretty quickly and my nerves were starting to get the better of me. I was super stressed to go up on stage, especially knowing it was going to be tough to beat the other participants. I was optimistic and pressed on … on Tuesday the demojam rehearsals start around midday. You get to run through your presentation twice, with others watching and the stage/event crew helping you out with monitor setup, displays, etc. You get to meet the funny and friendly Ian Kimbell, and also Tobias and Sebastian who coach you through your pitch once again, providing some really helpful feedback.  This really helps ensuring that your demo goes  smoothly.

A word of praise – The events team are amazing people, they will literally do anything to make are you are comfortable and happy and EVERYTHING you need to be successful. I do not have enough praise for them. They really go above and beyond, and this image should give you an idea of just how far they go.

photo.JPG

Jen Abrahamson (part of the talented events team) gave me this shirt just before the event!

While going through my 1st rehearsal I was so nervous I left out close to 40% of the pitch/demo and still had 2 minutes on the clock, at that stage, i realized I needed more practice and I spent the rest of the afternoon going through my talk over and over again to engrain it as best I could. In between that – as you can guess, I was also doing last minute code changes making some small modifications. Having the audience involved proved to add its own set of challenges, since I didnt have a full time HANA system and using the developer image on AWS, it was far from a very powerful machine, I spent quite a lot of time running load tests, but was never 100% sure who and how users were going to influence the performance and subsequently the outcome of the demo … major stress factor.

Around 8pm the event started and I got my time on stage, I went through my presentation and felt it really went pretty well. It was reasonably well received, with the exception of me forgetting to mention that the app has support for gestures using the Leap Motion. During the demo I walked over to the large monitor to show this functionality, it did not work – making me look like i was swatting or catching flies Boo.


“Tell people what you are doing and why, that way when it does not work, people at least understand what you were attempting to do….”

With around 2 minutes left I asked the audience to open up the demo website on their mobile device and they got to pick which pill they wanted to purchase from my online store (running on HANA) which was being monitored by Metric². Up on the screen you could see the orders being completed in realtime and how the system was reacting to the new load. In the end around 700 orders were completed which was great, and to me, is what made the demo a success.

Link to my Demojam presentation

I was subsequently voted off the stage in round 1, I walked off the stage pretty disappointed … all I could think about was what I would need to do to win it next year. Its official, I am hooked. I now understand why John Astill has done this for so many years over and over again.

In the end, I *really* want to win. I already have around 5 ideas about what I could potentially do next year and I really hope i get the chance to redeem myself and write a sequel to this blog post, but from a winners perspective

Thinking of submitting an entry? Here are a couple tips which I learned:

  • Think small – my challenge was that I developed a full featured app, with loads of features. This is challenging at best just to demo in 6 mins, never mind apply a crowd friendly use case.
  • Your presentation is 50% of your demo – Have a creative and complimentary presentation and you can succeed very easily. (The running demo of John and Greg is a great example of a creative presentation).
  • Creative use of a technology, far outweighs technology being used for creativity. I.e. Don’t just use HANA for the sake of it
  • Come to TechEd prepared, have your 6 min pitch nailed down. I didn’t and it was far more stressful than I had hoped.
  • Even if its the same, make it look different: On Tuesday morning Vishal Sikka and Sam Yen gave some great introductions to the SAP UI5/Fiori programming paradigm. Guess what, my app had a very similar look and feel. When they were going through their presentations I was literally sinking in my seat … I was actually considering changing my color scheme and if I had just a couple hours more, I probably would have. Point is, even if your demo is a UI5 app, don’t make it look like UI5 app.
  • The devil is NOT in the details, its in the use case. Its far more important to have a sound, solid and relevant use case, than a phone number having the correct input format of (111) – 111 – 1111.
  • Tell people what you are doing – (Sebastian suggested this) – it is helpful and adds context to your presentation. Its also often hard to see what your mouse is clicking on, or what watch button you are pressing.
  • Your jokes are not as funny as you think they are – I threw a couple small jokes in and all I heard was crickets chirping, nothing breaks moral more One of Ian’s tips are not to start with a joke, and I fully agree.

The Demojam event needs some changes …

I noticed a couple of tweets a couple of days ago about changing rules, and doing things differently, having now participated in an event, here is are my feedback/comments to the demo jam organizers:

  • We need more demo jammers, way more. if it means we need to reduce the 6 minutes to 4, so be it. The event starts pretty late which also may limit the amount of Demojam presentations, the SCN event is also prior which takes up quite some time while being a big advocate for this, it might be best to restructure the timetable a little?
  • I would like to see a couple different categories. It would be great to have a “open source” category and a “regular” category. Any others you can suggest?
  • I think there should be more prizes for different criteria or per category, this could or could not be known prior to the event. At Innojam LV, there were multiple winners and this really encourages more entries across multiple disciplines.
  • I would like to see SAP employees back in, even alone. Maybe a category for best SAP employee demo? They have developed some great tech in the past, I understand it could be a slight advantage due to technology accessibility, early access, and a couple of other aspects but I also believe the audience wants to see how internal SAP employees are innovating in their respective areas. I know there was a huge thread back in 2011 from Demojam winner Matt Harding about this topic, and this is just my opinion
  • Limit previous winners? No way, I applaud jammers who can win multiple times, it encourages all other jammers to build something more creative, more innovative and deliver a great presentation.
  • Please change the voting – the “Clapometer”? I would prefer to see a slightly more transparent process … winners could be based on amount of tweets? Voting page from a mobile app? Even a panel like X Factor handing out tasteless feedback would be pretty cool and a bit more enjoyable.

In the end ….

Demojam ROCKS, I am truly grateful for an opportunity to participate. It was a great feeling to have completed a life long dream of mine. I got 6 minutes of fame up on the same stage as the greats of Vishal Sikka and Hasso Platner. Here’s the best part – YOU can too. All of us have done something outstanding, something creative and something I/WE/YOU/SCN/Attendees want to hear about. I encourage you to create or share a concept, a dream or just a downright outrageous idea for next years demo jam, and hopefully join me in showing your peers, friends and family that you are a dedicated and a creative innovator of awesome enterprise technology.

Metric² Update

For those of you who would like to download/check out Metric² – its coming! I had a huge amount of great feedback at TechEd and I am in the process of making some of the suggested changes to make it a little better before throwing it into the wild. (I developed the entire app on the concept of polling due to the XS Engine limitation of not supporting Web Sockets in SPS6 = not perfect.) You can stay up to date using the website here: http://www.metric2.com


pastedImage_1

HANA Talk – A Simple helper class for SAP HANA Development

HANA Talk is a small Javascript class which help facilitate the communication between your front end html/js files and HANA database when using SAP
HANA XS Engine. This is intended to help people who are just starting out development on XS Engine and would like a easy place to get  their feet wet without having to go through too much trial and error. This is also a great starting point for people involved in events like InnoJam, Hackathons, etc. when POC’s and demos need to developed quickly, but not necessarily perfectly *cough* or securely *cough*

By providing this tutorial and the HANA Talk download, my hope is that it will encourage more people to consider using XS Engine as a app platform to drive their front end web apps and subsequently, encourage the use and innovation around HANA in general. Anne Hardy had a comment in her blog post regarding the Developer Advisory Board which was along the lines of “Developers want to get it in 5mins max; they want to build real stuff in less than an hour”.

I frequently get discouraged by the amount of learning and effort always needed to learn new technologies and products which companies “get into bed with”. Nearly every one of the apps I develop start out in a POC type phase, where my imagination and reality clash and inevitably produce the equivalent of a 3yr old’s self portrait, often resembling “Unconventional”. While going through these exercises I often wish it was simple to get somethings done, and understanding the cost and willing to accept the trade offs. This is why I developed HANA Talk – to make those unconventional portraits, easier to deliver

OK – so what does it do?

By adding a HANA Talk js and xsjs file to your project, you can simply write SQL statements in your HTML file and have the results returned synchronously.

e.g.  Index.html

  1. <script type=”text/javascript”>
  2. var resp = hana.executeScalar(‘SELECT 1 FROM DUMMY’);
  3. console.log(resp); //Outputs 1
  4. </script>

Super simple and easy. See below for further details.

Prerequisites

1. Download/fork these 2 files – client.js & server.xsjs from Github and add them to your package. The filenames/structure can be changed if you are feeling adventurous. In your HTML file, you will need to reference client.js, this is as simple as adding a tag to you header. For reference, if you are not using SAP UI5 or jQuery – you will need to add this to your HTML header as well.

A Basic Example

2. In your javascript code, instantiate a new HanaTalk object. We will use this to “pass” our SQL commands to our HANA DB.

  1. var hana = new HanaTalk(‘SYS’); //The ‘SYS’ reference is in relation to the Schema. It can be specified here or within your TSQL Statement

3. Call your HanaTalk object with the operation type and SQL you would like execute (see below for additional operations).

  1. var result = hana.executeRecordSet(“SELECT 1 FROM DUMMY”);

4. We can populate that response into our html (DOM)

  1. document.getElementById(“SomeElementID”).innerHTML = result;

A few more examples

a.) Insert/Update/Delete a record – use .executeChange, this will execute your code and respond with the records which have been updated

  1. document.getElementById(“resp4″).innerHTML = /*hana.executeChange(“UPDATE/INSERT/DELETE …. “) + */ ‘ Record Changed’;

b.) Return a Table – using .executeRecordSet will return a html formatted table, displaying the select’s record set

  1. document.getElementById(“resp2″).innerHTML = hana.executeRecordSet(“SELECT TOP 5 * FROM M_HOST_INFORMATION”);

c.) Return a Object – .executeRecordSetObj allows us to loop through records, and have quite a lot of control of the display of each record and its column name.

  1. document.getElementById(“resp3″).innerHTML = hana.executeRecordSetObj(“SELECT TOP 5 * FROM M_HOST_INFORMATION”);

Here are the results of the calls above:

As you can see, making these types of calls is fairly simple and since your files reside directly on the DB app server, responsiveness is not too bad. So what’s so bad about this method?

Security: Because you are passing your SQL text directly from your browser to the backend server, you open yourself up to all kinds of SQL Injection hacks. Technically, we are not parsing URL arguments/parameters into the server side DB request but rather entire statements. In order for us to hack this, we could simply change the URL request to include some devious SQL.

Speed: For simplicity and to avoid AJAX Callbacks, the execution is performed Synchronously (i.e. We wait for the server to completely respond before we continue processing). The good: Code is “inline” and easy to manipulate the response/results from the server. The Bad: if we do a Select * on a massive (read billion) row table, the entire window is locked/hung until it either times out or completes. Not a particularly nice behaving UI.

The usual disclaimer: As mentioned, the intent of this post/download is to encourage simplicity, this comes at a cost, both performance and security are less than perfect – in this case, we will do our best to mitigate some of these. In fact, I expect a snarling comment from Thomas Jung, like a high school math teacher, disappointed that one of his protege openSAP students would even suggest these obscenities

I encourage you to test this out and provide some feedback or makes some changes and share this with the community.


Image+[20]

Microsoft Power View and Netweaver Gateway – A easy com*bi*nation

I was recently at a customer site where they were using a Microsoft Excel based BI Tool, Power View for in house adhoc BI reporting and I was quite impressed. The ease, simplicity and functionality was quite refreshing and I started to do some additional digging into the application and its functionality. My first and unfortunately, my biggest shock, came at the fact it required Silverlight. Yes, you read that right … this means that the reports/functionality is not available on your favorite mobile device. This did not deter me and I was interested on how this could apply to the SAP world from a desktop perspective and was pleasantly surprised when I started scratching the surface.

Firstly, a bit of an introduction: Power View is a relatively new product from the Microsoft BI Stack and was recently included in Excel 2013 (Professional Plus editions). It was previously available in Sharepoint Server 2010 and still is included with SP 2013. You can use Power View directly from a Excel workbook, within Sharepoint, as well as deploy the Power View Excel sheet to a Sharepoint 2013 Server. Power View is an interactive data exploration, visualization, and presentation experience (and can be compared to SAP Lumira). When I started to do some digging I found that this blog around SAP MII tool also uses it as part of its data analysis options. Power View has a variety of functions which are covered on this site and I will not repeat.

A powerful feature of Excel, which is often overlooked, is the fact that you can use OData as a data source and this allows us to use data from REST based services built using SAP Netweaver Gateway. Since Gateway can subsequently expose data from sources like BW, HANA, CRM, ERP and so on, it provides a great reusable foundation for casual BI, mobile devices or web apps (like Fiori) for lightweight consumption. SAP Lumira also offers OData support with its recent v1 SP11 service pack which now makes it a bit more appealing for customers who have implemented Gateway.

What does this mean for SAP customers? With Excel being widely adopted, why not reuse your existing investments and services from Netweaver Gateway to power some casual adhoc BI tools for your business?

Here is a quick walk through which can get you started on developing Power Views for your business (please keep in mind that creating Power View sheets from Excel and Sharepoint is slightly different, in this case we will use Excel). If you don’t have Excel 2013 Pro Plus, you can download a copy here: http://technet.microsoft.com/en-us/evalcenter/jj192782.aspx

Data Source: As mentioned previously we can use a OData service as a source for our Power View, in this case we will use a great new service recently developed by Andre Fischer and available on the Gateway demo servers. If you would like to develop your own Power View Sheets based on these demo services, you will need an account. (Its quick and easy!).

So lets jump in … Open Excel 2013 and click on the “Data tab”, “From Other Sources” and “From OData Data feed” and enter the Gateway service details along with your username and password. In this example we will be using the Sales order collection service : https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection

Image [2].png

 

Image [3].png

Click Finish

Image [4].png

Once completed, you should be presented with the Power View Sheet and a basic layout, along with your data source field list on the right side:

Image [5].png

You can kick things off by selecting a few columns from the sales order collections table which will show up on your report in a tabular format.

Image [7].png

First thing we will do is go across to our data set (Our power PowerPivot sheet) and add a SUM function to our Gross Sales Column. Click on PowerPivot -> Manage. Select the GrossAmount Column and be sure to set its type to decimal. (You should do this for all your “numeric” based columns). Then at the bottom in the Calculated field, select the first row and “AutoSum”

Image [6].png

 

If you save and close the Power Pivot, you should have something like this showing:

Image [7].png

Next we will clone this table and use it for a Pie Chart, select the table, copy and paste it. Then with the new table selected, click the “Design” tab -> “Other Chart” and Pie.

Image [8].png

Once again we will take our original table and clone it. We will use this to display a line chart of the average order size by using a Line Chart. Resize your charts appropriately, and select the line chart. Also modify the Value to be Average, rather than the SUM.:

Image [9].png

As I am sure you have noticed by now, is that filtering on charts is easy and they are all linked. Selecting a specific customer will actually change all three of our charts appropriately which is very slick.

Image [10].png

Progress

Next we will be adding a KPI, this can be done by heading back to our PowerPivot sheet, selecting a column and selecting AutoSum -> Count.

Image [11].png

This will add an additional calculated column to your table list:

Image [12].png

Then select PowerPivot -> KPIs -> New KPI

Image [13].png

Your KPI base field should be selected and you can define a Absolute Value (“Goal”) of 250 and any other options you may prefer. Select OK.

Image [14].png

This will convert your calculated column to a KPI, and selecting one of the fields will add it to your Sheet.

Image [16].png

Lastly what we will be doing is adding a few filters to our view, this way we can filter all charts/KPIs with a single selection. You can do this by selecting “View” on the filter sidebar, then dragging and dropping your needed columns.

Image [17].png

In the end, it is a fairly simple process to build a interactive and useful dashboard … here is my finished Power View …

Image [20].png           Image [18].png

A couple other cool things to check out …

-> The Map “Chart” …
-> Drill down features … (hint – you need to create a Hierarchy)

To Do ….

-> Change your column names from PowerPivot as soon as you have your data set created
-> Add Currency prefixes to the columns/dataset
-> Remember that the dataset is stored in your file.

Here is the Power View Chart … download it, enter your credentials under the PowerPivot “Existing Connections” -> Advanced and you should be able to use it as a template …

Image [19].png

Here are a couple more helpful links to get you started:

http://office.microsoft.com/en-us/excel-help/create-a-power-view-sheet-in-excel-2013-HA102899553.aspx

http://www.slideshare.net/idigdata/excel-2013-power-view-and-powerpivot-basics

http://download.microsoft.com/download/A/9/9/A9971C42-ECB6-4059-BA60-7E7B5B98BD40/Microsoft_BI_Interoperability_with_SAP_White_Paper.pdf

http://www.jenunderwood.com/presentations/PowerViewPowerPivotBasics2013SP1.pdf

If you made it this and are still engaged I am curious to know if you are using Excel Power View in your organization? What do you think of it? Would you consider using it in the future?


Mockup

SAP UI5 – A Photoshop template of GUI elements

Mockup.png

For me (and I am sure many others!), UI5 has become a really useful toolkit for easily developing web apps and POC’s with little effort around the SAP Ecosystem. The OData support, rich controls and business inspired examples lend itself well to the mantra of “Don’t reinvent the wheel”. At a recent SAP Inside Track event in NY, I made reference to the fact, that while developing a mobile app, I, or a graphic designer, can spend close to 40% of the total design & development phase/hours on the UI. Building “consumer grade” enterprise apps is a different way of thinking for app developers. I am sure many of you, like me, started out with a redefined canvas of boring, grey .NET controls or a “Enjoy” SAP CXTAB_CONTROL. But times are changing and we should all look to the creative and vibrant consumer world of both web and mobile apps to reconsider our methods and processes for developing great apps.

When I started working with the UI5 toolkit, I would more often than not, start coding right away, without going through my traditional methods, and ultimately ended up in a frustrating muddled mess. Why wouldn’t you start coding immediately …. everything you need is right there? Wrong, planning is everything when it comes to design. Properly designing a app which is going to judged, by today’s standards, is not an option – but a requirement. I am not saying every application in your organization needs a UI Designer, but planning should start with sketches, process flows, and subsequently lead to the UI design and  development. When you have a toolkit which has some great looking components, its easy to skip this phase and get cracking. Don’t do it. Since I have spent a lot of time developing both web, mobile and traditional apps for the enterprise, my approach and system development life cycle (SDLC) resembles more of a artistic approach and design definition, than a “traditional” enterprise app. Yours can be entirely different, but the point is that I would like to encourage you to consider that a *great* design should be a part of your project goals.

The Reason

That’s the reason I developed the PSD file of UI5 elements. To make that design planning process quicker, easier and to encourage all of us collectively to make great designs and awesome apps.   Screen Shot 2013-07-09 at 9.19.16 PM.png

The Process

I used the online test suite and replicated the majority of the objects using shapes in Photoshop. Each object has groups/folders categorizing and defining it, both for extensibility and this makes finding a component/object easier. Some objects are not included: Icons and some of the VIZ Charts are not individual objects. Maybe in the near future if I can get some form of ‘OK’ from SAP for not copying/redistributing their font I will consider it (hint?).

Screen Shot 2013-07-09 at 9.15.29 PM.png  Screen Shot 2013-07-09 at 9.20.44 PM.png

The UI Toolkit

95% of graphic designers will spend time working with a UX/UI in Photoshop prior to coding and this toolkit makes creating those mockups considerably easier. Each object is defined as a shape and can be moved, and styled as needed. Currently the file has 897 individual layers which makes up the +- 8.2 million pixels of UI goodness . Building a new UI mockup should not take long … create a new file, duplicate the objects you need and get creative. (Please also consider the fact someone is going to need to code this and that your imagination, should be limited by *their* capabilities!)

Whats next …

I am hoping someone else interested in the UI/UX aspect will be able to contribute to the project and consider extending this file and its objects to cover all the aspects. I also hope that SAP will consider releasing its “Master” file to the community as ultimately it will encourage better adoption and use of the product.

My challenge to you ….

Does you/your company/partner have a UI/UX designer? Do you think its important to design a UI before coding? Do *you* have any great UI/Designs you would like to share? Post a link in the comments, or better yet, create a post here: https://experience.sap.com/ (SAP Why is experience.sap.com not directly integrated/on SCN??? )

You can download the file here:http://www.li-labs.com/downloads/sap%20ui5%20gui.psd

Don’t have Photoshop? Here are a couple of the elements split up and are available in PNG format: http://www.li-labs.com/sap%20ui5%20png.zip


icon_128x128

xSync – Making HANA XS development easier

A couple of weeks ago, I started playing with HANA development after going through a couple of the opensap courses. One of my biggest surprises was that HANA Studio was windows only. As a Web/Mobile/App developer spending majority of my time on the Mac platform, having to get my Dell out was a walk down memory lane, and caused me to not spend as much time working on the projects as I would have liked. Since Rev 52 of HANA included a XS Engine Lite IDE, a useful web based IDE for your XSJS/SAPUI5 development. I was been looking forward to getting it installed on my AWS machine and giving it a test drive. Last weekend Juergen Schmerder posted a updated really easy to follow and simple guide of going through the process and within a hour, I was up and running on the newest available rev.

First off, the web IDE was great. Secondly, not having to use my old Dell was bliss. From a development perspective I am OK with the fact I need to do schema modeling/view generation input etc. through the Studio IDE, its well built for this and has functions and customizations which are not as necessarily as generic as web/java/jquery development. I spent the weekend working on a small POC/rewrite I have been working on and enjoyed the development experience. But after a while, found myself wondering about code completion, syntax highlighting, text formatting and a few more features which are generally available in your favorite IDE.

After some digging, I came up with the concept of being able to develop locally and yet still be able to execute the code online through my browser as if I was using either the Lite IDE or the Studio. I am not sure if #openSAP intend for students to hack HANA, but that’s exactly what happened …

icon_128x128.png

Enter xSync … xSync is a *little* like dropbox for your HANA repository. Its a Mac app which runs in the Status Bar and watches file system changes to a designated development folder. As soon as you save, it saves your file in XS Engine to your package repository. Development is no longer is choir of saving, syncing and activating Even if you are not a developer, it has some useful features, it shows if your HANA instance is online – avoiding unnecessary costs, and also allows you to easily download all files from a HANA package or development project. This is great for creating local backup/snapshots regularly. Below is a quick video of the app in action.

If you are interested in testing the app out, please download a copy here – all feedback and constructive criticism is welcome as usual. Please keep in mind, this is still *very* beta. I have done my best to test, but from conception to app in a couple spare hours is always a challenge = DISCLAIMER, use at own risk!

Some technical notes and features:

- Features:
- Use your favorite IDE to edit files locally
- Save, Create, Delete, Rename files locally and have them *sync* with your XS Engine package
- Quick way to ensure your Instance is available (watch costs on your cloud hosted system!)
- Download all package contents to your local HDD
- Easily upload files/folders to your packages

- Requirements:
- HANA Rev. 52

- Usage:

1.) Download the app here

2.) Enter your HANA XS Engine URL, package (I suggest creating a test package to test the app out with)

3.) Enter the folder location of your local development objects (I suggest a blank folder to start as it will not upload existing objects already in the folder)

3.) Enter HANA username/password

4.) The log will show that the folder is being watched

4.) copy/create a new file in the folder and check the log to ensure the file was synced

5.) develop something awesome …

- Bugs/Issues:
- Renaming/creating folders could be a little better, since XS engine does not require spaces, it can be problematic

- Future Ideas:
- Sync existing files
- Maintain a local “folder” structure db for reference
- Sync from XS Engine if changes made there
- Insert your idea here
- Less ‘buggy’ for release 1

For all the HANA hackers out there … how do *you* think the app is integrated with HANA XS Engine? Any guesses? I will follow up this blog in a couple of days with the technical details and how the app works.

Screen Shot 2013-06-15 at 8.43.30 AM.png

Image2

A peek inside xSync and the HANA XS Engine

icon_128x128.png

On saturday I published a blog about a small app I wrote called xSync – basically a XS Engine app for Mac developers where you can sync a local development folder with your HANA repository. This is for rapid development and to encourage the “bring your own IDE” approach to application development on HANA. Here is a look behind the scenes on how the app works and some of the challenges of the project.

Image.png

As mentioned in my previous blog – I started using the IDE Lightweight editor after doing the upgrade of my AWS HANA box last weekend. I enjoyed the experience but after working with it for nearly a full day was wanting a little more. Syntax highlighting, easy commenting, easy indentation, CSS autocomplete and hints, etc. etc. so I started doing some peaking around the editor itself and came to find the editor is something called ACE, a pretty nice little open source IDE (written in JS). This got me thinking … maybe I could insert text directly into the Lightweight IDE browser text box, and submit the form as a save …. hmmm …. not a terrible idea …. just need to scrape the page, find the elements and submit the form via some injected JS. Pretty simple …  I did some digging and found the HTML objects I needed by using Firebug when a lightbulb went off … instead of populating the form via a HTML page, why not rather check the HTTP methods it is calling when doing the actual save, since there must be some integration with HANA directly … which is when I came across the mother load … a small file called reposervice.xsjs It seemed that every time I was saving or modifying my objects through the IDE, it was calling this file. After checking out the parameters it was `, it was very clear that the methods and text were easy to simulate. I fired up REST Client and within a couple minutes the concept was POC’ed. Pass your file contents as your body with a path param and a POST and you were off to the races

Screen Shot 2013-06-09 at 4.39.png

Using Firefox Rest Client to monitor system calls showed each save, create, delete operation was using a small file called reposervice.xsjs, which references the libraries needed for the repository modifications.

Image2.png

The diagram above displays the HTTP call made when saving/creating a file, and how the IDE initially does a HEAD request for the XSRF token, followed by the HTTP PUT.

The initial HEAD request is to fetch the CSRF Token, secondly the token along with the parameter of mode, path and activate are passed to the URL. Pending you are successful, a JSON message is returned with the status. For those of you are not familiar with Cross-Site-Request-Forgery, you can read about it here: http://en.wikipedia.org/wiki/Cross-site_request_forgery

Once I had this done, I was wondering what the best integration option would be and weighed up a couple options of a simple check in type procedure, but wanted something faster, easier and “click free”. Being a bit of a highly iterative developer myself, I find it easier to develop “online”, which is why I decided it would be best to do a File System watch of a particular folder and save any changes automatically to my HANA instance. Similar to a dropbox type approach.

I had my POC working nicely, a integration goal defined and set out to start developing the UI/Application in Objective-C (Xcode). I had a template type of app from one of my little SAP Note Viewer applications which could act as a foundation. I threw some code out and pulled some very useful little open source packages in as helpers. Within a couple hours in my evenings each night the app was running nicely and doing what I had expected, modify a file or two in a predefined location and sync up to XS. easy.

Thats generally where development grinds to halt for me, as I envision feature after feature to build a Mac clone of HANA Studio Luckily my senses got the better of me, and I worked on doing a recursive package downloader, the ability to create, rename and delete files and folders and not a HANA Studio rewrite Once this was all done, ironing out the bugs was painful. The cocoa FSEvents stream (File System Events) Class on the mac is not easy to work with and a bear at best. Having to monitor a folder for any modifications, deletes and creates turned into a bit of a logic nightmare. One of the interesting challenges is that if you “delete” a file on the mac file system, it does not get a “delete” FS Event but rather a rename! (Since it goes to the trash/recycle bin!). This leads to having to do multiple … if exists then …. type statements around each file and folder event

UI is another interesting one, I like apps to look somewhat decent … and I spent a good amount of time working on each of the elements in Adobe Photoshop as usual … (Whenever I do a mobile app development talk I mention that I spend close to 40% of entire project time in apps like Photoshop with design work! Most are surprised!)

If you are interested in incorporating some these types of features into your own app, I will be posting a copy of the integration classes on GitHub shortly.

PLEASE KEEP IN MIND: This is exploratory type work with undocumented API’s, I would not recommend using this in production, or any important production work (or your important opensap homework!). The reason I shared this was to encourage people to look under the hood and understand the how’s and why’s of how some of these great new tools work.

I would be interested to hear if anyone has any interesting use-cases for being able to manipulate both HANA repository and DB artifacts from outside of the Studio? Does anyone have any challenges with the HANA Studio today they would like to see changed?


Screen Shot 2014-04-23 at 4.57.06 PM

Communicating with other iOS apps using URL Schemes

When I started developing iOS apps a couple of years ago, I was concerned that enterprise apps were going to quickly succumb to the world of development scope creep. Since the apps were fairly focused and provided a single function or process solution, what if the business wanted more? For example, you have a CRM app displaying your customer contact information and some high level details regarding order history and credit limits, but what if the user wanted some additional details from your BI solution drilling down into historical orders and profit analysis? Well, in that case, you had a few options:

1) Extend the scope of the app – by adding functionality and options, sometimes a reasonable option if the scope is not excessive or the functionality is relative to the app. In our  above example, probably not.

2) Build another app with replicated functionality – duplicate code to display the customers and overview details in both apps.

3) Build 2 separate apps – But this would require the user to re-enter information (such as searching for and entering the Customer ID) in the 2nd app.

The fourth option is to use option 3 but with a twist, by enabling communication between the 2 apps by using URL Schemes. As the majority of iOS developers know, you can open other standard iOS apps like Safari, Mail or the Phone using the UIApplication SharedApplication class, what many developers and mobility architects overlook is that, not only standard apps can be called from your code, but any app which has a URL scheme defined. This gives you the ability to separate processes by application, and in the event a user would like drill deeper into additional information or functions, you can pass information to another app where that can be handled. What if the app is not installed? Good question … You device will smartly open the Apple App Store giving you the ability to install the missing app. Configuring and consuming the communication between 2 apps is very simple and straight forward.

http://developer.apple.com/library/ios/documentation/iPhone/Conceptual/iPhoneOSProgrammingGuide/Art/app_open_url_2x.png

 

 

You can read about the URL Schemes within the apple documentation here:

http://developer.apple.com/library/ios/#featuredarticles/iPhoneURLScheme_Reference/Introduction/Introduction.html

and here: http://developer.apple.com/library/ios/#documentation/iPhone/Conceptual/iPhoneOSProgrammingGuide/AdvancedAppTricks/AdvancedAppTricks.html#//apple_ref/doc/uid/TP40007072-CH7-SW18

Within the SAP Suite of iOS Apps you can also find URL Schemes already configured and in use, some examples of these are:

CRM @ SAP

Find account by number: crmatsap://openAccount?accountid=<number>

Open opportunity by number: crmatsap://openOpportunity?opportunityid=<number>

Open account by number: crmatsap://openAccount?accountid=<number>

SAP Pricing

Open Product by ID: pricingapp://openProduct?productid=<productid>

If you are still not sure if iOS URL Schemes are right for you here are a couple of Pro’s and Con’s

Pros:

- Use other custom apps to augment yours

- Use other companies apps to augment yours

- No need to redevelop similar functionality

- Communicate between apps

- Launch your app from a hyperlink (online/in a web page?)

- No need to be online for base functionality

Cons:

- Shouldn’t pass sensitive data

- Clearly define the scope of each app and avoid over simplifying (e.g. an app for searching for customers by name, another for searching by ID.)

- Should not be a critical function, since there is no guarantee that the data is being received by the other application

I am curious to know how many app developers out there are using URL Schemes within their custom development?