OAC―Knowing the Dimensional Modelling Basics (2/2)

Video 1. Create your initial data model from Relational Sources using Data Modeler

Video 2.  Create Time Dimension Tables Using Data Modeler

In a previous article "OAC―Knowing the Dimensional Modelling Basics (1/2)", we have covered basic concepts of dimensional modeling:

• Transactional versus Analytical Systems
• Data Warehousing
• Dimensions, Facts and Hierarchies

In this article, we will cover how Dimensional Modelling works in Oracle Analytics Cloud (OAC).

Figure 1.  Open Data Modeler in Oracle Analytics Cloud by clicking the action menu

Data Modeler

Using Data Modeler (see Figure 1) you can model data from various source types, such as star and snowflake, in various ways that make sense to business users.

Although not all source objects have star relationships, Data Modeler presents data as a simple star structure in the data model. In other words, the data model represents measurable facts that are viewed in terms of various dimensional attributes.

If you modeled your business data with Oracle BI Enterprise Edition, you don't have to start from scratch with Data Modeler. You can upload your data model to the cloud.^[14]

Figure 2.  Left Pane in Data Modeler

Figure 3.  Action menu associated with Objects in Data Modeler

Figure 4. Create Data Model in OAC

Video 3.  Create Hierarchies for Dimension Tables Using Data Modeler

Data Modeling Tasks

When building a data model in OAC, you perform the following tasks:^[3]

• Connect to the database containing your business data
• In Data Modeler, the left pane shows the Database, Data Model, Variables, or Roles menu (see Figure 2).
• Action menus contain actions that are relevant for a particular object or context, and are visible when the object is selected.
• For example, if a source object in the Database menu is selected, its action menu shows: Add to Model, Inspect, and Duplicate (see Figure 3).
• Add source tables or views to the model and classify them as either a fact table or a dimension table. 
• After you have started modeling data, the right pane in Data Modeler shows the fact tables, dimension tables, and joins (see Figure 4)
• The right pane in Data Modeler is a contextual pane that changes depending on what task you’re performing. 
• Define joins between fact and dimension tables 
• Ensure Correct Mapping 
• Every dimension table maps to at least one fact table, and that every fact table maps to at least one dimension table. 
• Edit object with its editor
• Specify aggregation rules for different fact columns
• Create derived measures based on expressions
• Create dimension hierarchies to support drilling
• See video 3
• Create level-based measures. 
• Publish data model 
• To permanently save the changes and make the data available for use in analyses
• When your HTTP browser session times out (after 20 minutes of inactivity), the lock is released and any unpublished changes are discarded.
• Similarly, closing a browser ends the HTTP session and discards any unpublished changes. 

Figure 5.  Create project in Oracle Analytics Cloud to use newly published data model (see video 1&2)

Figure 6.  Published Cust Rev Model displayed as a Subject Area

Visualizations / Dashboards / Analyses

After publishing your data model, you can start visualizing your data from your enterprise reporting Home page (see Figure 5 and watch video 1). Your data model displays as a subject area that you can use in visualizations, dashboards, and analyses (see Figure 6). The name of the subject area matches the name of your data model.

When you model source objects with multiple star relationships, they’re all part of the same data model and are included in the same subject area. 

Video 4.  Manage What Users Can See and Do in Oracle Analytics Cloud

Figure 7.  Use Console to create and manage application roles

Figure 8.  Configure application roles from the Administer Users and Roles page in the Console

Figure 9.  Application Roles tab shows predefined and custom application roles

Figure 10.  Add new members or remove members from an application role

Application Roles

You must have the BI Data Model Author role (including table access permissions) to author data models.

Predefined Application Roles in Oracle Analytics Cloud	Description	Default Members	Predefined Application Role in Oracle Identity Cloud Service Foot 1
BI Service Administrator	Allows users to administer Oracle Analytics Cloud and delegate privileges to others using the Console.	Administrator who created the service	ServiceAdministrator
BI Data Model Author	Allows users to manage data models in Oracle Analytics Cloudusing Data Modeler	BI ServiceAdministrator	ServiceAdministrator

Administrators configure what users see and do in Oracle Analytics Cloud from the Administer Users and Roles page in the Console (see Figure 7&8). This page presents user information in 3 different views:

Users and Roles Page	Description
Users tab	Shows users from the identity domain associated with your service. You can add users, delete users, and assign users one or more application roles in Oracle Analytics Cloud.
Roles tab	Shows roles from the identity domain associated with your service. You can add and remove roles (groups of users), and assign them to one or more application roles in Oracle Analytics Cloud. From the Roles tab you can also see who belongs to each role.
Application Roles tab	Shows predefined application roles for Oracle Analytics Cloud together with any custom application roles you define. From the Application Roles tab you can assign application roles to multiple users, roles, and other application roles. You can also create application roles of your own and assign privileges to them through other application roles.

You select members for an application role or change parent privileges using the Console.

1. Click Console (see Figure 7).
2. Click Service Administration, and then click Administer Users and Roles (see Figure 8).
3. Click the Application Roles tab (see Figure 9).
4. To display all available application roles, leave the Search field blank and Show Members: All.  To filter the list by name, enter all or part of an application role name in the Search filter and press Enter. The search is case-insensitive, and searches both name and display name.
5. Look in the Members area to see who belongs to each application role:
• The number of users, roles, and application roles that are members displays on the page. Click a number, such as 5 in this image, to see those members in more detail (either users, roles or application roles).
6. To add new members or remove members from an application role (see Figure 10):
1. Click Members.
2. Select either users, roles, or application roles from the Type box and click Search to show the current members.
3. Use the shuttle controls to move members between the Available and All Selected list.
• Some application roles aren't eligible to be members and these are grayed. For example, you can’t select a parent application role to be a member.
4. Click OK.
7. To see whether an application role, such as Sales Analyst, inherits privileges from other application roles:
1. Click the action menu.
2. Select Manage Application Roles.
• Inherited privileges are displayed in the Selected Application Roles pane. (see Figure 11)
8. To add or remove privileges:
1. Click Search to display all available application roles.
• Alternatively, enter all or part of an application role name and click Search.
2. Use the shuttle controls to move application roles between the Available Application Roles list and the Selected Application Roles list.
• You can’t select application roles that are grayed out. Application roles are grayed out so you can’t create a circular membership tree.
3. Click OK.

Figure 11.  User Ed Ferguson was assigned with the Sales Analysts application role

References

1. OAC―Knowing the Dimensional Modelling Basics (1/2)
2. Model Data for Reports (OAC)
3. Use Data Modeler (OAC)
4. Oracle® CloudPreparing Data in Oracle Analytics Cloud
5. OAC―Knowing Machine Learning Basics
6. Data Visualization (Forum)
7. Oracle Data Visualization Desktop (Documentation)
8. OAC―Loading Data with Data Sync
9. Edit Hierarchies and Levels (OAC Data Modeler)
10. Get Started with Oracle Data Visualization V5 (YouTube)
11. Explore Data in Oracle Data Visualization V5 (YouTube)
12. Create Data Flows in Oracle Data Visualization V5 (YouTube)
13. Data Science for Business (Safari)
14. Upload Data Models from Oracle BI Enterprise Edition
15. Learn Modern Data Visualization with Oracle Analytics
16. Click here for more A-Team Oracle Analytics (OAC) Blogs.

OAC―Knowing the Dimensional Modelling Basics (1/2)

Video 1.  Dimensional Modeling – Declaring Dimensions (YouTube link)

Operational Processing vs Data Warehousing

One of the most important assets of any organization is its information. This asset is almost always used for two purposes:^[1]

	Operational Processing	Analytical Decision Making
Flavor	Transactional	Analytical
Main Data Flow	The operational systems are where you put data in	The Data Warehousing and Business Intelligence (DW/BI) systems are where you get the data out
Optimization	Optimized to process transactions quickly	Optimized for high-performance queries
# of Transactions in Processing	Almost always deal with one transaction record at a time	Often require that many transactions be searched and compressed into an answer set
History Preservation	Typically do not maintain history, but rather update data to reflect the most current state	Typically demand that historical context be preserved to accurately evaluate the organization's performance over time

Dimensional Modeling (DW/BI)

Dimensional modeling is one of the methods of data modeling, that is the preferred technique for presenting analytic data.  It helps us store the data in such a way that it is relatively easy to retrieve the data from the data once the data is stored in database.

This is the reason why dimensional modeling is used mostly in data warehouses built for reporting. On the other side, dimensional model is not a good solution if your primary purpose of your data modeling is to reduce storage space requirement, reduce redundancy, speed-up loading time etc.^[1]

Figure 1.  Star Schema (left) vs OLAP Cube (right)

3NF Model vs Dimensional Model

Although dimensional models are often instantiated in relational database management systems (RDMS), they are quite different from third normal form (3NF) models which seek to remove data redundancies:

• 3NF Model (or Normalized Model)
• Divides data into many discrete entities, each of which becomes a relational table
• Sometimes are referred as entity-relationship (ER) models
• Entity-relationship diagrams (ER diagrams or ERDs) are drawings that communicate the relationships between tables
• Designed to reduce the duplication of data and ensure referential integrity
• Designed to improve database processing while minimizing storage costs
• Useful in operational processing because an update or insert transaction touches the database in only one place
• However, are too complicated for BI queries. 
• Dimensional Model (Star Schemas and OLAP Cubes)
• Both stars and cubes have a common logical design with recognizable dimensions; however, the physical implementation differs (see Figure 1):
• Star Schemas
• Referred to as star schemas in RDBS because of their resemblance to a star-like structure in RDMS implementation
• OLAP Cubes
• Referred to as online analytical processing (OLAP) cubes in multidimensional database platform
• Cubes can deliver superior query performance because of the precalculations, indexing strategies, and other optimizations
• The downside is that you pay a load performance price for these capabilities, especially with large data sets
• Contains the same information as a normalized model, but packages the data in a format that delivers user understandability, query performance, and resilience to change

Both 3NF and dimensional models can be represented in ERDs because both consist of joined relational tables; the key difference between 3NF and dimensional models is the degree of normalization.

Although the capabilities of OLAP technology are continuously improving, we generally recommend that detailed, atomic information be loaded into a star schema; optional OLAP cubes are then populated from the star schema.^[1]

Dimensional Modeling Case Study

Consider the business scenario for a fast food chain:^[2]

The business objective is to create a data model that can store and report number of burgers and fries sold from a specific McDonalds outlet per day. 

Below are the steps used for dimensional modeling: 

• Identify the dimensions
• Dimensions 
• Describe the “who, what, where, when, how, and why” associated with the business process measurement event (e.g. a sales transaction).
• In the above scenario, we have 3 dimensions - "food" (e.g. burgers and fries), "store" and "day"
• Separate dimension tables are created for separate dimensions
• The dimension tables contain the textual context (normally with set of descriptive nouns that characterize the business process) associated with a measurement event. 
• Identify the measurement events (or facts)
• Measurement Events
• A measurement event in the physical world has a one-to-one relationship to a single row in the corresponding fact table
• In the above scenario, we have 1 measurement event - "quantity"
• A fact table is created for storing measures and foreign keys to the dimension tables
• The fact table stores the "number" of food sold in "Quantity" column against a given store, food and day columns. 
• These store/food/day columns are basically foreign key columns of the primary keys in respective dimension tables. 
• Identify the attributes or properties of dimensions
• Attributes (or Properties)
• Each dimension might have number of different properties, but for a given context, not all of them are relevant for business
• Knowing the properties let us decide what columns are required to be created in each dimension table.
• In the above scenario, we could have
• Food: name (burgers or fries)
• Store: name, location, etc
• Day: date
• Identify the granularity of the measures
• All the measurement rows in a fact table must be at the same grain (i.e., day or month). 
• Having the discipline to create fact tables with a single level of detail ensures that measurements aren't inappropriately double-counted.
• History Preservation (Optional)
• Identify which dimensions are slowly changing (or fast changing or unchanging) is the last and final step of modeling (see video 1)
• There are 8 different dimension types, but only 3 are commonly used:^[3]
• Type 0 - Fixed, non changing attribute
• Type 1 - Changing attribute, no history kept
• Type 2 - Most complex, keeps historical changes

Figure 2.  Sample rows from a dimension table with denormalized hierarchies

Summary

• Dimensional Model (cf. Normalized Model )
• Dimensional schema is simpler and symmetric
• Business users benefit from the simplicity because the data is easier to understand and navigate
• Database optimizers process these simple schemas with fewer joins more efficiently
• Every dimension is equivalent; all dimensions are symmetrically-equal entry points into the fact table.
• Dimensional models are gracefully extensible to accommodate change
• With dimensional models, you can add completely new dimensions to the schema as long as a single value of that dimension is defined for each existing fact row.
• Fact Tables
• Fact tables tend to be deep in terms of the number of rows, but narrow in terms of the number of columns
• The most useful facts are numeric and additive, such as dollar sales amount. 
• Additivity is crucial because BI applications rarely retrieve a single fact table row.
• However, you will see that facts are sometimes semi-additive (e.g., account balances) or even non-additive (e.g., unit prices). 
• Facts are often described as continuously valued 
• Fact tables usually make up 90 percent or more of the total space consumed by a dimensional model. 
• All fact tables have two or more foreign keys that connect to the dimension tables' primary keys.
• Fact tables (or bridge table) express many-to-many relationships. 
• Dimension Tables
• Dimension tables tend to be shallow in terms of the number of rows, but wide in terms of the number of columns
• Each dimension is defined by a single primary key (surrogate key or natural key) , which serves as the basis for referential integrity with any given fact table to which it is joined.
• Robust dimension attributes deliver robust analytic slicing-and-dicing capabilities.
• In many ways, the data warehouse is only as good as the dimension attributes; the analytic power of the DW/BI environment is directly proportional to the quality and depth of the dimension attributes.
• Dimension attributes serve as the primary source of query constraints, groupings, and report labels.
• You should strive to minimize the use of codes or cryptic abbreviations in dimension tables by replacing them with more verbose textual attributes.
• Dimension tables often represent hierarchical relationships (See Figure 2)
• For example, products roll up into brands and then into categories. 
• For each row in the product dimension, you should store the associated brand and category description. 
• The hierarchical descriptive information is stored redundantly in the spirit of ease of use and query performance.
• You should resist the habitual urge to normalize data (i.e., snowflaking)
• You should almost always trade off dimension table space for simplicity and accessibility.
• Because dimension tables typically are geometrically smaller than fact tables, improving storage efficiency by normalizing or snowflaking has virtually no impact on the overall database size. 
• Fact or Dimension Attribute
• When triaging operational source data, it is sometimes unclear whether a numeric data element is a fact or dimension attribute.  It is
• A fact if
• The column is a measurement that takes on lots of values and participates in calculations
• A dimension attribute if
• The column is a discretely valued description that is more or less constant and participates in constraints and row labels
• Note:
• Continuously valued numeric observations are almost always facts; discrete numeric observations drawn from a small list are almost always dimension attributes.

References

1. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling
2. What is dimensional modelling?
3. Dimensional Modeling – Declaring Dimensions (Youtube)
4. Learn Modern Data Visualization with Oracle Analytics
5. Click here for more A-Team Oracle Analytics (OAC) Blogs.