A data source is any tabular structure. The data source's data values are provided when 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 containing 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 containing text, dates, or numbers that are required individually and are not aggregated (for example, Year, Category Country, Type, or Name).
Measure columns containing 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 cannot be defined in external 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 an external 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 an external source as stand-alone means that the data from the external source is used independently of a subject area. It is either a single file used by itself or it is 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 an external 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 external data source. For example, if the data source matches on ZIP code, then ZIP codes in the external 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 an external data source to a subject area:
Matches can be made to one or more dimensions.
The set of values in matched columns does not need to be unique in the external 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 only alongside the 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 external 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 are working with sources with no measures.
If a table has no measures, it is 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.
If a table has one or more measures, it is not a dimension even if it has attributes. There are very few considerations other than the rules that we already described for XLSX files earlier - starting in Row, column 1 etc. Note if you are working with a spreadsheet, then the spreadsheet must be structured correctly. For more information, see About Adding a Spreadsheet as a Data Source.
Tables with measures can be matched to other tables with a measure, a dimension, or both.
Tables matched to other tables with measure do not 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 will 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).