Monthly Archives: August 2013

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:

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 :

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


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:

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?

SAP UI5 – A Photoshop template of GUI elements


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: (SAP Why is not directly integrated/on SCN??? )

You can download the file here:

Don’t have Photoshop? Here are a couple of the elements split up and are available in PNG format: