Tips for converting data model to aggregated form

[Iwin]

Hi all,

Currently our app contains data at least granular level(daily) and contains large number of rows which is causing high load times.

Inorder to improve performance we have decided to load data in aggregated form (say monthly) or on Company level .

I have few queries which might help me before starting the process :

Before converting to aggregated form,what should be checked for feasibility in current data model?

Should the data be aggregated in extractor itself or in final report app?

how to aggregate dimension tables ?Any tips?

Any other thing that should be done before aggregating done?

Thanks.

[Sammy_AK]

i understand that you are dealing with large volumes of data, as mentioned by you grouping by helps you to aggregate the data set and if you do it extractor it will be beneficial as you save processing power and storage. 

you can also look for columns which are not useful (i.e. columns have one single value stored in all rows or columns which are not used in your dashboard).

what do you mean by aggregating dimension tables? are you referring to grouping of dimension. for e.g. if you have a column where it says apple, potato, pear, grape. you want to add a derived column which would say Fruits, vegetable (i.e. apple, pear and grape are classified as fruits and potato is a vegetable) ?

[Iwin]

Hi @Sammy_AK  The requirement we got is that the client wants to view data based on month and year  basis.

They are not interested in the customer wise or salesman wise data except for 1 sheet where they want to view only top 5 or top10 customers and salesman. 

So while loading customer dimension table or salesman dimension what changes should i make?

[Sammy_AK]

if you aggregate by date and group by data by month it should be good. as the requirement to show top10 is for one sheet or few visualization, i would say use limitations option in dimension which can help you to show top n customers / salesman.