Follow

Connecting your database to Power BI files (all except ID568

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

 

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

 

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)

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:

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 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]

ID770Permissions.PNG

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

NoPswd.PNG

2. 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).

1.PNG

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

2.PNG

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).

1.png 

Go to the System DSN Tab and click Add….

3.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.  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.

5.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 below.  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

 

 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

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 ID770. It is imperative that any future opening/refreshing of the file also be named ID770; 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.

ID770Login.PNG

 

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.

 

8.png

 

Once it authenticates successfully, you may see the below window pop up several times.  Click Run each time. 

10.png 

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.PNG

 

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:

MyWorkspace.PNG

 

Sharing:

Power BI Pro

Share1.PNG

 

Scheduling Refresh:

 

**The server hosting the Power BI Files must be turned on at the times these scheduled refreshes are set.**

Refresh1.PNG

 

You will need to set up Gateway under Datasets first:

Gateway.PNG

Gateway2.PNG

 

 

Then schedule refreshes and who to receive notice when refresh not successful:

 

Schedule1.PNG

We suggest refreshing once early AM and then every 2 hours throughout the day:

Schedule2.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