I have created a class to automatically create a closure table in Mondrian. Those who read the documentation know that they drastically improve the performance of parent-child hierarchies.
Since it's java code, you can cast it either from the command line, or from the javascript engine inside a Kettle tranformation or job. I prefer the former since it's easier to maintain.
PHP Code:
public class ClosureBuilder {
//static parameters for invoking the class from the command line
public static String JDBC = "com.mysql.jdbc.Driver";
public static String URL = "jdbc:mysql://server/schema?user=username&password=password";
public static String TABLE = "source_table";
public static String CLOSURE = "closure_table";
public static String KEY = "child_id";
public static String PARENT = "parent_id";
public static Object TOP_LEVEL = null; //value in parent_id that indicates a top level element.
private Object topLevel;
/**
* If can use this if you want to invoke the class from the command-line.
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
new ClosureBuilder().start(JDBC,URL,TABLE,CLOSURE,KEY,PARENT,TOP_LEVEL);
}
public void start(String jdbc, String url, String table, String closure, String key, String pkey, Object topLevel) throws Exception {
Class.forName(jdbc);
this.topLevel = topLevel;
Connection conn = DriverManager.getConnection(url);
conn.createStatement().execute("DELETE FROM " + closure);
String sql = "select " + key + ", " + pkey + " from " + table;
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
//populate table
HashMap<Object, Object> map = new HashMap<Object, Object>();
while (rs.next()) {
map.put(rs.getObject(1), rs.getObject(2));
}
pst = conn.prepareStatement("INSERT INTO " + closure + " VALUES (?,?,?)");
int count = 0;
//busca pais e distancia
for (Object current : map.keySet()) {
Map<Object,Integer> parents = new HashMap<Object,Integer>();
//add self as distance 0
parents.put(current, 0);
recurseParents(parents,map,current,1);
for (Object parent : parents.keySet()) {
pst.clearParameters();
pst.setObject(1, current);
pst.setObject(2, parent);
pst.setInt(3,parents.get(parent));
pst.addBatch();
count++;
}
System.out.println(count);
}
System.out.println("Executing batch...");
pst.executeBatch();
System.out.println("Done!");
}
private void recurseParents(Map<Object,Integer> parents, HashMap<Object, Object> map, Object key, int distance ) {
//catch infinite loop - change at will
if (distance > 20) throw new RuntimeException("infinite loop detected:" + key);
Object parent = map.get(key);
if (parent == null || parent == this.topLevel || parent.equals(this.topLevel)) {
return;
} else {
parents.put(parent,distance);
recurseParents(parents, map, parent, distance + 1);
return;
}
}
}
Creating closure tables
Reply With Quote
