Help required in creating aggregation table!

[#1]

Hi All

The performance of one of my pivot table is very bad as it does lots of calcuation in the front end. So thought of creating the aggregation table so as to improve the performance of it. As I never created such aggregation table before can some one help me out on this please. I have given you the details about the tables as well as the pivot table. Also have attached the sample application as well.

Following are the tables(Data & Calendar) which are there in the attached application:

Data:

LOAD A1,
A2,
A3,
A4,
A5,
A6,
A7,
A8,
A9,
A10,
A11,
A12,
A13,
A14,
TimeStamp(A15) as StartDate,
TimeStamp(A16) as EndDate,
A17 as StartDate1,
A18 as EndDate1,
A19,
A20
FROM

(ooxml, embedded labels, table is Sheet1);

Calendar:

Load *,
Week(date) as Week,
Year(date) as Year,
WeekName(date) as WeekName,
MonthName(date) as MonthName
;

LOAD DISTINCT
DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;

Where as the pivot table contains:

Dimension:

A1

A10

StartDate

EndDate

Time(Group(Cyclic): Week,Year,WeekName,MonthName)

Expression:

SUM(IF(StartDate<=date AND EndDate>=date,1))

[erichshiino]

Hi,

I didn't know which level you could aggregate, but in order to remove the if condition in your pivot table I created a table with one line for each day between start date and end date for each item of your table.

Basically, you get performance but you create more lines in you data model.

This is the code i got:

Data:
LOAD A1,
 A10,
 A1 & '|' & A10 as key,
 A11,
 A12,
 A13,
 A14,
 A19,
 A2,
 A20,
 A3,
 A4,
 A5,
 A6,
 A7,
 A8,
 A9,
 EndDate,
 EndDate1,
 StartDate,
 StartDate1
FROM

(biff, embedded labels, table is Sheet1$);

Keys:
load distinct key, 1 as count_key
resident Data;


min:
LOAD MIN(num(daystart(StartDate))) as minDate
resident Data;

vMin = peek('minDate');
max:
LOAD max(num(daystart(EndDate)+1)) as maxDate
resident Data;
vMax = peek('maxDate');

drop tables min, max;


for refDate = $(vMin) to $(vMax)

Event:
load key, $(refDate) as REFERENCE_DATE, 1 as controlFlag

resident Data
where num(dayStart(StartDate)) <= $(refDate) and
 num(dayStart(EndDate)+1) > $(refDate) ;



next

TRef_Calendar:
load distinct
REFERENCE_DATE
resident Event;

Ref_Calendar:
load
REFERENCE_DATE,
 Week(REFERENCE_DATE) as refWeek,
 Year(REFERENCE_DATE) as refYear,
 WeekName(REFERENCE_DATE) as refWeekName,
 MonthName(REFERENCE_DATE) as refMonthName
resident Event;

drop table TRef_Calendar;
Calendar:


Load *,
 Week(date) as Week,
 Year(date) as Year,
 WeekName(date) as WeekName,
 MonthName(date) as MonthName
 ;

LOAD DISTINCT
 DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;


exit script ;

[#3]

Hi Erich Shiino

Thanks alot! Really a great effort!

By the way may I know what is that I have to do instead of 'IF Statement' in the expression. So that I can pass the same and see how it is performing.

[erichshiino]

Hi,

With the new script, if you use the fields in Ref_Calendar, you already have this if condition that you had before in the pivot table.

If you want to count distinct values, you can just sum the controlFlag (there is one for each 'key' in the model (key is A1+A10)

Ref_Calendar:
load
REFERENCE_DATE,
 Week(REFERENCE_DATE) as refWeek,
 Year(REFERENCE_DATE) as refYear,
 WeekName(REFERENCE_DATE) as refWeekName,
 MonthName(REFERENCE_DATE) as refMonthName
resident Event;


Rgds,

[#5]

Hi

Sorry! I didn't get you! I have used 'Sum(controlFlag)' in the expression but it is not working. If you have free time please try to do the required changes in the attached application it will be helpful for me. As I don't what changes has to be done in the front end I could proceed further.

[#6]

Hi Erich Shiino

Was I am not clear? If so please do let me know. If your clear about what I have asked then please try to help me out.

[#7]

Hi

Can some one please help me out here. I didn't get any solution yet. It is very important for me to complete this task.

Hope you guys can understand that!

[erichshiino]

Hi, Please, check attachment. I left you table on the first sheet and created another one on the the other. I'm not sure how to get your results, for example, if select the dimension Year, i can get more than 365 in one line for one year. In my pivot I can not reproduce it. If you explain I can adjust the app.

Sorry I couldn't send it before...

Regards,

[#9]

Hi Erich Shiino

Yes I am not sure how to get the results. As I said some how I got the solution in the forum and luckily it is working. But only problem is there is lot of performance issue. I don't know what to explain as I got the expected result but the performance is is poor.

Look into it if you get any idea or else its ok. Anyway I am still working on the performance.

[erichshiino]

but is that result right? I mean, if I see only line and select year as dimension can it get to 800, for example?

I can't reproduce it because I don't know what that mean.