The Spreadsheet Advisor

Using another spreadsheet as datasource is one of the easiest dataloads to set up.

Why?

“What’s the point?” you might ask. Well, from a business point of view, it may have some advantages to keep the data source and data analysis apart. A few:

1) Security reasons. Situations where you need to add personal comments, -analysis and/or –calculations, which is for “your eyes only” or just to keep your own files, which others can’t edit.

2) Reduce workload. As mentioned in a prior post, the major advantage is the fact, that you only have to set up the analysis once, and then simply update the data ever after. No reason to explain the advantage in this solution.

3) Multiple data entry. In many cases you need to collect data from various people or colleagues (holidays, working hour registration, travel expenses, etc.) or systems (fx. logs of all sorts). In the cases where the number of people and data are small, a spreadsheet is a fine solution for this kind of data collection. With this approach, you have an “external” “data entry platform” (A) in which you can administer read/write access easily (through folder security), and a detached “data consolidation platform” (B), where you make your analysis.    

How – working example?

Situation: you are a leader of a sales team, who wants an easy overview of the mileage your team logs on an ongoing basis. Your goal is to get a picture of the number of miles each team member drives every week. 
Example of the mileage log:

Walk-through:

1) Open an empty spreadsheet.
2) Choose the "data"-ribbon and click on "from other sources" ("external data"-group)
3) Choose "Microsoft Query" 

4) Choose “Excel files*" (Note: be sure to mark “Use the Query Wizard to……”

5) browse your way to the spreadsheet (datasource) -> click "ok"

 
6) now you see all the sheets in the workbook (note: if you do not see any tables, click “options”-> mark “system tables”->click “ok”). Open the relevant datasheet (click on "+") and choose the columns needed.
In this example I will only be needing the columns:” Date”, “Person”, “Mileage”

7) click "next" through the guide -> click "ok"
8) in the popup "import data", click "pivotdiagram and pivottable"

9) now you setup your pivottable/chart for further analysis.
a. Add week or date in “axis fields”
b. “sum mileage” in values
c. “person” as filter
d. (layout is out of the scope of this post, but please ask me if you have any questions.)

And the final result:

10) finally you want to update your spreadsheet automatically everytime you open it:
Go to the ribbon "Data" -> click on "connection" (now you see the dataconnection you have created)and choose your dataconnection -> click on "properties" ->
choose the tab "use" and set a mark in "update data, when you open file" -> click "ok" -> click "close".
 

Now your pivottable and chart will update with the latest registrations everytime you open it.

Enjoy!

My posts in the category "Dataloads", may seem a bit more technical, than what you expect in this blog. You may think: "What on earth do I need this for ? I just paste my data in the spreadsheet, and work on them from there! That's the hard part, so let's get going!" 

However, I do consider setting up dataloads and dataconnections to be an important subject, due to the "ground rule", which we all try to work towards: "WORK SMARTER, NOT HARDER!".

Through my career, most Controllers, Business Analysts, Team Leaders, Process Managers, etc. I have seen using spreadsheets, make the same approach for data preparation: They cut the data from a datasource, paste it into a spreadsheet and then they begin to set the data up for analysis. The next time they need to update the data, they paste a new data set on top of the old one....and start the same datapreparation all over again. Lots of energy and time wasted. 

Why make the same work over and over again !?!

This approach can be improved significantly, by direct access to the relevant database. This "Direct-access" to the datasource have the advantage, you only need to make the datapreparation once. After that, you simply refresh the datamodel everytime new data are available and Voilá! It takes a splitsecond.  

It is true, that setting up a dataconnection to a database can be a tedious task for people who are not used to work with IT. Today, however, these tasks are, in many cases, much easier than you expect.

Therefore I will from time to time depart slightly from the primary scope of this blog, "advise business people in the use of business intelligence", and give some "tips´n tricks" and "walk-throughs" on how to set up dataconnections in Excel, in order to help you to "work smarter, not harder!"