Microsoft Power BI helps organizations empower everyone to make every decision based on data. We are excited to announce that we have Egnyte for Power BI Data Connector, so power users can connect and get easy access to Egnyte data and can query shape, and select data from multiple files to create dashboards and reports in Power BI Desktop.
As you publish these reports to Power BI Service, you will be able to auto-refresh data through Microsoft Gateway and can collaborate with the other users so they can access the latest data.
Initial Setup:
Prerequisites:
- Microsoft Power BI Pro or Premium license for both admins and users to access the content of the report
- Standard on-premises gateway is recommended if you want to collaborate with other users. The link is here.
Refreshing doesn’t work when the computer on which the standard gateway is installed is offline.
Computer Setup:
- If using Personal gateway - Create a "[My Documents]\Power BI Desktop\Custom Connectors" directory AND/OR
- If using Standard gateway - Create a "\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors" directory.
- Get EgnyteConnectorProduction.pqx file here and place it in the "Custome Connectors" directory above.
- Close all instances of Power BI and restart Power BI Desktop.
Note. If you don't see the Egnyte connector in the Power BI, try putting the connector file in both locations mentioned above in step 1 and step 2, and try again.
Power BI Gateway Setup (For refreshing data):
An on-premises data gateway (standard gateway) is software that you install in an on-premises network. The gateway facilitates access to data in that network. You can install a gateway either in personal mode, which applies to Power BI only, or in standard mode. We recommend the standard mode. Note. On a single computer, you can run a single instance of the personal and standard gateway.
- Install On-premise (Standard) and Personal gateway and follow the process and Microsoft wizard.
- Login using your admin credentials and Configure and name your Gateway Connection (The user installing the gateway must be the admin of the gateway)
- Add gateway admins who can also manage and administer other network requirements.
- Use the gateway to refresh an on-premises data source.
- Troubleshoot the gateway in case of errors, and make sure it is online.
Power BI Desktop (For Creating reports):
- Go to Windows.
- Install Power BI Desktop if you do not have it already.
- Login with the same Microsoft credentials where you have a Power BI Pro or Premium license.
- Enable the "(Not recommended) Allow ..." in Power BI Desktop (under File | Options, and settings | Options| Security| Data Extensions) to enable integration.
- Click "get data."
- Select the "Egnyte connector" from the list.
7. When clicked "Connect," - this will open a dialog and ask you to enter your Egnyte domain. Use Domain in the form here - "https://<YOURDOMAIN>.egnyte.com" and click OK.
8. Follow the Sign-in process and use your Egnyte credentials to complete the sign-in process.
9. Once you signed in, you will see the navigator table with your Egnyte files as below. You can select 1 or more files (supported formats are XLS, XLSX, CSV, and Text files)
10. Creating visualizations is not part of the integration - at this moment, mingle with the data as per the needs of your organization and create reports
11. Save the report/dashboard and create publish
Power BI Service (for Collaboration):
- Access the link which you have been provided in Step 11 and open Power BI Service in your browser. Alternatively, you could also access this report via browser, using the same account login (as used in the desktop app), and look for the published data there.
- Ensure your Gateway is running and is online and connected. Microsoft guide here
- On the machine where the gateway is running, enter the gateway in Windows search.
- Select the On-premises data gateway app
- Log in with Microsoft credentials as used above.
- Go to Connectors - and Ensure you have "Egnyte" showing in the dropdown list and select.
- Ensure that in the folder settings, you have EgnyteConnectorProduction.pqx file if using Personal Gateway OR, if using Standard Gateway (Note the directory structure difference as mentioned in Section A).
8. Go to your dataset on the left-hand side, and click on "scheduled refresh." In this example, we have used the "Life Expectancy" report, which is on the left-hand side, and "scheduled refresh" for illustration
9. Configure. If you are using a Personal gateway, select that checkbox and Apply.
10. If you are using Standard Gateway - ensure you select "data sources" and select the dataset you want to refresh for the report/dashboards. See the next 3 screens.
a) Configure data source setting, ensure you have valid credentials, and "connection is successful"
b) Ensure the above dataset is connected to the standard gateway you are using as below.
c) Ensure the data source credentials section when you scroll down, and there is no error.
11. If you want to apply auto-refresh, please configure. Note. Auto-refresh frequency depends on the type of Power BI license you have from Microsoft - Pro or Premium.
If you are using a personal gateway, and if your computer is offline, a refresh will not succeed. Additionally, when using a standard gateway, it will fail if the server on which it is installed is offline. Hence if a refresh is failed, please look into the status of the gateways to troubleshoot.
Remove Authentication for the Egnyte Domain:
Go under Power BI Desktop: File | Options, and settings | Data Source Settings | Clear permissions.
Additional factors and Power BI Knowledgebase
Per Microsoft documentation, you can install up to two gateways on a single computer: one running in personal mode and the other running in standard mode. You can't have more than one gateway running in the same mode on the same computer.
Personal Gateway: It allows one user to connect to sources and can’t be shared with others. An on-premises data gateway (personal mode) can be used only with Power BI. This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share any data sources with others. If you want to install this on your machine, and it is often offline.
Standard Gateway: It allows multiple users to connect to multiple on-premises data sources. You can use an on-premises data gateway with all supported services, with a single gateway installation. This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources. Note. you could use a Windows server or Windows machine that works all the time so that gateway is always online.
During tests, if multiple developers are working on the same MS accounts, it is essential to be sure whose personal gateway is used. If you, for example, create a new version of the connector but it right now is pointing to a different computer with a different version of the connector, then something will go wrong. Always be sure that the gateway is pointing to your machine. A similar issue is with a standard connector. If you want to use it with your source, be sure that it is using the same connector version so, recommend using the right naming conventions in settings and is online.
Please also note if you are using VM machines, then it could be looking at the different folder where .pqx file is located in order to find it and load it for you on the Power BI desktop, it depends on your setup.