Infobright.org Forums

Connection with Jasper

2011-10-22T00:25:49-04:00

I have downloaded and installed Jasper iReport on the same localhost where my Infobright IB_4.0_r13151_13690(ice) is (I’m running Windows 7).
Then I tried to connect and got the dialog box where the only choice I found was JDBC connection (1st screen print in my attachment).
After that I got to choices. I tried both and it didn’t work.
So I have a following questions:

1) Do I have to use JDBC driver?
2) Was the JDBC driver installed during Infobright installation?
3) Is there any other way to connect to Jasper?

Thanks

C_G

Mondrian giving wrong Results with infobright

2011-02-17T08:05:18-04:00

Hi ,
I am using mondiran with infobright database. I have created my table with below collation for case insensitivity.

COLLATE=latin1_general_ci

my problem is ,

i have one dimension “dim1” which contails 2 records with same name and different case(Battery, battery) .

i have data for “battery” in my Fact table and i dont have data for “Battery”

while generating report i din’t get data for “battery” also.

below the quries and results generted by the mondiran while creating report(Execting Mdx Qurey)
SELECT
NON EMPTY {[Measures].[Clicks]} ON COLUMNS,
NON EMPTY {[Adgroup].[Battery]} ON ROWS
FROM [Cube1]

[color=blue]select `DIM_ADGROUP`.`name` as `c0` from `DIM_ADGROUP` as `DIM_ADGROUP` group by `c0` order by `DIM_ADGROUP`.`name` ASC; [/color]
Result:

Average Cost Electricity
Battery
Battery Powered
BD - Battery N Recharge

select `DIM_ADGROUP`.`name` as `c0`, sum(`SEARCH_FACT`.`clicks`) as `m0` from `DIM_ADGROUP` as `DIM_ADGROUP`, `SEARCH_FACT` as `SEARCH_FACT` where `SEARCH_FACT`.`adgroupid` = `DIM_ADGROUP`.`id` and `DIM_ADGROUP`.`name` = ‘Battery’ group by `c0`;
Result
+————-+———+
| c0 | m0 |
+————-+———+
| battery | 1784 |
+————-+———+
1 row in set (0.11 sec)

But in UI i didn’t get any results.

I am expecting that mondian working as case sensitive. By merging these 2 queries , it unable to generate the report.

i have tried the same case by creating my database as case sensitive and i got correct result.

Can you please help in this.

Thanks,
Raja K

Getting to use SOA

2011-02-23T10:14:30-04:00

Hello,

Thanks for the help on the forum.
We have been looking into open source software for data management and we would be interested in a similar field.

We are wanting to implement SOA. It would be to complete our existing software. What would you know about open source SOA ?

Thanks a lot.

Why this query is not supported by Infobright?

2010-06-29T08:16:07-04:00

Hi,

I am running following query on Infobright.

SELECT `pt`.`parent_id` AS `c0`, `dim_subscriptions`.`value` AS `c1`, `acc_g`.`type` AS `c2`, `fd`.`year_num` 
AS `c3`, `fd`.`month_num` AS `c4`, SUM(`fd`.`usage_count`) AS `m0` FROM `sprotocols_page_type_view` AS `pt`, 
`fact_details_degenerated` AS `fd`, `dim_subscriptions` AS `dim_subscriptions`, `sprotocols_account_parent_view` 
AS `acc_g` WHERE `fd`.`page_type` = `pt`.`child_id` AND `pt`.`parent_id` = 115 AND `fd`.`subscription` = `dim_subscriptions`.`value` 
AND `dim_subscriptions`.`value` = 'subscribed' AND `fd`.`account_id` = `acc_g`.`child_id` AND `acc_g`.`type` = 'Institution' 
GROUP BY c0, c1, c2, c3, c4 

This is giving following error.
The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

How can i improve this query?
Note: table names starting with dim are dimension tables.
table names starting ending with view are database views on dimension tables.
table name starting with fact is fact_table.

How can i make Infobright case insensitiive?

2010-05-03T08:32:23-04:00

Hi,

Suppose i have following two tables with sample data as shown below.

Table - A

AId name
I1 v1
I2 v2

Table - B

BId name
1 V1
2 v2

If i make inner join on both the tables using mysql on name column, it will return me both rows. It means it is behaving case insensitive.

AId BId
I1 1
I2 2

But when i used same table using BrightHouse engine it is behaving case sensitive and returning only single row.

AId BId
I2 2

How can i make Infobright case insensitive?

Data Transfer Process

2010-05-03T02:17:02-04:00

In the process of data transfer in InfoBright We have dumped the data into sql script files ,
Then removed the primary keys and other indexes in the script , as info bright do not support them
And from the Script file we are trying the load the data into InfoBright

We successfully connected to info bright and started the data transfer
But the InfoBright server is disconnecting frequently in the middle of the data transfer
and giving the errors like

Lost Connection to MYSQL Server during Query, MYSQL Server Gone away,
can’nt connect to MYSQL Server on localhost

I tried to reconnect the InfoBright , by continuously observing the data transfer process
But at certain period of time , we are not able to restore the connection

please help so that i cant go in proper way of data transfer

How to avoid String function ‘UPPER’ for varchar field?

2010-04-30T02:57:40-04:00

Hi,

I am trying to optimize month column in my fact table.

If month column is integer type then following query is generated by jasperserver which takes 8 sec and 313 milliseconds. This is fine.

select `fd`.`date_month` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and `fd`.`date_month` = 1
group by `c0`
order by `fd`.`date_month` ASC;

But i want to display String value for month field like ‘Jan’,‘Feb’ etc. Therefore i added one more column month_name varchar field in my fact table. Now jasperserver generates following query which takes 1 minute and 59 seconds.

select `fd`.`month_name` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and UPPER(`fd`.`month_name`) = UPPER(‘Jan’)
group by `c0`
order by `fd`.`month_name` ASC;

Now if i compare both the query then the difference is marked in red. This is due to UPPER function in the query.
Now if i run the second query without UPPER function as show below it takes 8 seconds and 547 milliseconds which is equivalent to first query. It means UPPER function is taking maximum time.

select `fd`.`month_name` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and (`fd`.`month_name`) = (‘Jan’)
group by `c0`
order by `fd`.`month_name` ASC;

Please suggest me how should i optimize this.
1. Can i avoid UPPER function so that jasperserver does not generate it for varchar fields?
2. Can i optimize UPPER function in infobright?
3. Is there any alternate way to handle the same scenario?

What is the best way of defining date dimension using Infobright?

2010-04-21T00:03:31-04:00

Hi,

I have a request_date column in my fact table having values like ‘2009-11-28’.
I have tried the following ways to define my date dimension.

1. I am not using any separate date dimension table. I am directly using request_date column in fact table. It is very slow as i need to use date_format(request_date) function or year(request_date) or month(request_date) functions which are very slow.

2. I tried explicit year, month, day column in my fact tables which works fine for me. All these explicit columns are smallint, tinyint and tinyint type respectively. But at some places i need date like ‘Jan-2010’ etc which require date_format function which increases the response time almost 2 times.

3. Thirdly i tried a separate date dimension table with all the information pre calculated. Its response time is also similar to 2nd step’s 2nd scenario which increases the time 2 times.

Please suggest me what is the best way to define a date dimension?

comparison between integrated jasper/ICE and standalone jasper/ice

2010-04-20T01:43:24-04:00

Hi,

I am using standalone jasper server downloaded from jasperforge.org. Previously i was using mysql as database. Now i am exploring Infobright for better performance. Therefore i installed ICE and migrated my MYIsam tables to infobright tables.
Then i pointed my standalone jasper server to new Infobright database.

My question is:
will there be any benefit if i use integrated jasper/ICE as compared to standalone jasper/ICE as explained above? If yes then what kind of benefit?

I think there should not be any difference but as i am newbie i just want to confirm this.

Does ICE support inline tables in OLAP schema?

2010-04-19T05:58:44-04:00

Hi,

I am newbie. I am using ICE with jasper server.
In my OLAP schema, i have an inline table.
Following query is being generated by the jasperserver which is running on Infobright database

select `pt`.`id` as `c0`, `subs`.`value` as `c1`, `acc_g`.`type` as `c2`, date_format(request_date,’%Y’) as `c3`, date_format(request_date,’%m’) as `c4`, sum(`fd`.`usage_count`) as `m0` from `npg_page_type_view` as `pt`, `fact_details` as `fd`, (select 1 as `id`, ‘subscribed’ as `value` union all select 2 as `id`, ‘unsubscribed’ as `value` union all select 3 as `id`, ‘unknown’ as `value`) as `subs`, `npg_account_parent_view` as `acc_g` where `fd`.`page_type` = `pt`.`id` and `pt`.`id` in (‘19’, ‘20’, ‘1506’) and `fd`.`subscription` = `subs`.`value` and `subs`.`value` = ‘subscribed’ and `fd`.`account_id` = `acc_g`.`child_id` and `acc_g`.`type` = ‘Group’ and date_format(request_date,’%Y’) = ‘2010’ and date_format(request_date,’%m’) = ‘01’ group by c0, c1, c2, c3, c4

The result of this query is

The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

If i remove inline table from OLAP schema or replaces inline table with database table then it works fine.

Does it mean Infobright does not support inline tables in jasperserver?