Connecting to Quickbooks online.
- Begin with downloading the connector.
- Create an app in Quickbooks online environment
Creating an App
You can follow the steps below to create an app and obtain the OAuth client credentials, the consumer key and consumer secret. You will need to create an Intuit developer account if you do not already have one.
- Log in to http://developer.intuit.com.
- Click My Apps > Create New App > Select APIs. Select the Accounting API.
- On the Settings tab, define the Launch URL.If you are making a desktop application, set Launch URL to http://localhost/.For a Web application, set Launch URL to a page you would like the user to be returned to after they have granted your application permissions.
Once you have created the app, you will be redirected to a page with information about your app. The Development tab contains your Sandbox credentials and the Production tab contains credentials for use with a production account. The consumer key and consumer secret are displayed on the Keys tab.
- Make sure in the app you set the call back string in the Quickbooks online app to local host. You should be able to do this on the tab next to 'Keys'
- Connecting from Sisense. Open the connector and enter the following connection string
jdbc:quickbooksonline:CompanyId=xxxxxxxxxxxxxx;CallbackURL=http://localhost/;InitiateOAuth=GETANDREFRESH;Timeout=0;
**** To get the company ID, log into your Quickbooks online instance and press the following Keys.
PC: CTRL + ALT + ?
MAC: CTRL + Option + ?
This will load a pop up screen with your company ID. Make sure to delete all the spaces in the ID when putting it into the Connection String. ****
Then click connect. This should open a browser window for the user to verify credentials for the connection. Once complete you should see all the available tables.
Now if the tables you are looking for are not coming up, then you need to create the schema files for those tables. Begin by downloading DB visualizer (download the version for your operating system with Java VM).
Before using DB Visualizer, you will need to modify the connection string to work in DB Visualizer by adding the RTK Key. The RTK is the key used for the Cdata connector. To get the RPK key, Go to the following file directory
C:\ProgramData\Sisense\DataConnectors\QuickBooks Online\Sisense.QuickBooksOnline.JVM.1.0.17114.53.0.0
Open up the description file and grab the RTK key. You are going to need this to add to the connection string in DB Visualizer.
Once DB Visualizer is installed, begin by going to tools>Driver Manager. Select the plus sign in the top left corner. A new driver will pop up in the list with no name and the driver settings will be blank.
Name the driver Quickbooks Online and add the following connection string into the URL Format box.
jdbc:quickbooksonline:CompanyId=xxxxxxxxxxxxxx;CallbackURL=http://localhost/;InitiateOAuth=GETANDREFRESH;Timeout=0; RTK=XXXXXXXXXXXXXXXXXXXXXX; Location=C:\LocationFolder
The parameters of the Connection string are outlined below.
- Company ID - The QBO company ID. Follow the instructions
- CallbackURL- The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
- InitiateOAuth-Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
- Timeout - The value in seconds until the timeout error is thrown, canceling the operation.
- RTK- Needed to use the Cdata Connector
- Location - Where DB visualizer will save and look for schema files
Once the connection string has been created, you must add the driver file to the driver manager. Click on the folder icon on the right hand side and select the JAR file for Cdata in the following directory.
C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\QuickBooksOnline\lib
The driver should autopopulate the class and you should see a check mark appear next to the driver name.
Once you are connected, you should be able to access the stored procedures under quickbooks online connector. In the left hand menu, expand the Quicbooks connector untill you see tables, views and procedures, then expand procedures.
You need to run the procedure that responds to the table you want. Open the SQL tab (SQL Commander> New SQL Commander) and type the syntax exec <ProcedureName> . Once you run it, it creates a schema file that is added to the folder you specified in the Location parameter in the connection string.
Best practices dictates placing the .rsd file into the lib folder of the connector. This folder can be found in the following directory.
C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\QuickBooksOnline\lib
When you run a Stored procedure, it will create only a single schema file. Run each of the stored procedures you need. Then, move all the resulting schema files to the lib folder for the Quick books directory.
**** ONLY RUN SCHEMA FILES THAT CREATE A REPORT. DO NOT RUN PROCEDURES THAT DO NOT CREATE A TABLE AS ONE DEAUTHENTICATES THE AUTH TOKEN. ***
Next, restart the JVM service, when the connector appears for Quickbooks you will need to change the connection string to include the final location of the RSD files.
jdbc:quickbooksonline:CompanyId=xxxxxxxxxxxxxx;CallbackURL=http://localhost/;InitiateOAuth=GETANDREFRESH;Timeout=0; Location= C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\QuickBooksOnline\lib
Finally, when you connect you will see the new table(s) along with the original tables when adding data.
Customizing the Schema Table Range
Once you have your New schema file added and have added and built the table in Sisense, you may notice that the table does not have all the information you need. For example, it can be limited to a specific date range where it is only receiving data from the current year. To correct this, you can specify parameter values when executing the Procedure in DB Visualizer. Simply list them after the Procedure name and specify the values. Separate the column names by a comma.
In this example we set the start date and end date to capture data from 2016 and always grab future dates. The year 2030 was picked as this is not dynamic and will not have to be changed for a long time.
To see which parameter values you can pass in to the table schema, click on the procedure and look the column names.
Finally, run the Procedure which will create a new schema that needs to replace the previous schema in the location of the RSD files. You'll notice when you open the RSD file in notepad, there is a default value added to the specified parameters.
