{"id":3222,"date":"2021-06-08T18:05:43","date_gmt":"2021-06-08T18:05:43","guid":{"rendered":"https:\/\/www.biconnector.com\/blog\/?p=3222"},"modified":"2021-09-26T09:55:37","modified_gmt":"2021-09-26T09:55:37","slug":"oracle-analytics-cloud-oac-5-best-practices-for-data-modeling","status":"publish","type":"post","link":"https:\/\/www.biconnector.com\/blog\/oracle-analytics-cloud-oac-5-best-practices-for-data-modeling\/","title":{"rendered":"Oracle Analytics Cloud (OAC): 5 Best Practices For Data Modeling"},"content":{"rendered":"\n

Data-driven decision-making is inevitable in today\u2019s business world. <\/p>\n\n\n\n

Companies of all sizes are investing heavily in data visualization tools and analytics platforms such as Oracle Analytics Cloud or OAC.<\/p>\n\n\n\n

\n

Oracle Analytics Cloud (OAC): Jumpstart Guide >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

Though data visualizations play a critical role in uncovering insights, they rely heavily on data models to get the job done.<\/p>\n\n\n\n

In this blog post, we\u2019ll cover 5 data modeling tips for OAC users.<\/p>\n\n\n\n

What is Data Modeling?<\/h2>\n\n\n\n

In simple terms, data modeling is the process of creating multiple data tables (ex: Customers, Orders, Products, etc) and creating appropriate associations between them.<\/p>\n\n\n\n

Before you start modeling the data for analysis in any data viz\/analytics platform (including OAC), you should understand the importance of data literacy<\/a>.<\/p>\n\n\n\n

\n

How to Improve OBIEE Data Literacy With a Fail-safe Approach? >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

In order to create the right data models, one has to understand the 2 types of data tables – Fact tables, and Dimension tables.<\/p>\n\n\n\n

Fact Tables<\/h3>\n\n\n\n

Fact tables are tables recording the transactional data. <\/p>\n\n\n\n

For example, the Orders table is a fact table. A customer can place more than one order for various products at different times. <\/p>\n\n\n\n

In the Orders table, the Order ID column has unique values for each record and used to identify each order.<\/p>\n\n\n\n

Dimension Tables<\/h3>\n\n\n\n

Dimension tables are the tables in which the data stored are less likely to change with time. <\/p>\n\n\n\n

For example, the Customers table, which stores the customer info such as first and last names, email addresses, phone numbers, cities, state, country, etc. <\/p>\n\n\n\n

The information about a customer is less likely to change. Even if it changes, the data is updated in the existing record, rather than creating a new record for that customer. <\/p>\n\n\n\n

\n

How to Retain OBIEE\/OAC Joins and Aggregations in Power BI? >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

In the case of dimension tables, the users will have to be aware of the column used as the primary key. (i.e) the column with a unique id (say customer id, product id) assigned for each record in a dimension table.<\/p>\n\n\n\n

These keys will be used to associate the dimension tables data with the fact tables data for analysis.<\/p>\n\n\n\n

Once you\u2019re sure about which fact and dimension tables, and the primary keys you\u2019re going to use for analysis, you can go ahead with your data modeling.<\/p>\n\n\n\n

What is Oracle Analytics Cloud?<\/h2>\n\n\n\n

Oracle Analytics Cloud is Oracle\u2019s cloud-based Analytics platform. The platform is suitable for individuals, teams, and enterprises.<\/p>\n\n\n\n

OAC comes with a tonne of features ranging from data preparation to data visualization to machine learning to Natural Language (NLP\/NLG) and mobile capabilities.<\/p>\n\n\n\n

One of the major advantages of OAC is it doesn\u2019t limit the scope of self-service to data preparation and data visualization. It extends the scope to the Machine Learning features as well, helping users build no-code models.<\/p>\n\n\n\n

Another benefit of OAC (especially over Oracle Analytics Server – OAS) is that organizations don’t have to worry about hosting or updates. They are all taken care of by Oracle. OAC comes with automated lifecycle management capabilities. <\/p>\n\n\n\n

\n

OAC vs OAS – When to Choose What? >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

If you\u2019re using Oracle Business Intelligence Enterprise Edition (OBIEE), the migration path to OAC migration depends mainly on the OBIEE version (11g or 12c) you use. However, in both cases, the steps are simple as exporting files to OAC and getting started with it, as both are Oracle products.<\/p>\n\n\n\n

Many organizations, despite moving to OAC, are connecting Power BI or Tableau to OAC (and OBIEE\/OAS) via BI Connector, as a major chunk of their users are already familiarized with Power BI or Tableau, and prefer sticking to them.<\/p>\n\n\n\n

\n

Supercharge OBIEE and OAC With Power BI\/Tableau >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

From a pricing point of view, OAC is priced based on the number of users\/month or OCPU\/hour. <\/p>\n\n\n\n

OAC comes in multiple plans within the professional and enterprise editions, allowing organizations to flexibly choose the best option that suits their needs. More pricing and plan info here<\/a>.<\/p>\n\n\n\n

Now that we\u2019ve looked at the basic information about data modeling and OAC, let\u2019s jump into the core topic – 5 best practices for data modeling in OAC.<\/p>\n\n\n\n

OAC: 5 Best Practices for Data Modelling<\/h2>\n\n\n\n

In OAC, the 5 best practices for data modeling are:<\/p>\n\n\n\n

  1. Use star schema<\/li>
  2. Create separate logical table for each fact\/dimension tables<\/li>
  3. Rename logical column names with presentation-friendly labels<\/li>
  4. Retain only the required columns in the logical layer<\/li>
  5. Follow a standard naming convention<\/li><\/ol>\n\n\n\n

    Let\u2019s now look at each of them in detail.<\/p>\n\n\n\n

    \n

    Snowflake For Data Lake Analytics – Jumpstart Guide >><\/b><\/p>\n<\/div>\n<\/div><\/a>\n\n\n\n

    1. Use star schema<\/li><\/h3>\n\n\n\n

      In OAC, the data model used for analysis must be a star schema. When you identify the fact and dimension tables, you must associate them in the star schema model.<\/p>\n\n\n\n

      Star schema might sound to be a technical term, but it\u2019s a lot simpler.<\/p>\n\n\n\n

      To explain star schema with an example, let\u2019s consider 3 tables:<\/p>\n\n\n\n