Disclaimer - The opinions expressed in this blog are my own views and not those of SAP, Microsoft, or the guy in the white Honda who cut me off today. Trust me.
So the first bit of feedback I received on my blog post yesterday was the following – “You just complained about people taking data out of the system and dumping it into Excel, then you go on to say you’ll show them just how to do that in your next blog post? That’s a little odd.” And I think that’s a good place to start what is sure to be the most “controversial” of this series of blog posts.
I tried dancing around this first point when I started this post, then I tried couching it, and then I tried not saying it at all. But here goes –
I love Excel 2013. Seriously, it’s a fantastic BI tool, especially since PowerPivot and Power View are now baked into it. As long as Microsoft doesn’t screw it up, it has the potential – oh, what’s that you say? Those features are only available in the Pro Plus version, which is in many ways a step back from the ability in Office 2010 to install PowerPivot for free, regardless of the SKU?
Alright, well, let’s assume the vast majority of the companies out there have Pro Plus versions of the Office suite. As soon as they upgrade to Office 2013/Office 365, every user will suddenly find that a product they already know and use every day has transformed into one of the best BI products out there. And look how easy it is to – why are you looking at me like that? Oh, right – SAP sells quite a few BI products and Excel 2013 could be considered a threat to that business so why am I, a loyal SAP employee, talking about Excel 2013 as a BI tool?
Well, the two main reasons in my mind are –
- Unlike other BI products we might be competing against, no one is going to replace Excel entirely with SAP BI tools, and frankly, I doubt we’d ever expect them to. If we did, I hardly think we’d put in an “Export to Excel” button.
- To that end, I’m sure many of SAP’s customers have the aforementioned “data dump” scenario playing out on a daily basis. This has potential licensing violations associated with it, and rather than spending time doing a costly audit and going down the adversarial path with our customers, I’d rather highlight a scenario that has:
- numerous improvements around the customer experience, in terms of security, ease of use, automation, etc.
- makes the licensing questions a lot more transparent then trying to figure out who emailed what spreadsheet to whom
- it allows SAP to make a far more compelling case of why these folks at the customer should pay for a license, since they are getting a lot more features and functionality than they’d get from a simple data dump.
I could also go into showcasing some of our mobile BI options compared to using Excel – regardless, the point is I think it’s something we should consider complimentary, embrace the functionality it offers and leverage that to our benefit whenever possible.
Okay, now that I finished that high-wire act, let’s get into the meat of it – how can the current users who “just want it in Excel” leverage Netweaver Gateway to accomplish that task?
Well, the first thing you need to do is install Gateway (duh) – this document walks you through how to do that - http://scn.sap.com/docs/DOC-16328. (For those of you don’t have SAP installed where you work, you could do all this with an oData feed from a LightSwitch app, or any oData feed for that matter.)
You also need Excel 2013 Pro Plus – more information on how to get that is available here
Once you’ve gotten Excel 2013 installed and listen to that chirpy video they make you watch once it is installed, open up Excel and create a new workbook –
There’s actually several ways to now bring in an oData feed at this point –
- Under the Data tab
- Under the PowerPivot tab
- Under the Data Explorer tab (if you have the preview installed)
Let’s focus on the first two, since Data Explorer is still in preview (but definitely worth checking out). Every version of Excel 2013 has the first option available to users, which is bringing in the oData feed in the Data tab options. This is the best option if you are an Excel novice or just want to look at your data formatted as a simple table. So how do we create the connection and bring in our data? Easy!
Go to the Data tab, and then choose From Other Sources, then select From oData Data Feed
The Data Connection Wizard will pop-up and ask you to enter the datafeed URL and the security information. Let’s use the public feed and credentials Holger pointed out in his blogpost a few weeks back.
user name : GW@ESW
Once that’s entered, you’ll see the entities that are exposed in the feed.
Let’s just check the Products entity for now and hit Next. I’ll be prompted to save the information for the new Data Connection file I’ve created in case I want to use it in the future.
Then I can hit Finish. At that point I’ll get a few options on how I want to view this data.
Let’s first view it as a table in a New worksheet. I make my selections and hit OK.
Ta Da! Check it out –
All my records were imported and I’ve saved the data connection in the file. Now any time I want to see the latest information, I simply hit the refresh button and I get the latest information. And any subsequent pivot tables, charts, etc. that I build on top of this information will update any time the information updates. Plus, the data is now has security around it, so someone with improper credentials would be out of luck (Ideally, you are using WinAD in combination with SSO, but I'll leave that "fun" topic to another post).
Another option in the list was to expose the data as a Power View Report –
I would recommend you NOT create a data feed and push it right into a Power View sheet. Instead, I would recommend you pull it in via PowerPivot so you have many additional options on how you’d like to work with the data.
To enable PowerPivot in Excel 2013, go to File -> Add-Ins -> Manage COM Add-Ins
Hit Go, and you’ll see the following –
Check the box marked “Microsoft Office PowerPivot for Excel 2013” and hit OK. You’ll then see the PowerPivot menu in the menu bar.
Hit “Manage Data Model” to open up PowerPivot. You’ll see the data you brought in earlier through an Excel tab is already added to the PowerPivot data model.
Now, you can shape your data to best work with it in a pivot table, Power View report, etc. See how I highlighted the price column? It’s currently being treated as a text column – I’m going to change that to using the formatting options –
Let’s change it to currency
This will give me the ability to use the field like a number and handle things like sum, average and other mathematical functions properly. Now if I go back to my Excel spreadsheet and choose Insert - > Power View, I see both two options –
Products is the data from the PowerPivot, while Table_Products is the original data I pulled into an Excel table. There’s one major difference now in how the price field is being treated.In the Products option, it looks like this and the Sigma sign lets you know it is being treated as a numeric field -
In the Table_Products option, it’s still being treated as a text field -
So if I had brought the feed in directly to a Power View Report, it wouldn’t have been all that useful. I’d strongly suggest you create an explicit calculated field and not use the field directly from the table to do your calculations with, but there are much better PowerPivot blogs to walk you through the hows/whys of things like that.
So now in Power View, I can simply drag and drop fields from my table to make some really cool interactive dashboards/reports
Let’s do a quick table of the different suppliers and their products/prices. Simply check those boxes in the field list and you’ll see the following
What if I wanted to make it a chart instead? No problem, I just choose my chart type from the toolbar and it changes automatically –
This hardly does Power View justice in terms of the potential it has (including interactive mapping, multiple data sources on a single view, etc.), and frankly, this is a blog and not a novel, so I’ll hold some back and split this post into two parts so I don’t bore anyone to tears with my “beloved” dry walkthroughs. In the next part, I’ll briefly talk about some additional Power View and PowerPivot functionality and then get into some of “Actionable BI” scenarios as well.