Pivot Table Customization and Aggregation

[montubhardwaj]

Hello friends.  I am dealing with 90+ millions of records and it is quite big application. Users have particular requirements where they want the report to be exactly like a xls sheet. I am able to that exactly but problem is I have to aggreagte  and concatenate data again and again to bring it to this desitred format.

I have prepared a sample data and chart.Here, Product dimension has only 3 values (Clother, Shoes and Watches). Users wants another information in in the same pivot table as shown. I am able to achieve this but I hav to agregate and concatenate data again and again which is making my qvw size much bigger (3-4 times larger because of concatenation.

Is there a more elegant way to achieve this? I am attaching sample qvw and xls data to give more information.

Appreciate your help and time.

[montubhardwaj]

Also, I am doing aggregation and concatenation in script. Since I am dealing with lot of records, I would prefer an approach which is at back end.

[settu_periasamy]

Hi,

May be like this.. Not sure about the performance. Check and Let us know

DIRECTORY;
T1:
CrossTable(Type, Data, 😎
LOAD Product, 
     SubPrd, 
     Loc, 
     Country, 
     State, 
     Zip,
     Group, 
     [Super Area], 
     Amout, 
     Discount, 
     Other  
FROM
[Sample Xls Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Concatenate(T1)
LOAD 'Total Products Summary' as Type,
     Product,
  SubPrd, 
     Loc, 
     Country, 
     State, 
     Zip,
     Group, 
     [Super Area], 
     Data
 Resident T1 Where Type='Amout';


NoConcatenate
Final:
LOAD  if(Type='Other','Others/Extras',
      if(Type='Discount','Discounts Applied',
      If(Type='Amout',Product,Type))) as Product,
     SubPrd, 
     Loc, 
     Country, 
     State, 
     Zip,
     Group, 
     [Super Area], 
     Data as Measure     
Resident T1;


DROP Table T1;

[montubhardwaj]

Hello periasamy. Thanks for your response.

This is a good workaround if you have a smaller data set. In our data model, we have 40+ dimensions and more than 20 different  measure which makes it difficult to follow your approach. I tested it and there was hardly any impact on performance.

I appreciate your response and time.

[montubhardwaj]

Let me put this into a little different way. Please consider this scenario.

I have different measure for different products e.g MILK_COST,SHOE_COST,CLOTH_COST,SHIRT_COST,WATER_COST, RING_COST, PH_COST etc and 10 more cost.

In my database, there is no such column/field which constitutes all different products. So, I have to create all the these values as Synthetic Dimension and aggregate in pivot table. I am using PICK-MATCH combination in my expression to use correct measure against dimension.

But this is not an elegant approach as I am having millions of rows of data. Do you guys know if there is some better approach at script level so that I can avoid using synthetic dimension at front end?

Thanks for help.