Report Design with PBI Desktop: best practice to access Analysis Services cubes via gateway

[Michael_Mertens]

Hi everyone,

we are using PBI Premium and also SSAS on prem via gateway.

When I connect to the model from PBI desktop I can either use the PBI dataset (which is not much more than a “wrapper” around the live connection) or connect to the SSAS server on prem directly.

I’m wondering what the pros/cons of either method are and which one we should use:

1. Connect via dataset -> feels strange, connection goes from prem to cloud and back to prem via gateway
2. Connect to SSAS -> additional effort for publishing needed (replace SSAS source by dataset)

Can some who is using SSAS on prem via gateway share how they are working with it and why?

Thanks & Regards

Michael

[Michael_Mertens · Accepted solution]

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

[GilbertQ]

Hi @Michael_Mertens 

No matter which mode you use it will have to connect from the Power BI Service via the Gateway to connect to your On-Premise SSAS Server.

I have used question 1 (in your example) for years without any issues and works very well.

[Michael_Mertens]

Hi Gilbert,

thank you for your answers. However, I find that it is not so easy. 

I did more analysis, and as I already guessed, the data connection does not through the cloud, even if I use the dataset. PBI Desktop just asks fetches the metadata and tries to connect to the SSAS server directly.

In our company, it is not allowed to access the database server with SSAS directly. We are using the HTTPS endpoint. 

I don't see a way to change the server address if I connect to the dataset.

I agree, if the connection as dataset works, it is the better option. 

I'm afraid we need to take the other route with its publishing-difficulties.

Regards

Michael

[GilbertQ]

Hi @Michael_Mertens 

Yeah you are correct that it would have to go via the gateway directly to the SSAS Server. As far as I know currently there is no other way to connect.

[GilbertQ]

Hi @Michael_Mertens 

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]

Hi @Michael_Mertens 

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

[aj1973]

Correct, no need for Gateway when connecting to ASServer.

But What is the need for the Dataset "thedata" ??? What is your prefered connection Mode? What is your point?

Sorry am just trying to better understand and so I can help you

[Michael_Mertens]

Hm, it is difficult to understand for me where I might do things differently from others. I thought everybody using the gateway would have to perform exactly these thoughts that I try to document.

So, the scenario is:

I have an ssas server, I called abc123.internal.de in the example.

I have a Premium capacity where the reports are living. So I need a gateway to access abc123.internal.de (in live mode). 

So a dataset is created, I called "thedata". It doesn't contain data because I use live mode, not import mode. 

I think this is all standard and simple,

Now the question is how I create reports with Power BI Desktop. There the two alternatives come into place.

1. seemed to have the problem that the data is routed through the gateway - but I'm not sure about it.

2. has the problem that before publishing there is the need to switch connections from AS on prem to PBI dataset

Therefore 1. is the favorite but I'm still trying to fully understand it. 

Regards

Michael

[aj1973]

Okay, so this deffinetly not correct 

and your abc123 is in SSAS therefore you need a Gateway.

I would listen to @GilbertQ and do as he said:

Now choosing the type of connection mode would depend on the needs, and it is not true that live connection is the standard... All modes have their Pros and Cons.