Follow

Power BI Set Up - Connecting your database to Power BI files:

Jump to a specific section by clicking a link

Power BI Set Up Options | Steps for Install | Update Template

 

Why Power BI? We like this extensive article on Power BI.

Power BI does require initial set up via your IT Department in your Microsoft Office environment. CEO Juice is happy to work with your IT Department (for no additional charge) in getting Power BI in place. 

Consumers of Power BI Reports access them within the Microsoft environment just like any other app (i.e. SharePoint). Nothing special needed for these consumer's workstation, their Ooffice365 user will just need to be assigned a Power BI Pro License (details below). 

PBI1`.png

Video

Video:

Please watch this video providing overview of how Power BI works:

1. Anyone wishing to use a Power BI Report will need an Office365 Power BI Pro license which is $10 per month and has 60-day free trial. (You may already have a few Pro Licenses included in your Microsoft package.)

2. One Office365 user needs to be designated as the administrator for Power BI Reports and that user also needs a Pro License. We will use that user to facilitate everything Power BI. We suggest using one of your Office365 admin accounts or creating one just for administering (i.e. powerbi@yourdomain.com) to ensure if everything doesn't need to be re-done if today's Power BI administrator wins the lottery tomorrow.

Use this link to purchase and/or assign Power BI Pro licenses: https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-purchasing-power-bi-pro

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. 

 

*  *  *

Options for running Power BI

Options for running Power BI

We highly recommend your IT Staff administer our Power BI templates and reports from a server, not an individual's workstation. Your IT will need to be involved as they will need to create a SQL User with needed permissions to both your e-Auto and CEO Juice databases AND complete some downloads (free from Microsoft).

Advantages of Server Set Up:

(1) Ensures your data is secure and stays within your domain.

(2) Since servers are typically always on (except for routine reboots), the needed refreshes (updating reports with your latest e-Auto data and transactions) can happen without human intervention and during off hours so as not to weigh your server down.

(3) If facilitated from someone's workstation and that person leaves your company, now you have to re-do all of the set up.

(4) When facilitated from CEO Juice server, then CEO Juice Team can more readily help, troubleshoot, and review Power BI questions you have.

Minimum Requirements for Server or Workstation:

1. .Net Framework 4.8 or higher

2. 16GB RAM

3. Four cores for CPU

 

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

 

Individual Running Power BI Reports with NO Power BI Pro License:

1. 16GB RAM on computer/laptop

2. .Net 4.8 or higher

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

4. User can NOT share the report safely without a Power BI Pro License. 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. We DO NOT recommend this set up!

 

*  *  *

Steps:

Steps (to be completed by your IT Dept)

**Please complete ALL of these steps (ideally on your CEO Juice server) AND be sure that server meets these minimum requirements:

1. .Net Framework 4.8 or higher

2. 16GB RAM

3. Four cores for CPU

If you are ECi hosted, please reach out to help@ceojuice.com as we will need to involved ECi in getting needed credentials.

 

Steps 1 - 5 are all completed from the server:

  • 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 onto server
  • Step 4: Open CEO Juice template (.pbit file) and save as your report (.pbix)
  • Step 5: Publish file to workspaces so other users can access

Steps 6 - 7 are completed from the workspace at either server or PC level.

  • Step 6: Enterprise Gateway Set Up and Schedule refreshes so your report updates at least daily
  • Step 7: Create Power BI App

Step 1:

Step 1: Create SQL User with specified permissions

1. 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. Please create login name of: ceojuice_BI_User. This login requires:

db_datareader, db_datawriter, and db_executor roles to the CEOJuice database

AND

both db_datareader and db_executor roles to your eAuto/CoClient database.

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

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

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 the 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 workstation of the Power BI user administering)

Open the start panel in Windows, find Windows Administrative Tools and select ODBC Data sources (64-bit):

 

ODBC_64_bit.png 

Go to the System DSN Tab and click Add:

SystemDSN.PNG

 

Choose driver type of SQL Server or SQL Server Native Client 11.0:

ODBC1.png

Give your new data source the EXACT name “CEOJuice_BI”.  In Server, enter the 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, then click Next:

PB5.PNG

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:

Default_DB.png

Then select Finish:

7.PNG 

On the next screen you have an option to test the connection, be sure to test. Once successfully tested, click OK and close ODBC box:

8.PNG

Test_Success.png

 

Step 3:

Step 3: Download Power BI Desktop & Create Workspace in Power BI Service

(a) Download Power BI Desktop from HERE (64-bit version needed)

**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 Power BI Desktop to download the CEO Juice templates and then share to a workspace in the Power BI Service as shown in (Step 5).

(b) Create Workspace in Power BI Service. Log into your Office 365 account Go to Power BI (you may need to search your apps to add).

PBIb.png

In left margin, select Workspaces (NOT My Workspace):

PBIc.png

Click on + New Workspace and name CEO Juice:

PBId.png

Now you are ready to download templates! 

Step 4:

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

We suggest using the title/naming we've provided below to help your end users identify the reports for access.

ID770 - Financial Insights: http://hub.ceojuice.com/770

ID771 - Profit Insights: http://hub.ceojuice.com/771

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

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

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

ID517 - Sales Order Insights: https://hub.ceojuice.com/517

**When downloading template for first time (not subsequent updates/revisions), please allow up to 30 minutes before attempting to refresh to allow our background tables to populate. Please also note this report has needed variables on your ID517 subscription, review variables tab HERE please.

ID772 - Inventory Management: https://hub.ceojuice.com/id772

**This one requires you have ID806 installed and running, so please be sure you subscribe HERE. Without ID806 in place, the refreshes for ID772 will fail.

ID568 - eAuto Activity Trends: https://hub.ceojuice.com/id568

ID28 - Tech Productivity: https://hub.ceojuice.com/id28

 

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

PB2.PNG

Once you open our file, Power BI Desktop will automatically start refreshing the template with your data. 

**Some 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

 

 

You may 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. 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). As you save each template as a .pbix file, you will publish each to your CEO Juice workspace. 

PBIa.png

Now you are ready publish one report at a time. A few important notes:

1. ID517/Sales Order Insights has variables which we HIGHLY recommend setting on your ID517 subscription. Please see this link for details:https://support.ceojuice.com/hc/en-us/articles/4557448274324-ID517-Power-BI-Sales-Orders-Insights-Overview-Sample-#h_8af225ce-3e03-4d51-b4be-ad5db9267395

2. ID517/Sales Order Insights will NOT refresh immediately, so best to not attempt refresh from Power BI Desktop. Let it refresh 45 min after you publish to the workspace as we have background tables that will auto install and run automatically.

3. ID772 requires you be subscribed to ID806/Inventory Turns. The refresh will error out if you are not already running ID806. This refresh will also NOT work until the day after you subscribe to ID806.

Step 5:

Step 5: Publish file to workspaces

This will send the report to your Power BI Service in Office365 cloud. 

PBIe.png

Publish to your CEO Juice workspace:

PBIf.png

 

Once you have published ALL of your Power BI Reports to your CEO Juice workspace, you will need to set up scheduled refreshes (Step 6 below).

*Please note, 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 7 below).

 

Step 6:

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

You will need to set automatic refreshes of your Power BI reports so users will always receive the most current data when they access the report. You must publish (Step 5) BEFORE you can schedule refreshes.

(1) First you will need to set up Enterprise Gateway, (****Please be sure to download the Enterprise Gateway on the server, NOT your personal PC.)

(2) then connect each Dataset to that Enterprise Gateway,

(3) and lastly you will set scheduled refreshes. 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.

 

Setting up Data Gateway:

Download new Data Gateway / Standard Mode (this download can be found in Power BI Workspace). *****Please be sure to download the Enterprise Gateway on the server, NOT your personal PC.GW1.png

**Be sure to download Standard Mode

GT2.png

Once downloaded, you will be prompted for Office365 login credentials. This must be the same Office 365 user administering this entire Power BI process.

GW3.png

GW4.png

Name data gateway CEOJuice Data Gateway and create a Recovery Key. Be sure to save this Recovery Key (CEOJuice will not track this for you):

GW5.png

Success, OK to close:

GW6.png

Connect each reports semantic model to Enterprise Gateway:

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

PBISM1.png

PBISM2.png

Go to Gateway Connection:

PBIg.png

Select arrow to be taken to Data Source settings:

GW8.png

Enter these values exactly:

GW9.png

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

GWAA.png

Once successful, go back to dataset in Workspace and connect the ODBC connection to your 'CEOJuice Data Source':

GW11.png

 

Schedule Refreshes:

Once the ODBC is mapped to the CEOJuice Data Source (Gateway Connection) scroll down to to Scheduled refresh, turn ON, set time. Please be sure to set refresh times between 4AM - 6AM your time and DO NOT schedule any one data set to refresh the same time as another (This ensures our nightly background processes have completed which is required for refreshed data to push into your reports.)

**ID517/Sales Order Insights MUST be set to refresh at the top of the hour (i.e. 6:00, not 6:30) and can be set to refresh up to 8 times per day (i.e. 6AM, 9AM, 2PM, 4PM).

 

Refresh.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/

 

Step 7:

Step 7: Create Power BI App

The Power BI App is where consumers of the reports will access (not the workspace).

Go to your CEO Juice workspace and select Create App:

PBIApp1.png

There are three sections to the App: Setup / Content / Audience

Setup:

Here you will be required to add Enter a summary with option to upload logo and change app color:

PBIi.png

Scroll down and be sure to check off box Install this app automatically:

PBIj.png

Content:

Select Next: add content:

PBIk.png

Select + Add Content:

PBIl.png

Select ALL your reports, then click Add:

PBIm.png

Now select Next: Add audience:

PBIn.png

Audience:

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 on +Audience to create groups for these permissions:

PBIo.png

Double click in the New Audience to rename (i.e. Service, Sales):

PBIp.png

Click on any report to NOT be visible/accessible to this audience. This will gray it out. And add Office365 users to have access to this audience:

PBIq.png

Once you've created all needed audiences and added who has access, select Publish App:

PBIr.png

PBIs.png

We suggest copying this link to send to end users so they can easily access the Power BI app. **Remember ANYONE accessing reports from the app DOES NEED a Power BI Pro license:

PBIt.png

Sample of how App looks. Since you are Admin to the workspace, you see everything in the app.

PBIu.png

For users with permissions to specific audience, they will only see the reports added to that audience. For example, Sales audience only sees ID517 as shown below (they will not see the other audiences):

PBIv.png

 

YOU ARE FINALLY DONE! Reach out to us at help@ceojuice.com with any questions!

*  *  *

Update Template

Update Template

When you receive notice from CEO Juice of updated template available, please be sure to download this template ASAP and replace your existing corresponding report.  We issue updates as they provide enhancements and bug fixes.

Step 1:

Always be sure you have the most recent version of Power BI Desktop installed on your server.

Step 2:

Download the template from the URL provided by CEO Juice onto your server. This will automatically open Power BI Desktop for you and will automatically show you this 'Refresh' box. You can refresh from here, but it is much quicker and easier to do from your Power BI workspace (Step 5 below). We suggest (1) select 'Cancel', then (2) go to File to save as:

1.png

If you are prompted with this box you can select cancel as we will manually refresh later OR you can enter User name ceojuice_BI_User  with password you created for this SQL user (by your IT dept, CEO Juice does NOT know this password).12.PNG

 

Step 3:

Save file as .pbix file type and name EXACTLY as you had before so it replaces your existing:

3.png

4.png

5.png

Step 4:

Publish the report to your Office 365 Power BI Workspace, select destination (same as where report exists already):

6.png

PBIf.png

Will take just a few moments to publish:

8.png

Once completed, you can open the report in the workspace or select 'Got it' and login to Office 365 Power BI manually:

9.png

You can close Power BI Desktop and no need to save (as you already have):

10.png

 

Step 5:

From the workspace, you can (1) manually refresh the report using the circling arrow icon. 

PBIRefresh1.png

 

Step 6:

Review the Gateway connection via Semantic Model / Settings to ensure Connection and Scheduled Refresh is still set:

PBISM1.png

PBISM2.png

Gateway.png

mceclip1.png

 

Step 7:

Update the APP. Any changes made to the workspace will NOT automatically push to the Power BI App.

PBIApp2.png

 

Update

Now are you DONE! 

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