The star schema represents data as a table of facts (measurable values) that are associated with the various dimensions of the fact. Common dimensions include time, geography, organization, product and the like. I'm working with some folks whose facts are a bunch of medical test results, and the dimensions are patient, date, and a facility in which the tests were performed. I got an email with the following situation: "a client who is processing gigs of incoming fact data each day and they use a host of C/C++, Perl, mainframe and other tools for their incoming fact processing and I've seriously considered pushing Python in their organization.". Here are my thoughts on using Python for data warehousing when you've got Gb of data daily. Small Dimensions The pure Python approach only works when your dimension will comfortably fit into memory -- not a terribly big problem with most dimensions. Specifically, it doesn't work well for those dimensions which are so huge that the dimensional model becomes a snowflake instead of a simple star. When dealing with a large number of individuals (public utilities, banks, medical management, etc.) the "customer" (or "patient") dimension gets too big to fit into memory. Special bridge-table techniques must be used. I don't think Python would be perfect for this, since this involves slogging through a lot of data one record at a time. However, Python is considerably faster than PL/SQL. I don't know how it compares with Perl. Any programming language will be faster than any SQL procedure, because there's no RDBMS overhead. For all small dimensions. Load the dimension values from the RDBMS into a dict with a single query. Read all source data records (ideally from a flat file); conform the dimension, tracking changes; write a result record with the dimension FK information to a flat file. Iterate through the dimension dictionary and persist the dimension changes. The details vary with the Slowly Changing Dimension (SCD) rules you're using. The conformance algorithm is is essentially the following: row= Dimension(...) ident= ( row.field, row.field, row.field, ... ) dimension.setdefault( ident, row ) In some cases (like the Django ORM) this is called the get-or-create query. The Dimension Bus For BIG dimensions, I think you still have to implement the "dimension bus" outlined in The Data Warehouse Toolkit. To do this in Python, you should probably design things to look something like the following. For any big dimensions. Use an external sort-merge utility. Seriously. They're way fast for data sets too large to fit into memory. Use CSV format files and the resulting program is very tidy. The outline is as follows: First, sort the source data file into order by the identifying fields of the big dimension (customer number, patient number, whatever). Second, query the big dimension into a data file and sort it into the same order as the source file. (Using the SQL ORDER BY may be slower than an external sort; only measurements can tell which is faster.) Third, do a "match merge" to locate the differences between the dimension and the source. Don't use a utility like diff, it's too slow. This is a simple key matching between two files. The match-merge loop looks something like this. src= sourceFile.next() dim= dimensionFile.next() try: while True: src_key = ( src['field'], src['field'], ... ) dim_key= ( dim['field'], dim['field'], ... ) if src_key < dim_key: # missing some dimension values update_dimension( src ) src= sourceFile.next() elif dim_key < src_key: # extra dimension values dim= dimensionFile.next() else: # src and dim keys match # check non-key attributes for dimension change. src= sourceFile.next() except StopIteration, e: # if source is at end-of-file, that's good, we're done. # if dim is at end of file, all remaining src rows are dimension updates. for src in sourceFile: update_dimension( src ) At the end of this pass, you'll accumulate a file of customer dimension adds and changes, which is then persisted into the actual customer dimension in the database. This pass will also write new source records with the customer FK. You can also handle demographic or bridge tables at this time, too. Fact Loading The first step in DW loading is dimensional conformance. With a little cleverness the above processing can all be done in parallel, hogging a lot of CPU time. To do this in parallel, each conformance algorithm forms part of a large OS-level pipeline. The source file must be reformatted to leave empty columns for each dimension's FK reference. Each conformance process reads in the source file and writes out the same format file with one dimension FK filled in. If all of these conformance algorithms form a simple OS pipe, they all run in parallel. It looks something like this. src2cvs source | conform1 | conform2 | conform3 | load At the end, you use the RDBMS's bulk loader (or write your own in Python, it's easy) to pick the actual fact values and the dimension FK's out of the source records that are fully populated with all dimension FK's and load these into the fact table. I've written conformance processing in Java (which is faster than Python) and had to give up on SQL-based conformance for large dimensions. Instead, we did the above flat-file algorithm to merge large dimensions. The killer isn't the language speed, it's the RDBMS overheads. Once you're out of the database, things blaze. Indeed, products like the syncsort data sort can do portions of the dimension conformance at amazing speeds for large datasets. Hand Wringing "But," the hand-wringers say, "aren't you defeating the value of the RDBMS by working outside it?" The answer is NO. We're not doing incremental, transactional processing here. There aren't multiple update transactions in a warehouse. There are queries and there are bulk loads. Doing the prep-work for a bulk load outside the database is simply more efficient. We don't need locks, rollback segments, memory management, threading, concurrency, ACID rules or anything. We just need to match-merge the large dimension and the incoming facts.

WSDL is a version of XML used to better work with web severs. In this post, we'll learn how to better use it alongside the Java language.

The current Java Persistence API (JPA) standard does not mandate JPA provider to support data type conversions through annotations, not even a with simple boolean field. For readers who are unfamiliar with JPA, what I mean is, to persist a boolean field, JPA expects the database data type to be integer, where value of "1" means true, and value of "0" means false. We simply just can't annotate the boolean field, specifying our own boolean field value, such as "True/False", "T/F", "Yes/No", "Y/N", "-1/0" and then let the JPA provider to convert those boolean field on the fly. As an example, I am expecting JPA will allow me to annotate a boolean field @boolean(trueValue="Yes", falseValue="No") private boolean enabled; For me, this is a very annoying limitation, espeically when you have to deliver applications on an existing database with hundreds of tables. After some research, I decided to create my own Java Annotation Processing Tool (APT) Compile Time Annotation, called @BooleanMagic, with a compile time Java APT preprocessing factory, which will automatically generate additional code to work around the issue. I've also made some changes on the original code: Fixed the bug of Null pointer exception, when JPA return null on the annotated field. Introduce a new properties call ifNull, which allows user to configure what to return if JPA returns null, it expect enum of org.jbpcc.util.jpa.ReturnType, which have values of ReturnType.True, ReturnType.FALSE, and ReturnType.Null,. The default value of ifNull is ReturnType.Null So here is an example, assuming we have model class defined as below: package org.jbpcc.domain.model; import javax.persistence.Entity; import javax.persistence.Id; import org.jbpcc.util.jpa.BooleanMagic; import org.jbpcc.util.jpa.BooleanMagic.ReturnType; @Entitypublic class SomeVO { @Id private Integer id; @BooleanMagic(trueValue = "Yes", falseValue = "No", columnName = "OVERDUED", ifNull = ReturnType.FALSE) private transient Boolean overdued; public Boolean isOverdued() { return overdued; } public void setOverdued(Boolean overdued) { this.overdued = overdued; } } Using Java APT with JPABooleanMagicConverter factory, the code above will be now be converted to: @Entitypublic class SomeVO { @Id private Integer id; private transient Boolean overdued; //--- Lines below are generated by JBPCC BooleanMagicConvertor PROCESSOR //--- START : @Column(name="OVERDUED") private String magicBooleanOverdued; public Boolean isOverdued() { if (this.magicBooleanOverdued == null) return false; return this.magicBooleanOverdued.equals("Yes") ? Boolean.TRUE : Boolean.FALSE; } public Boolean getOverdued() { if (this.magicBooleanOverdued == null) return false; return this.magicBooleanOverdued.equals("Yes") ? Boolean.TRUE : Boolean.FALSE; } public void setOverdued(Boolean trueFlag) { this.magicBooleanOverdued = trueFlag ? "Yes" : "No"; } //--- END //--- GENERATED BY JBPCC BooleanMagicConvertor PROCESSOR } I have put the annotation with it compile time process factory under my open source project - Java Batch Process control center, at http://code.google.com/p/jbpcc, I also posted an article at my blog detailing the usage of the annotation. I hope some readers will find the annotation and the APT preprocessing factory useful. Do share your thoughts and suggestions.

This turns a class name (like LineItem) into a nice string (like "line item") line_item = LineItem.new puts line_item.class.name.underscore.humanize.lowcase #spits out "line item"

# Array#to_ranges # Converts an array of values (which must respond to #succ) to an array of ranges. For example, # [3,4,5,1,6,9,8].to_ranges => [1,3..6,8..9] class Array def to_ranges array = self.compact.uniq.sort ranges = [] if !array.empty? # Initialize the left and right endpoints of the range left, right = self.first, nil array.each do |obj| # If the right endpoint is set and obj is not equal to right's successor # then we need to create a range. if right && obj != right.succ ranges << Range.new(left,right) left = obj end right = obj end ranges << Range.new(left,right) end ranges end end

using System; using System.Collections.Generic; using System.IO.Compression; using System.IO; using System.Collections; namespace Utilities { class Compression { public static byte[] Compress(byte[] data) { MemoryStream ms = new MemoryStream(); DeflateStream ds = new DeflateStream(ms, CompressionMode.Compress); ds.Write(data, 0, data.Length); ds.Flush(); ds.Close(); return ms.ToArray(); } public static byte[] Decompress(byte[] data) { const int BUFFER_SIZE = 256; byte[] tempArray = new byte[BUFFER_SIZE]; List tempList = new List(); int count = 0, length = 0; MemoryStream ms = new MemoryStream(data); DeflateStream ds = new DeflateStream(ms, CompressionMode.Decompress); while ((count = ds.Read(tempArray, 0, BUFFER_SIZE)) > 0) { if (count == BUFFER_SIZE) { tempList.Add(tempArray); tempArray = new byte[BUFFER_SIZE]; } else { byte[] temp = new byte[count]; Array.Copy(tempArray, 0, temp, 0, count); tempList.Add(temp); } length += count; } byte[] retVal = new byte[length]; count = 0; foreach (byte[] temp in tempList) { Array.Copy(temp, 0, retVal, count, temp.Length); count += temp.Length; } return retVal; } } }

SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); Date date = sdf.parse(strDate); Calendar cal = Calendar.getInstance(); cal.setTime(date);

A simple time based cache build around a map store. import java.util.Map; import java.util.WeakHashMap; /** * Simple time-based cache. */ public class SimpleCache { private long maxAge; private Map store; /** * Instanciate a cache with max age of 1 hour and a WeakHashMap as store. * @see java.util.WeakHashMap */ public SimpleCache() { this.maxAge = 1000 * 60 * 60; this.store = new WeakHashMap(); } /** * @param maxAge maximum age of an entry in milliseconds * @param store map to hold entries */ public SimpleCache(long maxAge, Map store) { this.maxAge = maxAge; this.store = store; } /** * Cache an object. * @param key unique identifier to retrieve object * @param value object to cache */ public void put(Object key, Object value) { store.put(key, new Item(value)); } /** * Fetch an object. * @param key unique identifier to retrieve object * @return an object or null in case it isn't stored or it expired */ public Object get(Object key) { Item item = getItem(key); return item == null ? null : item.payload; } /** * Fetch an object or store and return output of callback. * @param key unique identifier to retrieve object * @param block code executed when object not in cache * @return an object */ public synchronized Object get(Object key, Callback block) { Item item = getItem(key); if (item == null) { Object value = block.execute(); item = new Item(value); store.put(key, item); } return item.payload; } /** * Remove an object from cache. * @param key unique identifier to retrieve object */ public void remove(Object key) { store.remove(key); } /** * Get an item, if it expired remove it from cache and return null. * @param key unique identifier to retrieve object * @return an item or null */ private Item getItem(Object key) { Item item = (Item) store.get(key); if (item == null) { return null; } if (System.currentTimeMillis() - item.birth > maxAge) { store.remove(key); return null; } return item; } /** * Value container. */ private static class Item { long birth; Object payload; Item(Object payload) { this.birth = System.currentTimeMillis(); this.payload = payload; } } /** * A visitor interface. */ public static interface Callback { Object execute(); } } And a couple of junit tests: import java.util.HashMap; import junit.framework.TestCase; public class SimpleCacheTest extends TestCase { public void testPutGet () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); c.put("key1", "value1"); assertEquals("value1", c.get("key1")); c.put("key1", "value1.0"); assertEquals("value1.0", c.get("key1")); c.put("key2", "value2"); assertEquals("value2", c.get("key2")); assertEquals("value1.0", c.get("key1")); } public void testMaxAge () throws InterruptedException { SimpleCache c = new SimpleCache(1000, new HashMap()); c.put("key1", "value1"); assertEquals("value1", c.get("key1")); Thread.sleep(1500); assertNull(c.get("key1")); c.put("key2", "value2"); Thread.sleep(750); c.put("key3", "value3"); Thread.sleep(750); assertNull(c.get("key2")); assertNotNull(c.get("key3")); Thread.sleep(750); assertNull(c.get("key3")); } public void testRemove () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); c.remove("key"); assertNull(c.get("key")); c.put("key", "value"); assertNotNull(c.get("key")); c.remove("key"); assertNull(c.get("key")); } public void testCallBack () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); assertEquals("value1", c.get("key1", new SimpleCache.Callback() { public Object execute() { return "value1"; } })); assertEquals("value1", c.get("key1")); // again with a new callback (value) c.get("key1", new SimpleCache.Callback() { public Object execute() { return "value2"; } }); assertEquals("value1", c.get("key1")); } }

class Array def swap!(a,b) self[a], self[b] = self[b], self[a] self end end You can now do stuff like.. [1,2,3,4].swap!(2,3) # = [1,2,4,3] etc.. Many thanks to Sam Stephenson and technoweenie for their suggestions.