Combined datasets ulpoaded in the service

[Jonathan45]

Hi,

I am looking for best practices for uploading a report in the service which contain multiple dataset linked together.

Let say I am building a report from 2 diffrents dataset from 2 differents workspaces. ( I have build permissions in both ), I combine them in a report so I don't have to recreacte all the mesures and data gathering from each dataset. 

For example purpose let say:

Dataset 1: all data sources are from oracle.

Dataset 2: has mixed sources oracle and excel/csv imported from a sharepoint.

In the documentation we can see this:

Do I understand correctly that my report published in the service won't show the data from the dataset 2 because it has one of the data source in import excel? and If it's the case in a large organisation if we want to promote reusing dataset, we should not use data from sources listed above?

thanks

[Jonathan45]

With further testing I know now what I did wrong and was not aware it would not work on the service.

I was connecting the dataset 2 when I was in the dataset 1 in import mode (with the data view and the possibility to edit query).

What I did and now it's working properly is I create a new report, I connect both dataset in direct query with the get dataset connector, do a report and then publish it on the service. 

[Jonathan45]

Hi @collinq ,

Thanks for your reply! In my example, the dataset that have an excel file is in import mode. It's when I am creating a new report and connecting the two dataset together that power bi desktop is asking to be connected to those dataset in direct query. 

Everything is working in the desktop, but when i publish it on the service, I have an error for the dataset that have an excel source. I tried an other scenario with 2 dataset that only have oracle source and I don't have this error problem in the service.

In addition, if I connect to the dataset that have an excel file an do a report only based on this dataset then publish it, I don't have a problem, it's only when I want to link another dataset in my report that i have this issue.

thanks 

[collinq]

Hey @Jonathan45 ,

Is the Excel file accessed through a gateway? 

Is this the scenario - that Dataset 2 has BOTH Excel (import) and Oracle (direct query).  And then you are connecting to THAT dataset?  If so, I think that you are being asked to use direct query to the already established dataset?

[Jonathan45]

Hi @collinq 

The excel file is hosted on a sharepoint, and the dataset is connected to this sharepoint so no need here to have a gateway when only working with the dataset 2.

---

@collinq wrote:

Hey @Jonathan45 ,

Is the Excel file accessed through a gateway? 

Is this the scenario - that Dataset 2 has BOTH Excel (import) and Oracle (direct query).  And then you are connecting to THAT dataset?  If so, I think that you are being asked to use direct query to the already established dataset?

---

The oracle source is also in import mode in the dataset 2. Power bi is only asking me to be on direct query mode when I want to link dataset 1 and dataset 2.

In fact, if we forget my scenario, I did some different tests and when trying to connect 2 dataset together, Power bi desktop is always asking to be switched to direct query. That's why I thought in my first post that we should not use one of the sources mentioned if we want to share an reuse multiple datasets together.

[collinq]

Hi @Jonathan45 ,

In Power BI you can have mutliple datasources using multiple datasets in one report.  That is one of the powerful bits of Power BI. 

So, the issue is really the TYPE of query you are performing.  In your screenshot it is indicating that you are looking at DirectQuery limitations.  Which, then it is true that your second dataset will have issue if you are trying to direct query with Excel.  But, why not import that Excel file and NOT use Direct Query for the Excel bit?  (You may or may not need a gateway for the Excel file, depending upon where it is located.)