First Rule of Demos: It will Crash

The first rule of doing any live demo is that it will crash regardless of how stable the product is. That is why you will often see videos of computer demos instead of an actual live demo. Today the first rule of demos reared its ugly head at the most inopportune time.

I had a meeting with my boss and he brought up the topic of needing to delete some data from our data warehouse. Not deleting the data puts us in jeopardy of being sued and so I wanted to show him all the different areas that contained the data. I made sure I had logged into our company's virtual private network (VPN) so I could get into the data warehouse. Then I tried to bring up a virtual desktop which is required to log into the database system. It immediately crashed. I tried several more times only to have the same thing happen. Ultimately I had to describe the many different places engineers had squirreled away the information.

Fortunately I didn't need to actually do a demo as I accurately described the many copies of the data. My boss explained that because the copies of data are anonymized, meaning there is no way to associate with with specific people, they do not need to be deleted, just the non-anonymized data. That came as a relief. Ultimately nobody is using the data and so there is no need to keep it around but deleting it is not an emergency.

I finished my video call with my boss and decided to troubleshoot what went wrong. As you would expect, everything worked fine. That only shows the validity of the first rule of demos. 

A Neat Trick in Excel: VLOOKUP

Lately I have been doing a lot of data analysis. One of the tools I have been using is Microsoft Excel and I recently learned a new trick using VLOOKUP. I thought I would put the information here in case I ever need it again. Oh and it may help you as well.

Most of the time I work with data, I use a relational database. That allows me to join various tables to replace cryptic values with more descriptive ones. A classic example is that of an EMPLOYEE table that uses a department number instead of the name. The database will then have a lookup table that converts the number into the name. This saves a certain amount of space and makes for much more efficient queries.

You can do the same thing in Excel using the VLOOKUP function. In my spreadsheet I have 2 tabs with the first being the employee information and the second being the department. Let's assume the employee tab has the following information:

	A	B	C
1	Firstname	Lastname	Deptno
2	Matthew	Bennett	10
3	Mike	Smith	20
4	Sara	King	30

 Now let's assume that those department numbers translate to something much more readable like:

	A	B
1	Deptno	Name
2	10	Research
3	20	Operations
4	30	Finance

You can use the VLOOKUP function in the 4th column of the table to include values from the department tab. It has the following syntax:

VLOOKUP(LookupCell, TabName!StartCell:EndCell, PasteColumn, Match)

So the LookupCell is that of the number that we want to convert to descriptive text. In the example above, we would use the column C values. 

The next values to fill in are that of the lookup table. While we could have them on the same page of the workbook, I find it easier to list them on a different one and call it the "department" tab. There are only 3 values and so we would start in column A on the second row. We would then run to B4.

The PasteColumn corresponds to the column number on the lookup tab that contains the value we want to show. Our descriptive text is in the 2nd column and so we would use the value "2".

The Match parameter indicates if we want to use an approximate or exact match. This example uses an exact match and so it should be set to "FALSE". I haven't tried doing an approximate match and so I am not sure of a use case for setting it to TRUE. Perhaps I can play with it for another blog entry.

Now let's put it all together. In cell D2, I would use the following value:

=VLOOKUP(C2, department!$A$2:$B$4, 2, FALSE)

I would then copy that formula and paste it into cells D3 and D4.

If those dollar signs confuse you, that is to hold the values constant regardless of what cell you paste the formula into. Otherwise Excel will adjust the reference. In the above example, the C2 reference doesn't have the dollar sign and will be converted into C3 when you paste it in the C3 cell.

This really helped me with some analysis I did and so I hope it helps you too.

Pretty Data

Every Thursday afternoon I have a meeting with several data scientists and we discuss how things are going. I am going to end up missing the meeting today because I have another commitment. However it got me thinking about last week's meeting and the work I have done this week, as I still had to submit a progress report for everyone to review.

Last week we got to see a pretty graph in 3D space. The presenter rotated it around so you could tell it was really 3D and not just meant to look that way on the screen. Anyone that doesn't stare at data all day long for his or her job might have been impressed with the graphic. My first thought was, "So what? What does this graph tell me? All I can see are a bunch of dots that look like a vapor cloud."

Now I suppose I might be a little harsh, but seriously, if you had zoomed out, it would have looked like an oval dot on the screen. That got me thinking about what makes data useful. I may modify this in the future, but today I am thinking that useful data needs to tell a story and/or create an action. For instance, watching a graph of Facebook stock over the past few days indicates that the original investors got screwed, at least for now. This may cause others to see the stock as a good deal and buy some. Current investors may see this data and decide to unload it before it tanks any more (Facebook stock is actually up 2 points today . . . the last time I looked). When I look at a cloud of dots, I don't see a story, nor do I want to do anything. Well I may want to take a nap, but that doesn't count.

So the next time you see a bunch of data, ask yourself, "Is it useful? Does it tell a story? Does it make me want to do something? Or do I just want to take a nap?" If there is no story nor do you feel compelled to take action, then you can probably ignore it.

Too Much Data

Yesterday I started a database report and let it run all day. When I was ready to leave work and the report was still not done, I let it run overnight. I figured if I came in this morning and the report was still running, I would try to figure out what the problem was and see if I could fix it.

I got into the office this morning and the report was still running. I had to have the report done this afternoon to send to management. I had no faith that the report would complete by the time it was due and so I started looking into other methods of creating it. On the off chance my report was going to eventually complete, I let it run.

Most modern databases are fairly smart, have efficient algorithms, and are pretty good at figuring the best way of doing things, but they are not perfect. I was able to come up with another way of getting the information and discovered an assumption I had made about the data was false. Instead of asking for a subset of the data from a single source, I was asking for all the data from multiple sources joined together in such a way that was overwhelming. Discovering this problem allowed me to finish the report and get it out on time.

This whole experience underscored the importance of not making assumptions.