cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blytonpereira Helper II
Helper II

Using Direct Query vs Import for a dataset of 120 million rows

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Using Direct Query vs Import for a dataset of 120 million rows

@blytonpereira

 

Currentlt, power bi doesn't support live connection or Direct query with postgre, you may vote for this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31619011-implement-live-directque....

To aggredate the values and generate new smaller tables, I'm need to import the data source into power bi firstly. If the datasize is very large, I would suggest you to do that in database.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Using Direct Query vs Import for a dataset of 120 million rows

@blytonpereira

 

Currentlt, power bi doesn't support live connection or Direct query with postgre, you may vote for this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31619011-implement-live-directque....

To aggredate the values and generate new smaller tables, I'm need to import the data source into power bi firstly. If the datasize is very large, I would suggest you to do that in database.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

scott_goley
Frequent Visitor

Re: Using Direct Query vs Import for a dataset of 120 million rows

@blytonpereira 

 

This is something that I'm actively working on since my business ran into a situation that made working around this an impossibility.

Read more about my progress so far here:

https://justreadrtheinstructions.com/post/2019-06-15-directquery-postgres-howto/

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors