Connect Xero Power BI

XERO & POWER BI DESKTOP

CONNECT XERO to POWER BI DESKTOP

With Power BI , Microsoft’s innovative reporting and business intelligence tool seamlessly integrate with Xero Accounting to create easy to read data visualisation reports. Start with the fundamental metrics and add more as you go.  

 

CONNECT XERO to POWER BI DESKTOP

There are 6 steps to gain access to one or more Xero data files:-

  1. Create an X.509 digital certificate. (need Java installed on computer to create certificate)
  2. Register an application on the Xero developer portal.
  3. Install Xero ODBC driver
  4. Configure connection properties on the ODBC driver
  5. Install latest Power BI Desktop from Windows site
  6. Connect Xero file to Power BI Desktop

All steps to be repeated for each Xero organisation file

Step 1 Create an X.509 digital certificate.

Here we are creating the digital public and private key certificates, for each Xero file.

1.1

Create two folders on the C drive

C:XeroKeys

Place the public/private Keypair in here

C:Xerolog

Log file will be saved here.

If you plan on connecting to many Xero files, I suggest creating an additional folder for each individual company name. For example

C:\XeroKeys\AccountingTeam and C:\XeroKeys\AAAProductions

1.2

Create a text file in C:Xerolog

For example

“XeroAccountingTeamlog.txt”

If you plan on connecting to many Xero files, I suggest creating an additional folder for each individual company name. For example

C:\Xerolog\AccountingTeam and C:\Xerolog\AAAProductions

1.3

Install CertGen certificate.

Ensure you have Java installed on your Computer.

I e-mailed the zip file to you when I replied to your query.

If you did not receive the ZIP file, e-mail me using the contact e-mail from  www.analyticsrealtime.com.au

and I will e-mail you a link to a Dropbox file with theCertGen file. (the e-mail settings may prevent you from accepting e-mails with certain attachments) 

1.4

Complete the certificate generation Subject area fields as per below however use your detail.

Common Name: Elizabeth Tachjian

Organisation:  Analytics Realtime

(Use the same as Xero login in detail)

1.5

Click Public Key button over to the right and select folder created in step 1.1 above.

Next, create a file name CompanyNamePublicKey.

The public key will display C:\XeroKeys\AccountingTeam\AccountingTeamPublicKey.cer

1.6

Next Click Private Key and do the same for the Private Key

C:\XeroKeys\AccountingTeam\AccountingteamPrivateKey.pfx

1.7

Enter password, same password as you would use to log in to the Xero files.

1.8

Serial number, this can be any set of numbers.  I normally use dayandmonth for example 0610

1.9

Document the Common Name, organisation and `password.  You will need this later.

1.10

Last Step,  Click Create Certificate.

Step 2. Register an application on the Xero developer portal.

Here we are going to register an app on the Xero Developer file for each Xero company file.

2.1 

Login into the Xero Accounting file

2.2

Then go to

https://developer.xero.com/myapps/

2.3 

Create a new app

-Go to, my apps to create a new private application

-Enter app name.  For example Company Name

-Select xero organisation from the drop down list

-Drag and drop public key in box below which is located in the C:XeroKeys. (step 1.6)

-If Payroll is setup, will normally have an option to access payroll, tick box

-Tick I have read…

-Click Create app

-Lastly click the save button.

Step 3. Install Xero ODBC driver

3.1

Download the below driver to your computer and install the driver by following prompts.  Take note of selection 34-bit or 64-bit.  If you have installed the 64-bit Power BI desktop, then you will also install the 64-bit Xero driver.

https://www.cdata.com/odbc/

Start with the trial to get 30 days free, then upgrade.

Please send me an e-mail hello@analyticsrealtime.com.au so I share with Cdata of the referral

Step 4. Configure connection properties on the ODBC driver

The ODBC driver should already exist on your windows computer.

4.1

– In the windows search type in ODBC data sources and open it

-Select System DSN tab

-highlight as below cData Xero Sys

-Click Add button

-Select, CData ODBC driver for Xero as per below

-click Finish

4.2

Configure ODBC driver

Make changes as per steps below:-

-Enter Xero file name in Data Source Name

-Set logfile to same as step 1.2

-change verbosity to 3

-OAuthAccessToken: Set this to the consumer key in step 2.3

-OAuthClientId: Set this to the consumer key in step 2.3

-OAuthClientSecret: Set this to the consumer secret in step 2.3

-Set Xero App Authentication to

Private

-Set Certificate store type of PFXFILE

-Certificate Store: set to same as step 1.6.  pick certificate with extension .pfx

-CertificateStorePassword: Same password in step 1.7

-Certificate subject same as step 2.3, copy paste the whole field as is

O=Analytics Realtime, CN=Elizabeth Tachjian

-Check that Schema is ACCOUNTING

-To finish off OBDC configuration, click OK.

Step 5. Install the latest Power BI Desktop

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Follow prompts to install Power BI

Step 6. Connect Xero to Power BI desktop

-Start power BI desktop

-Select Get Data, Other, ODBC driver and follow prompts

-Select company name from drop down list

-use the same credentials as you would to log in to the Xero file

You have now connected Xero to Power BI desktop and ready to commence Extract, Transform Load data.  Contact me for further help.

To help me, are you able to please endorse Microsoft Power BI and Xero skills on www.linkedin.com/in/elizabeth-tachjian-cpa-power-bi

or provide a review on google business

https://search.google.com/local/writereview?placeid=ChIJswOddk5d1moRi7NNCkxCT0Q

Or if comfortable have a play with the tables.  Makes as many mistakes as you like because you will not change the information in Xero and can always start the power BI report again.