cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nokia447
Frequent Visitor

How to connect to CSV files on server at datacenter?

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? 

 

powerbi.png

 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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).



2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

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).



2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

nokia447
Frequent Visitor

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?image.png image.png

SpartaBI
Community Champion
Community Champion

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.


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors