Data Cube: Looking for string concatenation in the summary field

I have a data cube and I wish to summarize over string data types.
Until now, the only working functions for summary fields of type STRING I found are: MIN, MAX, FIRST, LAST.
 
I wish to have a summary function like "concatenate all values using a given separator".
Is there any extension point where I can add such a summary-function?
 
A long time ago, a guy gave me the hint to use a grouped table instead of a data cube and to add sub-reports in the table cells.
That works for "small" data sets - but on larger data sets the runtime increases to more than 5 minutes.
I believe this is because the sub-reports are joins of three data sets and it looks like BIRT joins these data sets again for each sub-report.
 
Can anyone give me a hint?
 
Thanks 
Widi

Find more posts tagged with

Comments

JFreeman

What version of BIRT are you using?
 
Can you provide details on your use case?

BirtWidi

Jesse,
 
first, I use BIRT 4.4.2 with Zend Studio 12 as development environment and tomcat6 in the productive environment.
 
And second - more details. I have attached a sample report based on the SampleDB.
My manager likes to see, which orders makes his sales employees distributed over the months.
 
The first attempt was, to build a data cube with dimensions over the columns EMPLOYEENUMBER and ORDERDATE. But here I miss a summary function to list all ORDERDATES in one summary field.
 
The second attempt was to build a grouped table (group over EMPLOYEENUMBER), add 12 columns for the months, and add 12 sub-reports which are filtered by the EMPLOYEENUMBER and the MONTH of the column.
Here I really got the result I wish - but it runs long, sometimes too long for the timeout of a HTTP request.
You can see: the whole cross table needs approx. 130ms, but the grouped table needs 4400ms.
 
My real data base contains large tables which are fetched from an external server via REST. This data base system does not provide the ability to build joined views on the server. Therefore I have to join the data sets in the BIRT report.
Thus the preparation of the data set for one sub report needs 5seconds - thus 1 minute for one row.
 
And now ... any suggestions?
 
Thanks
Widi

JFreeman

So you are wanting to concatenate all of the dates together in order to display all of the applicable dates into a single cell in the crosstab like you have it in the table?
 
Created joined data sets can definitely cause a performance impact depending on the situation.
I would typically try and avoid a joined data set if possible.

BirtWidi

<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="136924" data-time="1433867831">
<div>
So you are wanting to concatenate all of the dates together in order to display all of the applicable dates into a single cell in the crosstab like you have it in the table?
</div>
</blockquote>
 
Yes, thatÂ´s what I need!
You can convert the dates to strings and concatenate the strings - that doesnÂ´t matter. For this a computed column may be introduced in the data set (in the BIRT report - not in my foreign and poor data base system).
The result I need are lists of dates in the summary cells of the crosstab - like in my table.
 
Regards,
Friedbert

JFreeman

I've been working on trying to get this done quite a few different ways and between your data source/set setup and the way the aggregations work in crosstabs, I'm not finding a good way to create the string concatenation you are wanting within the crosstab.
 
I think the best option is to go the table route and instead focus on improving the setup and performance of your data source/sets.

BirtWidi

Jesse,
 
thanks for your work.
I have also gained the impression that this problem is difficult to solve with the given environment.
I believe, I have to look for other solutions. One possibility would be to copy the data to a separate SQL database. Then I would have the full power of SQL and could do the joins efficiently from the SQL data base machine.
But so far I had no desire to handle a redundant copy of the data.
 
Friedbert

JFreeman

Yeah, handling redundant data is less than ideal.
 
You can investigate some other options to maybe optimize pulling in the data from your existing data source setup. However, from your previous description, without making changes to the data sources directly, I think you are probably still going to be encountering performance issues.