How to connect to CSV files on server at datacenter?

[nokia447]

I our datacenter we have our ERP system installed on one server. The ERP on regular basis extract data as .csv files and stores the files in a folder. 

I want to create a PowerBi report that connects to that folder on the server. Then we want to publish the PowerBi service and have scheduled refresh of the data. 

We have installed the Gateway on the on-premises server - but now I can't figure out how I can build the report in PowerBi desktop on my Windows PC, I simply do not know how to find the folder. Do I need to mount the server as a network drive on my PC?

Or do I install PowerBi desktop on the server and create the dataset etc. from remote desktop?

I guess that once it is successful for me to run it on my local PC, the gateway on the server will in the end make it possible for the PowerBi service to reach the csv-files on the server? 

[SpartaBI · Accepted solution]

Hi @nokia447,

Regarding the RDP
If users need to create reports based on the same data model, you should create a single dataset on the server and then publish it to the Power BI Service. This will create a dataset (that will be refreshed using the data gateway). Users will then be able to connect to the dataset (see here) and design different reports.

If each user needs to create his own data model, you have a couple of options:
1. Make sure that the users connect to the files using a path that can be recognized from on the server (create identical local paths with sample data on the users machines or use a connection to a shared folder that can be identified from the server itself by the same hardcoded path as from the users machines).
2. Use dataflows to upload the csv files to the Power BI Service (using the data gateway). You can set a scheduled refresh for the dataflows. users will then be able to connect to the csv files hosted on the cloud.
3. Note that, using Power BI Desktop, only the connection to the csv files and Power Query transformations must be done on the server. After you'll load the data, you will be able to continue the work on your local machine until the next time you'll need to make a Power Query change...

Regarding the gateway & connection
You should create the connection in Power BI Desktop, then publish it to the Power BI Service.
This will create a dataset object with a datasource. You will need to connect the datasource to the data gateway. See the example here (This is for an on prem SQL Server, but the steps are the same for csv files).

Showcase Report – Contoso By SpartaBI

     

[nokia447]

Thanks, I'm not sure that users that are about to create/design the PowerBi reports in are allowed to login through Remote Desktop to the server it self.  I can now see the gateway in the PowerBi service, and it is online. But when I want to create a dataset, I can't find the Gateway among the choices, shouldn't it be here? 

[SpartaBI]

Hi @nokia447,

Once the dataset will be published to the Power BI Service, the data gateway will use the same connection string you defined in Power BI Desktop to retrieve the data from the file system.
You'll need to make sure the file system path, defined in Power BI Desktop, can be reached from the server.

The best course of action will be to install Power BI Desktop on the server, then develop using RDP.

Showcase Report – Contoso By SpartaBI