I have a dataset of 120 million rows. Currently I am using import from a PostgreSQL server.
There are 10 columns of data and is arranged somewhat as follows:
P2, P3, P4, P5, P6, Value, FC Version, Location Label, Country, Region etc.
P2 is the lowest level, like a SKU. Then P3 is one level up, P4 is the brand which is another level up and so on. Value is a decimal represents the forecast.
FC Version can only have two values, for example 2018M01 or 2018M02. Hence 60 million rows represent 2018M01 and the remaining 2018M02. This basically means comparing the forecast we release last month, to the forecast we release this month.
As can be seen due to the setup of this table there are sveeral duplicated rows.
Most of my calculations if not all are based on displaying simply displaying the Sum of the Forecast column, for the current FC Version - (minus) the Previous FC version.
I would like to understand is there any added benefit in my case of using Direct Query for the 120 million rows large fact table and creating a smaller aggegated table for e.g. P2,P3,P4,Value and utilising IMPORT to speed up filtering in PowerBI ? This aggregated table would be smaller since it only conains columns that I am frequently using, i.e P2, P3, P4, and Value.
I understand also that Postgre SQL cannot suport direct query so I will need to confirm if there is any imporvement before investing in moving my datasets to another daabase service that supports Direct Query.
Thank you
