So your boss is going to want stats for your OSD sequence? [Part 2 – Reporting with Excel]

How to provide hard statistics on your build sequences.


In Part 1 I went into how I collected the data that would be used to report on the 2 goals my boss set out for our OS deployment; 1) a 90% success rate and 2) 60 minute build times.


Part 2: Reporting on the Results using Excel

With the data collected, you can then report on it using a variety of tools.  You could open the CSV in Excel and create graphs and pivot tables to illustrate the results.  You could import it into Tableau or Power BI and create the reports.  We’ll start with Excel here and  get into Tableau and Power BI in the next posts.

Report Examples

Over time I’ve used all three to generate reports on build statistics.  I started with some basic Excel spreadsheets and then moved to Tableau when I started creating charts and graphs to put on our SharePoint site.  Then at MMS this past Spring I attended a session presented by Mike Terrill and Ronnie Jakobsen called “Power BI and Configuration Manager: Data Your Way”.  Well, I was hooked on Power BI at that point.

Sample Data

To help things along I wrote a small script that you can use to generate some sample data.  [You can download it here.]

I’ve also made my sample CSV file available as well here any my sample Excel workbook here.

The script will read in 3 simple text files.  The first is a list of computer model names.  I populated it with the top 70-some models being used at work.  The second is a list of task sequence names.  I just made up some names for a little variety.  The final is a list of failure reasons.  I just took some sample failures from work and populated this list.  Keep the script and the 3 text files in the same folder.  Feel free to modify and/or replace the contents of the 3 list files.

The script will prompt you for how many records you wish to generate.  It will then start writing out an “OSDResults_Sample.csv” file.  The computer names will be “PC####” in numerical order.  Build start and finish times will be randomly generated.  Model names, task sequence names and success or failures will also be randomly generated.  I geared it to generate about a 4% failure rate.  For those tagged as failing, a random failure reason from the text file will be selected.

I’ll be using the same sample results file generated from this script for all of my examples.

I’ll illustrate basic reporting on:

  • Results (Success vs. Failure)
  • Average Build Times
  • Model Counts
  • Failure Reasons

Excel

Let’s start with Excel.  Of course, you can probably do similar reports using any spreadsheet application like OpenOffice Calc or Google Docs Sheets.  We’re going to use Pivot Table/Charts.  Pivot Tables are a great tool in Excel that makes consolidating data very easy.  For an introduction to Pivot Tables see here.

You can download my Excel workbook which will have all of these Pivot Tables and Charts here.

Open the CSV in Excel and select all of the cells (Ctrl-A).

Screen Shot 2017-08-23 at 1.55.35 PM

From the Insert menu select Pivot Table and create the pivot table on a new tab.


Results (Success vs. Failures)

We are going to want to see the ratio of failed builds to successful builds.  If you have different build sequences you can break it down by those sequences or you can show overall success v. failures.

For overall results, in the Pivot Table editor, drag Results from the field list to the
“Rows” area.  Then drag Results from the field list to the “∑ Values” area.

Screen Shot 2017-08-23 at 2.11.42 PM

You’ll end up with a cute little table like this:

Screen Shot 2017-08-23 at 2.16.49 PM

 

On the Ribbon, you should see the options for the various chart types.  Select the pie chart and pick one to your liking.

Screen Shot 2017-08-23 at 2.18.49 PM

It will drop a simple pie chart onto the sheet.

Screen Shot 2017-08-23 at 2.20.59 PM

It’s as simple as that.  You can edit the charts to change headings, colors, etc.  But that is the basic process for the other Pivot Tables/Charts we are going to create.

For example, here is a different chart that shows the results broken down by each task sequence.

Screen Shot 2017-08-23 at 2.24.01 PM

The Pivot Table editor is just setup slightly different.

Screen Shot 2017-08-23 at 2.25.12 PM

 

By dragging the TSName field to the “Rows” area the data is broken down by the different task sequences.

Next, drag Results to the “Columns” area so that successes and failures will be tallied up separately.

Finally, drag Results to the “∑ Values” area to tally up the different results.

For the graph, I just selected a stacked horizontal bar graph.  Excel automatically worked out the bar lengths as a percentage.

 

 

 

 

 


Average Build Times

Create another Pivot Table.  For this one, we’re going to look at average build times.

Screen Shot 2017-08-23 at 2.30.22 PM.png

Screen Shot 2017-08-23 at 2.32.30 PM

 

Like before, drag TSName from the available fields to the “Rows” area.

Next, drag BuildTime to the “∑ Values” area.  You’ll notice that Excel added up all of the build times.  Click on the arrow next to “Sum of BuldTime” in the “∑ Values” area and select “Value Field Settings”.

Screen Shot 2017-08-23 at 2.34.43 PM

 

 

 

 

 

 

 

 

Change the Summarize value field by to Average.  Click on the Number Format button to adjust things like the number of decimal places if you would like.

Screen Shot 2017-08-23 at 2.36.07 PM

For the graph, simply select a basic horizontal bar chart.


Model Counts

Create another Pivot Table tab.  (You should be an old hand at this now.)

Screen Shot 2017-08-23 at 2.40.50 PM

 

Drag Model to the “Rows” area and then drag Model to the “∑ Values” area.  You’ll then have a table showing how many of each model has been built.

Screen Shot 2017-08-23 at 2.42.42 PM


Failure Reasons

Create one last Pivot Table tab.  Time to see what problems are causing our failures so we can focus on big issues.

Screen Shot 2017-08-23 at 2.45.30 PM

Screen Shot 2017-08-23 at 2.46.01 PMAs you’ve probably guessed, drag FailureReason to the “Rows” area and then drag FailureReason to the “∑ Values” area.

For the graph, again just a simple horizontal bar chart.

 

 

 

 

 

 

 

 

 

 

Now, notice that you have “Success” far out pacing everything else in the graph.  That’s a good thing, but this report is meant to focus on the reasons builds are failing.

Screen Shot 2017-08-23 at 2.44.47 PM

It is easy to remove the “Success” entries.  At the top of the table, click on the down arrow by Row Labels and remove the checkmark from “Success”.

Screen Shot 2017-08-23 at 2.49.59 PM

There, fixed it!

Screen Shot 2017-08-23 at 2.45.30 PM


That’s some basic examples on how you can report on your build results using Excel.  It’s easy and it’s quick.  You can dress up the charts, put them into documentation, or onto your internal web site.  Plus with some decent PowerShell you could automate this entire process.

Next time we’ll do the same sort of thing using Tableau.  Again, I’ll use the exact same sample data to make it easier to compare reports side by side.

Advertisements

Posted on August 24, 2017, in Excel, Handy to Have, OSD, Task Sequences and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: