Good news, everyone. Not only is my basement finally clean (see my previous post if you are wondering why you would care), but LightSwitch star Michael Washington begged reluctantly agreed to cross-post all of my new items on his blog as well! So for everyone who is viewing this on Michael’s website and is used to his detailed and well-structured posts around LightSwitch, I apologize in advance.
This post, however, is one that I’ve been waiting for almost a year to write – this is the first post where I demonstrate pulling your SAP data into a LightSwitch app using Netweaver Gateway!
Back when I first started this series, I mentioned a few common scenarios I would address. Two of those scenarios were:
- I want to be able to run analysis and then take measurable action on it (commonly referred to as “Actionable BI”)
- I want to use my SAP data in conjunction with my group’s local data so I can better run my business
Since I’ve written that post, I’ve had the opportunity to speak with several different customers around what their current business needs are and how they could be addressed by Microsoft and SAP. To be honest, I thought the scenarios they’d most be interested in would be around Excel, and that’s why I started this series of posts focused on those topics. And while they found the new capabilities in Excel 2013 quite compelling, more folks seemed interested in a simple app I built in LightSwitch. This app did nothing more than take an SAP data source using Gateway and a Sharepoint List in Office 365, create a relationship between them, and allowed them to update their Sharepoint data in the context of that relationship. It didn’t require any updating of their SAP information at all.
Now for those of us who have been using LightSwitch for the last couple years, you’re probably chuckling because you realize that scenario is a snap to build in LightSwitch (it took me under thirty minutes to do the whole thing - from creation to loading the app into Office 365) assuming, of course, you can actually GET the SAP data you need. And let’s be honest – until Gateway came along, unless you company had (wisely) invested in Duet Enterprise or a third party solution (and there are some really nice ones out there), this would have been far more difficult to achieve, and certainly wouldn’t be something you can roll out in thirty minutes.
So how does that simple scenario tie into the “Actionable BI” scenario I’ve long teased I would get into? First of all, what exactly is “Actionable BI”? If you do a search on Google or Bing, you’ll see a number of different descriptions/definitions/interpretations. For me, I can tell you it boils down to something a Global VP said to me a couple years ago when I presented him with a fancy new dashboard I had toiled over for about a week and was sure he was going to love. Instead, I got the following response -
“Meh. So what?”
I couldn’t believe it. But I’d worked so hard on it! It was so pretty and had the latest numbers and –
“It doesn’t show me what we’re specifically doing to on this team around the numbers. I need a report that does that, or better yet, an application that ties all this crap together with the stuff the team does to run the business day in and day out instead of everyone keeping their own spreadsheets.”
Did he realize what a pain that would have been to try and do that back then? That information was scattered across god knows how many corporate systems, databases and spreadsheets – trying to pull that together to give him that report would have been a pain in the ass, let alone building an actual app that did all that.
But that conversation happened BEFORE LightSwitch was launched – now that I not only have “The Power of LightSwitch” (not to be confused with “The Power of Greyskull”), but also Office 365 and SAP Netweaver Gateway, so this is barely a challenge at this point. But just for fun, let’s go ahead and tackle this project in this blog post as our demo scenario for a new company I made up right this instant called, um, TBQ Software.
This new fake company is an SAP and Microsoft customer, and we just setup Office365 in the cloud. I have the latest version of Visual Studio installed on my Surface Pro PC with Windows 8 installed. And we only know the basics about LightSwitch – I’m doing it this way because almost no one I spoke to at these events or meetings had even heard of LightSwitch, so I’m trying to do this like someone who has been tasked with doing this at one of those locations and has never done this before.
Here’s the data we need to pull into our LightSwitch project -
- SAP CRM and Financial Data
- A master list of partner information we’re currently using in Sharepoint that we share with other teams
- A database hosted in SQL Azure that houses data from a small LOB system around events our team hosts we’re replacing with this new application
- an oData feed from the Azure data market
- a new database that gets deployed to an once we create the application to house some new LOB-specific information for this team
Once we’ve pulled all that in, this app is going to be used by a team supporting the sale of partner software solutions we resell. I’ve identified what they need to accomplish and report against, and which data source I believe the information comes from. The team needs to -
- Be able to monitor the sales pipeline from the SAP CRM system used by the sales team for their particular partner (SAP Data Source)
- See if the companies in these opportunities they’ve identified are scheduled for any campaigns or events they have scheduled, or create a new item based on current deals (SQL Azure Data source)
- Cross-reference this information against a list of potential existing customer references who already own the product identified in the sale (Sharepoint List Data)
- Add status updates on an ongoing basis to keep the manager informed around what’s happening with upcoming events and deals they relate to. (Application Data)
Their manager wants to –
- See all the status updates entered by his team on a daily basis
- See the current sales pipeline for each partner and take an on-demand “snapshots” of this information each week after his weekly call (SAP Data Source and Application Data)
- Get an e-mail alert any time a new event has been added that is looking for funding (SQL Azure Data)
- See the closed deal information for each partner and what events or campaigns they did (if any) that helped close the most deals.
We’ll use the HTML5 client, and we’ll need to deploy this to Office 365 as a Sharepoint App. Everything is setup with IT in terms of security and potential firewall issues, which is a major advantage of using Office 365, since that is far easier to achieve than it otherwise could be. Oh, and don’t forget, we’ll need a number of reports.
So let’s start with the data we need from the central SAP systems – we need to get the data out, but we DO NOT need to write anything back to the central systems. This is a read-only scenario – if this the case, consuming an SAP Netweaver Gateway feed inside of LightSwitch is very straightforward. Once the feed is setup on the SAP side (please refer to this post for more information around that), there is no special setup work in LightSwitch to consume that feed.
I can right-click on the Server folder and choose “Add a new data source”
Select “OData Service” from the options, and hit next
Enter the connection string information and check the “read-only” checkbox and your security information (FYI - security in general will be covered as its own topic in-depth at some point to talk about various scenarios, including SSO)
I can choose the entities I wish to bring down into my project and hit “Finish”. Now depending on what entities you’re trying to pull into the project, you may get some warning messages from LightSwitch at this point -
Let’s go through each of these – the first item lets you know that currently Gateway doesn’t enable paging inside of LightSwitch. Gateway DOES support paging – however, this screenshot is against a slightly earlier version of Gateway we use for testing that doesn’t support it. You shouldn’t get this error going forward.
The second item is around a specific SAP data type that isn’t supported. There are a couple SAP data types that aren’t currently supported in LightSwitch, including “Address” and “Media Element” (in fact LightSwitch doesn’t support Media Element from any oData source currently). These are simply ignored by LightSwitch if they are included currently.
The third item talks about a one-to-one relationship that’s being ignored. This could potentially be worked around on either the LightSwitch side or the SAP side, depending on what your business scenario demands. I don’t actually need to use those entities for now in this new application, so I can safely hit “Continue” at this point to finish bringing in the SAP data.
(NOTE: I’d like to suggest something – if all you’re doing is bringing in SAP data that you will
NEVER want to update back into the core SAP systems from this app you’re building, it might make a lot of sense to setup a Gateway feed from your SAP BW system (which you can do). That would generally eliminate some of these issues, and it should have the data already structured in a manner that would be the path of least resistance when bringing it into LightSwitch. You need a BW system (duh), and keep in mind it won’t allow you to perform full CRUD operations against the data source ever. But in this example, it’s certainly would meet my needs.)
Now that I’ve tied in my SAP data source – this includes pipeline information and closed deals.
This information will help us “take action” later. Think about what we’ve just done for a moment
– we’ve tied our SAP data into an app we’ll be hosting in Sharepoint without using Business Connectivity Services, special coding, third party tools, etc. IN A THREE-STEP WIZARD. For anyone who’s tried doing this in a pre-LightSwitch world, you appreciate just how incredible this ability can and will be going forward in scenarios like this one.
Next up is tying in the Sharepoint list we have sitting in our Office365 platform – unlike pulling in the data from Gateway, this is a pain. Why? First off, you have to have Sharepoint enabled in the LightSwitch project, like they do in this image I am shamelessly re-using from the blogpost I reference below
Once you do that, you are locked into deploying it as a Sharepoint App. Full stop. This is a big change from being able to consume an on-premise Sharepoint list with any LightSwitch app. Also, you can only debug in a developer site you setup – not the end of the world, but I’m guessing any list you’re pointing at isn’t sitting on that developer site, so it’s possible to run into a scenario where you want to debug the app but not actually SEE the data you’re pointing at while debugging because it sits in a different site collection and you run into permission issues (or you do what I did, which is debug it in my personal dev site, which has a different userid/password than where I want to deploy it. Ugh, painful). I used this nice walkthrough on how to attach to Office365 based Sharepoint lists from the LightSwitch team to help me work through any issues I had -
See how simple that was? Easy peasy lemon squeasy! (Quick note – I know we mentioned earlier we have no issues with firewalls, but your company firewall around TCP/IP traffic could make this scenario significantly more challenging. If that’s the case, the easiest workaround would be to pull that into a seperate LightSwitch project and turn it into an oData service that you deploy to Azure. But I digress.)
The other item we’re going to pull in an Azure data feed we get from the Windows Azure Marketplace. We’re going to use the free datamarket app called “DateStream” to bring in a number of date/time entities via oData that will assist us with some reports we’re going to use later on. You’ll need to sign up for an account on the Azure Marketplace at no cost to take advantage of this. Once you’re registered, you’ll need to make you have your customer ID and Primary Account Key to use as your credentials from the LightSwitch Attach Data Source Wizard. You can enter the credentials in like you see below:
Alright – we’ve brought all four external data sources into our project.
Okay, so everything’s based off a partner and the products each has. There’s a master list of all the partners and their products that makes sense to add as a new table in the Application Database. Unfortunately, it only exists as an Excel right now that includes the partner name, product ID, and product name of each product the partner sells. Let’s clean that up a bit when we create the tables – we’ll create a partner_master and partner_product table and create a one-to-many relationship between them.
Now we’ll create relationships between the new partner_product table and the different data sources –
- We use the Product ID to link in the SAP datasource
- We use the Product Name to link in the campaign/event information from the SQL Azure data source
- We use the Product Name to link in the Sharepoint list around customer references
Now if I had my druthers, I’d re-do the structure of the Sharepoint list/SQL Azure piece to have the product ID in there, since using a string field (and a name no less!) is, shall we say, less than ideal. But often times the real world can be messy, and we’re going to pretend we don’t have any way to redo those old data sources. So we work with what we have, which again illustrates how valuable LightSwitch is.
We also wanted to capture status updates from folks around deals they are supporting. So let’s add a table called status updates that has the following fields –
I’m also going to create a relationship based on the Update_Date based on the Azure Datamarket stream and the Pipeline and Closed Deal information based on the Opportunity_ID field.
So we’ve gotten halfway through our project, and it’s a good time to take a break because according to SkyDrive I’ve spent almost 900 minutes on this so far (I’ll admit, that seem high). And while what we just did seemed simple, it only seemed that way because of LightSwitch. This would have been considerably more time-consuming without it.
When we resume this later this week, I’ll finish this up and perhaps even do it in a little video Beth Massi style. Until then!