Data warehouse – Identify Fact Tables

What is a fact table?

The fact table is the largest table at the center of the star schema. The fact table contains the business metrics (i.e., numerical measurements). The fact table is usually highly normalized, containing only keys and metrics.


1. Identify subject areas

Start by identifying the focus or topic of the analysis (e.g., sales, human resource, finance). A subject area must contain discrete metrics (e.g., sales contains dollars sold, units sold) and have a data source available (e.g., operational source data).

2. Identify the facts

a. Within each subject area, identify the operational transactions that depict key business events. A sample operational transaction is a customer purchasing transaction.

b. Examine the data created by these transactions and identify facts that are used by the business processes. Facts should be numeric, have a value, and be additive.

c. Confirm with the end-users that you have identified all the facts that the user wants to know about.

3. Identify the Data that References the Facts

a. Identify the major dimensions for each fact table. Start by examining the operational system's logical data model that contains the entity depicting the fact table. Identify the entities that are associated (i.e., have relationships) with the entity depicting the fact table. For example, the entity purchasing transaction has a relationship with the entity customer and with the entity product.

b. Use Data Warehouse Query Modelling to determine how the data will be analyzed (i.e., define the business queries). Structure the fact table's dimensions to represent the focus of the business queries.

4. Verify that a Fact is Really a Fact and a Dimension is Really a Dimension

a. Look for fact tables that contain both facts and dimensions. This often happens when fact tables are derived from operational entities. For example, a property entity in a property management operational system may contain the property address and a series of dates indicating when various events took place (e.g., the grass was mowed, the snow was shoveled). The property entity could be identified as a fact table, however this would be incorrect. The correct design would be to identify a property events fact table that contains a row for each event that occurred on the property. The property fact table would be associated with a location dimension table that contains the address and a time dimension table that contains the dates the events took place.

b. In many cases an entity (from a operational system) could be either a fact or a dimension. If the entity has four or more dimensions then it is probably a fact.

5. Identify Historical Requirements

a. Identify how long the detail facts need to be stored. The requirement will vary by business function and business area. In some cases the detailed information will be required for a long period of time (e.g., five to 10 years) but in many cases an aggregation and/or summarization of the data will be sufficient.

b. When the detail facts are no longer required on-line, they can be moved to near-line or off-line storage. Where the archived data is stored depends on how quickly the data must be restored.

6. Identified Required Attributes

a. For each fact table, identify the attributes required to create the business queries. A fact table attribute should provide information about a specific occurrence. Attributes (that are not keys) in a fact table should be summable. For example, store "unit sales" and "dollar sales" and derive "price."

b. Summarized data, and aggregated data should not be stored in fact tables. Use summary tables to address performance issues.

7. Identify the Keys

a. Every fact table has a primary key. A primary key uniquely identifies an occurrence of a fact.

b. A fact table's primary key is always a composite key. The composite key is composed of multiple foreign keys. The foreign keys document the relationships between the fact table and the dimension tables. A foreign key in a fact table is the primary key of a dimension table.

c. If a table has a composite key then it is a fact table. If a table does not have a composite key then it is a dimension table.

d. The foreign key of the fact table (i.e., primary keys of the dimension tables) can be the unique identifier used in the real world or a generated key that points to the real world unique identifier. Use the real world unique identifier if the identifier will not change during the life of the data warehouse, otherwise use a generated key.

8. Factless Fact Tables

a. A factless fact table is a fact table that does not contain any facts (i.e., metrics). Factless fact tables are used to track events that did, or did not, happen.

b. Use a factless fact table to track events of interest to the organization. For example, attendance at a cultural event can be tracked by creating a fact table containing the following foreign keys (i.e., links to dimension tables): event identifier, speaker/entertainer identifier, participant identifier, event type, date. This table can then be queried to find out information, such as which cultural events or event types are the most popular.

c. Factless fact tables can also be used to track events that did not happen. Using the example above we can also identify the events that were not attended or the event types that were the least popular.

Database Design simple steps

1) Requirement Formulation and Analysis

2) Conceptual Design

• ER Modeling
• ER Diagram Process
• From E-R Model to Database Design
• Normalization (1stNF, 2nd NF, 3rd NF)

3) Physical Design & Implementation


Download from here

Software architect - Responsibilites

A software architect is responsible for creating or selecting the most appropriate architecture for a system (or systems), such that it suits the business needs, satisfies user requirements, and achieves the desired results under given constraints. This article describes the myriad responsibilities of a software architect, and attempts to identify human personality traits that naturally aid a person in such position.

• abstracts the complexity of a system
• maintains control over the architecture
• stays on course in line with the long term
• progressively makes critical
• sets quantifiable
• works closely with executives
• inspires, mentors, and encourages colleagues
  
• creates and distributes tailored views of software
• acts as an agent of change

More>>

Duties, Skills, & Knowledge of a Software Architect

Crafting the right architecture to solve the problem at hand is only part of architects' responsibilities. They must also

• define, document, and communicate it
• make sure everyone is using it, and using it correctly
• make sure that it comes out in stages in a timely way so that the overall organization can make progress before it's complete
• make sure the software and system architectures are in synchronization
• act as the emissary of the architecture
• make sure management understands it (to the detail necessary)
• make sure the right modeling is being done, to know that qualities like performance are going to be met
• give input as needed to issues like tool and environment selection
• identify and interact with stakeholders to make sure their needs are being met
• make sure that the architecture is not only the right one for operations, but also for deployment and sustainment
• resolve disputes and make tradeoffs
• resolve technical problems
• maintain morale, both within the architecture group, and externally as well. The latter is done by providing a sound design, when needed, and providing good presentations and materials to let everyone know the organization is on the right track.
• understand and plan for evolutionary paths
• plan for new technology insertion
• manage risk identification and risk mitigation strategies associated with the architecture

For more details click here