Step 1 - Analyzing your Business Model
Your first task is to thoroughly understand your business model. You have to 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 allowing analysts to structure queries in the same intuitive fashion as they would ask business questions. This model must be one that business analysts will inherently understand and that will answer meaningful questions correctly.
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?
- Are there hierarchical elements in each dimension and what are the parent-child relationships that 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:
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. All dimensional models build on a star schema. That is, they model some measurable facts that are viewed in terms of various dimensional attributes. Below are some concepts used in Analyzing the Business Model for designing your repository.
Fact Tables: It is a table with measures that are typically calculated data such as dollar value or quantity sold and they can be specified in 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.
Dimension Tables: A business uses facts to measure performance by well-established dimensions, for example, by time, product and market. Dimension tables contain attributes that describe business entities. Dimension table attributes provide context to numeric data, such as being able to categorize service requests. The best method to identify dimensions and their attributes is to talk with the analysts in the organization who will use the data.
Bridge Tables: A bridge table resides between the fact table and the dimension table where there is many-to-many relationship between the two types of tables. For example, if Employees table is a fact table and Jobs table is a dimension table, it is possible that an employee can have multiple jobs such as clerk and programmer. Additionally, the job of programmer can be held by many employees. There is a many-to-many relationship here between the Employees table and the Jobs table. The Bridge table lists out the Employee ID and Job ID in a separate table to account for the many-to-many relationship.
Identifying Dimension Hierarchies: A hierarchy is a parent-child relationship between certain attributes within a dimension. These hierarchy attributes, called levels, roll up from child to parent; for ex, months can roll up into a year. Similarly consider the hierarchies in the Time-Period dimension that need to be defined such as Month/Year to Quarter, Days to Week, Weeks to Year, etc.
Star and Snowflake Models: Star schemas have one-to-many relationships between the logical dimension tables and the logical fact table. For example the Logical Dimension Tables, such as the products, customers, regions, time periods have a one-to-many relationships with the Logical Fact Table - Sales.
Snowflake schemas have the same type of relationship, but also include one-to-many relationships between elements in the dimensions. For example, Town's Table has a one-to-many relationship with Sales Table and State Table in turn has to one-to-many relationship with Town's Table, and so on...
It is recommended to use the Star schemas and minimize use of Snowflake schemas.
Step -2: Creating the Physical Schema, Business Model and Presentation Layers |
