Re: [Mifos-developer] GSOC Mifosx XBRL Reporting Integration

[Kojo Gambrah]

Hi Yanna, 

Thanks for your interest in mifosx. Your prior experience with XBRL will come in handy. In the mean time, I suggest you join the mailing list and get familiar with the project. 

Let me know once you get your build up and running. 

Welcome!

Kojo

On Wed, Apr 17, 2013 at 2:06 PM, Yanna Wu <wuyan...@gmail.com> wrote:

Hi Mr Gambrah,

My name is Yanna. I'm a master student at CMU. I learned the Mifosx project from GSOC. I'm very interested in this idea of XBRL reporting integration you mentor because I am both interested in programming and finance. I am familiar with XBRL taxonomy because I did my graduation project on visualization of XBRL data (check the project here). I also have experiences with HTML, XML,Java and SQL. So I think I can handle this task. I'd like to hear about the first few things that I can contribute to the project. Also, what kind of proposals are you expected?

Best regards,

Yanna

[Yanna Wu]

Hi Mr Gambrah,

I have joined the mailing list and set up my local git and deployed mifosx server on my machine. By the way, it's my first time to use Gradle. I'm impressed by its convenience.

Now I'm getting familiar with the code. I'd like to know the further progress on XBRL integration. Thank you.

Best regards,

Yanna

2013/4/18 Kojo Gambrah <kgam...@gmail.com>

[Kojo Gambrah]

Hi, 

The data warehouse is a layer of abstraction for reporting. A data warehouse also makes it easier to do historical reporting without changes to the application. I find that when the main application is burdened with complex and changing reporting requirements, it becomes harder to understand and change. 

Consider that a a change is made that breaks the query for a client name. Assuming several reports use this query, all of them will be broken and will have to be fixed individually. On the other hand, when you have a data warehouse, you only need to fix it in one place since the DW will continue to present the same interface to all the reports. 

Yes the Mifos BI project did this for the legacy Mifos application. Yes the ETL will be similar.  

- Kojo

PS: Mr. Gambrah sound quite awkward. :)  Kojo will do. 

On Sat, Apr 20, 2013 at 4:40 PM, Yanna Wu <wuyan...@gmail.com> wrote:

Hi Mr Gambrah,

I'm trying to get everything clear. I'd like to know what kind of data will be loaded into MifosX data warehouse, why can't we just use the Mifos data. Also, I can see that there are ETL scripts in Mifos BI projects. Is the ETL we need to write similar to those?

Thanks

2013/4/20 Kojo Gambrah <kgam...@gmail.com>

Hi Yanna, 

It seems you've had a good start. I just took a second look at the XBRL project and it assumes we have the business intelligence layer (which was true for mifos). A first step will be to write the ETL (extract, transform, load) scripts to load data into a data warehouse for MifosX. Since there are several reporting needs (including analytics) a data warehouse is a good investment to make implementing the specific reports easier. 

I'd suggest the following as next steps:

1. download pentaho kettle - http://community.pentaho.com/

2. read up on ETL script & data warehouse development

- Kojo

[Yanna Wu]

Hi Kojo,

Thank you for the explanation. But I still have several questions. 

1) The Mifos BI project does have a DW already, so What's the difference between Mifos and MifosX in reporting? As you said DW is an abstraction layer for reporting, so it does not need to be totally replaced even if the reporting requirements changed. So can we use the DW in Mifos BI to generate XBRL?

2) I can see that most data of MifosX are stored in mifostenant-default db. Is it the db that we will extract, transform and load into the DW? If yes, is there any description of the fields in each table, so that I can check what we have in db to generate an XBRL report.

3) I'm now learning the pentaho kettle. Is there any ETL job I can do now to test my learning?

Best regards,

Yanna

2013/4/21 Kojo Gambrah <kgam...@gmail.com>

[Kojo Gambrah]

Hi Yanna, 

I had some discussion with John Woodlock who implemented the data warehouse for Mifos. The conclusion was that since we can build the XBRL reports off the operational databas so lumping together with the data warehouse effort is not a good idea. We want to keep it simple. 

The target consumer of the XBRL reports is MixMarket. MIX is working on a reduced set of the MIX XBRL taxonomy but the current taxonomy should help you develop a task break down for the project.

We are looking forward to receiving your application. 

Regards,

Kojo

On Sun, Apr 21, 2013 at 8:23 PM, Kojo Gambrah <kgam...@gmail.com> wrote:

Hi Yanna,

1. True. We should be able to fix the ETL scripts to work for MifosX and maintain the structure of the data warehouse. Since MifosX is not yet fully featured, I expect that we might not be able to obtain 100% parity. 

2. There will be data warehouse per tenant so the db to look at is the  mifostenant-default. 
see -  https://mifosforge.jira.com/wiki/display/MIFOSX/Mifos+X+Database

3. You can attempt writing a script for creating the client dimension. That is basically a de-normalized(flattened) structure of all details relating to a client. 

Thanks,

Kojo

[Kojo Gambrah]

Hi, 

Sorry for my delayed response. Considering the XBRL reporting on it's own, using Pentaho Kettle might be a bit of an overkill. I would rather stick with a java xml library to build the report. This way, other developers don't have to pick up a new tool in order to maintain the report. 

Your task break down looks good. Looking at the current XBRL taxonomy, some of the data is not captured in mifosx so a couple of forms will have to be built to capture such data. 

Thanks,

Kojo

On Thu, Apr 25, 2013 at 4:22 AM, Yanna Wu <wuyan...@gmail.com> wrote:

Hi Kojo,

I'm getting familiar with the ETL process on Pentaho Kettle these days. I managed to load data from several tables like m_client, m_group_client, m_client_identifier, m_loan into the denormalized client dimension table. During my learning, I can feel that Pentaho Kettle is a very strong tool in data integration, so even if we are not using data warehouse, we can still use Pentaho to output XML. That's just my suggestions. I'd like to hear your plans of building XBRL reports.

I am working on my application for GSOC, and I'd like to get an outline of what I will do for this project. Here I divide the idea into several sub tasks. 

1. identify the taxonomy subset that is required to generate xbrl report based on the existing db

2. generate XBRL instance with Pentaho

3. provide XBRL report service to frontend

4. implement the XBRL reporting user interface in community apps

Please tell me if I am generally right about the whole progress of this project. Also please tell me if there's anything I'm missing.

Best regards,

Yanna

2013/4/25 Kojo Gambrah <kgam...@gmail.com>