How to join a customer list from SharePoint with a large Teradata table using a date range match?

[M_Andersson]

Hi all,

I have a customer number list (~800,000 rows) stored in SharePoint, which I load into Power BI. For each customer, I need to retrieve name and address information from a large Teradata table.

The Teradata table contains historical data where each row has:

• CustomerNum

• StartDate

• EndDate

• Name

• Address

  Since customers can appear multiple times in the Teradata table (with different time periods), I want to join based on:

  • Matching CustomerNum, and

  • A given reference date (e.g., "2024-06-01") that must fall between StartDate and EndDate

    I want to do this as efficiently as possible, without loading the entire Teradata table into Power BI service (which causes spool space issues).

    My goal:

    For each customer number from the SharePoint list, get the one valid row from Teradata where:

     CustomerNum = <match> AND <ReferenceDate> BETWEEN StartDate AND EndDate
    

     CustomerNum = <match> AND <ReferenceDate> BETWEEN StartDate AND EndDate

    Questions:

    1. What's the best approach to perform this join (ideally server-side) in Power BI or with a minimal query?

    2. Can this be done with query folding so the filtering happens in Teradata?

       Thanks in advance for any help or examples!

[v-nmadadi-msft]

Hi @M_Andersson ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @M_Andersson 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @M_Andersson ,

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.

Thank you.

[v-nmadadi-msft]

Hi  @M_Andersson 
Thanks for reaching out to the Microsoft fabric community forum.

In Power BI, a practical solution is to pre-stage the SharePoint customer list directly in Teradata, allowing you to perform the join server-side before importing the final results into Power BI.

1) Load the customer number list from SharePoint into a staging table in Teradata.
2) After loading the customer number list from SharePoint into a staging table in Teradata , the next step is to create a Teradata view that performs a join between this staging table and the existing historical Teradata customer data filtering out the unrequired data as per your requirement.
3) Once the view is created in Teradata with all necessary filters and joins applied, the final step is to connect Power BI directly to this view using the Teradata connector.

This approach avoids importing the entire historical table into Power BI there by reducing the need to handle large volumes of data resulting in less load times and avoiding memory or spool space issues.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[M_Andersson]

Thanks for reply. That the way i do today. I load a staging table to teradata through python and do joins in teradata and take it to powerbi desktop. The problem i have is that i can only write to a kind of lab environment and my lab cant connect to powerbi service. I have the tables in diffrent dataflows, but to join them in pq is way to heavy... so thats why i wonder if there is another way. other way i have to learn to live with manual refresh 😄

[v-nmadadi-msft]

Hi  @M_Andersson 

If Power BI Service can’t directly access your lab, but your scripts can access both SharePoint and Teradata, consider moving your ETL process to a Data Pipelines. This could perform the merge and write the result to a service-accessible location (like Azure SQL DB or a Lakehouse) on a schedule  enabling automated refresh.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[BhavinVyas3003]

To join your SharePoint customer list with the large Teradata table efficiently, it's best to use a native SQL query that matches CustomerNum and filters rows where the given reference date lies between StartDate and EndDate. This join should happen on the Teradata side itself through query folding, so that you don’t need to load the entire Teradata table into Power BI. You can either push the SharePoint list into a staging table or pass it as a parameter using Value. NativeQuery, which ensures the filtering is done in Teradata and avoids performance issues.

[M_Andersson]

Ok, dont know exactly to stage an excelfile from sharepoint to teradata without loading it to a table. Best is of course to do it on terdata side. according to parameters. How can i do that when there is also date involved and start and end date? Should of course be dynamic så if i want to withdraw new data it should work automatically