Ask advice for using gateway and optimization Power BI

[Nikita4More]

Hi dear community,

I would like getting more information for this usage case.

I'm working on a project with Power BI which have to get data from a SQL Server, and now we don't
have a gateway.

I would like to know :

If I get gateway, isn't necessary to have a user for connection to the gateway and the database.
If the user can access to the report without access to the database
We don't have any datalake, neither datawarehouse, I'll get access to the data by Power query, Power BI,
I am afraid that the model would be too loud, there are best way to optimize, may be the gateway would be feel the most effort for uploading the data instead my computer.

I really thank you for help and suggestion.

Best regards,

Nikita4More

[FBergamaschi]

Hoping I understood your question:

You need a gateway if your SQL is on-premises.

Yes, you will need to indicate a username and password (domain or batabase credentials, that depends on the SQL settings) so that Power BI will provide them to the SQL database. This user is usually not a person user but a serviceaccount.

The users that will consume the report will need to have access to the worskpace and (if the semantic model is in  a sperata workspace) also to the semantic model.

But I am afarid I did not get exactly what you are askign, what do yo mean by "I am afraid the model will get too loud"?

best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[burakkaragoz · Accepted solution]

Hi @Nikita4More ,

Great questions about gateway setup and optimization. Let me break this down for you:

Gateway and User Access: Yes, you can absolutely set up gateway access so users can view reports without needing direct database credentials. Here's how it works:

• The gateway uses a service account to connect to SQL Server
• Users access the Power BI reports through their Power BI licenses
• They never need SQL Server access directly

For your optimization concerns: You're right to think about performance early. Without a data warehouse, here are some practical approaches:

Gateway will help with performance - your computer won't be doing the heavy lifting anymore. The gateway server handles data refresh, which is much more efficient.

Power Query optimization tips:

• Use query folding wherever possible (avoid custom columns that break folding)
• Filter data early in your queries, not later
• Consider incremental refresh if you have large tables with date columns
• Import only the columns you actually need

Model optimization:

• Use DirectQuery for very large tables if real-time data isn't critical
• Consider a composite model (some tables imported, some DirectQuery)
• Aggregate tables for summary-level reporting

Quick question: How large are we talking about? Number of rows and tables? This will help determine the best approach.

The gateway is definitely worth it for moving the processing off your local machine and enabling scheduled refreshes.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[Nikita4More]

Thank you both for your answers.

My english also is not perfect, howerver you both understand my mind.

Thank you @burakkaragoz  for your advices.