Issues with refresh, Azure database & OneDrive excel files

[user10]

Hi,

This seems like it should be a simple task but I've had no success trying various approaches.

I have a pbix file with 3+ web data sources (excel files on OneDrive for Business), and 1 connection to an Azure database. I'm trying set up refresh so this doesn't have to be done manually.

First approach: Add azure connection to pbix, publish, then add web connections to pbix and re-publish.
1. I added the Azure connection as DirectQuery and published the pbix. I updated crednetials on Power BI web... this seems to work.

2. Added web connections, change all connections to import mode and publish. When I go to check the dataset settings to update credentials, it does not take the credentials I enter: I suspect this is
because the data source is specified as 'unknown'.

Second approach: Add all connections at once and set up scheduled refresh.
1. Add all data connections and publish (import mode). I only have the option to set up refresh by installing a gateway connection.

My questions are:
1. Which is the best approach? My goal is to have the dataset refresh automatically.
2. If a gateway is required, do I use a personal gateway or enterprise? Upon reviewing this post, I think personal..? http://community.powerbi.com/t5/Integrations-with-Files-and/Unable-to-schedule-refresh-
with-sharepoint-and-database/m-p/73135/highlight/true#M5639

[v-micsh-msft]

Hi user10,

I just tested with Import mode, with Azure SQL database and Excel in OneDrive for Business connected both, the refresh works without any issue.

What should be take noted here is that we need to choose the Get Data Type to Web, when connecting to Excel files in OneDrive For Business, follow this guide:

Use OneDrive for Business links in Power BI Desktop

After published, editing the credentials for both (For Azure, choose basic. For Excel in OneDrive for Business, choose oAuth2 ).

Please post back if you need any further assistance on this topic.

Regards

[user10]

Thanks @Greg_Deckler

Hi @v-micsh-msft,

Thanks for testing out the config.  This is one of the issues I've run into.

We have an enterprise gateway set up and I've been made an administrator but al the options are greyed out and it tells me I need to install a gateway.  Any idea why this is?

Thanks

[v-micsh-msft]

Hi user10,

There is no need to configure a gateway here.

The connection type is connect directly. If it connects with a gateway, the option would be changed to

Power BI gateway (personal)

or

Data gateway with your data source (known as the On-premises data gateway)

Showing Connect directly means that this Dataset didn't require a gateway.

I suggest that we remove the testing dataset, and start a new data connection from scratch. Follow the guide I shared above to configure the Excel file path.

If this is still not working, then please show us the M code in Advanced Editor of Power BI Desktop, we will help to check it out.

Regards

[user10]

Hi @v-micsh-msft,

Thanks again for the reply.  I have other datasets that only use excel files on OneDrive and I don't have any issues setting the Connect Directly setting.

I definitely have to be missing a step here in the setup; I've followed your suggestion of removing the datasets and re-creating but still get the same result.  I think this is because Power BI Desktop is forcing me to switch from DirectQuery to Import mode when I add the web excel files.... ?  If this is indeed the issue, how can stop it from making me switch modes?

Here are the steps I followed to re-create the data set:

1. Create new Power BI file.

2. Added Azure Sql database as data source, select DirectQuery option.

3.  Add 2 web data sources for excel files, select 'Basic' option.  At this point, Power BI tells me that I need to switch to import mode because I am connecting to an excel file:

4. Apply the changes to query editor and close.

5. Create 2 chart visualizations to check the data.  Publish the file.

6. In Power BI Web, I try to set the dataset settings but I have no options to do so.

Thanks again for any assistance you can provide.  I've been struggling with this for awhile now.

[v-micsh-msft]

Hi user10,

The code is OK, which is similar to mine.

I also have the switch from Direct query to Import mode reminder, but the refresh works at my side.

Have you tried to seperate Azure Database and the Excel in OneDrive for Business to check this out?

Specially for the Azure source, as you have successfully configured the Excel in OneDrive for Business before, I suspect the issue should be related with the Azure database side.

I will discuss this with my colleague, and will update here if I get any helpful information on this.

Regards

[user10]

Hi @v-micsh-msft,

I've tried both types of connections individually in different pbix files and both work fine.  That's when I went to testing the 2 approaches I initially posted here and couldn't get working.

I tried my original first approach again (as outlined in my original post) but used a specific sql statement to see if data was the issue since there are columns with images in the table, but this doesn't seem to have any impact.  Since I know Power BI does not process binary data, I am wondering if I need to use a view of the table which excludes the columns with binary data, in order for this to work.  Thoughts?

I still get that 'Unknown' specification for the excel datasources though.. any thoughts on that?

It has been suggested that I create 2 separate data sets for the Azure database(1) and excel files(2) and build the reports in Power BI Web but that does not work since I need to create relationships between the data.  Thoughts on other workarounds?

Thanks

[v-micsh-msft]

Hi user10,

Apologize for the late response.

I also tested this with my other teammates, and the dataset refresh also works there.

To further analyze this, please take Fiddler traces when publish the PBIX file, and configure the Refresh credertials part.

We will check this out and if necessary, would involve some senior engineers to take a further invertigate on this.

For the report workaround, would it be possible to create single slicer based on the column used to build the relationship, and use that to filter the needed data?

Regards

[Greg_Deckler]

If all of your data is in the cloud, you should not need to use a gateway, the "cloud gateway" will refresh your data.

Personal versus Enterprise is a matter of need. If it is for your own personal use, you can use Personal. If it is something that should be shared by many people, Enterprise. Although, I tend to default to Enterprise regardless.