Getting Started with BICS – BICS Data Sync

I’m going to deviate from the actual BICS tool in today’s blog post to touch on BICS Data Sync. You can load data for analyses via Excel in BICS, but if you want to get relational data (among other data sources) into BICS, Data Sync is the tool to do so.

So, what is BICS Data Sync? DS is the Oracle tool that provides the capability to sync data from your on-premises sources to BICS. The sources you are able to sync are from relational databases and flat files. Sources can include DB2, MSSQL, MySQL, Oracle (BICS), Oracle (OCI8), Oracle (Thin), Oracle BI Connector, Teradata, TimesTen, and flat files.

Installing, Configuring, and Using BICS Data Sync

1. Go to http://www.oracle.com/technetwork/middleware/bicloud/downloads/index.html and download the Java tool.

2. Extract the file to a location on your machine.

I chose to extract it to my C: drive.

3. You will need to update the location of your JAVA_HOME in the config.bat file (for Windows). You will need to include the path to the JDK home. Note that if you have spaces in your path (like me in “Program Files”), you will need to include the path in quotes.

Here is the location of the config.bat file:

Update this line of code ONLY:

4. You will be brought to the Data Sync Configuration wizard.

5. Choose if you want to configure a new environment or copy an existing configuration.

6. Give your new repository a name.

7. Choose a password for the repository.

8. You will get confirmation that your configuration is complete.

9. Now we will create a project. If you chose to have the system save your password, it will be auto-populated for you in this screen.

10. Give your new project a name.

11. Now you will be brought to the main BICS Data Sync screen.

12. The first thing you will want to do is set up the *target* BICS instance. Enter your username and password to your BICS instance.

13. When you click the URL icon, you will be brought to a text box where you will enter your instance URL.

Reminder that this is in your initial subscription email. Copy this URL.

14. Paste the URL in the box.

15. IMPORTANT!! You will need to remove the “/analytics” at the end of the URL for the connection to work!

16. Test your connection.

Hopefully you will see the connection tested successfully.

17. I am choosing to upload a Garmin CSV extract to my BICS instance, so I am choosing a “File Source” as my connection type.

18. To actually load my flat file, I need to go to the “Project” tab, then click on the “File Data” tab, and click “New”.

19. You will be brought to a wizard. First things first…choose the file.

20. Give the import some options.

21. Create the new target name for the database.

Note that the name listed below is not the name I eventually went with for the table name. I had errors (which you will see below) and when I went through the process again, I changed the name to “GarminData”.

22. You will have the option to modify the data types of the columns. I knew that the last two columns were numbers, so I changed from VARCHAR to NUMBER.

Note: This is where I made a mistake that will come back to haunt me in a bit.

23. I can now see my job in the list.

24. To run the job to load the data, click the button saying “Run Job”.

25. You will get confirmation that the job ran.

26. However, it doesn’t mean that the job ran successfully! I can see that the job ran 0% successfully.

27. You can see the logs in the following location:

28. When I open the log, I can see the details:

29. At the end of the line, I see what the detailed error was:

30. Which makes me need to redo my column data types. Specifically, resetting the 50’s to 38’s.

31. When I try to rerun the job, it runs successfully.

32. And since we all know that I don’t trust the information in front of me, I went into my BICS instance to see if the data was there in the catalog. Not sure about data yet, but the table is there!