Bonus Material: FREE Xero KPIs Cheat Sheet (along with the SQL code you need) PDF
You are using one of the best accounting software in the market. Yes, I am talking about Xero. You are a happy Xero power user that you or your data analyst is looking to get your data out of Xero for your data warehousing reasons or for easy SQL Access to ask your custom questions.
Sweet. You go to Xero, fully expecting to be a walk in the park, like this:
But when you explore it a bit more, THIS is what you see instead:
Whoops. Did I forget to mention that you will probably need a developer to get your data out of Xero into your data warehouse? And then monitor it? And then maintain it? And then fix it when it breaks?
Yes you can get it in excel too, there are ways to achieve awesomeness with Xero & Excel
So what happens when you get your Xero data into your data warehouse or what more will I find with SQL? Why is it so important?
- You may free yourself from the limited reporting capabilities that Xero is offering.
- Or you could combine data from other sources like email marketing and customer support.
- Do financial modeling and forecasts.
- Or do easy data consolidation and more.
“But George, I know all that. We will do it on our own and maintain it and monitor it. I will need to have one of my developers dedicated to that…”
What if I tell you that you can export your data from Xero in minutes? Get these data into an SQL database like Postgres or Microsoft SQL Database or even on Redshift in no time. And get every new update there consistently along with all of your other resources like Zendesk or Intercom or Facebook Ads.
I’ll let these two GIFs do the talking for you.
We will need three steps:
- Connect to your SQL database.
- Connect to your Xero organization
- Use a tool like Blendo that will help you get the data from Xero (or any other source) in no time and easily send it to the destination you like.
Step 1: Connect to your SQL database
You will need the SQL database credentials
- Host Name
- Port
- Database Name
- Username & Password
1. Go to your Blendo Dashboard.
2. If it is the first time you login to Blendo you will see the screen bellow. Click on the box with SQL Server.
On the next screen, click on the box with SQL Server.
3. Add your credentials to connect to Microsoft SQL Server.
- SQL Server Host: For our example, this will be the gandalfxero.database.windows.net
- SQL Server Port: Leave the default
- SQL Server Database: For our example, this will be Myxerodb
- SSL: In our case, we do not use SSL, so leave it blank. NOTE: It is advisable to have SSL!
- Username: For our example it is testusr
- Password: the password
- *Export table prefix: It is not mandatory, I used myxero_ for me to know which tables will be created inside the SQL database.
If You have successfully connected, all your data will automatically go from Any source to Azure MS SQL, and that will happen for every new update that happens to that source. Blendo will get your data at specified intervals and send it to your SQL database.
If you also need a full guide, take a look at our MS SQL Server documentation:
- Step 1 (Azure): Connect Windows Azure Microsoft SQL Server
- Step 2 (Azure): Setup Windows Azure MS SQL Server
- Step 3 (Azure): PermissioningBlendo to Windows Azure MS SQL Server
- Step 4 (Azure): How to connect Windows Azure MS SQL Database as Data Warehouse Integration
Click here to get our FREE 10+ page PDF Xero KPIs Cheat Sheet!
Step 2: Connect to your Xero organization
For this “how-to,” I will use the Demo Company (Global) that is found inside Xero. Note: For anyone to access its data from Xero, it will require to create an account in Xero Developer Centre.
Considering that you already have created your account in Xero Developer Centre, here are the steps to follow on:
1> Click Add Application
2> That is Private.
Step 3: Load your data from Xero to your SQL database
Login to Blendo. Add Xero as a source.
Add your credentials, and you are Done!
Blendo will make sure you will always have in one place all your Xero data or other data sources, most current and consistently! Let me know your thought in the comments below.
References & Further Reading:
