Follow

Connecting your database to Power BI files

Jump to: Power BI Set Up Options | Steps for Install

When opening a new Power BI file from CEO Juice, you will need to connect it to your database before you can refresh your data. Please involve your IT Department as this will take some front end set up.

**If using Power BI for ID568 eAutomate Activity Trends, please GO HERE.

VPN connections are not recommended for users as they are VERY, VERY slow. A user could wait an hour or more for data to refresh/process only to come upon an error. It is better to install Power BI Pro on a local server that users can then access via cloud service. Watch this video for our top reasons you should sign up for Power BI Pro.

Use this link to purchase Power BI Pro: https://powerbi.microsoft.com/en-us/power-bi-pro/

 

*  *  *

Options for running Power BI

Options for running Power BI

Your company will need to decide how extensively users will access these Power BI reports and dashboards.

 

 

Watch this video on Power BI options:

We highly recommend setting Power BI on a server managed by your IT Department alongside the use of Power BI Pro, as then you can (1) share the reports so users can access via the Power BI app from anywhere, (2) set for organization wide auto refreshes of your data, and (3) control who has access to your data within your organization. For this set up, you will be required to have a Power BI Pro license ($9.99 per month per user). Your IT Department should install using Admin Account login to Power BI Pro. 

If you opt to only install for one user on their computer/laptop, .Net Framework 4.8 and 16GB RAM are required. The user will be able to share the report with anyone, giving you no way of securing your data. 

 

 

Server Hosting Power BI (Highly Recommended): 

Install and run Power BI Pro on a server so end-users can access on the cloud via Power BI Services. This enables your IT department to do all needed set up, automatic refresh of data, and controls security.

Requirements for that Server:

1. Server must ALWAYS be turned on to ensure data is refreshing - best to install on the server CEO Juice accesses or your e-automate server

2. .Net Framework 4.8 or higher

3. 16GB RAM

4. Four cores for CPU

5. Power BI Pro Account ($10/month). Server and Users will need a Pro Account.

This link shows how to purchase and what is needed, such as info on purchase and assign licenses in the Microsoft 365 admin center, you must be a member of the global administrator or Billing administrator role in Microsoft 365.

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-purchasing-power-bi-pro

**Power BI Pro is only needed for (1) whomever will download the templates and share to the workspace, and (2) for whomever will access the report in the workspace to create dashboards. Consumers of the report and dashboards can access via the Power BI App and do not need a Pro License.**

 

End User Running Power BI Reports (no server hosting):

1. 16GB RAM on computer/laptop

2. .Net 4.8 or higher

3. Does NOT require Power BI Pro

4. Can schedule auto refreshes via Power BI Services (device must be turned on during refresh times)

5. User can NOT share the report safely. HOWEVER, user can publish reports to the web, but these reports will then be made public, which isn’t ideal when you’re dealing with proprietary business data.

Here is proof!

 

1.PNG

 

2.PNG

3.PNG

 

 

*  *  *

Steps:

Steps (to be completed by your IT Dept)

 

Overview of Steps:

  • Step 1: Create SQL User with specified permissions
  • Step 2: Set up ODBC Connection on server to host Power BI
  • Step 3: Download Power BI Desktop
  • Step 4: Open CEO Juice template (.pbt file) and save as your report (.pbx)
  • Step 5: Publish file to workspaces so other users can access
  • Step 6: Schedule refreshes so your report updates at least daily

Step 1:

Step 1: Create SQL User with specified permissions

1. Will need at least version 4.8 of .Net Framework for Power BI

2. Before downloading Power BI and/or our file, IT will need to create a NEW login to the SQL Server running your e-automate and CEOJuice databases. Do NOT use ANY ANY existing SQL user for this purpose.

Found HERE:

HERE.PNG

NOT Here:

NotHere.PNG

This user will be used to log in to the Power BI File. We suggest creating login "ceojuice_BI_User". This login will need db_datareader, db_datawriter AND db_executor OR db_owner rights to both your e-automate and CEO Juice databases.

If you do not have option for db_executor, please run this script to create:

CREATE ROLE db_executor

GRANT EXECUTE TO [db_executor]

ID770Permissions.PNG

Be sure to set user with no check mark at 'Enforce password policy':

NoPswd.PNG

 

Step 2:

Step 2: Set up ODBC Connection on server to host Power BI on your e-automate server

Create the ODBC Data Source (64-bit) connection on your e-automate server:

(if you are NOT setting up a dedicated server to run the Power BI service on, then this connection is set up on the PC of the Power BI end user)

Open the start panel in Windows, find Windows Administrative Tools and select ODBC Data sources (x64).  

1.png 

 

Go to the System DSN Tab and click Add….

SystemDSN.PNG

 

Choose SQL Server as your Data Source and click Finish.

4.PNG 

Give your new data source the name “CEOJuice_BI”.  In Server, enter your SQL Server instance name of your e-automate server.  Most of the time, you can click the drop down and it will search for available SQL Server instances.  Select the one where e-automate resides.  Click Next.

PB5.PNG

If you have your domain login with access to the e-automate and CEOJuice databases, you can select  “With Windows NT Authentication using the network login ID”.  Otherwise, select “With SQL Server authentication…” and enter your Login ID (ceojuice_BI_User) and Password as created in Step 1.  Click Next.

6.PNG 

Check the “Change the default database to” and click the drop down and select the CEOJuice database.  Click Next.

6.png

7.PNG 

On the next screen you have an option to test the connection.  You should get these results from the test.  Click Ok, then click Finish on the next window.

8.PNG

7.png

 

Step 3:

Step 3: Download Power BI Desktop

Download Power BI Desktop from here.

Please note Power BI Desktop is not intended as space to use and access dashboards. Desktop is intended for developers of Power BI Dashboards. You will use to download the CEO Juice report files and then share to workspace on the cloud (Step 5).

 

 

1.PNG

 

Skip this as this is only an invitation to Microsoft Office, not Power BI:

2.PNG

Once you have installed Power BI Desktop, you are ready to open our template

Step 4:

Step 4: Open CEO Juice template (.pbt file) and save as your report (.pbx)

ID770 - Financial Dashboard File Link: http://hub.ceojuice.com/770

ID771 - Service MIF & Profitability File Link: http://hub.ceojuice.com/771

ID704 - Financial Performance Benchmarks Link: https://hub.ceojuice.com/id704

ID774 - Customer Business Review: https://hub.ceojuice.com/id774

ID281 - Customer Retention Link: https://hub.ceojuice.com/id281 

 

The links above will provide you a .pbt (template) to download. 

PB2.PNG

Once you open our file, Power BI Desktop will automatically start refreshing the template with your data. DO NOT use Save As Option. The file originates as a .pbit file type which is just our template.

You will be prompted with this approval request multiples 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).

PB3.PNG

PB4.PNG

Once refresh is completed, save file to a File Folder for your CEO Juice Power BI Dashboards:

Go to File (upper left corner) and select Save As:

FileSave.PNG

SaveAs.PNG

 

FIleName.PNG

This MUST MUST MUST be a .pbix file format:

pbix.PNG

Now you are ready publish!

Step 5:

Step 5: Publish file to workspaces so other users can access

This will set report so available in Power BI Services on Office365 cloud. First you will publish, then you will share.

If installed on a laptop/computer, then this will use your Office365 login.

If installed on server to host, then user logged in to publish should be the Administrator of your Power BI files. 

Be sure you have a Shared Workspace created first. This needs to be done from the Power BI Service (not Power BI Desktop):

PBWS.PNG

 

 

Select Publish:

 

Publish.PNG

 

If you do not have Power BI Pro, you will need to save to My Workspace as this will only be available to you as the end user.

If you do have Power BI Pro, then save as file name making this report available in a shared workspace:

MyWorkspace.PNG

**Once published, you will need to login to your Office365 account on the same server you have been using Power BI Desktop on. This will be imperative to setting up gateway for auto refreshes in next step.

Go to workspace to see both report and dataset. Once all reports are in the workspace, be sure to select Create App to finalize steps for users to be able to access via the app:

PB9.PNG

Name your App:

PB10.PNG

Can change color of App - then publish App:

PB11.PNG

You will automatically be given a link you can copy/paste to email directly to users to access the app. Select Go to app:

PB12.PNG

However, best to set users who have permission to access via Permissions tab at top:

Ok to leave remaining default settings for access as they are:

PB12.PNG

Now when you navigate to Apps, you can see the App you created:

PB14.PNG

This report will show in the app:

PB15.PNG

When a user first access, that user will need to be in their Power BI app and select Apps, Get apps: App (be sure users log out and back into Power BI AFTER you publish):

PB16.PNG

User should select Organizational Apps to see the App you've just created:

PB17.PNG

Step 6:

Step 6: Schedule refreshes so your report updates at least daily

You should set automatic refreshes of your Power BI reports so users will always receive the most current data when they access the report. The server hosting the Power BI Files must be turned on at the times these scheduled refreshes are set AND the user you've logged onto server as MUST not officially "log off" the server. This ensures the gateway connection stays connected for the refreshes. After five failed attempts the scheduled refreshes will be removed automatically and you will have to reset them.

You must publish (Step 5) BEFORE you can schedule refreshes.

Start by accessing the file via the Workspace. You MUST select Dataset Type (not Report), use the three dots to navigate to settings:

R1.PNG

You will need to set up, in this order:

1. Gateway Connection

2. Data source credentials

3. Scheduled refresh

R2.PNG

 

1. Gateway connection under Datasets first. Use Install now to set up (only needed once). It is imperative you are logged into Office365 on the server you started this entire process on. This ensures this gateway connection is on this server which enables the scheduled refreshes.

Gateway.PNG

2. Then Data source credentials (use same User name and Password set in Step 1):

Gateway2.PNG

 

3. Now ready to schedule refreshes and who to receive notice when refresh not successful:

 

Schedule1.PNG

We suggest refreshing once early in the morning and no more than every 2-hours throughout the day:

Schedule2.PNG

 

Connect to Teams

Connect to Teams:

You can set your Power BI files so users can access via Microsoft Teams, see this link on how to set: https://powerbi.microsoft.com/en-us/blog/power-bi-teams-up-with-microsoft-teams/

 

FAQ/Errors:

FAQ/Errors:

 

1.  Desktop users may see this error

This is typically "because of administrative policy restrictions that the Power BI on-premises gateway placed on named pipes on the local machine." (https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-error-launching-desktop)

 

PowerBI_Error.PNG

 

If so, then you will need to right click on Power BI icon on your desktop, then right click on 'Power BI Desktop' to select 'Run as Administrator':

PowerBI_Admin.PNG

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk