By: S. Peterson
Published in: PLoPD1
Summary: An easy-to-query schema for decision-support systems.
Online transaction processing (OLTP) systems are usually optimized for recording business transactions. Develop a new database optimized for easy querying. The new database will probably be implemented on a different machine from the one that hosts the OLTP database.
To understand a schema for your business, use easily recognized names for the objects in Query Optimized Database. Find entities in your domain that are directly relevant to your problem by examining the reports you use to monitor your business. Make a list of these entities.
You're using Whole Business Entities. Characterize entities by how they are related to each other. By defining these relationships, you're defining the role of each entity.
You're using Key Business Activities and Influences. For each activity, create a fact table with all the information and the transaction history for the activity.
You're using Key Business Activities and Influences. Create a table for each person, place, or thing that has a part in the transactions you're examining. These tables should model the entity.
To measure changes in business activity, data from a range of specified times must be analyzed. Create a dimension table containing units of time that correspond to some significant event in your business.
You've applied Transaction History. You want to roll up the level of activity in your business so you can see the big picture for some group of records in a table. Create a table to represent the larger organization to encompass your business dimension. This table should include all information related to the thing to be included on a report. These things should already be listed in Whole Business Entities