cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
blytonpereira Regular Visitor
Regular Visitor

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 Team
Community Support Team

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 Team
Community Support Team

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
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 155 members 1,946 guests
Please welcome our newest community members: