For those who joined the party late, this blog is about doing advanced analytics with free open source tools. And this month we are developing a completely free analytical solution for a hypothtical consumer finance company. You can get more details in the Open Source Analytics Category.

Today we shall design a Data Warehouse for the data.

In Consumer Finance Data Model - I, we described what AFS (our fictitious consumer finance organization) does, and the main pieces of information it deals with. To summarize, here are the pieces of data reproduced below:

    Loan Level (specified for each loan):

LoanID, Product (auto/Personal/etc.), Amount Finance, Asset Cost, Loan Duration, Interest Rate, EMI Amount, Disbursal Date, and No of Installments. Also, POS (Principal Outstanding), DelPOS, Bucket, and Late Fee.

    Customer Level (specified for each customer):

CustomerID, Name, Address, email, Age, Sex, Marital Status, Educational Qualification, Income, Years in Current Job, etc.

    Marketing Information:

CustomerID, PromoID (uniquely identifies the prmotion or marketing offer), PromoDate, Product, and ConversionFlag (True if customer took up the offer within one month, False otherwise).

Listing down the data elements in this manner gives us a direct insight what the basic tables in the DW would be ad also their structure. We now know that we need at least thee tables, namely: Loan_Table, Customer_Table, and Promo_Table. And you guessed it right, we will be doing this on MySQL, the most widely used open source database.

Homework:
1) Download and install MySQL Server and MySQL Query Browser from theMySQL website.
2) Open MySQL Query Browser (connected to the just installed server) and create the three tables above. Query Browser is an excellent GUI tool and it shouldn’t be tough to find your way around.

If you are getting stuck, and/or need more specific help, please drop a comment below and I’ll help you out.

Keep walking…