How to connect to specific tables in Power BI without loading the entire table set until a specific query with adjustable parameters is executed?

Question

I'm working on a Power BI report that requires me to connect to a specific table in my SQL database. However, I don't want the entire table set to be loaded automatically when opening the report as this can take a lot of time and resources.

The idea is that my service team has the option to enter a specific EAN number, and only after this EAN number is entered should a specific query be run on that table. This way we can optimize the performance of the report by loading only the relevant data based on the user input. The output should be the result of the query that is transferable to an Excel or CSV.

1. Is it possible to connect to specific (multiple) tables in Power BI without loading the entire table set directly?

2. And if so, how can I ensure that a query is run against that table set only after the specific EAN number has been entered by the team?

3. Is it possible to use DECLARE to perform a powerBI direct query?

The following variables need to be dynamic (EAN and year) so the service team is able to adjust these to their needs. The service team only has access to Power BI and has limited technical abilities, so I need to run everything as much as possible in the background.

The query we are using is:

DECLARE @EAN nvarchar(18) = 'xxxxxxxxxxxxxxxxxx'
DECLARE @Year int = 2023

SELECT DATEFROMPARTS(YEAR(iucr.StartDate)
,MONTH(iucr.StartDate),1) AS 'Month'
,o.Reference, o.Name
, EAN, iucr.StartDate
, iucr.EndDate
, ISNULL(iucr.UsageLDN,0) UsageLDN
, ISNULL(iucr.UsageODN,0) UsageODN
, iucr.Price AS Marktprijs
, pp.Price AS GvO
, ISNULL(cp.Price, gp.Price) + ISNULL(rb.Price,0) AS Opslag
, ISNULL(cpp.Price,gpp.Price) + ISNULL(rbo.Price,0) AS Afslag
, (ISNULL(UsageLDN,0) * (ISNULL(iucr.Price,0) + ISNULL(pp.Price,0) + ISNULL(ISNULL(cp.Price, gp.Price),0) + ISNULL(rb.Price,0)))
- (ISNULL(UsageODN,0) * (ISNULL(iucr.Price,0) + ISNULL(ISNULL(cpp.Price,gpp.Price),0) + ISNULL(rbo.Price,0))) AS Bedrag
FROM CRM.InvoiceUsageCalculationRow iucr
INNER JOIN DWH.EnergieleveringContract ec ON iucr.ContractId = ec.ContractId
INNER JOIN dwh.Connection c ON ec.ConnectionId = c.ConnectionId
INNER JOIN dwh.Organization o ON ec.OrganizationId = o.OrganizationId
LEFT JOIN (SELECT * FROM DWH.PropositionPrice WHERE PriceComponentType = 'Premium') pp ON ec.MeeliftContractPropositionId = pp.PropositionId
LEFT JOIN (SELECT * FROM DWH.PropositionPrice WHERE PriceComponentType = 'GenericSurchargeLDNBase1') gp ON ec.PropositionId = gp.PropositionId
LEFT JOIN (SELECT * FROM DWH.PropositionPrice WHERE PriceComponentType = 'GenericSurchargeODNBase1') gpp ON ec.PropositionId = gpp.PropositionId
LEFT JOIN (SELECT * FROM DWH.ContractPrice WHERE PriceComponentType = 'GenericSurchargeLDNBase1') cp ON ec.ContractId = cp.ContractId
LEFT JOIN (SELECT * FROM DWH.ContractPrice WHERE PriceComponentType = 'GenericSurchargeODNBase1') cpp ON ec.ContractId = cpp.ContractId
LEFT JOIN (SELECT * FROM DWH.ContractPrice WHERE PriceComponentType = 'ResellerBasePremium') rb ON ec.ContractId = rb.ContractId
LEFT JOIN (SELECT * FROM DWH.ContractPrice WHERE PriceComponentType = 'ResellerBaseOdnPremium') rbo ON ec.ContractId = rbo.ContractId
WHERE 1=1
AND C.EAN = @EAN
AND YEAR(IUCR.StartDate) = @Year
AND iucr.AdvancePaymentIsCredi
ted = 0
ORDER BY iucr.StartDate

I tried connecting with the database and run a specific EAN. It showed an error (maybe due to DECLARE variable) and I could not find solutions for the table selection and then specifying by user input. The query in running normally in SSMS but running with DELCARE gives the following error:

Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near ')'.

The desired output looks like this in SQL and i want the same in powerbi but then wit dynamic EAN's and year.

Answer 1

Using Paginated Reports in Power BI, you can use arbitrary TSQL and have explicit control over the queries, which are only sent when the user provides the parameter value.

Using Power BI interactive reports with DirectQuery, you will not be successful using complex queries. The tables in your semantic model should map to tables in the underlying database. See DirectQuery model guidance.

Otherwise you can use Import mode, and load the data on a schedule. And you can use Incremental Refresh to only refresh newer data.