Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Data Aggregation on DB side
tachi512
Hello.
Can You help me with data aggregation?
I have star schema (or simple table either) and I try create count measures. Great. It created and work. I realy enjoy with report developing in designer. But all aggregation performs on BIRT iServer. Its realy disappointing. Can You tell me how make BIRT perform aggregation staff at DataBase.
Thank in advance.
Find more posts tagged with
Comments
micajblock
Through SQL?
select a,b,c, sum(d), Count(*)
from x
group by a,b,c
In certain scenarios we will automatically push filters and sorts to database but not aggregates.
tachi512
<blockquote class='ipsBlockquote' data-author="'mblock'" data-cid="82901" data-time="1316194995" data-date="16 September 2011 - 10:43 AM"><p>
Through SQL?<br />
select a,b,c, sum(d), Count(*)<br />
from x<br />
group by a,b,c<br />
<br />
In certain scenarios we will automatically push filters and sorts to database but not aggregates.<br /></p></blockquote>
<br />
<br />
Thank You, for your reply. <br />
Its really sad because "in memory" aggregation cannot fulfill our tasks.<br />
Using SQL is manual way. Through SQL I can do anything but user must not do anything complicated
I think I will try use manual way somehow.<br />
Can I ask about Actuate BIRT? Does it use same solution or custom?<br />
<br />
<br />
P.S. sorry for my bad English - my native language is differ.
tachi512
I think I must explain myself.
For example, I try create crosstab. I define Data Source/Set/Cube and some metrics in Data Cube with certain aggregation (COUNT, for example). This aggregation performed "in memory". Can I perform this on DB side? (sorry I repeat topic's question, but I'm not sure that I asked it in right form)
micajblock
There are two choices.
One - Do everything 'In-Memory'. Define the cube in BIRT. Use the Actuate BIRT Data Analyzer to load the cube in to memory and then perform all your analytics in memory. In this scenario once the cube is loaded in to memory there is no database.
Two - Do everything in the database. Build the aggregates in SQL, then run a BIRT report that displays the aggregates that were done in the database.
I hope this explains more.
BTW, I do not understand why you would want the aggregates to be done in a database. It will be much faster when done 'in-memory',
Hans_vd
<blockquote class='ipsBlockquote' ><p>
BTW, I do not understand why you would want the aggregates to be done in a database. It will be much faster when done 'in-memory',<br /></p></blockquote>
Have you measured it?<br />
Did you really see a performance gain when getting the grouping and aggregates out of the database and putting them in a BIRT report? Or a loss of performance when doing the other way around?<br />
<br />
Regards<br />
Hans
tachi512
<blockquote class='ipsBlockquote' data-author="'mblock'" data-cid="82935" data-time="1316296321" data-date="17 September 2011 - 02:52 PM"><p>
There are two choices.<br /></p></blockquote>
Thank you. Its more clear now, but I have some new question about manual way.<br />
Can I have crosstab in this case? If I understand right, Crosstab can be created only on DataCube so I must define aggregation in DataCube. Or I can use measure without DataCube aggregation in crosstab? (I try, but dont see solution yet)<br />
<br />
<blockquote class='ipsBlockquote' data-author="'mblock'" data-cid="82935" data-time="1316296321" data-date="17 September 2011 - 02:52 PM"><p>
BTW, I do not understand why you would want the aggregates to be done in a database. It will be much faster when done 'in-memory',<br /></p></blockquote>
It's not right if you use big amount of records. If aggregation performed on DB it's much more faster than download all tables from DB server or local machine, put it all in memory(over million value) and perform some aggregation.<br />
Sometimes in memory faster than DB but it's not that case.
micajblock
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="82937" data-time="1316339448" data-date="18 September 2011 - 02:50 AM"><p>
Have you measured it?<br />
Did you really see a performance gain when getting the grouping and aggregates out of the database and putting them in a BIRT report? Or a loss of performance when doing the other way around?<br />
<br />
Regards<br />
Hans<br /></p></blockquote>
<br />
OK let me rephrase this. The point of the Actuate BIRT Data Analyzer is that I can perform analytics on the data. The idea is that I create dimensions and measures in an in-memory cube and then I can slice and dice the data and create charts and the data. This type of analysis will always perform faster on a pre-aggregated in memory cube then going to the database for every action.
micajblock
<blockquote class='ipsBlockquote' data-author="'tachi512'" data-cid="82938" data-time="1316341184" data-date="18 September 2011 - 03:19 AM"><p>
Thank you. Its more clear now, but I have some new question about manual way.<br />
Can I have crosstab in this case? If I understand right, Crosstab can be created only on DataCube so I must define aggregation in DataCube. Or I can use measure without DataCube aggregation in crosstab? (I try, but dont see solution yet)<br /></p></blockquote>
<br />
You still can create the aggregates in the database. Yes you still need to create the data cube in order to create a crosstab. However if the data is already aggregated the data cube will not add overhead. Also, once you create the crosstab AND you have the Actuate Interactive Viewer, end users can further analyze the data in the cube.<br />
<br />
<blockquote class='ipsBlockquote' data-author="'tachi512'" data-cid="82938" data-time="1316341184" data-date="18 September 2011 - 03:19 AM"><p>
It's not right if you use big amount of records. If aggregation performed on DB it's much more faster than download all tables from DB server or local machine, put it all in memory(over million value) and perform some aggregation.<br />
Sometimes in memory faster than DB but it's not that case.<br /></p></blockquote>
<br />
The whole point of the cube is to store pre-aggregate data for analysis. The query that is used to create the cube can also filter and aggregate data. If this is a single purpose report just create the query with the rows, columns, aggregates that you need. In this scenario you can (and probably should) aggregate the data in the query before you load the data in the cube.
tachi512
Thank you. I will try this.
Hans_vd
mblock,
I'm sorry, I might have been a little too quick commenting on your solution.
I wasn't looking further than "just this one report", while you were looking at the bigger picture.
regards
Hans
