You can add your own data to visualizations for analysis and exploration.
Here are the common tasks for adding data from data sources.
Task | Description | More Information |
---|---|---|
Add a connection |
Create a connection if the data source that you want to use is either Oracle Applications or a database. |
|
Create a data set |
Upload data from spreadsheets. Retrieve data from Oracle Applications and databases. Creating a data set from Oracle Applications or a database requires you to create a new connection or use an existing connection. |
Adding a Spreadsheet as a Data Source |
Blend data |
Blend data from one data source with data from another data source. |
|
Refresh data |
Refresh data for the files when newer data is available. Or refresh the cache for Oracle Applications and databases if the data is stale. |
|
Extend uploaded data |
Add new columns to the data set. |
|
Control sharing of data sets |
Specify which users can access the data that you added. |
|
Remove data |
Remove data that you added. |
A data source is any tabular structure. You get to see data source values after you load a file or send a query to a service that returns results (for example, another Oracle Business Intelligence system or a database).
A data source can contain any of the following:
Match columns - These contain values that are found in the match column of another source, which relates this source to the other (for example, Customer ID or Product ID).
Attribute columns - These contain text, dates, or numbers that are required individually and aren’t aggregated (for example, Year, Category Country, Type, or Name).
Measure columns - These contain values that should be aggregated (for example, Revenue or Miles driven).
You can analyze a data source on its own, or you can analyze two or more data sources together, depending on what the data source contains.
When you save a project, the permissions are synchronized between the project and the external sources that it uses. If you share the project with other users, then the external sources are also shared with those same users.
Combining Subject Areas and Data Sources
A subject area either extends a dimension by adding attributes or extends facts by adding measures and optional attributes. Hierarchies can’t be defined in data sources.
A subject area organizes attributes into dimensions, often with hierarchies, and a set of measures, often with complex calculations, that can be analyzed against the dimension attributes. For example, the measure net revenue by customer segment for the current quarter and the same quarter a year ago.
When you use data from a source such as an Excel file, it adds information that is new to the subject area. For example, suppose you purchased demographic information for postal areas or credit risk information for customers and want to use this data in an analysis before adding the data to the data warehouse or an existing subject area.
Using a source as standalone means that the data from the source is used independently of a subject area. It’s either a single file used by itself or it’s several files used together and in both cases a subject area is not involved.
Note the following criteria to extend a dimension by adding attributes from a data source to a subject area:
Matches can be made to a single dimension only.
The set of values in matched columns must be unique in the data source. For example, if the data source matches on ZIP code, then ZIP codes in the source must be unique.
Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.
All other columns must be attributes.
Note the following criteria for adding measures from a data source to a subject area:
Matches can be made to one or more dimensions.
The set of values in matched columns doesn’t need to be unique in the data source. For example, if the data source is a set of sales matched to date, customer, and product, then you can have multiple sales of a product to a customer on the same day.
Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.
A data source that adds measures can include attributes. You can use these attributes alongside external measures and not alongside curated measures in visualizations. For example, when you add a source with the sales figures for a new business, you can match these new business sales to an existing time dimension and nothing else. The data might include information about the products sold by this new business. You can show the sales for the existing business with those of the new business by time, but you can’t show the old business revenue by new business products, nor can you show new business revenue by old business products. You can show new business revenue by time and new business products.
Working with Sources with no Measures
Note the following if you’re working with sources with no measures.
If a table has no measures, it’s treated as a dimension. Note the following criteria for extending a dimension:
Matches can be between one or composite columns. An example of a one column match is that product key matches product key. For composite columns, an example is that company matches company and business unit matches business unit.
All other columns must be attributes.
Dimension tables can be matched to other dimensions or they can be matched to tables with measures. For example, a table with Customer attributes can be matched to a table with demographic attributes provided both dimensions have unique Customer key columns and Demographic key columns.
Working with Sources with Measures
Note the following if you are working with sources with measures.
You can match tables with measures to other tables with a measure, a dimension, or both.
When you match tables to other tables with measures, they don’t need to be at the same grain. For example, a table of daily sales can be matched to a table with sales by Quarter if the table with the daily sales also includes a Quarter column.
Working with Matching
If you use multiple sources together, then at least one match column must exist in each source. The requirements for matching are:
The sources contain common values (for example, Customer ID or Product ID).
The match must be of the same data type (for example, number with number, date with date, or text with text).
You can create, edit, and delete database connections and use the connections to create data sources from databases.
You can create connections to databases and use those connections to source data in projects.
After you create database connections, you can use those connections to create data sets.
You can edit a database connection. For example, you can change the name of the connection.
You can delete a database connection. For example, you must delete a database connection and create a new connection when the database's password has changed.
Note:
If the connection contains any data sets, then you must delete the data sets before you can delete the connection.You can create Oracle Application data sources that help you visualize, explore, and understand the data in your Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas and analyses.
You can create connections to Oracle Applications and use those connections to create data sets.
Note:
The connection is visible only to you (the creator), but you can create and share data sets for it.You use the Oracle Applications connection type to access the Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas that you want to use as data sets.
You use the Oracle Applications connection type to access the analyses that were created in Oracle Fusion Applications with Oracle Transactional Business Intelligence and Oracle BI EE subject areas that you want to use as data sources
You can edit Oracle Applications connections. For example, you must edit a connection if your system administrator changed the Oracle Applications login credentials.
You can delete an Oracle Applications connection. For example, if your list of connections contains unused connections, then you can delete them to help you keep your list organized and easy to navigate.
Note:
If any data sets use the connection, then you must delete the data sets before you can delete the connection. Oracle Applications connections are only visible to the user that creates them (connections aren’t shared), but a user can create data sets using those connections, and share the data sets with others.You can create connections to Dropbox and use those connections to source data in projects.
You can create connections to Google Drive or Google Analytics and use those connections to source data in projects.
You can create generic JDBC connections to databases and use those connections to source data in projects. For example, to connect to databases that aren’t listed with the default connection types.
\lib
directory.Note:
If you import a project containing a JDBC connection into a Data Visualization installation where the JDBC driver isn’t installed, the import still works. However, the connection doesn’t work when you try to run the project or Data Flow. You must recreate the JDBC connection, and JDBC driver to a suitable data source.You can create generic ODBC connections to databases and use those connections to source data in projects. For example, to connect to databases and database versions that aren’t listed with the default connection types.
You can only use generic ODBC connections to connect on Windows systems.
Note:
If you import a project containing an ODBC connection into a Data Visualization installation where the ODBC DSN doesn’t exist, and the ODBC driver isn’t installed, the import still works. However, the connection doesn’t work when you try to run the project or Data Flow. You must recreate the ODBC connection, and recreate the ODBC DSN, and ODBC driver to a suitable data source.You can create connections to Oracle Autonomous Data Warehouse Cloud and use those connections to source data in projects.
You can create a connection to access data in Oracle Big Data Cloud Compute Edition and use those connections to source data in projects.
You can create connections to Oracle Talent Acquisition Cloud (OTAC) to access data for analysis and use those connections to source data in projects.
You can add a spreadsheet as a data source. You can browse for and upload spreadsheets from a variety of places, such as your computer, Google Drive, and Dropbox.
Data source files from a Microsoft Excel spreadsheet file must have the XLSX extension (signifying a Microsoft Office Open XML Workbook file). You can also add CSV and TXT files.
Before you can upload a Microsoft Excel file as a data set, you must structure the file in a data-oriented way and it mustn‘t contain pivoted data. Note the following rules for Excel tables:
Tables must start in Row 1 and Column 1 of the Excel file.
Tables must have a regular layout with no gaps or inline headings. An example of an inline heading is one that repeats itself on every page of a printed report.
Row 1 must contain the table’s column names. For example, Customer Given Name, Customer Surname, Year, Product Name, Amount Purchased, and so on. In this example:
Column 1 has customer given names.
Column 2 has customer surnames.
Column 3 has year values.
Column 4 has product names.
Column 5 has the amount each customer purchased for the named product.
The names in Row 1 must be unique. Note that if there are two columns that hold year values, then you must add a second word to one or both of the column names to make them unique. For example, if you have two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.
Rows 2 onward are the data for the table, and they can’t contain column names.
Data in a column must be of the same kind because it’s often processed together. For example, Amount Purchased must have only numbers (and possibly nulls), enabling it to be summed or averaged. Given Name and Surname must be text as they might be concatenated, and you may need to split dates into their months, quarters, or years.
Data must be at the same granularity. A table can’t contain both aggregations and details for those aggregations. For example, if you have a sales table at the granularity of Customer, Product, and Year, and contains the sum of Amount Purchased for each Product by each Customer by Year. In this case, you wouldn’t include Invoice level details or a Daily Summary in the same table, as the sum of Amount Purchased wouldn’t be calculated correctly. If you have to analyze at invoice level, day level, and month level, then you can do either of the following:
Have a table of invoice details: Invoice Number, Invoice Date, Customer, Product, and Amount Purchased. You can roll these up to day or month or quarter.
Have multiple tables, one at each granular level (invoice, day, month, quarter, and year).
You can upload an Excel spreadsheet, CSV file, or TXT file dta source located on your computer to use as a data set.
Before you add a spreadsheet as a data set, confirm you’ve done the following:
Confirm that you have either an Excel spreadsheet in .XLSX format or a CSV or TXT file as the data source used to create a data set.
For an Excel spreadsheet, ensure that it contains no pivoted data.
Understand how the spreadsheet needs to be structured for successful import. See About Adding a Spreadsheet as a Data Set.
Follow these steps to add a spreadsheet from your computer and use it as a data source:
If you’re storing spreadsheets in Dropbox or Google Drive you can add a spreadsheet to create a data set.
Before you add a spreadsheet from Dropbox or Google Drive, do the following:
Confirm that a connection exists. See Creating Connections to Dropbox and Creating Connections to Google Drive or Google Analytics.
Confirm that the spreadsheet you want to use is either an Excel spreadsheet in .XLSX format, a CSV file, or a TXT file.
For an Excel spreadsheet, ensure that it contains no pivoted data.
Understand how the spreadsheet needs to be structured for successful import. See About Adding a Spreadsheet as a Data Set.
Binning a measure creates a new column based on the value of the measure. You can assign a value to the bin dynamically by creating the number of equal size bins (such as the same number of values in each bin), or by explicitly specifying the range of values for each bin.