Is JasperAnalysis smart when use aggregate table?

[szymic1]

I have one big fact table 'Calls'  and one dimension table 'Date' with levels Year,Month,Day and measure 'CallDuration'. I add
one aggregation table (using AggName) that aggregate CallDuration per day.
Suppose that for one day I have 100,000 calls and I store 300 days what gives 30 000 000 rows. When I create very simple MDX that displays CallDuration for each day for each year server is using aggregate table what is proper behaviour, but when I want to display CallDuration for each Month  JasperAnalysis  does not use aggregation table (probably it will work when I make one aggregation table where I aggregate CallDuration per month). In my opinion
  JasperAnalysis should sum rows from aggregation table with day's values (it will be definitly faster than calculating sum from whole table) but it does not happen :( Is it possible force JasperAnalysis to use
aggregation table with per day values when it find date for months?

Michal Szymanski
http://blog.szymanskich.net 

[szymic1]

Should I use closuere table somehow?

Regards

Michal Szymanski

[swood]

Closure tables are for parent/child hierarchies only.

 

The aggregration table has to be at the lowest level of granularity you want to access. In your case, it sounds like you can use your aggregate table as your fact table, because you are not going below the Day level in your analysis.

 

Can you show us the schema and the table structures?

 

Sherman

Jaspersoft

[szymic1]

Short version of schema is in attachment, and table definitions are quite simple and look similar to this:

 warehouse.cdr_group_view  has following fields:
 call_status,
 id_start_time_invite_date,
 crm_group_type,
 payment_type,
 cdr_id,
 call_duration,
 call_cost,
 ps_call_cost,
 command,
 id_start_time_invite_time
 
 Time dimension:
CREATE TABLE warehouse.warehouse_time
(
  warehouse_time_id bigserial NOT NULL,
  time_hour integer,
  CONSTRAINT warehouse_time_pkey PRIMARY KEY (warehouse_time_id)
)

CREATE TABLE warehouse.warehouse_date
(
  warehouse_date_id bigserial NOT NULL,
  calendar_date date,
  calendar_year integer,
  calendar_day_of_year integer,
  calendar_day_of_month integer,
  calendar_month_number integer,
  calendar_month_name character varying
);
ALTER TABLE warehouse.warehouse_date OWNER TO carrierexadm;

Fact table has millions of rows, biggest dimension has around 600 rows. Performance is not so bad (to see date for each year user have to wait 2-3 min) but we expect more rows in the future and response time in tens of minutes is not acceptable.

 

Regards
Michal Szymanski
http://blog.sztmanskich.net

[szymic1]

The aggregration table has to be at the lowest level of granularity you want to access. In your case, it sounds like you can use your aggregate table as your fact table, because you are not going below the Day level in your analysis.

Maybe it is good idea to create aggregate table for Day level (or Hour level) and use it as a fact table. But in such situation I will lose 'zoom and drill' functionality. It will be nice to have such functionality where in schema file we define that for 'Day' level Analysis Server use aggregate table but when we use use 'zoom and drill' it  use table will full table.

By the way I have big problem with Aggregate Designer after adding few aggregation levels I cannot save in logs I've got:

11:03:26,421 INFO  [AggListController] Event index: 0 aggTable.selectedRow0: 0

java.lang.NullPointerException

        at com.thoughtworks.xstream.io.xml.PrettyPrintWriter.writeText(PrettyPri

ntWriter.java:203)

        at com.thoughtworks.xstream.io.xml.PrettyPrintWriter.writeText(PrettyPri

ntWriter.java:199)

..........................

I cannot write what I create i this program? Have you encoutered this bug? Maybe I have wrong Java version, now I have 1.6.

 

Regards

Michal Szymanski

http://blog.szymanskich.net

[swood]

You can certainly have an agg table that summarizes to the Day level and a fact table that has more detail.

 

The Aggregation Designer is a separate project. I don't know the details of the problem - can you show the full logs? It may be an issue with JDK 1.6, which has its own XML implementations.

 

 

Sherman