Oracle Data Visualization Cloud Service – Managing Data Sources

Since Oracle has expanded its Data Visualization portfolio, (particularly with the launch of Data Visualization Cloud Service (DVCS) and Data Visualization Desktop (DVD)), I have written a couple blogs providing an overview of the two products’ key features and, more recently, an overview of access controls in DVCS.

This month’s post will focus particularly on managing data sources in DV and highlighting key differences between DVCS and DVD.

Data Sources

Data sources within DV can be managed by clicking on the ‘Data Sources’ card in the home page in both DVCS and DVD.

On entry, a list of the data sources that have already been created is displayed. Additionally, the user is presented with buttons to create a new data source or to manage connections.

Figure1. DVCS Data Sources.

Data can be retrieved from a number of sources as outlined in the table below.

While direct connection to on-premise databases in DVCS is not yet possible, it is possible to use other tools to add to DVCS data sources, for example:

• REST API
• Use the Oracle Data Visualization Cloud Service REST API to load on-premise data to DVCS
• Data Sync can upload on-premise data from the following source:
• CSV or Microsoft Excel file
• Relational source
• SQL Query
• Oracle Transactional Business Intelligence source

Data sources can also be created whilst in a Visual Analyzer project.

Database Connection in DVD

DVD has the advantage over DVCS of allowing direct connection to on-premise databases.
These include to:

• Oracle
• Spark
• SQL Server
• DB2
• MySQL
• Teradata
• Redshift
• Hive
• Impala
• MongoDB
• Sybase IQ
• Salesforce
• Greenplum (Beta)
• RightNow (Beta)

To do this in DVD, within Data Sources click on the “Connections” and then “Add Connection”, then select “From Database” card.

Enter the required connection details, including selecting from the list of database types.

Figure 2. DVD Database Connection.

Note: A new connection can be created by the same means via the “Create New Data Source” button.

To create a data source from the new connection, click on the “Create New Data Source”, then the “From Database” card. Your database connection is established, so select from the list of schema and then from the list of tables in the database to create your data source.  Alternatively, instead of selecting specific tables, the users can enter their own SQL to generate the data source.

Figure 3. DVD Database Table Data Source.

Once selected, the user can select the query mode:

• Use local cache
• Live – sends live database queries for every analysis

Figure 4. DVD Database Data Source.

Then “Add Data Source” and the EXTERNAL_CONTACT data source is now in the listed Data Sources.

DVCS Data Source via DataSync

So, in DVCS it is not possible, at the moment, to connect direct to databases. However, it is possible to upload to DVCS using DataSync. The screens below illustrate how to do this.

First, establish the source (database) and target (DVCS) connections.

Figure 5. DVCS DataSync Connections.

Second create a project. In the case below, this is based on SQL (Data From SQL), but it could equally have been based on a table (Data From Table). Specify the name of the target data set (what the data source will be called in DVCS). Then specify the database connection and the SQL which acts as the source of the data.

Figure 6. DataSync Create a Project.

Third, create a job to make the connections and transfer the data from database to DVCS. This job could be scheduled.

Figure 7. DataSync Create a Job.

Then run the job. The job completes successfully and the EXTERNAL_CONTACT data set is posted to DVCS Data Sources.

Figure 8. DVCS Data Sources.

Data Mashup

Once you have established all your Data Sources within DV, DV allows users to mashup the data to gain insights within Visual Analyzer (VA). DV automatically attempts to match data sources (find ‘joins’ between them) – if attributes in these data sources share the same name and datatype. This is quite a nice feature, but users will still need to understand the data sufficiently to ensure that the correct matches are made.

These matches are not made within the Data Sources menu, but when the data sources are added to VA projects. So, a VA project is created and a ‘Sales’ data source is added to the project, which contains attribute ‘Item ID’. If a second data source, ‘Items’, which also contains attribute ‘Item ID’, is added to the project DV will match these two datasets.

Figure 9. VA Matching Data Sources.

Matches can be removed or created manually.

Within VA a source diagram can be opened, which illustrates the matches between all the data sources used by the project. Matches can also be added or removed here.

Figure 10. VA Source Diagram.

Summary

So, we can see that DV has the ability to bring data together from multiple sources and help the user to mashup that data to help them understand that data better. Currently, DVD has the advantage of being able to establish direct connections to on-premise RDBMS. I understand that it is in the roadmap for Oracle to align DVCS with DVD for future releases. In the meantime, it is still possible to upload your on-premise data to DVCS using other tools such as DataSync or the DVCS REST API.

Next month I will illustrate some of the rich functionality available for users to gain insights into their data using Visual Analyzer and highlight key differences between DVCS and DVD.

Trial Data Visualization

Both DVCS and DVD are available to use to trial / evaluate:

• DVD no-production version available from OTN: Download DVD now from OTN
• DVCS available for 30 day free trial: Data Visualization Cloud Service (DVCS) and click ‘Try It’

Other Links

• Oracle Data Visualization Desktop community
• Oracle Help Center: Oracle Data Visualization Cloud Service – Get Started
• Oracle Help Center: Oracle Data Visualization Desktop – Get Started
• Oracle Help Center: Using Oracle Data Visualization Cloud Service – Adding Your Own Data
  
  
  

Author: Philip Ratcliffe

Job Title: Oracle APEX Development Consultant

Bio: Philip is a Development Consultant at DSP-Explorer. Building on considerable experience in development including using Oracle PL/SQL and supporting Oracle EBS, Philip is employing APEX to provide quality, bespoke software solutions to a range of organisations.