[Mifos-users] HELP: Pentaho reports on Mifos

[Dale Fuertes]

21 July 2012

Hi everyone,

I am using Mifos 2.4.1 and Pentaho BI 1.2 Windows. I input in Mifos some branch, centers, loan officers, groups, clients, loans, savings data, and applied PPI survey to try first. Done these in the past couple of days. Encountered some problems below on Pentaho reports. 

I processed the daily reports first and checked on Pentaho after. Reports below have problems:  

--Balance Outstanding --> No data shown

--Balance Outstanding by Source of Funds ---> No data shown

--Center Schedule by Loan Officer --> No data shown

--Client Summary -->For PDF: Failed to load; For Excel: Error 6 (net::ERR_FILE_NOT_FOUND): The file or directory could not be found.

--Loan Classification by Product --> Shows the products, but all "0" data 

--Loan Officer Detailed --> No data shown

--Loan Officer Performance Summary (Cumulative & During Period) -->All "0" data and Name of loan officers not shown

--MFI Progress --> Shows data on Number of branches existing, all others are "0"

--Mifos Transactions (Detail & Summary) --> Shows only transactions made on group and group members, none on individual clients

--PPI Client Export - Shows: Found error: [object XMLHttpRequest] -parsererror, Error: TypeError: Cannot read property 'documentElement' of null

I hope there are ways to fix these. Any help or direction will be appreciated. Thanks so much.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

[Nayan Ambali]

Hi Dale,

When you open the report, along with input params you see one more info like "Data for this report last updated on 2012-07-21 at 04:01:08" . Is that info showing some Date or any error message ? 

Thanks

Nayan Ambali

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Mifos-users mailing list
Mifos...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users

--
Regards
Nayan G. A.
9901154134

[Dale Fuertes]

21 July 2012

Hi Nayan,

Yes, I just noticed. It shows at the right corner --> Data for this report last updated on ERROR

I just checked now if there were issues before on this type of Error. Found these:

http://comments.gmane.org/gmane.comp.finance.mifos.devel/12948 (mentioned here by John Woodlock: usually (but not always) as a result of data migration onto mifos or moving system dates back and forth --> However, I didn't do any migration and didn't move system dates.)

http://comments.gmane.org/gmane.comp.finance.mifos.user/3328  (Based on this, I am attaching the Mifos Database backup file in case you may need.)

Please advice what to do. And possibly how can this be prevented in the future. Thanks so much.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: Nayan Ambali <nayan....@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>; A good place to start for users or folks new to Mifos. <mifos...@lists.sourceforge.net>
Sent: Saturday, July 21, 2012 3:11 PM
Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos

[John Woodlock]

Dale,

I'll run your backup thru the ETL (which produces the
datawarehouse)... prob on my Monday.

It doesn't cover all the probs you identify... because some are not to
do with the datawarehouse but its a start

John

[Dale Fuertes]

21 July 2012

Hi John,

The previous days I ran it manually by cd'ing into the pentahos_mifosbi_1_2_windows folder and entering: initialise_datawarehouse.bat (https://mifosforge.jira.com/wiki/display/MIFOS/Windows+Mifos+BI+1.2+%28Pentaho%29+Installation+Guide). But I also scheduled it in the Windows Task Scheduler for daily processing. Reports generated are those mentioned with problems. (OBSERVATION- in the 

ETL Task Schedule note in Last Run Result, it shows--> (0x1). While other Windows tasks scheduled have notes shown as -->The operation completed successfully (0x0). 

I tried to run the ETL manually to check like you've said--> has been ~24hrs already, still running. It takes so long with such small trial data I input (around 7 clients only, <10 loans made, <10 savings). How long does it typically take? Please give example for certain # of clients, daily transactions, and hardware if you can. For our cooperative, now just only 500 clients. I wonder how long daily processing will take.

Will try to give update once ETL is done. Looking forward to the result of the Run you will made on the backup. Thanks so much.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>; mifos...@lists.sourceforge.net
Sent: Saturday, July 21, 2012 5:17 PM

[John Woodlock]

Dale,

can you run another backup as the one you sent had a backup per table? Just do

mysqldump -uUSERNAME -pPASSWORD DATABASENAME > john.sql


The data size you are talking about isn't a problem and should run
through in about a minute or a few minutes max.

As you are on Mifos 2.4.1 you need a later version of the Mifos BI
(i.e. 1.3.1) as there was a field name change after Mifos BI 1.2.
I'll give you instructions for upgrading once I've run through your
backup.


John

[Dale Fuertes]

23 July 2012

Hi John,

Here is the backup file (zip) and log file. Earlier was not zipped, just knew of the 750kb limit. 

Thanks so much.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>

Cc: mifos...@lists.sourceforge.net
Sent: Monday, July 23, 2012 11:29 AM

[John Woodlock]

Dale,

I applied the sql script (attached) to your data and the ETL ran and I
could see data for all the reports (however, sometimes I had to use a
future date to pick up data e.g. center schedule by loan officer).

At a command prompt you type

mysql -uUSERNAME -pPASSWORD mifos < dalefix.sql

or you can copy the lines from the attached file and run it in any sql
GUI you have.

The problem was the use of single quotes in some of the office names
(i.e. its a bug). However, if you can live without single quotes in
the office name the attached sql fix is all you need to do (it
translates single quotes for `)



As I said previously, I use Mifos BI 1.3.1 and I think you should (and
probably have to) too.

You can get the latest Mifos BI release here (1.3.1):
Mifos BI https://ci.mifos.org/hudson/job/bi-1.3.x-archive/lastSuccessfulBuild/artifact/mifos_bi-1.3.1.zip

There's not a single install for mifos BI 1.3.1 but since you have
already installed Mifos BI 1.2 you can upgrade just by unzipping the
downloaded zip and following these instructions.

1/ In your biserver-ce\pentaho-solutions delete directories
commonActions, Dashboards, DashboardsMenu, dataAccess, standardReports

2/ from unzipped directory mifos_bi-1.3.1\reports copy directories
commonActions, Dashboards, DashboardsMenu, dataAccess, standardReports
into you biserver-ce\pentaho-solutions

3/ from unzipped directory mifos_bi-1.3.1\reports\CDFReportingTemplate
copy *.html into your
biserver-ce\pentaho-solutions\system\pentaho-cdf

4/ from unzipped directory mifos_bi-1.3.1\reports\CDFReportingTemplate
copy *.jpg into your
biserver-ce\pentaho-solutions\system\pentaho-cdf\resources\style\images

5/ in your biserver-ce\tomcat\webapps\pentaho\WEB-INF\classes
delete directory org

6/ from unzipped directory mifos_bi-1.3.1\reports\i18n copy
directory org into your
biserver-ce\tomcat\webapps\pentaho\WEB-INF\classes

7/ in pentaho server, do a Tools/Refresh/Repository Cache before
testing any reports


It's a hassle copying and deleting files but hopefully you'll be fine.
If not, or if there are still issues with your reporting, let me know
and I'll send you back my skype details or some other way that I can
share the screen with you.


John

[Dale Fuertes]

23 July 2012

Hi John,

Thanks for taking time help. Below are the updates:

I applied the following: 

    Single quotes removed in branch office names 

    Applied sql script

    Ran ETL in Task Scheduler (completed between 30-60mins)

    Upgrade from BI 1.2 --> BI 1.3.1 

    Refreshed repository cache in Pentaho before generating reports

Results: (Problems reported before ---> Results now after applying above)

--Balance Outstanding (Shows :Data on this report last updated ERROR) --> No data shown --> Same

--Balance Outstanding by Source of Funds (Shows: Data on this report last updated ERROR) ---> No data shown --> Same

--Center Schedule by Loan Officer --> No data shown --> Shows only for Loan Officer with group/client (Is that expected? Other loan officers with schedule but no clients, these not shown)

--Client Summary -->For PDF: Failed to load; For Excel: Error 6 (net::ERR_FILE_NOT_FOUND): The file or directory could not be found. --> OK NOW

--Due Vs Collected by Branch/Center/Loan Officer (Shows :Data on this report last updated ERROR)--> No data shown

--Source of Funds  (Shows :Data on this report last updated ERROR) ---> No data shown

--Loan Classification by Product --> Shows the products, but all "0" data --> Same

--Loan Officer Detailed (Shows: Data on this report last updated ERROR)  --> No data shown --> Same

--Loan Officer Performance Summary (Cumulative & During Period) -->All "0" data and Name of loan officers not shown --> Same

--MFI Progress --> Shows data on Number of branches existing, all others are "0"--> Same

--Mifos Transactions (Detail & Summary) --> Shows only transactions made on group and group members, none on individual clients ---> OK NOW

--PPI Client Export - Shows: Found error: [object XMLHttpRequest] -parsererror, Error: TypeError: Cannot read property 'documentElement' of null---> Same

OBSERVATIONS:

1. When tomcat loads when pentaho is started, it shows lines with 'Warning: Running an XSLT 1.0 stylesheet with an XSLT 2.0 processor' 

2. Whenever I ran a report with note 'Data on this report last updated ERROR', tomcat shows "Invalid chunk ignored'. 

I do not know if above observations will be helpful, I attached the tomcat log for today.

At least some reports are ok now. Please advice what can be done for others. I'll try to follow. Thanks so much

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>; mifos...@lists.sourceforge.net

Sent: Monday, July 23, 2012 1:32 PM

Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos

[John Woodlock]

Dale,

There's probably a couple of issues here but first

1. run the ETL again now that you have upgraded to 1.3.1 - it produces
a log file output in the same directory. Can you send that log and a
zip of the database again... directly to me (not the list).

I assume you run it like:
initialise_datawarehouse.bat mifos_dw

It takes 2 mins on my laptop. How much memory have you got on your
machine... 1Gb?

The XSLT warning message doesn't matter and neither does the chunk
msgs (I get them too)



John

On Mon, Jul 23, 2012 at 8:20 PM, Dale Fuertes <dale_f...@yahoo.com> wrote:
> 23 July 2012
>
>
> Hi John,
>

[Dale Fuertes]

23 July 2012

Hi John,

Please see attached the backup and log file. 

Thanks so much.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>

Cc: mifos...@lists.sourceforge.net
Sent: Monday, July 23, 2012 11:29 AM

[Dale Fuertes]

10 August 2012

Hi John,

Thanks again for the help on Pentaho reports. The problem on "Data last updated on ERROR" was solved in my trial database in laptop.

Just 2 days ago that I have installed in our coop PC both Mifos 2.4.1 and Pentaho BI 1.3. I am currently in the process of manual entry of clients and accounts starting January 2012 (PC was backdated before installation and still is since I am still on January 2012 transactions). So far no problem in reports generation the other night. However, last night the same problem on "Data last...ERROR" came up on similar problematic reports like before in my laptop. Please see attached the mifos_dw log file and database dump file.

Note: Yesterday, after creating a client profile, upon submission I encountered a warning that similar item ..{1}.. already exist. I checked and found that Government IDs were similar for 2 clients created, typo error. However, this cannot be edited anymore. I just continued creating other client accounts. This was the only error encountered in mifos since installation.

Please advice on what to do, I'll try to follow. Thanks so much

 

Regards,

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

________________________________

>> From: John Woodlock <john.w...@gmail.com>
>> To: Dale Fuertes <dale_f...@yahoo.com>

>> Cc: mifos...@lists.sourceforge.net
>> Sent: Monday, July 23, 2012 6:34 PM

>>
>> Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos
>>

[Dale Fuertes]

10 August 2012

Hi John,

Thanks again for the help on Pentaho reports. The problem on "Data last updated on ERROR" was solved in my trial database in laptop.

Just 2 days ago that I have installed in our coop PC both Mifos 2.4.1 and Pentaho BI 1.3. I am currently in the process of manual entry of clients and accounts starting January 2012 (PC was backdated before installation and still is since I am still on January 2012 transactions). So far no problem in reports generation the other night. However, last night the same problem on "Data last...ERROR" came up on similar problematic reports like before in my laptop. Please see attached the mifos_dw log file and database dump file.

Note: Yesterday, after creating a client profile, upon submission I encountered a warning that similar item ..{1}.. already exist. I checked and found that Government IDs were similar for 2 clients created, typo error. However, this cannot be edited anymore. I just continued creating other client accounts. This was the only error encountered in mifos since installation.

Please advice on what to do, I'll try to follow. Thanks so much

 

Regards,

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>
To: Dale Fuertes <dale_f...@yahoo.com>

Sent: Friday, July 27, 2012 9:45 AM

Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos

Dale

I Can do 10pm my time (7pm Ho Chi Minh) next Monday, Tues or Wed on
skype.  Is any of those times good for you.  My skype is
john.woodlock_melb.

If you are able, it might be good to download "Mifos BI 1.3 for
windows" before the skype in case we need to reinstall from the
beginning.  The download link is

http://sourceforge.net/projects/mifos/files/Mifos%20Business%20Intelligence/Windows%20Installer/install_mifos_bi_1_3_Windows.zip/download

Its pretty big (about 250Mb)

John

On Wed, Jul 25, 2012 at 5:49 PM, Dale Fuertes <dale_f...@yahoo.com> wrote:
> 25 July 2012
>
>
> Hi John,
>
>
> I am using MySQL Community 5.1.63.
>
> Running ETL still shows similar results on problematic reports.
>
> Screen share would be better to do, in Skype, so you can see if there are
> lapses in my procedure or anything else.
>
> --> My Skype: dale_fuertes
> --> I am in Ho Chi Minh City, Viet Nam
> --> Time: Your preference, ok with me
>
> If there is problem with installation compatibility or procedures, it is ok
> for me to start from scratch. I am trying Mifos with BI in my laptop first
> before I apply it in our cooperative in Philippines. I have to make sure I
> can run it well first, then I will follow same installation/procedures for
> our cooperative by 2nd week of August.
>
> Thanks so much.

>
>
> Regards,
>
> Dale A. Fuertes-Pham
> Email:    dale_f...@yahoo.com

> Skype:  dale_fuertes

>
>
> ________________________________
> From: John Woodlock <john.w...@gmail.com>
> To: Dale Fuertes <dale_f...@yahoo.com>

> Sent: Tuesday, July 24, 2012 8:26 PM

>
> Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos
>
> Dale,
>

> Using the database you sent me, the ETL job went thru fine on my machine.
>
> Also 2Gb should be fine to run this process in about 3 minutes
> (assuming there are not lots of other things running).
> What version of mysql are you running - mine is 5.1.45-community - I
> got it by running in my mysql gui:
>
> show variables like 'version'
>
> There is probably some issue with installation (I think) as I see you
> even had some problem with pdfs (assuming your browser can normally
> display pdfs).  If you are interested we could probably screen share
> using skype or join.me  - this would give me a better idea I think  -
> if you want to do this let me know and we can arrange a time
> convenient for us both (I'm in Melbourne Australia)
>
>
> John
>
> On Tue, Jul 24, 2012 at 2:31 AM, Dale Fuertes <dale_f...@yahoo.com>

> wrote:
>> 23 July 2012
>>
>>
>> Hi John,
>>

>> Please see attached the database zip file and the log file generated after
>> ETL job.
>>
>> Memory in my laptop--> 2 GB.
>>
>> Thanks.

>>
>>
>> Regards,
>>
>> Dale A. Fuertes-Pham
>> Email:    dale_f...@yahoo.com
>>
>>

>> ________________________________
>> From: John Woodlock <john.w...@gmail.com>
>> To: Dale Fuertes <dale_f...@yahoo.com>

>> Cc: mifos...@lists.sourceforge.net
>> Sent: Monday, July 23, 2012 6:34 PM

>>
>> Subject: Re: [Mifos-users] HELP: Pentaho reports on Mifos
>>

[John Woodlock]

Hi Dale,

Yes, one loan was created on the 28th Jan but approved on the 4th Jan.
I assume this was a computer clock thing.
Anyhow, if you run the sql below in your mysql workbench and then
rerun your initialise_datawarehouse.bat all reports should be fine.

use mifos;
update account
set created_date = '2012-01-04'
where account_id = 123



As for your government_id problem. There must be a slight bug with
that check on government_id with Mifos 2.4.1 or maybe in previous
versions. Anyhow, you have ended up with 2 sets of duplicates. If
you want to manually correct the values run the following in your
mysql workbench

EDIT `mifos_dale`.`customer` where government_id in ('0', '001-043');

Then find the government_id column (about 7 columns from the end) and
edit the values to the ones you want. Then client the green tick,
apply and finish.


John

[Dale Fuertes]

10 August 2012

Hi John,

Applied your advice, Pentaho reports now running correctly. Thanks so much.

I thought I did not move back and forth the dates. Trying to do entries 1-way only, I will be more careful this time. 

As for the government_ID, got an error on running the script in workbench. But as long as the duplicates were not the source of problem in Pentaho reports, it is not a priority.

Once again, thanks for the help! Have a nice day.

 

Regards,

 

Dale A. Fuertes-Pham

Email:    dale_f...@yahoo.com

---

From: John Woodlock <john.w...@gmail.com>

To: Dale Fuertes <dale_f...@yahoo.com>; mifos...@lists.sourceforge.net
Sent: Friday, August 10, 2012 12:44 PM

[John Woodlock]

Your welcome Dale.

btw, I got the database name wrong in that government_id sql (I used
"mifos_dale" but I'm sure yours is just "mifos").

John