A Holistic Approach to Fix America's Broken Political System

Advanced SQL: Group Overlapping Events Using Analytic Window Functions

Problem: Group overlapping events

Given a list of events with start and end times, group them by their overlap. The problem is easier to understand with the following graph.

Solution 1: Use cross self-join

I found the following forums with a similar solution.

• https://stackoverflow.com/questions/2561130/merge-overlapping-date-intervals
• https://www.sqlservercentral.com/Forums/Topic826031-8-1.aspx

Here is my own implementation of the solution.
Even though the code is surprisingly short, it is very hard to understand or explain. I am intrigued to develop an alternative solution.

Solution 2: Use analytic window functions

Sort events by their start and end times. Then loop through the event sequence 3 times.

1. In the 1st loop, at each row (event), calculate the max value of its previous events' end times (prev_max_end). This can be done using a running total calculation, an analytic function with a window clause (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). Compare prev_max_end against the current row's start time. If the start time > prev_max_end, then the current event starts a new group. In other words, a group boundary is detected at the current row. In the above graph, the detection is positive at Event 4. Event 1 automatically starts a new group because its pre_max_end is NULL.
2. In the 2nd loop, at each row (event), count the running total of group boundaries. Again, magic of window clause (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Every event within the same start and end boundaries will have the same count. For example, events 4, 5 and 6 all get running totals of 2, because they all see the boundaries at events 1 and 4. The running totals, therefore, can be used as group IDs.
3. Finally in the 3rd loop, find min(start time) and max(end time) within each group as the group's start and end times.

Here is an implementation.
It is very easy to verify and understand the solution step-by-step on a demo execution, as shown below.

Performance comparison

In addition to easiness of understanding and explaining, solution 2 also has a much better performance. Solution 1 has 3 cross self joins, which result in O(N4) total row counts. On the other hand, solution 2 has no joins. It just loops through the same number of rows 3 times, O(N). On a 2000-events data set, solution 1 runs for 12 seconds, and solution 2 less than 1 second.

Advanced SQL: Find groups of students who take the same courses

How to group students who take the same courses using SQL? The trick is to assign a unique value to the combination of courses taken by a student, and then group students by their assigned values. There are 2 options for the assignment.

1. Assign every course a unique bit. For example, course A = 1 (0001), course B = 2 (0010), course C = 4 (0100), etc. Then, for all possible combinations of courses, their sum of the bits are unique.
2. Sort and concatenate course IDs.

Option 2's string concatenation is not SQL standard. Different RDBMS has different syntax. Option 1, on the other hand, can be implemented in standard SQL. Below is an example for option 1.

Query Neo4j using PowerShell and Bolt .Net driver

I failed to query Neo4j using PowerShell and the latest .Net driver (version 1.4.0) following the instructions from this blog post, which is based on version 1.0.2. The issue is that the new driver has changed its package dependencies. You need to import a different set of DLLs at the beginning of your PowerShell scripts, as shown in this code example.

OBIEE Hack: Cleanup column headings of direct SQL reports with a single mouse click

OBIEE automatically enclose column headings with double quotes if they contain spaces. This is totally unnecessary and outright annoying. Here is a bookmarklet that can do the dirty work for you: Cleanup Headings.

Drag and drop it to your browser's bookmark bar. On a direct SQL report's edit page, click it. It will do the following cleaning tasks to all column headings:

• Remove all double quotes.
• Replace all underscores with spaces.
• Uppercase first letters and lowercase remaining letters of all words, except for all CAPITAL words.

Browser compatibility: Google Chrome (yes), FireFox (yes), Opera (yes), IE (no).

Here is the JavaScript code:

OBIEE Hack: Repeat all columns of a direct SQL report with a single mouse click

By default, OBIEE suppresses all columns of a direct SQL report. In other words, adjacent rows with the same value are merged like this.

Suppressed column format is excellent for viewing with human eyes. This is probably why OBIEE makes it the default format. However, it is a nightmare for sorting and filtering when exported to Excel.

In order to set a column to repeat format, at least 3 mouse clicks at different locations are required. It is common for reports to have 10+ columns, which means total 60+ mouse moves and clicks. It sure feels like a chore.

Drag and drop this bookmarklet to your bookmark bar: Repeat All Columns.

Now repeating all columns is just a single mouse click (of the bookmarklet).

Browser compatibility: Google Chrome (yes), FireFox (yes), Opera (yes), IE (no).

Here is the JavaScript code:

OBIEE Hack: Save and Restore Column Formatting While Editing Direct SQL Reports

OBIEE's direct SQL reports query databases directly, bypassing its logical data model layer (RPD). This is a quick-and-dirty solution for ad-hoc reporting requests. However, there is an annoying issue with direct SQL reports. Every time you modify a report's SQL, you lose its column formatting. Reformatting the same reports again and again could be frustrating if your customers frequently ask for changes to their reports. I previously developed a client-server hack to preserve column formatting while editing direct SQL reports. This post is a pure client-side, simpler, and more elegant solution.

Just drag and drop this bookmarklet to your browser's bookmark bar: Direct SQL Wizard.

When you edit a report's SQL, click the bookmarklet. It will add 3 new buttons sandwiching the native "Validate SQL" button as shown in the following screenshot. The new buttons' function is self-evident.

Below is the bookmarklet's JavaScript code.
Browser compatibility: Google Chrome (yes), FireFox (yes), Opera (yes), IE (no).

OBIEE Hack: Preserve Column Formatting While Editing Direct SQL

Update: I posted a better solution here.

OBIEE's direct SQL reports query databases directly, bypassing its logical data model layer (RPD). This is a quick-and-dirty solution for ad hoc reporting requests. However, there is an annoying issue with direct SQL reports. Every time you modify a report's SQL, you lose its column formatting. Reformatting the same reports again and again could be frustrating if your customers frequently ask for changes to their reports. Here is a hack to preserve column formatting while editing direct SQL reports.

When you modify and validate a direct SQL report's SQL, OBIEE reset all columns to default settings. However, the reset happens only at client side, not on the server. In other words, the original formatting is still saved on the server. It is possible to retrieve the original formatting from the server, and merge it with the modified SQL from your local computer. The bookmarklet below grabs and posts the following information to a web service.

• OBIEE server host name
• Full path of the report on the server
• Modified and validated report in XML format to be saved on the server

The web service is where you do the merge. There are various technologies for implementing web services and manipulating XML. You can mix and match them based on what's already available in you company's technology stack.

A subtle difference between PowerShell ForEach and ForEach-Object

This blog post explains the major differences between PowerShell's ForEach statement and ForEach-Object cmdlet. There is a subtle difference, however, not mentioned in the post.

ForEach statement will not loop into a $null array variable, while ForEach-Object cmdlet will loop it once with a $null value. This may bite you with NullExceptions.

Here is a code snippet to demonstrate the issue.

OrientDB Completely Misses the Point of Graph Databases

What is the point of a graph database? It is all about relationships.

First of all (as explained here), relationships are treated as first-class data in a graph database, instead of second-class metadata in a relational database. Data is queryable or discoverable, while metadata is not. You have to learn metadata through other methods, such as taking training courses, reading documentations, reverse-engineering, or data profiling. The more complex a relational database, the more metadata you need to learn before querying for data. For companies with complex business domains, it is very time-consuming and expensive for employees to learn metadata. It is even more expensive to keep metadata-rich employees.

Secondly (as explained here), you need a true graph query language to take full advantage of relationship data. The old-fashioned SQL is just not designed for this purpose. SQL assumes that you already know relationships as metadata. So it expects you to explicitly spell them out in FROM clauses. If you are trying to use SQL to query/discover unknown relationships in a graph database, you are putting the cart before the donkey.

OrientDB is a self-claimed graph database. However, it completely misses the above 2 points. This is evident when it tries to compare itself against a true graph database, Neo4j.

Comparison Point	OrientDB	Neo4j
Complex Domains	Support schemas around graphs, i.e. vertex and edge hierarchies.	Only support "flat" labels for vertices and edges.
Query Language	OrientDB's query language is built on SQL. Considering most developers are familiar with SQL, working with OrientDB is just easier.	Neo4j has its own Query Language called "Cypher" which requires training to learn a new language.

What are "schemas around graphs?" They are METADATA! Neo4j's not supporting schemas is a conscious  strategic design decision, not a trade-off between simplicity or capability. Hierarchy is just a tree structure, a specific type of graph. No matter how complex a domain hierarchy is, a graph database can model it natively as data. There is absolutely no need to introduce metadata. Modeling domain hierarchies as metadata instead of data in a graph database is like putting the donkey before a car.

Cypher provides liberating expressiveness for querying graphs. Its advantages over SQL is at the same level as comparing OOP to procedure languages. Criticizing Cypher on the base of developers' familiarity with SQL is like a swordsmith running a negative ad against a gun shop. "Every knight knows how to use a sword. It requires training to use a gun."

A Graph is Worth a Thousand Words, Part 2

A Graph is Worth a Thousand Words, Part 1.

What are the things that a graph database can do, but a relational database can't? This is the burning question anyone from SQL world would ask when they first hear about graph database.

In part 1, I pointed out that a graph database allows you to discover/query complex relationships, while a relational database assumes you already know the relationships beforehand. This is mainly contributed to the fact that relationships are treated as first-class data in a graph database, and second-class metadata in a relational database.

There is another major reason why a graph database makes relationship discovery possible: its new graph-based query language. To discover relations implies that you don't know them in advance. Without knowing the relationships, you cannot write any SQL queries. You must explicitly spell out the relationships in a SQL query's FROM clause to join tables together. Using SQL to discover relationships is to put the cart before the donkey.

A graph query language, e.g. Cypher, allows you to query relationships without explicitly spelling out their specifics. This is equivalent to allowing wildcards in FROM clauses, such as "TABLE_A join *", or even "* join *". Not only the tables can be wildcarded, but also the join levels, like "A join (1..3) B" or "* join(*) *".

This makes graph databases extremely tool-friendly, or self-serving-friendly. The current generation of self-serving data discovery tools relies on predefined data models. You cannot point such a tool to an unknown relational database and start to discover. With a graph database and a Cypher-powered tool? Yes We Can!

A Graph is Worth a Thousand Words, Part 1

What are the real advantages of graph database, neo4j in particular, over relational database? In other words, what are the things that a graph database can do while a relational database can't. This is the burning question a developer/analyst asks when he/she makes the decision whether or not to pick up the new technology. Here is neo4j's own sales pitch.

Why does My Enterprise Need a Graph Database? 

Today’s CIOs and CTOs don’t just need to manage larger volumes of data – they need to generate insight from their existing data. In this case, the relationships between data points matter more than the individual points themselves.

In order to leverage data relationships, organizations need a database technology that stores relationship information as a first-class entity. That technology is a graph database.

Ironically, legacy relational database management systems (RDBMS) are poor at handling data relationships. Their rigid schemas make it difficult to add different connections or adapt to new business requirements.

Not only do graph databases effectively store data relationships; they’re also flexible when expanding a data model or conforming to changing business needs.

Obviously, it is targeted at CIOs and CTOs. If you are a developer or analyst from RDBMS/SQL world, and feel somewhat detached from the above sales pitch, below is my translation for you.

Graph database maximizes discoverability of information

A relational database captures the information of a business system as a mixture of data (entities) and metadata (relationships). Metadata is as important as data. It glues data together, and gives data meanings. Yet it is treated as a second-class citizen. You can discover information (through queries) only from data. You cannot ask questions (query) about metadata. Metadata must be obtained from other (usually costly) methods BEFORE you write queries. Here is a simple example to illustrate the point.

This is the universe of information (some movies, persons and their relationships) to be captured. There are 2 different designs. 

Both designs capture exactly the same amount information. The difference is that a portion of the information (relationship type) is captured as data in design #2, and metadata in design #1. The more information captured as data in design #2 allows more types of questions to be asked. For example, is Tom Hanks related in anyway to Finding Dory? In order to find the same information in design #1, the question has to be broken down to 3 smaller ones. 1) Is Tom Hanks an actor in the movie? 2) Is Tom Hanks a director of the movie? 3) Does Tom Hanks rate the movie? The breaking down of the question requires prior knowledge of the metadata. The information captured in metadata is the information lost discoverability. The more complex a business system, the more relationships among entities, the more information captured as metadata, the more discoverability lost.

A graph database, on the other hand, captures ALL information as data, including relationships. Therefore, it maximizes a business system's discoverability, no matter how complex it is. This is why a graph (database) is worth a thousand words (of metadata in RDBMS).

A Graph is Worth a Thousand Words, Part 2.

Tribal Wars 2 Building Manager

Introduction

As an (ex-)active Tribal Wars 2 (TW2) player with more than 50 villages, I felt the pain of leveling up buildings. Its "finish for free" feature is supposed to award players who are always online. You save 5 minutes here and 10 minutes there. The time savings quickly add up, and put you hours or even days ahead of less active players. However, you have to constantly loop through your villages to see which ones are in "finish for free" mode. Otherwise, you just miss the savings. It quickly becomes a chore when you have 30+ villages. I felt it more like a punishment than a reward.

Tribal Wars 2 Building Manager (TW2BM) comes to the rescue.

What is TW2BM?

TW2BM is a Google Spreadsheet, which color-codes your in-progress buildings' remaining times. Here is a screenshot of TW2BM.

The remaining time is adjusted for "finish for free" time based on headquarters levels, and counted down  every minute. The villages are sorted by their remaining times, so the ones need your attention are always at the top. There is no need to scroll up and down the sheet even if you have a lot of villages. After finishing a building, you update the village with next building's construction time. The remaining time is automatically re-calculated; and the villages are re-sorted.

The best part of TW2BM is that you don't have to manually setup the spreadsheet or type in any village information. All you need is a browser bookmarklet and, of course, a Google account. The spreadsheet is a private document in your own Google account. Nobody else can see it.

How to use TW2BM?

First, click this link to create a template Spreadsheet on your Google Drive: Create TW2BM Spreadsheet.

Then, drag and drop this bookmarklet to your browser's bookmark bar: TW2BM.

Open a village's Headquarters screen, and click the bookmarklet. If this is the first time you click it, it automatically creates the world sheet for you. It also sends the village name, the headquarters' level and the current building's remaining time to the spreadsheet. If the village has never been registered before, a new row is added. Otherwise, it updates the existing row. Then go to the next village.

Disclaimer

Use of TW2BM seems a violation of TW2's bot/script rule. Your account may be banned. Use it at your own risk. However, I strongly feel that using TW2BM is not cheating. It doesn't interact with the game server directly, or mimic your mouse clicking or key stroking. All it does is collecting and managing your game information. Besides, who can tell you are using TW2BM unless you tell someone?

OneSql PowerShell Module Fits All Relational Databases

OneSql is a PowerShell module providing a universal query interface to all relational databases, as long as there is a .NET data provider or an ODBC driver for the database. Supported databases include, but not limited to,

• SQL Server
• Oracle (provider)
• SQLite (provider)
• MySQL (provider)
• Postgresql (provider)
• DB2 (provider)

Here is the code on GitHub.

Date Calculation in Oracle and SQL Server

Oracle

In Oracle, the trick of date calculation is to align a date to its appropriate baseline with TRUNC() function, and then make adjustment by days and/or months. Here are some examples.

Date		Oracle
Week	First day (Sunday)	trunc(sysdate, 'd')
	First workkday (Monday)	trunc(sysdate, 'd') + 1
	Last workday (Friday)	trunc(sysdate, 'd') + 5
	Last day (Saturday)	trunc(sysdate, 'd') + 6
	Previous Sunday	trunc(sysdate, 'd') - 7
	Previous Monday	trunc(sysdate, 'd') - 6
	Previous Friday	trunc(sysdate, 'd') - 2
	Previous Saturday	trunc(sysdate, 'd') - 1
Month	First day	trunc(sysdate, 'mm')
	Last day	add_months(trunc(sysdate, 'mm'), 1) - 1
	Previous 1st	add_months(trunc(sysdate, 'mm'), -1)
	Previous last	trunc(sysdate, 'mm') - 1
Quarter	First day	trunc(sysdate, 'q')
	Last day	add_months(trunc(sysdate, 'q'), 3) - 1
	Previous 1st	add_months(trunc(sysdate, 'q'), -3)
	Previous last	trunc(sysdate, 'q') - 1
Year	First day	trunc(sysdate, 'y')
	Last day	add_months(trunc(sysdate, 'y'), 12) - 1
	Previous 1st	add_months(trunc(sysdate, 'y'), -12)
	Previous last	trunc(sysdate, 'y') - 1
Fiscal Year 7/1-6/30	First day	add_months(trunc(add_months(sysdate, -6), 'y'), 6)
	Last day	add_months(trunc(add_months(sysdate, 6), 'y'), 6) - 1
	Previous 1st	add_months(trunc(add_months(sysdate, -18), 'y'), 6)
	Previous last	add_months(trunc(add_months(sysdate, -6), 'y'), 6) - 1

SQL Server

In SQL Server, the basic strategy of date calculation is to compare a date against day 0 (01/01/1900, Monday). First, calculate the difference between day 0 and today at a particular unit (year, month, week, etc). Then add the difference back to day 0. You can adjust the difference and/or shift day 0 to reach the final result. Examples follow.

Date		T-SQL
Week	First day (Sunday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1)
	First workkday (Monday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 0)
	Last workday (Friday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 4)
	Last day (Saturday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 5)
	Previous Sunday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -8)
	Previous Monday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -7)
	Previous Friday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -3)
	Previous Saturday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2)
Month	First day	DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
	Last day	DATEADD(mm, DATEDIFF(mm, 0, getdate()) + 1, -1)
	Previous 1st	DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)
	Previous last	DATEADD(mm, DATEDIFF(mm, 0, getdate()), -1)
Quarter	First day	DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0)
	Last day	DATEADD(qq, DATEDIFF(qq, 0, getdate()) + 1, -1)
	Previous 1st	DATEADD(qq, DATEDIFF(qq, 0, getdate()) - 1, 0)
	Previous last	DATEADD(qq, DATEDIFF(qq, 0, getdate()), -1)
Year	First day	DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
	Last day	DATEADD(yy, DATEDIFF(yy, 0, getdate()), 364)
	Previous 1st	DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
	Previous last	DATEADD(yy, DATEDIFF(yy, 0, getdate()), -1)
Fiscal Year 7/1-6/30	First day	cast(datename(year, dateadd(month, -6, getdate())) + '0701' as datetime)
	Last day	cast(datename(year, dateadd(month, 6, getdate())) + '0630' as datetime)
	Previous 1st	cast(datename(year, dateadd(month, -18, getdate())) + '0701' as datetime)
	Previous last	cast(datename(year, dateadd(month, -6, getdate())) + '0630' as datetime)

OBIEE

In OBIEE, the calculation strategy is the same as SQL Server.

Date		OBIEE
Day	Today	CURRENT_DATE
	Yesterday	TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)
Week	First day (Sunday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
	First workkday (Monday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last workday (Friday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-05')
	Last day (Saturday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-06')
	Previous Sunday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-24')
	Previous Monday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-25')
	Previous Friday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-29')
	Previous Saturday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-30')
Month	First day	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-31')
	Previous 1st	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-01')
	Previous last	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Quarter	First day	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1900-03-31')
	Previous 1st	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1899-10-01')
	Previous last	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Year	First day	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1900-12-31')
	Previous 1st	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1899-01-01')
	Previous last	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Fiscal Year 7/1-6/30	First day	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6, CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
	Last day	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, 6, CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)
	Previous 1st	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -18, CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
	Previous last	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6, CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)