Publish composite model to the service based on a powerbi dataset and excel

[Spiedo]

I successfully combined the information of an existing powerbi dataset with an excel. 

I did this by creating a local model.

Now i want to publish it to the powerbi service but there is no cloud/on-premise connection defined to the powerbi dataset.

The service proposes a connection string, i edited it underneath a bit because of anonimization but it gives you an idea

powerbi://api.powerbi.com/v1.0/myorg/DED%20NL%20-%20Inkoop

When defining a cloud connection using analysis services as the connection type it does not work.

Is it possible to setup a cloud connection to an existing powerbi dataset?

[ibarrau]

Hi. In order to create a new dataset that contains another PowerBi Service dataset and an excel file, you have to work with composite models. First of all, connecting to a semantic model in cloud: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets

That will create a report with live connection. However, once you add another source it will ask to convert the live connection to Direct Query to work with composite. You can read about it here: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

After you have done all with Desktop, make sure the Tenant Admin portal settings will let you work with composite at Service. Publish your report. Go to dataset settings and make sure the excel connection is configured (it's recommended to keep it in sharepoint). The connection to the dataset will ask for your Microsoft work account credentials.

I hope that helps,

[Spiedo]

Hi Ibarrau, thank you for the answer. I am not facing problems building a composite model with a powerbi dataset.

The problem I get is as soon I deploy it to the powerbi.com service the powerbi dataset is transformed into a connection. So it desires a connection to be mapped even though the dataset is already simple in the powerbi cloud within the tenant.

Although it is direct query, and although the tenant settings are oke, I get the following odd thing. The powerbi dataset (semantic model) is shown with a cross in this picture

[ibarrau · Accepted solution]

Ok. Can you share more about the other datasource? because the first post talked about excel, but right here I'm seeing two analysis services. That can be a powerbi dataset, a azure analysis services or even a sql server analysis services. 

On the other hand, consider that if you are using an on premise data gateway to connect the data, consider that power bi dataset is a cloud source. When you work with both cloud and onpremise, you need to clarify that to the gateway in order to let them understand that not all the sources should be checked by gateway.

Go to settings on the top right corner > manage data gateways. Then pick yours and:

I hope that helps,

[Spiedo]

Hi Ibarrau the specific setting in the gateway you mentioned made the difference. The powerbi dataset now also turns into a green checkbox ! Thank you very much !