Last week I mentioned that I was going to take a look at some interesting Unix utilities that you can also run on Windows by way of GnuWin (or some similar GNU on Windows library).

The first item that I want to look at is the “join” program. Join can perform operations very similar to a SQL join, except that it works with text files. Let’s say I have two text files, one named people, and one named colors. The contents of the people file is a unique ID followed by a name, and the colors file is everyone’s favorite color – where the columns are the person’s ID and then their favorite color.

Here’s the people file:

1 Jason 
2 Cameron 
3 Celvin 
4 Michael 
5 Opal 
6 Amy

Here’s the colors file:

1 Orange
2 Pink
3 Purple
4 Red
5 Blue
6 Hot Pink

As a very simple case, we can run join with just the names of these two files:

join people colors

And we’ll get this output:

1 Jason Orange
2 Cameron Pink
3 Celvin Purple
4 Michael Red
5 Opal Blue
6 Amy Hot Pink

In this case, the join program just assumed that the the first columns of each file were the same thing, and join accordingly. Again by default, the output was all of the columns from each file.

Let’s change things up and use a different people file, this time named people-small and containing fewer entries, like this:

1 Jason 
4 Michael 
5 Opal

Now if we run join with this file but the same colors file, like this:

join people-small colors

This will be our output:

1 Jason Orange
4 Michael Red
5 Opal Blue

If you come from the SQL world at all, you will notice that by default, join performed an inner join with respect to the data – that is, the three rows in the “left” data store (people-small) were paired up with the matching rows from the colors file, but the colors that didn’t match anything were just discarded. Similarly, if we run the full people file with a smaller colors file (named colors-small), we’ll see something similar. Consider this colors-small file:

2 Pink
3 Purple
6 Hot Pink

And running this command:

join people color-small

Then we’ll get this:

2 Cameron Pink
3 Celvin Purple
6 Amy Hot Pink

Again, an inner join was performed, in that only matched up rows (from one side or another) were paired up and put into the output.

Note that this behavior (assuming the first column is the ID to match, also, performing an inner join) is just the default behavior for join. As with most Unix tools, join has a plethora of command-line switches that define its behavior. You can choose different join methods, fields, and more.

The Hyperion Connection

I wanted to mention this tool specifically because I think for many of us working with ostensibly relational data (in text files), it can come in handy every now and then. I actually had a perfect use case for this the other day were I just wanted to quickly load some data but I didn’t want to mess with ODI to load it up or deal with importing it to a database. I had to sort the records in the input file first, but after that the join program worked like a charm and generated a file that I could easily use with a load rule to load up to a cube.

I came across a really useful tool the other day for generating sample relational data. It’s a web service called genereatedata.com (inventively enough). It lets you define the columns, data types, how to generate the data (such as random names, states, numbers, numbers in a range, and more), then generate the data. You can get data in a delimited form or even directly as INSERT statements for your database of choice. It looks like it generates 100 rows of your data for you for free, but that you need to pay for a premium service that is able to generate more rows, if you need it. Fortunately in my case I was good with 100 rows.

Of course, if you need to generate data for your cubes, you might want to use a tool designed specifically for doing so, such as the Cubedata tool I built some time ago (version 2 is in the works!).

That title is a bit of a mouthful, ain’t it?

I often see VB code in FDM jobs that is creating SQL statements – concatenating things together, adding in parameters from variables, lots of quotes, single quotes, plus signs, underscores, and more all over the place. It’s hideous.

For example, check some of this garbage out:

strSQL = "INSERT INTO " & TEMP & " (ENTITY,ACCOUNT,COSTCENTER,AMOUNT) " & _
" SELECT Entity.label, ACCOUNT.label, Entity.label+'_CC'", & Period & _
" from " & DATABASE & " as FACT"

For the record, I’m not saying if you see this or write this that you’re a bad developer. What I will say, however, is that you and/or your predecessors were hobbled by a clunky programming language and now you can atone for your syntactic sins with Python (Jython).

Python offers some nice facilities for handling strings. If you are looking for a straight translation of the normal VBScript string concatenation, you could do this:

account = "170100"
select = "SELECT Amount FROM Transactions WHERE Account = " + account

Python uses the plus operator (+) instead of the ampersand (&) to concatenate strings. This is straightforward and works well for simple situations. Of course, things get a little harder to read when we need to surround our value with single quotes (since it’s a SQL query after all):

select = "SELECT Amount FROM Transactions WHERE Account = '" + account + "'"

This isn’t terribly hard to read but we’re on a slippery slope. One thing we can try is simple “printf” style string interpolation:

select = "SELECT Amount FROM Transactions WHERE Account = '%s'" % account

In this case we have a %s placeholder meaning that we will place a string in it. Then we are using the modulo (%) operator to place the value of account into the string. This probably looks funny if you’re not familiar. What’s going on with this syntax?

Remember how you used the ampersand in VB to concatenate strings but in Python you use a normal plus? Well, the fact that we can use the plus in Python is due to operator overloading. This is programming term that essentially means we can actually program in our own behavior for math operations. You’re really used to this with integers (think 2 + 3 = 5), but maybe not so used to it for strings. It’s a clever trick and a nice facility that let’s us work with strings (or indeed, other types of objects) without having to resort to a lot of boilerplate code like newStr = StringUtilities.concatenate(query1, query2) or something similarly awkward.

Along those lines, whereas the modulo operator in math means “give me the remainder when dividing these two numbers” (such as 5 % 3 = 2), in Python this operator has been overloaded to provide convenient operations with strings (after all, how on earth would you ever perform modulo arithmetic on strings?). So when we write string1 % string2 (which is what we had above with account), Python calls the appropriate handler for the string object, which in this case is a fancy string replacement functionality.

This string replacement facility, as it turns out, is pretty handy – and this leads me to the final example of this post.

Python supports objects known as dictionaries. For those from the Java world, this is essentially the same as a Map (or perhaps more like a Map). VBScript has these objects too but they aren’t part of the language itself – they have a clunky syntax. In Python we can make a dictionary simply like this:

params = {
    "ACCOUNT" : "0170200",
    "PERIOD"  : 2
}

Next, Python also lets us create multi-line strings (and we don’t even need the line continuation operator from VB where the lines end with & _). We tell Python we have a multi-line string simply by using three quotes:

select = """
    SELECT 
        Amount
    FROM
        Transactions
    WHERE
        Account = '%(ACCOUNT)s'
        AND Period = %(PERIOD)d
""" 

Note that this string has been parameterized with ACCOUNT and PERIOD placeholders. The syntax is %(KEYNAME)s for strings and %(KEYNAME)d for integers (there are other codes as well but these are the most common).

Now we have a multi-line string with placeholders that we can replace with values from a dictionary. We could print this out easily:

print select % params

And we get this code:

SELECT 
    Amount
FROM
    Transactions
WHERE
    Account = '0170200'
    AND Period = 2   

This works exceptionally well for crafting SQL queries (also, yes, I know about placeholders with ? that are specific to SQL query parameters, let’s save that for another post). The wins here are that you get to format the query how you want, you don’t have quotes and ampersands and plus symbols all over the place, it’s easy to read, easy to maintain, and even faster to write.

Remember, code for readability. Also keep in mind that if you are converting VB to Python/Jython, you are not supposed to slavishly translate line by line – you are translating the solution as whole.