So I wasn’t really planning on doing a post on this next topic, but it’s probably better than my original plan to sit around all night buying stuff from my childhood on EBay. For those of you who’ve spent any time building reports/dashboards for folks in sales, you’ve probably heard the following –
“I need to see how our pipeline looks against our target.”
Then they hit you with the good old –
“By the way, my targets are by industry for the year” or “My targets are by partner by quarter” or “My targets are in my head” or “Target is a great place to shop.”
If you’re a BI professional for a living, this request isn’t a big deal for most of you, since you’re used to using SQL Server Analysis Services or Business Objects and probably already have a nice cube with this information at everyone’s fingertips.
You’re not a BI professional for a living, just found out there are new target numbers in place and the bigwigs want to see updated numbers from your group first thing in the morning. Would your advice to folks be this?
Not a terrible idea, but perhaps there is a better way to handle it than everyone entering Thunderdome. This post will cover a quick and dirty method I’ve shown to a couple folks in the past. We’ll use PowerPivot in Excel 2013, an SAP CRM BW oData feed (using SAP Netweaver Gateway), and a simple LightSwitch App and the oData feed from that (you could use also use a SharePoint list, but you’ll see why I recommend using LightSwitch in the next paragraph).
The first thing we’ll do is create a simple LightSwitch App so various users can type in/upload their target values – for the purposes of this example, we’ll assume that there are three basic items they are looking to compare to the current CRM information: a category, a time period and the target amount in dollars. I’ll also add a field for the user ID or user name so I can add an entity filter and let users see the data they are adding for now. The table you create in the app will look something like this then –
The (C#) code for the entity filter (if you choose to use one) would be –
partial void Targets_Filter(ref Expression<Func<Target, bool>> filter)
filter = e => e.UserName == this.Application.User.Name;
}You also should add code to have the user’s name inserted into each record added to make sure the filter is, you know, useful -
partial void Targets_Inserting(Target entity)
entity.UserName = this.Application.User.Name;
Okay, once that’s done, add an Editable Grid Screen to the project and maybe a button to allow folks to upload their data from Excel if they want to (best to have the data centralized in case we need to re-use it for other reports in the future). We’ll set targets for this quarter by industry in LightSwitch -
Now that I have my LightSwitch project done and my target numbers entered, I can bring my oData feeds into PowerPivot from SAP CRM and LightSwitch. My PowerPivot file now has two tabs each representing a different data feed, one from LightSwitch and one from SAP. (We don’t need to bring them into Excel first like we did in the previous post – we can add them directly into PowerPivot by going to Get External Data -> From Data Service -> From OData Data Feed)
Which leaves me with the following view -
Okay, so we have our two data sources – how do we link the two? Well, we know the comparison will be made on Industry (Category) and Quarter/Year, and we know that you can create relationships in PowerPivot (or if you don’t, there is a great tutorial here that will walk you through the process), so I just need to create a relationship between the two on those common fields and I’m all done! Wow – this was so easy. Vintage GI Joe Hovercraft on EBay, here I come!
Well, not quite. When I choose the Design Tab and click Create Relationship, this happens –
Uh oh – I can’t create a relationship on those fields because neither is UNIQUE to that table. That’s the key. So how can I accomplish what I need to do? I need to create some unique lookup “sheets” that I can link into PowerPivot and then create relationships to those from each data source. This is pretty easy to accomplish.
Jump back to your Excel sheet and create two sheets. One we’ll call “Industries” and the other we’ll call “Quarters”. Each tab should then contain the unique data for each –
To add them to your PowerPivot data model, make sure you are on the PowerPivot tab and select “Add to Data Model”
There will be a new tab in PowerPivot that looks like this –
Make sure you do this for each table you wish to add. This will allow you to change data without changing the data model in PowerPivot at any time by simply hitting “Update All”.
Ah, that’s better. No errors when I go to create my relationships!
I’ll link CRM to the Quarter lookup table on the Cal_Yr_Qtr
field in the CRM table
I’ll link CRM to the Industry lookup table on the Industry field in the CRM table
I’ll link Targets to the Quarter lookup table on the TimePeriod field in the Targets table
I’ll link Targets to the Industry lookup table on the Category field in the Targets table
My PowerPivot “cube” should look like this (click this button to see the relationships) –
I’m guessing at this point you’re curious to see if this worked. Let’s create a PivotTable based on this PowerPivot cube by clicking the PivotTable button -
Add the Industry field from the Industry lookup table you created and add it as a row field, and the Quarter field from that lookup table you created and make that a column field –
I always create calculated fields to bring into my Values section because then it allows me to do some nifty calculations with them later on without retyping a lot of stuff. I am going to go to the PowerPivot tab in Excel and select “New Calculated Field” from the Calculated Fields dropdown.
For Target, I am going to add a “Target Total” field by using the formula =SUM(Targets[TargetAmount])
Targets is the Table Name I am using, and TargetAmount is the column I wish to create a total for. I’ll do something similar for the CRM amount as well
Let’s add each of those to the PivotTable as Values and see what happens for the first quarter
Ta da! My target totals look correct, and my CRM totals appear to be rolling up properly. Let’s do a quick double check of the data back in PowerPivot by summing up the Amount column and filtering based on 1/2013 –
Well what do you know? I’m not as dumb as I look (according to my father, no one could be that dumb – that was a rough fourth birthday).
Next post I’ll get back to what I promised to cover in this post - showing an “actionable BI” scenario and revisiting Power View. Until then!
(Oh and I was just kidding about my dad - he didn't say that until I was at least 6 or 7)