Modeling Your Data with Self-Service Data Sets in Oracle Analytics

May 24, 2021 | 4 minute read
Pravin Janardanam
Director, Product Management, Oracle Analytics
Text Size 100%:

The Oracle Analytics 6.0 release introduces self-service modeling capabilities in data sets. 

Prior to this release data sets were based on only one table. This could be data from a worksheet in an Excel file, a CSV file, a database table, or an object from a SaaS application. To create a data model containing data from multiple tables, you created a data set for each table, added the data sets to a project and created relationships between matching fields in the project. A model implicitly existed within a project.

With the new self-service modeling feature, you can add tables from one or more connections to a dataset and define joins using the matching columns in a data set. This in turn lets you create a data model with a data set. This data set can be used in multiple projects for analysis. 


Self-Service Data Modeling with Data Sets

 

Check the following videos to create data sets with multiple tables from the same connection or different connections and how to blend the data sets.


Creating Data Sets with Multiple Tables

Adding multiple tables to a data set helps to:

  • Create a data model that defines relationships between data from multiple data sources like relational databases and SaaS applications in a single data set.
  • Provide consistent results across multiple projects since the same data set can be used in multiple projects.
  • Applying transformations and recommendations to multiple data set tables in a data set. These transformations on multiple tables in a data set can be reused in many projects.
  • Share a data set with other users and roles which in turn shares the entire model in the data set.
  • Create a schedule to refresh the data periodically for all tables in a data set.

Multi Table Datasets in Oracle Analytics (1/5)

Add multiple tables to a data set and define joins to create a data set that acts as a self-service data model for analysis

Now that you have learned how to create a data set with multiple tables, here are some additional information to help you start building your data sets.

  • When creating a data set, start by adding the primary table for your analysis. This could be a transaction table or a fact table and then add other tables that provide context for your analysis.
  • By default, any joins that are defined in the data source are automatically created in the data set. If joins don’t exist in the data source, then Oracle Analytics creates joins between data set tables based on matching column names.
  • Columns are treated as measures by default if they are if they are of number data types. To create a join based on a number column, treat the column as an attribute first before creating the join condition.
  • In some cases, the data in two tables that you want to join might not match and may need to be massaged before you can create a join condition. In such cases, you can transform the data in the transformation editor and then use the column output to create a join condition.


Blending Data Sets for Analysis Based on Multiple Fact Tables

To analyze data based on two fact tables with conformed dimension tables, create two data sets and blend the data sets in projects

In the above video you have learned that to perform an analysis that includes Sales and Costs, you must create a data set for Sales analysis and another data set for Cost analysis, and then blend the two data sets in a project on conformed columns in both data sets. 

In addition to defining joins between tables within data sets, you still have the option to blend data sets with multi tables in a project. For an analysis that involve cross functional analysis or cross fact table analysis, create a data set for each area or fact table and then blend the data sets in projects.

Creating a Hybrid Data Set

Create a data set with tables from different connections like Oracle Service Cloud and Oracle Autonomous Data Warehouse

In the above video you have learned how to create a data set with tables from multiple heterogeneous connections. This provides a powerful capability to create a data model that combines data from SaaS applications and relational data sources. Oracle Analytics federates the queries across multiple connections and combines the results from each of the queries to enable cross- functional analysis across applications to self-service users.

With self-service modeling capabilities in data sets, you can now create data models that spans multiple heterogeneous connections. These data sets still retain all the powerful preparation capabilities that you are used to in Oracle Analytics along with other features like indexing data sets and sharing data sets with others.

To learn how you can benefit from the latest features in Oracle Analytics, visit Oracle.com/Analytics, and don’t forget to subscribe to get the latest posts sent to your inbox.

 

Pravin Janardanam

Director, Product Management, Oracle Analytics


Previous Post

Oracle Analytics Best Practices: Time Dimension Design

NICOLAS BARASZ | 3 min read

Next Post


Why the 2021 Gartner's Critical Capabilities for Analytics and Business Intelligence Platforms is a must-read 

John Hagerty | 5 min read