Simple Business Intelligence (BI) solution
This is a follow-up post for this one. I want to share a simple approach to implement BI in your company.
What is BI ?
It helps the company to aggregate data, clean-up data, and present data (provide a way for end-user to analyze and then take action upon it)
For example:
In retail company, we would want to know something like this
Seller A, on date B, visited store C, sold in product D with quantity E and cost F
How to do it ?
- Aggregate data
Overview
Transaction data will flow from POS system back to server in branches, then server in HQ and finally consolidated in national server.
Implementation for both link 1 and link 2 should depend on the infrastructure (most likely Internet connection) and capability
- for unstable Internet connection: use Offline method (Data will be extracted to some flat file, the file will be manually sent to HQ. In HQ it will be manually extracted and imported to the server)
- for stable Internet connection: use Online method (Data will be transfered directly from branches to HQ)
How to directly transfer ?
-
Database built-in tool (MSSQL DTS)
-
Dedicated solution (Pentaho)
- Clean-up data
-
Very important step (garbage in garbage out)
-
Make sure that master data (e.g. customer, product) is always up-to-date
-
Know what the expected output to filter the input
-
Know what the potential garbage/overhead to fix it
- Present data (provide a way for end-user to analyze the data)
-
End-user doesn't have knowledge to do SQL query. They prefer something simple (e.g. Excel sheet)
-
We can't put all the data into 1 output file (e.g. Excel 2003 and older have the limitation of 65536 rows)
Excel sheet approach
-
Pivot table is a powerful tool since it allows end-user to drag and drop the fields
-
Office 2010 has implemented powerful built-in BI tool (PowerPivot - based on OLAP)
Template approach
-
Ask key users the key metrics they want to see
-
Finalize the template that will be sent to them automatically from the system (push-approach. Let the users drag and drop pivot is pull-approach)
Key notes
TBA