14 Replies Latest reply: May 2, 2011 2:28 AM by At titude RSS

    Help required in creating aggregation table!

      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
      [C:\Data.xlsx]
      (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))

       

        • Help required in creating aggregation table!
          Erich Shiino

          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
          [C:\Users\erich.shiino\Desktop\sample extract.xls]
          (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 ;



          • Help required in creating aggregation table!
            Erich Shiino

            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,