Help using multiple datasets - a FACT table from SQL, but a lookup table from excel/sharepoint

[nickmason1973]

I'm sure this is simple, but...

I'm trying to publish a report on the PowerBI service.

The report is powered by a huge fact table out of SQL. That's accessed through a data gateway. So far so good.

But the report also refers to an excel file which provides information on one of the dimensions of the fact table.

In PowerBI desktop there's no problem with this - the dimension tables are a combination of hard-coded tables in PowerBI or csv/.xls sources.

But I don't seem to be able to get the report to be able to reference the csv/.xls files. So it won't refresh. I've tried uploading them to the service as a dataset, but then I don't seem to be able to reference them when building the report in PowerBI desktop (I'm not allowed multiple data sources?)

Is there a workaround where I can import the csv/.xls files so that they appear as hard-coded tables?

I'm sure I've got some architectural misunderstanding here, but can't see what!

thanks

[Burningsuit · Accepted solution]

Hi @nickmason1973 

That's a very common setup, Fact table in SQL and Dimension tables in Excel, its absolutely supported in Power BI.

The issue here is the Gateway and the location of the Excel files.

Your Excel files need to be added to the Gateway, so that they can be accessed when Refresh happens (Ignoring the fact they may not change). You need reference the Excel files from a path both you and the Gateway can access, and then put that path into the Gateway. If you have many Excel dimensions that could be a shared folder on a server both you and the Gateway can access, then add the folder to the Gateway.

An alternative, if you use Onedrive, is the place the Excel files on Onedrive. As a cloud based system, data on OneDrive does not need a Gateway, just the credentials to access the data.

See : Install And Configure Data Gateway For On-Premise Excel Files In Power BI (c-sharpcorner.com)

and : Refresh a dataset created from an Excel workbook - cloud - Power BI | Microsoft Docs

Hope this helps

Stuart

[nickmason1973]

That is helpful, as it's what I thought... but I'm still running into problems.
I'm trying to avoid using the gateway for non SQL stuff. Mainly because it's all in serious IT control!
So I'm trying to use the OneDrive approach - where the excel is saved to a sharepoint site. But it seems that (when I look at the dataset settings in the PowerBI service) that is still being included in the gateway. 

[Burningsuit]

HI @nickmason1973 

That's possibly because you're specifying the local copy of the Onedrive File, on your hard disk - so it still needs a Gateway.

You need to specify it as a web data source, and point to the Excel file,

See; Use OneDrive for Business links in Power BI Desktop - Power BI | Microsoft Docs

Hope this helps

Stuart

[nickmason1973]

Yup - that was it. Thank you.