Forum Discussion
Connection few reports (SQL, Excel, Semantic model)
Hello,
I would like to connect 2 reports together.
Data source are:
for "A" report:
Excel on TEAMS
Snowflake database
PBI semantic model
for "B" report:
SQL database
Excel on TEAMS
"A" report.
This is shown in the graphic below.
Two reports works separately, but when I connect report "A" and "B" then On premises data gateway is turned off and I cannot use it.
Is there any way to combine these reports?
I made a few tests and my conclusion is:
1. The connection configuration does not work: SQL+Semantic model from another report + Excel on TEAMS
2.. However, the connection does work: Semantic model from another report + Excel on TEAMS
Hello The_Crow
hello The_Crow
Thanks for getting back with details
the problem lies with with personal gateway only.
The Personal Mode Gateway does not support combining data from multiple sources when one of those sources is another Power BI report (semantic model). This limitation arises because Personal Mode lacks the ability to manage complex mashups involving both cloud and on-premises data sources.
• When you try to connect a semantic model from another report along with SQL Server and Excel on Teams, the gateway cannot handle this combination due to its restricted functionalityMixing cloud-based data sources (like Power BI semantic models) with on-premises data sources requires advanced capabilities that are typically supported by the Standard On-Premises Data Gateway, not the Personal Mode Gateway
https://radacad.com/the-power-bi-gateway-all-you-need-to-knowplease accept this answer and give kudos if this is helpful
5 Replies
Hello The_Crow
could you please make sure you are on the latest version of gateway
and check this too
Power BI’s On-Premises Data Gateway can be configured to support merging or appending on-premises and cloud data sources in a single query. However, this requires enabling the setting “Allow user’s cloud data sources to refresh through this gateway cluster” in the gateway configuration. Without this setting, the gateway will not execute queries that combine both types of data sources
https://learn.microsoft.com/en/power-bi/connect-data/service-gateway-mashup-on-premises-cloud
Hello nilendraFabric
I'm using On Premises data gateway Personal Mode 3000.254.4.
I could not find option: “Allow user’s cloud data sources to refresh through this gateway cluster”.As I mentioned, I can download the data using SQL and e.g. excel, but it stops when connect another PBI report.
This is not in description in those 2 links you attached.Hello The_Crow
hello The_Crow
Thanks for getting back with details
the problem lies with with personal gateway only.
The Personal Mode Gateway does not support combining data from multiple sources when one of those sources is another Power BI report (semantic model). This limitation arises because Personal Mode lacks the ability to manage complex mashups involving both cloud and on-premises data sources.
• When you try to connect a semantic model from another report along with SQL Server and Excel on Teams, the gateway cannot handle this combination due to its restricted functionalityMixing cloud-based data sources (like Power BI semantic models) with on-premises data sources requires advanced capabilities that are typically supported by the Standard On-Premises Data Gateway, not the Personal Mode Gateway
https://radacad.com/the-power-bi-gateway-all-you-need-to-knowplease accept this answer and give kudos if this is helpful
Hi The_Crow .
- Issue: Power BI does not support On-Premises Gateway (SQL) + DirectQuery to a Semantic Model. This disables the Gateway.
- Works: Connecting Semantic Model + Excel on Teams works because both support DirectQuery.
- Fixes:
- Use Import Mode instead of DirectQuery for the Semantic Model.
- Pre-aggregate SQL data into a dataset and connect it separately.
- Use a separate dataset for SQL and merge it later in Power BI Service.
