1 Get Started with Data Sources in Oracle Analytics
Topics
About Data Sources
You can connect to many types of data source, such as Cloud databases, on-premises databases, and many commonly used applications, such as Dropbox, Google Drive, and Amazon Hive.
You create a connection for each data source that you want to access in Oracle Analytics. Once connected, you can visualize your data to create insights.
A data source is any tabular structure. You see data source values after you load a file or send a query to a service that returns results.
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. 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.
When you save a workbook, the permissions are synchronized between the workbook and the external sources that it uses. If you share the workbook with other users, then the external sources are also shared with those same users.
Any data that you upload (as a dataset) is stored securely in Oracle Cloud.
Data Sources and Subject Areas
You can combine data sources with subject areas to explore and analyze the data.
A subject area either extends a dimension by adding attributes or extends facts by adding measures and optional attributes. You can't define hierarchies 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, the file 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 or several files used together, and in both cases doesn't involved a subject area.
You can extend a dimension by adding attributes from a data source to a subject area:
-
You can only make matches to a single dimension.
-
The set of values in matched columns are unique in the data source. For example, if the data source matches on ZIP code, then ZIP codes in the source are unique.
-
You make matches 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.
You can add measures from a data source to a subject area:
-
You make matches to one or more dimensions.
-
The set of values in matched columns aren't necessarily 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.
-
You make matches 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 city and state from separate columns creates the composite City_State in a customers address.
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.
Data Sources and Measure Columns
You can work with data sources that either include or don't include a measure column.
-
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, the tables don’t need to be at the same grain. For example, you can match a table of daily sales table to a sales by quarter table.
A table with no measures is treated as a dimension.
-
Matches can be between single or composite columns. A single column match might be the product key in one table matching the product key in another. A composite column match might be where company and business unit in one table matches company and business unit in the other table.
-
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.