How can i use multiple merge in powerbi or huge data processing ?

[himanshusharma]

The main concern of mine is that is power bi has capablity to extract huage set of data mention below , or it is used of small extracted set of data for visuallization purpose.

The reason i am asking this is because is this .

Let say , I have four table .

user -> with 2000k record
buyer -> same record
buy -> 50k record
prod -> 1 m record

So i want to apply this query in power bi .

SELECT c.buyer_id,
c.buyer_name,
c.client_no,
p.person_id,
p.first_name || ' ' || p.last_name,
p.email,
wip.product_name
FROM tablename.user p
JOIN tablename.buyer c ON p.buyer_id = c.buyer_id
JOIN tablename.buy s ON s.user_id = p.user_id
JOIN tablename.prod wip ON wip.product_id = s.product_id
WHERE s.product_id IN (1,2,3,45,67,78)
AND s.start_date > (TRUNC(SYSDATE) - 30)
AND (s.stop_date >= TRUNC(SYSDATE) OR s.stop_date IS NULL)
ORDER BY c.buyer_id, p.first_name, wip.product_name;

This is a sample query for the report , there is more complex query with many joins.

Does the powerbi do so ?

> When i merge one table to other , let say (user with buyer) it take
> lot of time , then the resultant merge with (buy) , then resultant
> merge with (prod) and then applying filter filters of where clause .
> we can apply filter before the merge as needed.

**Does powerbi is made to work with such huge data ? Or it is make for small set of extracted data .**

Because if i do with above approach it can take 1 day also to fetch the resultant row every time .

What is your approach ?

Thanks.

[Greg_Deckler]

I feel like there is a lot of things wrong overall with this honestly. If it is taking a day to pull a query you really ought to fix that. At the very least create a View so that it can be cached. Something. 

But, yes, Power Query and Power BI were designed to ingest and work with large amounts of data quickly. Some individuals in my user group say that it is even faster than SSIS. I've worked with millions of rows in Power BI models, no worries. I've seen it scale to billions and of rows when working with live data (you don't have to import it).

But, not even Power BI can fix whatever train wreck you have if it is taking you a day to pull data. You need to figure that out first. Some things to consider, build a view, build a cube, more hardware.

[himanshusharma]

Can you tell me the solution of the query , also the process to merge multiple table so that each filter don't take time . Please see the bold text in question . 

Why you are saying i should make view ? 

Does making view means to do all complex join in database and showing the appropiate result by help of view . If that is the idea that means for complex join we will avoid in powerbi . In every report i have more then 6 , 7 joins in table and then some form of condition , that all i have in join. 

Whats your suggestion ?