NATURALINNERJOIN - Large Tables

[rnola16]

Hi,

I got two large tables  in our database (Teradata)  each having 300 cols and 3 billion records.

In PBI, using direct query I brought in 3 fields from each of the tables and created a relationship between them. I wanted to create a join and used DAX fn NATURALINNERJOIN( TABLE A, TABLE B), which stops me with a message

"Something went wrong. The resultset of a query to external data source has exceeded the maximum allowed size of 1000000 rows"

Is PBI not desinged for large datasets ?

Thanks.

[v-nmadadi-msft]

Hi @rnola16 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks

[v-nmadadi-msft]

Hi @rnola16 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @rnola16 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Deku]

PowerBI has two engines to answer queries, the Formula Engine and Storage Engine. With Import mode the storage engine is Vertipaq. The Vertipaq engine has a limited operation set, and the formula engine is more extensive. The formula engine will request a data cache from the storage engine. The storage engine will perform as much data processing as possible and pass the results to the formula engine, which can perform additional more complex processing.

In DirectQuery mode the Formula engine issues queries to the remote datasource, in this case Terradata. You want to write your DAX to ensure as much processing occurs in the remote datasource is possible, and return data cache below the 1 million row limit. Since the query will be trying to satify a visual, 1 million rows should sufficent, as you will not be showing or plotting that many data points.

Without more info on what you are trying to do with that innerjoin is it hard to offer any help.

[rnola16]

Was trying to establish a join relationship between two tables without needing to do a customized SQL in direct query. The tables does have parameters on date and other cols. I do not need the million data points into my result but to query against the two tables.

sample query :

SELECT A.EMP_ID, B.NAME

FROM

A LEFT OUTER JOIN B ON A.ID = B.ID

WHERE

A.EMP_ID = 'Pamater1' and B.NAME = 'Parameter2' and B.Date >= 'Parameter3'

[Deku]

It's still not clear why you are manually creating a query with a inner join. With a tabular model if you have a relationship defined you don't need to mention it in the DAX query, it's inherent 

[rnola16]

Because under 'Manage Relationships' you only can define one-one/one-many etc but not the kind of join you want like ( left/inner).

[Deku]

Assume Referential Integrity 

[lbendlin]

Sort of.  It is designed for large dataset as long as the join column cardinality is not higher than 50000-ish