Identify the Content of the Business Model
To determine what content to include in your business model, you must first identify the logical columns on which users need to query.
Then, you must identify each column's role as either a measure column or a dimensional attribute. Finally, you need to arrange the logical columns in a dimensional model based on the relevant roles, relationships between columns, and logic.
Businesses are analyzed by relevant dimensional criteria, and the business model is developed from these relevant dimensions. These dimensional models form the basis of the valid business models to use with the Oracle BI Server.
Although not all dimensional models are built around a star schema, it's a best practice to use a simple star schema in the business model layer. In other words, the dimensional model should represent some measurable facts that are viewed in terms of various dimensional attributes.
After you analyze your business model requirements, you need to identify the specific logical tables and hierarchies that you need to include in your business model.
This section contains the following topics:
Identify Logical Fact Tables
Logical fact tables in the Business Model and Mapping layer contain measures that have aggregations built into their definitions.
Logical fact tables are different from physical fact tables in relational models. Physical tables in rational models define facts at the lowest possible grain. Logical fact table can contain measures of different grains,
You must define measures aggregated from facts in a logical fact table. Measures are calculated data such as dollar value or quantity sold. You can specify measures in terms of dimensions. For example, you might want to determine the sum of dollars for a given product in a given market over a given time period.
Each measure has its own aggregation rule such as SUM, AVG, MIN, or MAX. A business might want to compare values of a measure and need a calculation to express the comparison. You can specify aggregation rules to specific dimensions. You can define complex, dimension-specific aggregation rules in the Oracle BI Server.
You don't explicitly label tables in the Business Model and Mapping layer as fact tables or dimension tables. The Oracle BI Server treats tables at the one end of a join as dimension tables, and tables at the many end of a join as fact tables.
The image shows the many-to-one joins to a fact table in a Business Model diagram. In the Business Model diagram, all joins have an arrow, indicating the one side, pointing away from the Fact-Pipeline table; no joins are pointing to it. For an example of this in a business model, open a repository in the Model Administration Tool, right-click a fact table, select Business Model Diagram, and then select Whole Diagram.
Identify Logical Dimension Tables
Dimension tables contain attributes that describe business entities such as Customer Name, Region, Address and Country.
A business uses facts to measure performance using established dimensions such as by time, product, and market. Every dimension has a set of descriptive attributes. Dimension tables contain primary keys that identify each member.
Dimension table attributes provide context to numeric data, for example, by providing the ability to categorize Service Requests. Attributes stored in a service requests dimension table could include Service Request Owner, Area, Account, and Priority.
Dimensions in the business model are conformed dimensions, that is those dimensions that are consistent across. If a specific data source has five different instances of a specific Customer table, the business model should only have one Customer table. To achieve conformance, all five physical source instances of Customer are mapped to a single Customer logical table, with transformations in the logical table source as necessary. Conformed dimensions hide the complexity of the Physical layer from users, and enable combining data from multiple fact sources at different grains. Conformed dimensions enable combining multiple data sources.
The business model uses business keys for a dimension and level keys instead of generated surrogate keys. For example, you would use Customer Name with values like Oracle instead of Customer Key with values like 1076823. Using business keys in the business model ensures that all sources for that dimension can conform to the same logical dimension table with the same logical key and level key.
Generated surrogate keys can exist in the Physical layer where the keys are useful for their query performance advantages on joins. The Business Model and Mapping layer doesn't have surrogate key columns.
Identify Dimensions
Dimensions are categories of attributes by which the business is defined.
Common dimensions are time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Within a given dimension, there are many attributes. For example, the time period dimension can contain the attributes day, week, month, quarter, and year. Exactly what attributes a dimension contains depends on the way the business is analyzed.
Dimensions contain hierarchies that are sets of top-down relationships between members within a dimension. There are two types of hierarchies:
-
level-based hierarchies (structure hierarchies)
-
parent-child hierarchies (value hierarchies)
In level-based hierarchies, members of the same type occur only at a single level, while members in parent-child hierarchies all have the same type. Oracle Analytics Server supports a time dimension level-based hierarchy that provides functionality for modeling time series data. In level-based hierarchies, levels roll up from a lower level to higher level, for example, months can roll up into a year. These roll ups occur over the hierarchy elements and span natural business relationships.
In parent-child hierarchies, the business relationships occur between different members of the same real-world type such as the manager-employee relationship in an organizational hierarchy tree. Parent-child hierarchies don't have explicitly named levels. There isn't a limit to the number of implicit levels in a parent-child hierarchy.
To define your hierarchies, you define the contains relationships in your business to drive roll up aggregations in all calculations, as well as drill-down navigation in reports and dashboards. For example, if month rolls up into year and an aggregate table exists at the month level, you can use the table to answer questions at the year level by adding up all of the month-level data for a year.
You must use the right type of hierarchy for your needs. To determine the appropriate type to use, consider the following:
-
Are all the members of the same type such as employee, assembly, or account, or are they of different types that naturally fall into levels such as year-quarter-month, continent-country-state/province, or brand-line-product?
-
Do members have the same set of attributes? For example, in a parent-child hierarchy like Employees, all members might have a Hire Date attribute. In a level-based hierarchy like Time, the Day type might have a Holiday attribute, while the Month type doesn't have the Holiday attribute.
-
Are the levels fixed at design time (year-quarter-month), or can runtime business transactions add or subtract levels? For example, if you can add a level when the current lowest-level employee hires a subordinate, who then is the new lowest level.
-
Are there constraints in your primary data source that require a certain hierarchy type? If your primary data source is modeled in one way, you might need to use the same hierarchy type in your business model, regardless of other factors.
About Dimensions with Multiple Hierarchies
Dimensions can contain multiple hierarchies. Dimensions with multiple hierarchies must always end with the same leaf table.
For example, time dimensions often have one hierarchy for the calendar year, and another hierarchy for the fiscal year.
The image shows a dimension with multiple hierarchies in the Business Model and Mapping layer.
Description of the illustration mult_hier.gif
Identify Lookup Tables
When you need to display translated field information from multilingual schemas, you create a logical lookup table that corresponds to a lookup table in the Physical layer.
A lookup table stores multilingual data corresponding to rows in the base tables. Before using a specific logical lookup table, you must designate the table as a lookup table in the General tab of the Logical Table dialog. See Localizing Metadata Names in the Repository in Administering Oracle Analytics Server.
You can use lookup tables to display one set of values to users, while using a different, corresponding set of values in the physical query. You can use the lookup table to provide human-readable values that are looked up in a different data source.