Power BI Set Up - ECi Hosted / Managed By Your IT

This forum post details the steps to connect your ECi hosted e-automate database to the Power BI templates. These steps will need to be completed by your IT, if you would rather CEO Juice manage your Power BI setup, please click HERE for further details on that service.

If your e-automate is on-premise and NOT hosted by ECi, click HERE for the directions for clients that host their own e-automate.

Jump To: ECi Hosted Setup | Server Requirements | O365 User | Steps to Connect Database | Troubleshooting

ECi Hosted Setup

ECi Hosted Setup

There are three pieces of information that will need to be obtained from ECi in order to set up the Power BI connection:

1. We will need to know the public static IP address for your company. We will coordinate with ECi to whitelist this IP address.

2. ECi will need to create a SQL user to be used in the setup of the report connection. Once we have the IP address, we will reach out to ECi to have them create the SQL user needed.

3. ECi will also provide the IP and Port that will be used in setting up the ODBC connection. 

CEO Juice will coordinate with ECi, with you on copy, to obtain all of this information. Once you have all three pieces of information, you'll be ready to create the connection between your e-automate database and the CEO Juice Power BI reports.

Server Requirements

Server Requirements

Your IT staff will administer our Power BI templates and reports from a server. Doing so ensures that your data is secure and remains within your domain.

Server requirements:

1. .NET Framework 4.8 or higher.

2. 16 GB RAM.

3. Four cores for CPU.

* * *

O365 User

Office 365 User 

You will need to designate an Office 365 user to administer this process, ideally, this would be a service account managed by your IT and created just for Power BI. We HIGHLY recommend that you do NOT use a personal Office365 account to manage the Power BI reports, if the user administering the reports leaves your company, the entire set up to connect your database to the CEO Juice Power BI templates will need to be redone. This Office 365 user will need to have a Power BI Pro License.

Any users in your company who wish to use the Power BI reports will need a Power BI Pro license.

* * *

Steps

Steps to Connect Database

Follow the steps below to connect your e-automate database to the CEO Juice Power BI templates. 

Jump to: Step One - SQL User | Step Two - Download Power BI | Step Three - ODBC Connection | Step Four - Download Gateway | Step Five - Download Templates | Step Six - Create Workspace | Step Seven - Publish Templates | Step Eight - Semantic Model | Step Nine - Schedule Refreshes | Step Ten - Create Power BI App

You will need to authenticate as the Office 365 user created to administer the Power BI reports at several steps in this process. Before starting, please check that you have all the needed credentials for the Office 365 user.

Step One

Step One - Create New SQL User

ECi will create the SQL user to be used with the Power BI reporting. CEO Juice will coordinate with ECi to create this user.

Step Two

Step Two - Download Power BI

The second step is to download Power BI to the server.

Download Power BI Desktop from HERE (64-bit version needed). You will need to download Power BI desktop to the server that will be used for the Power BI reports.

**Please note Power BI Desktop is not intended as a space to use and access dashboards. Desktop is designed for developers of Power BI Dashboards. You will use Power BI Desktop to download the CEO Juice templates and then share the templates to a workspace in the Power BI Service.

Step Three

Step Three - Setup ODBC Connection

The next step is to create the ODBC Data Source (64-bit) connection on the server. The ODBC connection needs to be created on the server where Power BI Desktop is located.

**If you are NOT setting up a dedicated server to run the Power BI service on, then this connection is set up on the workstation of the Power BI user administering**

1. Search for the ODBC connector and select the ODBC Data Sources (64-bit).

2. Go to the System DSN tab.

3. Click on Add.

4. For the driver, select either SQL Server or SQL Server Native Client 11.0.

5. The name of the data source must be CEOJuice_BI.

6. The server will be the IP and Port provided by ECi. The format is IP,Port with no spaces.

7. Select the option to authenticate with SQL Server Authentication.

8. The login ID will be the one provided by ECi.

9. Select the option to change the default database and then select the CEOJuice database from the drop-down.

10. Click on the Finish button to finish creating the ODBC connection.

11. On the next screen, click on the option to Test Data Source to ensure the connection is working:

12. You should see the test result 'TESTS COMPLETED SUCCESSFULLY':

Step Four

Step Four - Download the Data Gateway

Step four is to download the data gateway in standard mode from within Power BI. You will need to be logged into Office 365 with the credentials for the O365 user that will administer the Power BI reports. 

**You will need to download the Enterprise Gateway on the server, NOT your personal PC**

1. Once logged into Office 365, open Power BI. In the upper right of the screen, click on the down arrow.

2. Select Data Gateway.

3. Download the Standard Mode.

4. Once the gateway has been downloaded, you will be prompted to enter an email address to use with the gateway. This will be the O365 email address set up to administer the Power BI reports. You will need to be able to authenticate as the user.

5. Once the email address has been authenticated, select the option to Register a new gateway on this computer.

6. Name the gateway CEOJuice Data Gateway.

7. Create a Recovery Key. Be sure to save this Recovery Key (CEOJuice will not track this for you).

8. You should see a message indicating that the gateway in online and ready to be used.

Step Five

Step Five - Download the Power BI template from the CEO Juice Website

The next step is to download the Power BI templates from the CEO Juice website (these are the .pbit files) and save the templates as the report (these are the .pbix files). You'll need to download the template file on the server where Power BI desktop is installed.

1. Go to the CEO Juice website to subscribe to the alert for the Power BI template that you would like to download. You can view the list from the CEO Juice website by searching the subscriptions page for the tag 'Power BI Reports'.

2. After you click on the ID number that you want to download, click on the red 'Click to Subscribe' button. Subscribing to the alert ensures that you will be notified every time a new version of the Power BI report is released.

3. There will be a link on the link to download the Power BI template file.

4. On the download page, enter your email address.

5. Select a Job Role from the drop-down list.

6. Click Submit to download the template.

7. When you click on the template file, it will automatically open in Power BI Desktop and start populating with your data.

Getting an error in Power BI Desktop? Click here to go to the troubleshooting section.

8. You may be prompted with this approval request multiple times as the data refreshes. Essentially at this point, the template is refreshing with your e-automate data. Please select RUN each time (note this can take quite a bit of time - hence the need for 16GB RAM as this is processing A LOT of data).

9. When you see the pop-up that the template is refreshing, you can click cancel as the template will be populated once pushed to the workspace.

10. Use the Save As option to save the template as a .pbix file to a folder on the server. We recommend keeping all Power BI reports one folder on the server where Power BI Desktop is installed.

11. Click on the Browse this device option at the bottom of the screen to open the folder where you will save the file.

12. Create a folder for the Power BI reports. 

13. Save the template as a .pbix file.

The template MUST be a .pbix file format. We suggest giving the report a meaningful name along with the ID# as you will overwrite these files and their names in the future (as we release updated templates/versions).

Step Six

Step Six - Create the CEO Juice Workspace

Step six is to create a new workspace named CEO Juice in the Power BI Service.

A Workspace is just that, a place to work on the report and should be reserved for those managing the refreshes and those intending to create dashboards from existing reports. End users/consumers of reports should only consume reports via the Power BI App (Step 10 below).

1. Click on the waffle icon from within the Office365 account.

2. Select the Power BI icon.

3. If the Power BI icon is not shown, you can search for Power BI in the search bar.

4. Click on the Workspaces icon in the left-side menu.

5. Click on the green button to create a new workspace.

6. Name the new workspace CEO Juice.

Step Seven

Step Seven - Publish templates to the CEO Juice Workspace

Now that the templates have been downloaded and saved as .pbix files, the templates will be pushed to the CEO Juice workspace individually. For each Power BI report, double-click on the file in the folder used in step five to open the report in Power BI Desktop.

**You must be logged into Power BI Desktop with the O365 user that is administering the Power BI reports in order to push the reports to the CEO Juice workspace. You can do this either by clicking on the Sign in icon in the upper right, or you will be prompted to login and authenticate when pushing the report to the workspace.**

1. With the template file open in Power BI Desktop, click on the Publish icon in the upper right corner.

2. Publish the report to the CEO Juice workspace created in step four.

**You will need to repeat steps one and two for each Power BI report that needs to be uploaded to the CEO Juice workspace.

Step Eight

Step Eight - Link the Semantic Model to the Gateway

Once all the Power BI files have been published, return to the O365 Power BI workspace and link the semantic model to the gateway created in step five.

**You will only need to go through the steps to link the semantic model to the gateway once, once this is done, the CEOJuice Data Source will show as an option in the data sources list and you can select the CEOJuice Data Source.

1. Hover over the first report in the workspace, where the type = Semantic Model. (NOT the line that has a type of Report)

2. Click on the button with three periods, this will open more options for the report.

3. Click on Settings

4. Go to Gateway and Cloud Connections

5. Click on the arrow under Actions to show the data sources included in the semantic model.

6. Click on Add to gateway.

7. In the New connection section, set the Connection name as CEOJuice Data Source.

8. The Connection name is CEOJuice Data Source

9. The Connection string is the ODBC connector created in step three.

10. The authentication method is basic.

11. The username and password are the ones created by ECi.

12. Click on the create button to finish and test the connection.

13. There will now be a drop-down under Data sources included in this semantic model, select the CEOJuice Data Source.

14. Click on the Apply button to save the change.

Step Nine

Step Nine - Schedule Automatic Refreshes of Data

Once you have connected the semantic model to the data source, scroll down in the Settings list to Refresh to set a refresh schedule for each Power BI report. 

1. Click on Refresh in settings to open the refresh settings.

2. Under Configure a refresh schedule, switch the toggle from Off to On.

3. The refresh frequency should be daily.

4. Click on Add another time to add a refresh time to the report.

**Please schedule those refreshes between 4AM - 8AM your local time as we have background processes that run each night. This ensures you have the most up to date data from the day before. Most reports can only be refreshed once per day, click HERE for details on reports that can be refreshed more frequently.

5. If the report fails to refresh, by default, the Semantic model owner will receive the failure notifications. (This is the email address created to manage the Power BI reports and MAY NOT BE THE BEST EMAIL TO RECEIVE FAILURE NOTIFICATIONS.) If you want to send the failure notifications to another email address, select the These contacts option and enter the emails into the box below.

6. Click on the Apply button to save the refresh schedule.

Step Ten

Step Ten - Create Power BI App

Once you have set the data source and refresh schedule for each Power BI report in the workspace, create the Power BI app. This is where users will go to view the data in the Power BI reports.

1. From the CEOJuice workspace, click on the Create app button.

2. In the Setup section, enter the App name.

3. This section also requires a summary for the App.

4. There is an option to update a logo to use for the App.

5. You can also change the color of the App if you would like.

6. Click on Advanced settings.

7. Under Glocal app settings, select the checkbox for Install this app automatically.

8. Click on the Next: Add content button to move to the next section of the app.

9. Click on the Add content button.

10. To add all reports to the App, hover over the top line and click on the check box. 

11. Click on the Add button to add the reports to the App.

12. The reports that will show in the App will now be listed under the Add content button.

13. Click on the Next: Add audience button.

14. The first tab of the Audience section will be named the same as the App and show all the reports.

15. You can control if the report is visible by clicking on the eyeball icon. 

16. You can limit the users that can see this audience by entering their emails in the Specific users or groups section.

**The audience is where you set different groups with accessing to needed reports. Audiences enables you to control access so all consumers don't need to have access to ALL reports. You may, for example, want to set an Audience for Service so they only have access to service reports (not your financials) and an audience for Sales. Click HERE for more details on creating these audiences.

17. Once done configuring the audiences, select the Publish App button.

18. Click on the Publish button on the popup screen.

19. Once the app is published, you'll see a popup indicating success.

20. You can copy the link to access the app to send to users if you would like. (The app can also be found in the users Power BI.)

**Remember ANYONE accessing reports from the app DOES NEED a Power BI Pro license.

21. Click on the Go to app button to view the App.

* * *

Troubleshooting

Troubleshooting

For each troubleshooting tip, click on the grey box to expand and view the details of the error. If you are unable to resolve the issue, please send an email to help@ceojuice.com.

Jump to: Named Pipe Error | Report Not Refreshing

Named Pipe Error

Named Pipe Error

Error: Microsoft Power BI Desktop couldn't start properly due to errors with named pipe connections.

This typically occurs "because of administrative policy restrictions that the Power BI on-premises gateway placed on named pipes on the local machine." (See this link for further details)

To correct the issue, right-click on the Power BI icon on your desktop (1), then right-click on 'Power BI Desktop' (2), then select 'Run as Administrator' (3).

Report Not Refreshing

Report not Refreshing

If the Power BI reports are not refreshing on schedule, check these things:

1. Make sure the On-Premise data gateway service is running on the workstation/server where you administer your Power BI reports.

- Go to Services.

- Check that the 'On-premise data gateway service' is running.

- If the service is not running, right-click on the gateway and choose 'Restart'.

2. Check that the semantic model has the proper gateway connection set and that the refresh schedule is still in place.

- Go to the CEOJuice workspace and hover over the semantic model for the report.

- Click on the three dots (1) and then select settings (2).

- Under Gateway and cloud connections, the datasource should be mapped to the CEOJuice data source. 

- Under Refresh, the daily refresh schedule should still be in place.

3. Manually refresh the report to see if the data refreshes.

- In the workspace, hover over the semantic model.

- Click on the circle arrow (1) to refresh the data on the report.

 

* * *