1. Creating Analytics for Risk Management
  2. Subject Areas and Star Schemas

Subject Areas and Star Schemas

Each subject area is built on a star schema approach. A star includes "facts" and "dimensions":

  • At the center of the star is a fact: something you can count. In the Compliance Real Time subject area, for example, facts about the Control object include the count of controls and the count of key controls.

  • Dimensions branch out from facts, and they define characteristics about the fact. For example, the Control object has dimensions such as Control Name, Type, and Frequency.

Objects have defined relationships to one another, and subject areas are built on star schemas that reflect these relationships. In Oracle Fusion Cloud Financial Reporting Compliance, for example:

  • The Process object is the hierarchical parent of the Risk object. In the Compliance Real Time subject area, a Process fact extends to dimension details pertaining to processes and to risks.

  • The Risk object is the hierarchical child of the Process object, but parent of the Control object. In the Compliance Real Time subject area, a Risk fact extends to dimension details pertaining to processes, risks, and controls.

  • The Control object is the hierarchical child of the Risk object. In the Compliance Real Time subject area, a Control fact extends to dimension details pertaining to controls and to risks.

  • In the Compliance Real Time subject area, Process and Control have no dimensions in common.

When an analysis reports on unrelated objects, be sure it includes a fact for each of those objects. For example, you're building an analysis that documents the relationships among object records. It includes Process Name, Risk Name, and Control Name columns. Your data includes objects that have the following relationships to one another:

  • Process1 is related to Risk1, which is related to Control1 and Control2.

  • Process2 is related to Risk2 and Risk3.

    • Risk2 is related to Control1 and Control2.

    • Risk3 is related to Control2.

If you were to build the analysis without adding any facts, however, it wouldn't return any values for Process Name. It would return one value each for Risk1 and Risk2, and for Control1 and Control2, but it wouldn't show their relationships to other objects.

That's because the Process star schema and the Control star schema aren't related by any common dimensions. To show the proper output, you need to include a process fact and a control fact. The Process and Control stars then have a relationship through the Risk star, and so the analysis would return the proper results. If you don't want the analysis to show the facts you've added, you can hide them.

Knowing the relationships between the objects you're working with is key to building analyses. Basically, if you find an analysis doesn't return the results you expect, try adding a fact from each dimension in your analysis one by one, until it returns the data you expect.

However, because facts are the center of a star schema and serve as a gateway between dimensions and other star schemas, one fact may override another fact and cause a join between dimensions you didn't intend. Your best bet is to start small, run your analysis, and add to it across dimensions. That method enables you to tweak the facts required for inclusion as you go.