Forecast for tomorrow: sore everywhere, with scattered achy

There was one thing that I had hoped to get done on my vacation, and that was yard work. We're officially in early autumn here in greater Chicagoland, but the casual observer or visitor would not have surmised that based on the temperatures, it was late August. According to some websites, the area averages nine days per month with high temperatures over 80 degrees F (26 C), but this year we had fourteen. Only the last three days of the month saw highs below 70 F (21 C). We also had thirteen rain or thunderstorm days. So, not a lot of yardwork got done in September.

Earlier in the week, I mowed the front yard, but it was hot so I left the back for the following day. The next day, it rained. Of course. I swear I cannot make this stuff up.

The following day was warm and breezy. I did the back. Yay... the first part of my work was done. The next part, though....

Today, Saturday, started off as the perfect autumn day for yardwork. While Jennifer prepared a much anticipated breakfast of pancakes, sausage gravy and effs over easy, I made a cup of tea and got out of the way. Schwarz came out and sat down on the picnic table. I savored the moment, taking in the cool humid air while catching up on Words with Friends and sipping my tea. The local high school (American) football team was at home, and occasionally I'd hear the spectators cheer or the band strike up a tune. As I'd completely caught up my games and the tea was gone, I went back in and had the most incredible breakfast!

The next hour or so was taken up by a long overdue project: removing a line of concrete blocks I had installed many years ago to separate a narrow strip of garden from the lawn. Once they were removed, I backfilled the resulting trench with compost. Which means that... most of the yardwork is done!

In Lego/data news, the Ferris wheel is moving along slowly, as I had anticipated that it would. I experimented last night with creating supports for the spokes, and I have a promising system... pics to follow if it works!

In other Lego/data news, Sensei Wu has an upgraded Bō. Astute observers are probably wondering at this point why I'm messing with Minifigs when they are clearly not an area of interest for me?

Well, for starters, Wu is a cool-looking character, and his hat is a fairly rare part (yes, I'm one of those AFOLs [Adult Fans of Lego] that will buy a set based on parts rather than the model). Wu seemed to beg for an upgrade, though, and the best one I could think of was to turn the Bō into a scythe. No problem- Mr. T and I eventually came up with the True Neon Green x346 part, but the process of finding the part was tedious. I probably have somewhere in the neighborhood of 50-100 of these in various colors, but none of them are in a dedicated parts drawer.  We located the green one in a drawer dedicated primarily to Bionicles, but with so few Bionicles currently assembled, the drawer needs to be sifted through. An inventory and better parts organization would have probably saved me ten minutes.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Meanwhile, back in the Secret Underground Lair....

Those kind folk who have been following this blog for a bit may be aware that our home office, the Secret Underground Lair (SUL), is undergoing a bit of a reconfiguration. That's actually a "bit" of an understatement.

Before going to work last Monday evening (3/7 or 7/3 depending on your calendar), Mr. T and I spent about a hour of quality time grunting, straining and sweating as we resituated eighteen cartons of books. While moving the books, I discovered a few things: not all of the boxes or plastic storage containers were properly or efficiently packed, and more importantly, there are MANY books in storage that I need access to, so we will be swapping some books out.of storage and replacing them with books which really should be in storage.  It was worth it, though: for the moment, we have TWO walkways in the office. Mr. T tried some scenarios in Microsoft Visio, though, and is currently of the opinion that we will not be able to maintain this corridor if we want the best use of our limited space. I hope to do a bit of work in the Dungeon to free up some additional space. As we are on the cusp of Daylight Saving Time (ugh!), it's nearly midnight, so any more SUL work will take place tomorrow (Sunday 3/13 or 13/3!). And if anyone is doing the math, it has taken me seven days to get this far with this latest blog entry... overtime is great for our bottom line, but it definitely puts a damper on other things- like blogging!

Data!

In my previous post Working with someone else's data, I mentioned that I'm using peeron.com's data as the basis for my Lego data. I also indicated that although it's a great list, I have a good amount of work to do before it is useful to me. The following two lines are a sampling of consecutive rows copied from the .txt file, and illustrate quite well my "issues" with their data:

3001px2 Brick 2 x 4 with Eyes and Wavey Mouth Pattern   nose, nostril, snout
3001px20        Brick 2 x 4 with Red Danger Stripes and Two Horizontal Stripes Pattern

For starters, peeron.com has done a nice job of making a list of Lego parts. Their website is excellent, and their search engine is very nice. However, I'm not interested in their frontend, or even their instruction scans.

I'm cataloging and categorizing MY data, and their part number list is what I need. And although I've also mentioned before that this list is at LEAST four years out of date, up to date is not what I initially need. I need the basics first. And although peeron's 18,511 rows of data may seem impressive to someone who isn't an Adult Fan of Lego (AFOL), for me the list is horribly incomplete as I have purchased sets that were manufactured after 2012. Also, it has a lot of data that is extraneous for me, primarily part numbers for sticker sheets and parts which have somehow been redesignated, renumbered or somehow made obsolete. So, I need to do a bit of rework on their data.

The red bolded text above is two consecutive rows of data copied from their .txt file. Based on these two lines, I hope to show how much work I have cut out for me.

For starters, I took their .txt file and copied it into an Excel spreadsheet. My first task is to make two columns out of this data- one for the part number, and one for the description. I'm using 3001- this is the Lego designation for the ubiquitous 2x4 brick which nearly every human being has stepped on at least three times. The next few characters describe the variation/description of the brick. So far, so good. First problem: I don't know of an easy way to separate the part numbers from the descriptions- I'm certain there's some software that can parse the data, find the first empty space, and move the next set of characters into a new column. So, until I find a software solution, I'm doing CRTL+X, CRTL+V to manipulate things. Second problem: Once they are separated, I can't sort these, because although px2 comes before px20, px20-23 come before px3 in Excel. And some of these numbers run into the hundreds. So, nearly every suffix will need to be reformatted to actually display in numeric order. To do this, I'm going to create a new part number that my database will use; if I ever create my own Lego website, only the original Lego part numbers will be used.

After the columns are created (and before I start tweaking the part numbers), I'm going to pull out the extraneous part numbers and put them in a separate worksheet. After that, the remaining data descriptions will get trimmed down and standardized with a few hits with Find and Replace.

I've currently got 2,022 rows separated- roughly 11% of the 1st stage is complete. That's it for now.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Managing data better

I just discovered a fairly large data loss. As mentioned in a previous post, I rebuilt my laptop due to a failed HDD. Unfortunately (no one is looking, you can raise your hand if this has ever happened to you), I had a fair amount of data on that drive which was not backed up.

To paraphrase Hall and Oates, its gone. So, to paraphrase the unofficial motto of Chicago ("Vote early, vote often"), Save early, save often.

As promised in my previous blog, I'm going to spend a bit of time on Lego, because as far as my data is concerned, it is handled a bit differently than the other items which I am cataloging.

Lego and I go way back, but I didn't attempt to catalog my Lego collection until fairly recently. I've given consideration to including Lego in the "big database" (Forty-Two), but have found that- at least for me in this particular application, Excel is the better tool for me to use. Let me try to unpack that a bit.

Some time ago, I had the opportunity to observe how several different businesses utilized software to work with data. Some preferred Microsoft Access, and most preferred Microsoft Excel, or, to be a bit more generic, spreadsheets were preferred over databases. In only one business were both used- but independently, rather than in a complimentary fashion. The preference of software had little to do with function, generally speaking. Rather, it was more about culture and familiarity. In every case I observed, the results could have been improved simply by not just "thinking outside the box," but merely by thinking.

In my case, a spreadsheet seems to be the best solution- based upon my experience. My primary reasoning behind this is because Lego is a single thing which does not need to be linked (or, related) to anything else. And, even though I may be interested in a bit of analysis of the Lego "population", in the larger scheme of things Lego exists in its own unique bubble: shapes, colors, themes, sizes. I could build tables based upon these and other categories, but once again, they would relate only to Lego.

Forty-Two, on the other hand, illustrates quite well the differences between a flat database (the Lego spreadsheet) and a relational database (Forty-Two). With the Lego spreadsheet, I can see a snapshot of all the facts of my Lego collection. But with Forty-Two I can write an ad hoc query to tell me where all Lord of the Rings media is located. It would show where each book, soundtrack, game and video is located. It would also show the number of copies in a given format. It would also tell me the last time the media was viewed.

There's also one additional aspect of Lego databasing which is unrelated to software, but makes cataloging it so much easier: storage.

I'm an AFOL (Adult Fan of Lego). AFOL is a title; more of a descriptor, actually, as it really doesn't carry any of the clout that, say, CCNA carries. Still, it differentiates me from most adults who play with their kids while playing with Lego. And, its somewhat hard to say that without sounding like some sort of pompous jerk, because it sounds like I'm slamming parents who "play" Lego with their kids. Quite the contrary! If you're a parent who engages with your kids over a pile of Lego, kudos to you!

An AFOL, though, uses Lego as their primary creative medium. There are professional Lego artists out there who make a good living by building amazing models for corporate clients. There are also educators who use Lego in either a standard classroom setting, or in a program for ASD (autism spectrum disorder) kids. Even architects use Lego for models. And although each of these examples is an example of adults working with Lego for a living, the typical AFOL is something else.

They're a bit of a subject expert on Lego. They probably have some advanced building skills. But mostly, I think, they are something of a type of a Leonardo da Vinci. They are probably the precursors of the maker movement, which is another subject entirely.

Back on track: AFOLs need organization, and the English word for this is storage. Over the course of many years, many bricks (elements) will be collected. I've found that the (current) best way to keep track of these is to put them in plastic bags (connected in groups of 10) with a card inside indicating the count and the date of the count. These, in turn, reside in drawer organizers.   

As always, I am hochspeyer, blogging data analysis ad management so you don't have to.

Tables and chairs

In my previous post, The Elusive Balance, I had mentioned that my goal was to enter twenty-three CDs into the Media_Title table. I am happy to report that this goal was reached, and the rack they were destined to fill is now completely occupied. After doing the updates to this table, however, I discovered some flaws in my "main" media table, which has caused me to reconsider the design of my database.

I wish I could remember where I had read this, but someone once wrote that the best way to start a database is to design it with pencil and paper. Although I've always agreed in principle that this was a great idea, in all of the databases which I've designed, I've rarely heeded this sage advice. This is partly because planning things often results in frustration and headaches for me, but also partly because I can "see" with my mind's eye what the database will look like and what it will do prior to a single keystroke being executed (note to the faint of heart and the true database newbs: semi-professional DBA on a closed course. Do not attempt at home or work. Especially at work).

One other caveat is necessary when discussing database design: don't be too afraid of change.

When the idea of Forty-Two first came into my mind is hard to say- it predates this blog by a few years at the very least. The concept was fairly modest, at first, and didn't even actually have music or media as its primary focus. It was actually my Lego collection. This was another project that grew by fits, false starts, and occasional bursts of inspiration and diligence. And it was done entirely in Excel. Over time, though, I saw the possibility of building something more powerful (and consequently more useful) in Access.

The earliest Access versions- starting in Access 2003, then moving to 2007 and finally 2010- were not grand by any stretch of the imagination, and were at first only intended to catalog music CDs. Eventually, movies seemed to be a natural addition and were incorporated, followed by books, software, console games and finally books in the latest iteration. And all of it goes back to Lego.

One of the problems I have with Lego elements is that although I do not have a large collection by many AFOL (Adult Fan of Lego) collectors' standards, I have enough to necessitate them being stored in different containers and in different locations in our home. As I was working in Excel, I found that I didn't have a really good way of taking this into account. When I realized that Access could handle this particular problem, another thought occurred to me: I could do this with media as well, and eventually incorporate things that were totally unrelated, and this could be useful in insurance planning.

That's the short version of the history of Forty-Two. The immediate future, as alluded to in the first paragraph, will see the end of what I often refer to as the "primary" table. It will be replaced by several (relatively) smaller tables, each being tasked with holding a specific type of media: music, videos, books, etc. Other tables will host data on Legos and electronics, for starters. And lastly there will be the helper table which exist primarily to normalize the database.

Whew! A whole blog post devoted to data and nothing but data. At this point some may be wondering what is up with the chairs in the title? Well, "Tables" alone sounded boring; "chairs" is pretty much an attempt at a hook to draw a potential reader in.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Not in the cards, I guess

Several years ago, we picked up a nice piece of software from Nova Development called Art Explosion Greeting Card Factory. We've been very happy with it, try to keep up with new version releases, and as a result have probably purchased no more than five preprinted greeting cards in that time. Jennifer uses it fairly often, but I don't. As such, it's been a while since I made a card.

As I've told folks in real life as well as on some of the LinkedIn groups of which I'm a member, most software does so many different things that there will always be something you don't know, and the easiest way to learn software is by using it. And so it went with me yesterday- I could have asked Jennifer to make the card for me, and she probably would have. She also would probably have made a nicer card... but as toddlers often say, "I can do it MYSELF!"

Well, there's that, but more than that, I wanted to show the recipient that I could do a thing or two with graphics, layout and text. So, I sat down and began to create. In addition to not being the resident expert and not doing this very often, this was also a new version of the software which I had never used before.

I left the inside for last, as there would only be a short note and a piece of clipart there and started on the front, setting the card to landscape. I found a very nice picture on Google and pulled it in, resized it and sent it to the back. Next, I checked for the exact verbiage and put a nice quote on top as a headline, and changed the color of the font. Finally, I grabbed a few other images off of the internet and saved everything. I went back to the internet and grabbed a few nice fonts. Since I haven't done any of this in some time, I learned that I should have grabbed the fonts first. I saved my card and closed the program.

I added the fonts to the Windows fonts directory and reopened my card. Instead of my beautiful art, there were diagonal neon green and orange stripes! Feathers ruffled but undaunted, I asked Mr. T what graphics editor he preferred. I was going to use GIMP, but I recalled both he and Amanda didn't like GIMP- he suggested Paint.net. Back to the desk... download Paint.net. Redownload both images, crop the smaller one and save them as individual .jpeg's. Satisfied, I reinserted the background, cloned the smaller one a few times, positioned the clones and saved. Then, I added a bunch of text to the back cover and formatted, positioned and saved the project. This time I printed it, and closed the program.

I was pleased with the proof copy, so I opened up the Save again. Images were gone, and stripes were back. I needed to get this card off in the morning, so I got rid of the stripes, pulled the images back in and repositioned everything. I added some clipart and text to the inside, saved and printed it. I was happy with the result. Time from start to finish (including searches, downloads and installs): around five hours... but it was a fun and satisfying five hours!

Lastly, a data update. I'm in a particularly unpleasant bit of data entry right now- absolutely no copying is possible. For all you AFOL's (Adult Fans Of Lego- and yes, that's a real acronym and definition) out there, I'm in the 3005 01x01x01 elements. The ones that have printing on them. I'm not sure exactly how many I did, but it seemed like a few hundred!

That's all for now- I have a card to mail!

As always, I am hochspeyer, blogging data analysis and management so you don't have to.