Power BI Set Up - ECI On-Premise / Managed by Your IT

This forum post details the steps to connect your on-premise e-automate database to the CEO Juice 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 hosted by ECi, click HERE for the directions specifically for hosted clients as they do differ from the directions for those that have e-automate on-premise.

Jump To: Server Requirements | O365 User | Steps to Connect Database | SQL Template | Troubleshooting

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 to Connect Database

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

The first step is to create a new SQL user to be used exclusively with the Power BI process, the user settings need to be exactly as described below.

**It is important that a new SQL user is created for this purpose and that an existing user is NOT used.**

1. In SQL, expand the security folder.

2. Right-click on the Logins folder.

3. Select the New Login...option.

4. The login name must be set to ceojuice_BI_User.

5. Be sure to note the password assigned to the user as it will be needed when setting up the ODBC connection.

6. Uncheck the option to enforce the password policy.

7. Click on the User Mapping page to set the rights for the user to both the CEOJuice database and the e-automate database.

8. For the CEOJuice database:

• db_datareader
• db_datawriter
• db_executor

9. For the e-automate database (CoSystem):

• db_datareader 
• db_executor 

This is needed because we read data from both databases and need to execute procs on both databases as well. We need db_datawriter to the CEOJuice database because we log activity there. 

If you do not have the option for db_executor, please run this script to create (you will need to run against both your e-automate and CEO Juice databases):

CREATE ROLE db_executor

GRANT EXECUTE TO [db_executor]

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 should be the server where your e-automate database resides.

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

8. The login ID will be the ones set in Step One.

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.

If you are using the SQL templates instead of the regular .PBIT template files, go here for the directions on downloading and saving the files. 

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.

**If you are using the SQL templates, click HERE for instructions on how to set up the Data Source.

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 SQL user created in step one.

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.

* * *

SQL Template

SQL Template

For clients using the SQL templates with Power BI reports (not common, built for clients with large e-automate databases to improve performance and load balancing), you'll need to email help@ceojuice.com and ask for the SQL template to be emailed directly to you. 

You'll follow the steps to connect your database to the Power BI reports as indicated in the Steps to Connect, with two changes:

Download and Save File

Step Five - Download and Save File

As mentioned above, you'll need to reach out to CEO Juice by emailing help@ceojuice.com and asking for the SQL template. You will not be able to download this template from the website.

When opening this template in Power BI Desktop, you'll need to enter this information:

1. Param_Server: The name of the server your e-automate database resides on.

2. Param_Database: ceojuice

3. You may be required to enter the SQL user name and password, this is the SQL user created in step one.

Return to Step Six to continue working on setting up the connection.

Data Source

Step Eight - Gateway Data Source

When creating the data gateway connection in step eight, follow these steps:

1. In the Gateway and cloud connections section, click on the arrow under Actions.

2. Click on Add to gateway.

3. The name of the connection will be CJDataGateway.

4. The server is the server name of your e-automate server.

5. The database is ceojuice.

6. The Authentication method is basic, the user name and password to be used is the SQL user created in step one.

7. Under General, the Privacy level is Organizational.

8. Click create to create the connection.

9. Now you can connect your SQL Server connection to the CJDataGateway.

Return to Step Nine to finish setting up the connection.

* * *

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.

 

* * *