Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Ask advice for using gateway and optimization Power BI

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

Need Help
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    Hi Anonymous ,

     

    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.