Category: Power BI

I wasn’t planning on this being part 4 of this blog series.  No, the plan was to do an end-to-end scenario using real data and a real business use case.  Instead, you’re getting a rant that I’m dressing up as a blog post.  The rant of a man who spent his Saturday afternoon doing what’s the hardest part of any BI project – actually getting the data and making sure it is clean and correct.

There’s been a number of new announcements recently around new analytics solutions from companies like Amazon, SAP and Tableau.  The announcements all had a similar theme – we’ve got a snazzy new product that will help people to easily explore, visualize and analyze their data.  And to show you how ground-breaking it really is, they give you a chance to try it using one of their pre-loaded sample datasets.  With just a few quick clicks, you too can make a bar chart that shows you how much ticket sales were for Jurassic World.  Or better yet, how many third class passengers died on the Titanic!

Yes, the best friend of BI pre-sales personnel everywhere, these perfect public datasets do a fantastic job helping tell the story you want customers to hear – just take your perfect dataset (preferably a single table on a spreadsheet), load it up and watch the magic happen.  And everyone uses these datasets – yes, including here at Contoso Microsoft.  It’s just the reality of doing demos – privacy concerns make using real company data very tricky, you don’t want the demo to go wrong, etc.  So like the latest Macklemore video, these demos look great at first glance, but trying to find any actual meaning around the information being presented is probably wasted energy.

That was why I wanted to finish off my perfect productivity series with a grand finale – use real data and a real business scenario that showcased the value of Power BI and Office 365.  And I had an excellent one – my wife runs an online business I help her out with from time to time.  She’s had some moderate success with it, but she’s never really used the data in any meaningful way, other than the normal tax stuff one has to do.  She suggested I look at the data to see if there were some things she could be doing to make more money at it.  Jackpot!

So yesterday, I sat down to dig into the data – surely, this was going to be a piece of cake.  All the data was in a central location, I knew the business, etc.  That’s when I ran into a data access problem.  The site she sells the items on only lets you get the records for the current month and the past three calendar months.  Since she had sales going back to October 2013, that wasn’t going to help.  I then tried pulling the data from a site called Terapeak, which I love to play with because it has historic sales data, but they too limit the number of records you can download.  Gee, what a surprise, central IT was preventing me from getting the data I needed to do my job . . .

After I snapped out of my shadow IT flashback, I did what I used to do in these situations.  I figured out how to work around the data access limitations.  I discovered the data was also available from PayPal, and those you could run for a custom time period.  Woohoo!  So I ran the report, downloaded the file, and opened it up in Excel to review the data before I got started.  That’s when the “fun” began.

The first problem was PayPal had two records for each item someone bought, one that reflected the item when it was in the shopping cart, the other when it was it actually paid for.  It looked like this –

Then I noticed there was stuff in the download that had nothing to do with the sales themselves, but were potentially for office supplies or the like.  Plus it had a bunch of extra columns, date formatted in a dumb way, etc.

No big deal for me – Power BI desktop and Power Query in Excel eat these types of problems for breakfast.  And unless I missed something in my BI 101 class, no snazzy visual or finger gesture on my tablet is going to make up for the fact my dataset from the company’s “single source of truth” is giving me complete garbage.  Oh, but it gets better – this particular report included all the listing information, including the listing title, but didn’t include the listing category, which I needed.  So I’d have to recreate that myself for the purposes of this report, based off of the item title.  Also, the publication year for the item being listed, which was very helpful for certain listings, was buried in with the main title text.  I needed to pull that out and make it its own column.  It was becoming clear that my grand plans to play Witcher 3 all afternoon were evaporating fast.

We’re now teetering dangerously close to Ken Puls/Bill Jelen/Rob Collie territory in terms of ability required to make this happen, and last time I checked, there weren’t folks like this littered across every department of companies I’ve spoken to (see my previous post for some context on that).  Unless of course, I’m simply muscling through the spreadsheet and typing values into new columns manually as I eyeball each line.  Which is EXACTLY WHAT 99% OF PEOPLE WHO THESE NEW TOOLS ARE MARKETED FOR WILL DO.  It’s going to be faster for the vast majority of people in the short term.  Or maybe you’re using Google Docs, or haven’t heard of Power Query, whatever.  But not ME.  No, I’m going to make the folks at PASS proud by writing this great M script and –

If you’re thinking that ends with the phrase, weep like a baby when you notice yet another data issue, you’ve obviously been on this goat rodeo before at a customer site.  As I went to start my data import, I looked more closely at the title field – in the report, if someone bought multiple items in the same transaction, it only lists it as a single transaction line item, with a summarized sales amount.  BUT, it has all the items bought as separate values in the item title field, separated by commas.  So it looked like this –

Sadly, Bo didn’t know profits.

I knew what each item’s sales price was for, because the prices were always the same for the same types of item – for example, an old book would be listed for $9.95, and old Xbox game would be $14.95, etc.  But I still had to break these records into individual line items and then adjust the value on each line to reflect what the price was for that item only, since the overall item total would be copied to each line.

Uncle.

I’m sure it’s possible to get an M script that would automate this, and I’ve done stuff like that before in SQL Server, but I wasn’t about to fiddle with it for the 175 or so rows I needed to clean up.  I sucked it up and did that part of it manually in Excel.  The rest of the data clean-up I outlined I did do in Power BI desktop, but it took some custom M script, two Ken Puls articles (this one is especially useful) and about 200 (!) steps.  Oh and about nine hours of my time.  And Geralt’s time.

But the pain was all worth it – my properly cleaned dataset contained some remarkable insights that I was able to light up in Power BI with some of the new custom visuals that were introduced last week.  And it also reminded me of what it’s like in the real world, because this scenario happens every day, in every company in almost every department.  It certainly happened in my old group more often than my boss coming to me with a perfect data export of Titanic deaths he was looking to throw a pie chart on.

Jen Underwood touched on this theme in her post asking if you’d bought a self-service BI fantasy.  I’d take that one step further – you might need to ask yourself if you’ve bought a corporate BI fantasy as well. Because for all the flaws people feel the Microsoft solutions might have, it’s the only one I’ve found was designed and consistently used as part of every solution here in the real-world, where data is messy or unavailable or both.  And we’ve got pretty great visuals as well – ever seen a Datazen dashboard or that cool fishbowl in Power BI?

Have a great week at PASS!

This next scenario was one I was going to tackle at some point, but I decided to do it now because of a couple articles I read this past week.  Interestingly enough, one of the articles was a blog post from Tableau, and the other was a survey done by Domo.  I found them interesting because they both touched on a topic I’ve been intimately involved with for the last several years, and that’s sales reporting.  I’m guessing, however, my reaction to each article was different than other folks who read them because of that.

The Tableau post told the same story that every company that sells a BI solution talks about to their customers – here’s how we use our own product to run our business.  For example, Marc Reguera does a great job telling customers how the finance team at Microsoft uses the Microsoft stack to run the business.  I’d have been shocked if the article was about how the sales teams at Tableau reported on their business using Power BI (although I’m guessing James Phillips would have blogged about that article as opposed to me.  But I digress).  The report they showed had the usual metrics you see every sales organization look at – top opportunities in the pipeline for the quarter, YoY growth rates, Achievement vs. Quota, etc.  There was nothing “wrong” with the post at all – but my first reaction was immediate and made me laugh.  That was because I created a report similar to this four years ago for a VP I supported at the time.  I’d spent months doing the following –

– Getting security access to the right data

– Ordering a VM to host my reports

– Setting up SQL Server on the VM to host my data from these sources

– Creating SSIS jobs to cleanup and bring the data in from various .csv files

– Setting up SQL Server Reporting Services

– Building the report in Report Builder

– Deploying the report

There was no doubt in my mind I was about to be the new BI hero in town.  I presented the report to him and waited for his reaction.  Which was the following –

Shrug re-enactment drawing courtesy of Rob Collie

Yep, that was his reaction.

“You don’t like the report?” I asked.

His response and subsequent explanation really stuck with me.  And it was partially highlighted in the survey results I called out earlier – on slide 17, some specific issues were culled from the responses.  The ones I noticed right away were these –

– inaccurate

– not up to date

– unreliable

– hard to understand

When it comes to sales reporting, the vast majority of data comes out of the Customer Relationship Management, or CRM, system.  This is where the sales organization enters all of the information around their customers and the sales opportunities they have with them.  You might have heard of some of the companies that sell CRM systems – Microsoft, Salesforce, and SAP are three of the largest vendors.  However, every CRM system has the same “flaw” – they all rely on salespeople to enter data in the system.  And the quality of the information entered can vary wildly depending on the salesperson for a number of reasons.    

This isn’t meant to be an blanket indictment of folks working in sales.  Being a good salesperson is HARD.  Having a quota you are responsible for, presenting or speaking to customers most days, making cold calls or traveling – it is a job that comes with a lot of pressure.  And every time you enter something into CRM, it’s going to be immediately tracked, dissected, discussed by your manager, their manager, etc.  Which means sometimes, people are very “selective” about what they put in CRM – some might think to themselves, why bring a lot of extra attention to yourself if you don’t have to?  Or they’ll put the value of the opportunity much lower than it really is for similar reasons.  Because of travel, perhaps they don’t update their data in the system for a few days because their too busy.  Or they hate entering stuff in CRM and put it off as long as possible.  Or they are told to put a bunch of opportunities in the system that they have little confidence in but need to show a “full pipeline” for management.  Combine this with the usual number of data entry errors folks will make, and you have a scenario where you have a sexy dashboard that is showing a pretty picture of a lot of bad data.

Should these folks be doing these things?  Of course not.  But the point my manager made was that since this did happen, he needed a solution that could account for this “shadow pipeline” as well as what came out of CRM so he could make a proper forecast of where the quarter would end up.  And he wanted it structured the way they had it currently in Excel, which looked like similar to this overly simplistic re-creation –

Why couldn’t he just force his team do it properly in CRM, you might ask?  Because they were a global team that worked with the regional sales folks, but didn’t directly enter any of the data.  This is hardly unusual – most, if not all, customers I talked to had many, many groups leveraging this data that weren’t entering it directly.  So they needed a way to include this information in the report.

At first glance, this seemed like an easy enough request to add.  I’d follow a similar pattern to what I did in the first post in this series and build a form where his direct reports could enter their shadow pipe, then show it in the format he wanted in the SQL Server Reporting Services report I’d built.  So I went back and did that (which took several hours of work) and re-presented it to him.  Again with the shrug.

“What’s the problem now?” I asked.

“I can’t change the shadow pipe values,” he said.  “I need to be able to do that on the call.”  He was referring to the regular team call this report was used on.

“Well, I can show you how to change them, but I don’t understand.  Why do you want to change values on the call they just typed in for that call the day before?”

In hindsight, this was a really stupid question.  He could have said, “Because I said so, dumbass.”  But he decided to explain anyways.

“There could be a number of reasons.  First off, a lot of stuff can happen around here in a day, so there could be an update that needs to be reflected.  Second, I just hired three new people.  They don’t know the accounts real well and might change their forecast when we talk about it.  Third . . . ”

“Okay, I get it.  But you can change the values – just open the app I built, change the dropdowns, find the value to change, hit save, re . . .”

That was not what he wanted to hear.

“I’m not doing all that, Chris.  I want to change it like I do now, where it takes a second and all the calculations update automatically.  And I also want a way where I can add notes on the call that I can use when I share this information up the chain.  You should never give an executive a sales report without context.  Otherwise you’re just creating a bunch of additional work.  They’ll always want to know why the numbers are what they are, what are we doing to help the teams drive more revenue, do we need to re-allocate funds around . . .”

He was absolutely right – I’d seen that scenario play out time after time.  So I went back and took another crack at it using Excel and this new add-in Microsoft had released called Power Pivot.  However, the way the data needed to be laid out meant people would have to enter data in “holes” in the middle of a pivot table.  There were also a bunch of subtotals in the report currently where I needed to add up data from both the entered data in Excel and the summarized data from SQL Server that also sat in locations in the current report I couldn’t see how to do in Excel.  It also didn’t address the issue of having a central place for folks to go and enter the data because we didn’t have SharePoint setup (this project spurred me to do this for the team on my own, actually).

Eventually, the solution I provided him was a completely custom solution in Visual Studio that took probably three weeks to build, and countless additional hours to tweak over the time I was supporting them using it.  The good news was, he was happy with this and it led to a number of great projects I worked with them on.  The bad news was, making changes was something only I could do, so it died as soon as I left and they needed to make a change. Plus, I never felt comfortable I’d found the best solution.

What if I had the opportunity to do it over again now?  Well, I’m not waiting any longer – let’s redo this baby right now using Office 365 and Power BI and what we can come up with.

I’m going to run through the entire scenario in my head as best as I remember how this used to work, and map it to the functionality in Office 365 or Power BI in each step –

– A meeting invite was created and sent to the participants via e-mail (Outlook and Exchange)

– Data needed to be cleaned up and combined from CRM and another data source that has the quarterly sales targets stored (Power Pivot/Power Query in Excel 2013).

– As opposed to building a custom app, I’ll create the report using Excel as the front-end and taking advantage of Cube Formulas.

If I had simply spent more time reading Rob Collie’s blog post from January 2010 (!), I would have seen I could have provided my manager with the exact functionality he was looking for by leveraging cube formulas.  For the people who’ve never used them before, in essence you have the ability in Excel to blow up a pivot table and layout the cells however you want while still being linked to the data model dynamically.  Rob spends more time talking about cube formulas than I do about Datazen (well, close anyways), so I’d strongly urge you to read his blog to learn more, but here’s how I’d do it for this project –

1. From the Power Pivot window in my Excel 2013/2016 Preview workbook, I’ll create a new PivotTable (or I could create it on the Insert tab in Excel.  It doesn’t matter) –

2. Add to the pivot table the results I need for my report from the CRM system.  I added a slicer as well just to make sure everything is still connected to the live data source after I convert the data –

3. Under PivotTable tools, select OLAP Tools, and Convert to Formulas in the dropdown  –

You’ll see this has converted the cells to individual formulas in the formula bar that looks something like this, depending on which cell you have highlighted –

4.  Cut and paste the last row of values down one from it’s current location so your report looks like this –

5.  If I click the slicer, my values update to the proper results

I now can lay out the report any way I need to, allowing data entry in certain fields, add formulas that properly sum the fields that are entered and tied to the data source, add an area for comments to be entered for each row, etc.  It would have met the requirements he had for his frontend experience.  Single tear . . .

– Central place to collect shadow pipeline and notes from users for the report (OneDrive for Business Access Web App)

As I thought about how I’d handle this piece, I initially considered the idea of just saving my report to OneDrive for Business, sharing a link with the users and allowing them to enter the data in the shadow pipeline field accordingly since the report was small enough it could be used with Excel Online.  He could simply overwrite that on the call with them, or add additional notes and call it a day. There were a couple issues there I had concerns about –

1. Anyone could potentially overwrite the fields that contained cube formulas when editing in the browser.  It wouldn’t be the end of the world, since I’d have multiple backups of the report, but still wouldn’t be ideal.

2. Instead, I looked at adding protection to the sheet while leaving certain cells unlocked and available for editing.  Problem was, this only would work when folks opened it in Excel locally.

For the record, if I had offered this second idea to my manager on how to handle this, he would have said that was fine and not to overthink it.  I know this because we ended up doing something similar for a different project once I got SharePoint stood up.

A better answer in my mind would be to let his team enter the value and notes via an Access app in Office 365 like I showed in the last post, but give him a field where he could enter a value as well, and supersede any entry folks made in the calculations if he entered a value.  Why bother doing this extra work if I know he would have accepted the previous option as a solution?  Because I know he’ll want to capture these values in a way that he could see historically what people were forecasting and how accurate they were vs. the actual sales amount (I know because he did ask for it later on, by the way).  My proposed solution would allow him to do just that, and then he’s the only one ever updating data directly in the report.

– Share the report with my manager and his team (OneDrive for Business and Power BI)

By loading the report to OneDrive for Business and then accessing it through Power BI, I get the best of both worlds.  I now have a way to share a link to the Excel document with my manager so he could view and edit it, provide a link to anyone else so they can view it only, and leverage the Power BI personal gateway to refresh the data on a schedule basis.

This is pretty easy to do.  I can save the file right to OneDrive for Business from Excel directly.  I just go to File –> Save As, then add an Office 365 SharePoint location

Enter my credentials and my OneDrive for Business information is added.

Once I save it there, I flip to my Power BI area and add data from my OneDrive for Business folder

then choose my Excel file I want to connect to.  I want to see and use the file just like I would in Excel Online, so I choose that

Then I can view or edit it as an Excel document AND leverage scheduled refresh.  No conversion to a new Power BI report necessary.

– Run the online meeting for the global team where they review the report and talk about their part of the business (Skype for Business)

There, I was able to redo everything using Power BI and Office 365 and meet his specific requests asks around being able to provide context for the shadow pipeline figures that were captured as part of this process.  I wonder if I could connect the data in that Excel sheet to the PowerPoint presentation he wants to give at the end of each quarter.  I bet I can . . .

Hmm – I’ll save that for another post in this series down the line.  So remember, the next time you’re asked to build a sales dashboard or report, see if can you provide context to the shadow with it.  Because if your solution doesn’t have that level of flexibility, people looking at your report might be “in the dark” when it comes to having the right answers (Yes!  I was looking for a terrible pun to end this post with, and I succeeded.  In your face, good storytellers.)

Until next time!