Business Intelligence - Part 1 - Date/Time Dimensions, Table Design for periodic aggregate reports

Introduction

Often we end in scratching our head for writing SQLs for a report, where we finally end up writing few SQL in the loop and make a final report. There are age old techniques to achieve that, while we miss the view in doing that.

Sometimes single query could solve our issue in much faster approach than the queries in loop.

Going in search of knowledge of such options I have ended up in learning Data Warehouse and Business Intelligence. The primary approach would be to take baby steps one by one and to reach the destination.

Problem:

What we have:

It is a small store with sales data, what we have is just products, sales invoice and invoice items.

What we need:

Simple intelligence reports

1. Sales per day of the provided month
2. Sales per day of the provided week
3. Sales per quarter of the provided year
4. Sales per year overall
5. and more if possible

Solution

Introduce date and time dimensions

Add new dimension tables as above, these tables help to give more business related information like weekday name like Monday, Tuesday, or the quarter of the year Q1, Q2,Q3,Q4 etc.,

The date dimension should have the date_key as long value like 20091125 to map a 2009, November 25th.
Having this as a numeric field like long will help the joins to be faster. Each other table columns are expected to repeat the values in detail. year as 2009, month as 11, day_of_month as 25 etc. We could add more columns as much as needed to provide the business reports.

The time would have a fixed 24 x 60 entries of 1440. If in case we need a second based match, we may need to have 86400 records, but better to avoid second level reporting as it is not required for the store management.

Introduce dimension mapping columns

Add the dimension mapping columns in the invoice for the invoice date, which will have invoice_date_key and invoice_date_time_key.

Both would have numeric values like 20091125 and 1429.

Write the queries.

1. Sales per day of the provided month

SELECT dd.day_of_month, SUM(invoice.total_amount) FROM invoice
RIGHT OUTER JOIN dim_date  dd ON dd.date_key = invoice.invoice_date_key
AND invoice.invoice_date BETWEEN 'x' AND 'y'
GROUP BY dd.day_of_month
ORDER BY dd.day_of_month

The above would result something like
1 $100
2 $90
3 $2000
4 $1200
5 $600
etc...

1.1 Sales per day of the per month, for provided year

SELECT dd.month_short_name_en, dd.day_of_month, SUM(invoice.total_amount) FROM invoice
RIGHT OUTER JOIN dim_date  dd ON dd.date_key = invoice.invoice_date_key
AND invoice.invoice_date BETWEEN 'x' AND 'y'
GROUP BY dd.month, dd.day_of_month
ORDER BY dd.month, dd.day_of_month



The above would result something like
Jan 1 $100
Jan 2 $90
Jan 3 $2000
...

Feb 1 $110
Feb 2 $20
Feb 3 $1000

Feb 4 $1200
Feb 5 $600
etc...


The join with the dimension can be varied and more grouping and aggregation can be done to form variety of reports in a single query, which could impress the business.

Quick Setup of JBoss 7.1 with MySql Datasource

This post will help you to setup JBoss AS 7.1.x with MySql Datasource. 

As a precondition, we should have the following ready in our development environment
JDK 1.7
MySql
JBoss AS 7.x 

Run JBoss Server
Once downloaded and extracted JBoss AS 7.1, you can run the server by executing the <JBoss home>/bin/standalone batch (Windows) or shell(Linux) file, based on your OS. Make sure the server got started without any errors by checking the logs. You can also verify by visiting the url http://localhost:8080 in your browser, which shows you the JBoss home page.

Create User
We have to create users to access JBoss administration console. To add new users run add-<JBoss Home>/bin/add-user batch (Windows) or shell(Linux) file, based on your OS. This utility requires Realm, Username and Password. Releam is the name of the realm used to secure the management interfaces, by default it is 'ManagementRealm' so you can just press enter. Also enter Username and Password to complete the user creation. Now you can login using the created Username and Password, by clicking the Administration Console link from JBoss home page.

Setup Datasource
To setup MySql datasource we have to add MySql driver as a module and create the driver & datasource. Let us get into detail on how to do this. Download MySql Connector Java, jar file and place it in <JBoss Home>/modules/com/mysql/main directory. Create a xml file in the main directory named, module.xml and copy paste the following code in it,

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-5.1.18.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
    </dependencies>
</module>

Open <JBoss Home>/standalone/configuration/standalone.xml file to add the MySql driver and create datasource. Find the datasource subsystem(<subsystem xmlns="urn:jboss:domain:datasources:1.0">) node in the xml file, and add the following code under drivers node.

<driver name="mysql" module="com.mysql"/>

To create datasource, add a new datasource node under datasources node with your MySql database configurations in datasource subsystem. The following is a sample one.

<datasource jta="true" jndi-name="java:/name-of-the-data-source" pool-name="name-of-the-pool" enabled="true" use-java-context="true" use-ccm="true">
         <connection-url>jdbc:mysql://localhost:3306/db-name</connection-url>
         <driver>mysql</driver>
         <security>
             <user-name>root</user-name>
             <password>root</password>
         </security>
         <statement>
              <prepared-statement-cache-size>100</prepared-statement-cache-size>
              <share-prepared-statements>true</share-prepared-statements>
         </statement>
</datasource> 

Once you are done with the above, restart the server, login to admin console, and click Datasources to view the created datasource. You can use this datasource in your J2EE application to connect to MySql database.