Planning Your Business Model
Planning your business model is the first step in developing a usable data model for decision support.
After you have followed the planning guidelines in this section, you can begin to create your repository.
Analyzing Your Business Model Requirements
Your first task is to thoroughly understand your business model requirements.
You must first understand what business model you want to build before you can determine what the Physical layer needs to have in it.
In a decision support environment, the objective of data modeling is to design a model that presents business information in a manner that parallels business analysts' understanding of the business structure. A successful model allows the query process to become a natural process by enabling analysts to structure queries in the same intuitive fashion as they would ask business questions. This model must be one that business analysts inherently understand and that answers meaningful questions correctly.
Unlike visual SQL tools such as Oracle BI Publisher, the business model defines the analytic behavior of your BI application. In contrast, the Physical layer only provides the components used to assemble a physical query mapped from business model logic.
This requires breaking down your business into several components to answer the following questions:
-
What kinds of business questions are analysts trying to answer?
-
What are the measures required to understand business performance?
-
What are all the dimensions under which the business operates? Or, in other words, what are the dimensions used to break down the measurements and provide headers for the reports?
-
Are there hierarchical elements in each dimension, and what types of relationships define each hierarchy?
After you have answered these questions, you can identify and define the elements of your business model.
Identifying 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 is 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:
Identifying 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, which instead define facts at the lowest grain of the table.
Measures aggregated from facts must be defined in a logical fact table. Measures are typically calculated data such as dollar value or quantity sold, and they can be specified 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. Also, aggregation rules can be specific to particular dimensions. The Oracle BI Server lets you define complex, dimension-specific aggregation rules.
You do not explicitly label tables in the Business Model and Mapping layer as fact tables or dimension tables. Rather, 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 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 Administration Tool, right-click a fact table, and select Business Model Diagram > Whole Diagram.
Identifying 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 by well-established dimensions, for example, by time, product, and market. Every dimension has a set of descriptive attributes. Dimension tables also contain primary keys that identify each member. Unlike logical fact tables, which are different from physical fact tables in relational models, logical dimension tables behave very much like relational dimension tables.
Dimension table attributes provide context to numeric data, such as being able to categorize Service Requests. Attributes stored in this dimension might include Service Request Owner, Area, Account, Priority, and so on.
Dimensions in the business model are conformed dimensions. In other words, even if a particular data source has five different instances of a particular Customer table, the business model should only have one table. To achieve this 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 you to combine data from multiple fact sources at different grains. They also enable you to federate multiple data sources.
In the business model, use business keys for dimension and level keys rather than generated surrogate keys. 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.
Although generated surrogate keys can still exist in the Physical layer, where they are useful for their query performance advantages on joins, you typically do not have surrogate key columns in the Business Model and Mapping layer at all.
Identifying 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 may be 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 Business Intelligence also supports another type of level-based dimension, called a time dimension, that provides functionality for modeling time series data. In level-based hierarchies, levels roll up from lower level to higher level, for example, months can roll up into a year. These rollups 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 do not have explicitly named levels. There is no limit to the number of implicit levels in a parent-child hierarchy.
To define your hierarchies, you define the contains relationships in your business such as geographical, product, or time relationships to drive rollup 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 does not have the Holiday attribute.
-
Are the levels fixed at design time (year-quarter-month), or can run-time 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.
See Working with Logical Dimensions for more information.
About Dimensions with Multiple Hierarchies
Dimensions can contain multiple hierarchies.
For example, time dimensions often have one hierarchy for the calendar year, and another hierarchy for the fiscal year.
Note:
Dimensions with multiple hierarchies must always end with the same leaf table.
The image shows a dimension with multiple hierarchies in the Business Model and Mapping layer of the Oracle BI Administration Tool.
Description of the illustration GUID-36D61851-B553-425D-9EAE-E12CDE26DBF5-default.gif
Identifying 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 System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about localization and lookup tables.
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 value that are looked up in a different data source.