Forum Discussion
Resolver IReport Design with PBI Desktop: best practice to access Analysis Services cubes via gateway
I would like to sum up this thread and also add some words about the behaviour in Excel.
If you use Analyze in Excel, it works similar to accessing a dataset from Power BI Desktop. It is more transparent what happens, because you can have a look at the connection properties in the Excel file that is generated by "Analyze in Excel".
The screenshot shows that the connection is based on the gateway configuration.
So again there is the assumption that a client can use the same connection to acess the server that is used by the gateway.I would call this at least a "design gap" that it is not possible to add an https-endpoint to the configuration that is used by clients to access an on premises server.
In respect to Excel online I would call it even a "design flaw", because the local adress definitely can't work. The gateway would be needed!Summary for connecting to SSAS on prem through the dataset in Power BI service.:
Power BI Desktop: works, if the client has access to the SSAS box directly (and OLE-DB driver installed)
Excel Desktop: works, if the client has access to the SSAS box directly (and OLE-DB driver installed)
Excel Online: doesn't work
Super User
I would just be using the SSAS via the On-Prem gateway. The reason being is that there is a single connection to the same SSAS. Having multiple ways to connect can create confusion for users.
It also makes it easier to manage when there is one data source.
- Michael_Mertens
Hi GIlbert,
yes probably that is the route to go, unless we encounter any problems.
I tried this today and didn't find any of the requests in the gateway logs. So I'm wondering whether Power BI Desktop is so clever to use the dataset only to get the metadata and then query the on premises server directly.
To make it more understandable, let me give an example:
AS Server: abc123.internal.de
Dataset: thedata -> connected to a dabase of abc123 through gateway
With PBI Desktop I can use either:
1. Type: Dataset, Source: thedata
2. Type: SSAS live, Source: abc123.internal.de
Now my guess is, that even using method 1, the data is fetched directly from abc123.internal.de, so that the gateway is not used. PBI Desktop would just use the dataset "thedata" to get the metadata information.
Can someone confirm this? Then alternative 1 would be conceptionally better and even as efficient than 2.
Regards
Michael
- aj1973
Community Champion
Both methods are going to query the source through the Gateway. It's the connection mode that is different Method 1 could be using Import Mode while method 2 is using Live connection mode. Both methods have Pros and Cons therefore the effenciency would depend on the need .
- Michael_Mertens
Hi Amine,
maybe my explanation was not very good - I'm talking only about the live mode.
Method 2 definetely doesn't use the gateway at all. It is purely on premises, just a client accessing a server. No cloud involved.
Method 1 uses the cloud dataset and I'm just wondering whether the system is clever enough to fetch data directly from the server without routing through the cloud.
Regards
Michael
