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/e-auto Activity Trends, please GO HERE.
Options for running Power BI
Options for running Power BI
1. Your Company uses Office365 AND has a Power BI Pro Account ($10/month per user)
-new Pro Accounts are free for first 60-days, great option to see its benefits
-allows for secure publishing to workspace on the cloud to ensure who can see your company data
-automatic refreshing of data scheduled so anytime a user accesses, it is real time data
-users will need a minimum of 16GB RAM on their desktop computer to run reports in Power BI
-users can access via Power BI in the cloud
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
2. Your Company uses Office365, but has NO Power BI Pro Account (did you know the first 60-days are free?)
-individual users will need to set up at desktop-level (see instructions below)
-individual users will need to refresh manually to have real time data
-please note, anyone can then share the report and therefore no control of security
-users will need a minimum of 16GB RAM on their desktop computer to run reports in Power BI
-users can access via Power BI in the cloud
**see this link on differences between free and Pro Account: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-features-license-type
3. Your Company does not use Office365
-CEO Juice can install Power BI only on the server we access at no additional charge to you, but firm Server Requirements of 16GB RAM and 4 Cores for CPU to be completed by your IT.
-CEO Juice will need to receive in writing from your IT Dept permission to install once minimum server requirements are met
-your IT will need to publish the report, set up gateway connections and share with users, and will need to do so any time CEO Juice updates our report templates.
-please note, anyone can then share the report and therefore no control of security
-users will need a minimum of 16GB RAM on their desktop computer to run reports in Power BI
Prerequisites
Prerequisites (to be completed by your IT Dept)
Step 1:
Step 1:
1. Will need at least version 4.7 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:
NOT Here:
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 eauto 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]
Be sure to set user with no check mark at 'Enforce password policy':
Step 2:
Step 2:
1. Download and install free Power BI Desktop here.
- Best for Power BI Pro to be installed on a server (Server Requirements: 16GB RAM and 4 Cores for CPU) and have users access the Power BI file via Cloud Service or provided link within Power BI workspace. If you do not have Power BI Pro, then users can still access via the cloud but will need to manually refresh each time they access for updated data. (More on that below).
- VPN connections are not recommended for users as they are VERY VERY slow. User could wait an hour or more for data to refresh/process only to come upon an error. Best alternative is to install Power BI on a local server that users can remotely access.
If you do not already have Power BI, you will need to download and sign into Microsoft to create a Power BI account (this does NOT require Power BI Pro).
Skip this as this is only an invitation to Microsoft Office, not Power BI:
Step 3:
Step 3:
You will then be prompted to create a System DSN (ODBC) connection to your SQL 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). **If you install a 32-bit version of Power BI, choose ODBC Data Sources (x86).
Go to the User DSN Tab and click Add….
Choose SQL Server as your Data Source and click Finish.
Give your new data source the name “CEOJuice_BI”. In Server, enter your SQL Server instance name. 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.
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 below. Click Next.
Check the “Change the default database to” and click the drop down and select the CEOJuice database. Click Next.
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.
Now you are ready to open the Power BI File!
Opening the PowerBI File
Opening the Power BI File:
ID770 - Financial Dashboard File Link: http://hub.ceojuice.com/770
ID900 - Financial Performance Benchmarks Link: https://hub.ceojuice.com/900
Once you download the file to your server or workstation, double click on the file (Click on the link, DO NOT use Save As Option) to open within Power BI. (this will automatically be a .pbit file type which is just a template). Once the file is refreshed, make sure to save as .pbix file. It is imperative that any future opening/refreshing of the file also be named ID770 or ID900; otherwise, you will end up with two different reports/files.
If your IT selected to use “with SQL Server authentication…” when creating the ODBC connection as described above, you will be presented with this screen. Enter the login ID and password used when setting up the ODBC connection.
Once file opens, click the Refresh button and save as .pbix file with all the data embedded.
If you are NOT using Power BI Pro, then subsequent access to the report will require Refresh of .pbix file to ensure you have most recent e-auto data.
If your are using Power BI Pro, be sure IT has scheduled auto refreshes so you always have real-time data.
Once it authenticates successfully, you may see the below window pop up several times. Click Run each time.
That’s it! It may take some time to refresh, but once it completes, users are ready to use the Power BI report.
Publishing
Important Notes on Publishing / Sharing
-Best to use Power BI Pro as it allows you to set auto refreshes throughout the day, manage who has access to the report and see who is using the report.
-If you are not using Power BI Pro, then users will need to refresh the report each time they access which is not ideal as this is time consuming and offers no guarantee that users are, in fact, refreshing and seeing real time data/results.
-To set up sharing to make available on Office365 cloud:
Publish:
Publish:
If you do not have Power BI Pro, you will need to save to My Workspace.
If you do have Power BI Pro, then save as file name:
Sharing:
Sharing:
Power BI Pro
Scheduling Refresh:
Scheduling Refresh:
**The server hosting the Power BI Files must be turned on at the times these scheduled refreshes are set.**
You will need to set up Gateway under Datasets first:
Then schedule refreshes and who to receive notice when refresh not successful:
We suggest refreshing once early AM and then every 2 hours throughout the day:
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)
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':
0 Comments