Dimensions and Measures in Datawarehouse

Data warehouse consist of both dimensions and measures.

Dimensions are descriptive details about various objects allowing for their detailed analysis. For example time dimension allows us to see object with respect to year, quarter, month, day and hour. And customer dimensions helps in analyzing the customer who affects the business more.

Measures unlike dimensions give the fact numeric value instead of the detailed analysis.

Different type of measures are listed below:

• Additive measures are measures that can be added across all dimensions. For example customer count in numbers can be added across all dimensions.
• Semi-additive measures are measures that can be added across some, but not all dimensions. For example the bank account balance is simply a snapshot in time and cannot be added over time. However you could add multiple accounts of the same customer to get the total balance for that customer.
• Non-additive measures are measures that cannot be added across any dimensions. For example the procurement is simply a snapshot in time and cannot be summed over time. Also you cannot combine procurement for various items.

Conformed Dimension in Obiee

The best definition about Conformed dimension is that it is dimension which is consistent across the whole business and can be linked with all the facts to which it relates .The best example is the Date Dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning across all the facts. The month January will be the same for all the departments in an organization. Unless there are some departments that operate on a different fiscal calendar to the rest of the organization  we can consider date dimension as conformed.

• Conformed dimensions should be defined at the most granular level so that each record in these tables corresponds to a single record in the fact able.
• Data should never be defined for a specific function or department. Good data is one, which is widely shareable and conformed dimensions help in doing that.
• Conformed dimension ensures that the facts and measures are the same across all facts and data marts so that reporting is consistent.
• A conformed dimension is important because it allows to Drill Across. i.e linking of two different fact tables with same granularity.

All dimensions in your warehouse need to be conformed to get the exact power of a Data warehouse. Below are some of the  commonly used  Conformed Dimensions:

Customer

Product

Date/Time

Employee

Account

Region /Territory

Vendor

What are the different kind of FACT Tables in OBIEE ?

There are basically three types of fact tables:

• Transaction Fact Table
• Periodic Snapshot Fact Table
• Accumulating Snapshot Fact Table

Transaction Fact Tables

A transactional table is the most basic and fundamental view of the business’s operations These fact tables represent an event that occurred at an instantaneous point in time. A row exists in the fact table for a given customer or product only if a transaction event occurred. Conversely, a given customer or product likely is linked to multiple rows in the fact table because hopefully the customer or product is involved in more than one transaction. Transaction data often is structured quite easily into a dimensional framework. The lowest-level data is the most naturally dimensional data, supporting analyses that cannot be done on summarized data. Unfortunately, even with transaction-level data, there is still a whole class of urgent business questions that are impractical to answer using only transaction detail.

Periodic Snapshot Fact Tables

Periodic snapshots are needed to see the cumulative performance of the business at regular, predictable time intervals. Unlike the transaction fact table, where we load a row for each event occurrence, with the periodic snapshot, we take a picture (hence the snapshot terminology) of the activity at the end of a day, week, or month, then another picture at the end of the next period, and so on. Eg: A performance summary of a salesman over the previous month .

The periodic snapshots are stacked consecutively into the fact table. The periodic snapshot fact table often is the only place to easily retrieve a regular, predictable, trendable view of the key business performance metrics.Periodic snapshots typically are more complex than individual transactions.

Advantages:When transactions equate to little pieces of revenue, we can move easily from individual transactions to a daily snapshot merely by adding up the transactions, such as with the invoice fact tables from this chapter. In this situation, the periodic snapshot represents an aggregation of the transactional activity that

occurred during a time period. We probably would build the daily snapshot only if we needed a summary table for performance reasons.

Where to use Snapshots?

When you use your credit card, you are generating transactions, but the credit card issuer’s primary source of customer revenue occurs when fees or charges are assessed. In this situation, we can’t rely on transactions alone to analyze revenue performance. Not only would crawling through the transactions be time-consuming, but also the logic required to interpret the effect of different kinds of transactions on revenue or profit can be horrendously complicated. The periodic snapshot again comes to the rescue to provide management

with a quick, flexible view of revenue.

Accumulating Snapshot Fact Tables

This type of fact table is used to show the activity of a process that has a well-defined beginning and end, e.g., the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated.

Accumulating snapshots almost always have multiple date stamps, representing the predictable major events or phases that take place during the course of a lifetime. Often there’s an additional date column that indicates when the snapshot row was last updated. Since many of these dates are not known when the fact row is first loaded, we must use surrogate date keys to handle undefined dates.

In sharp contrast to the other fact table types, we purposely revisit accumulating snapshot fact table rows to update them. Unlike the periodic snapshot, where we hang onto the prior snapshot, the accumulating snapshot merely reflects the accumulated status and metrics. Sometimes accumulating and periodic snapshots work in conjunction with one another

Also read about OBIA

What is a FACTLESS FACT TABLE?Where we use Factless Fact

We know that fact table is a collection of many facts and measures having multiple keys joined with one or more dimesion tables.Facts contain both numeric and additive fields.But factless fact table are different from all these.

A factless fact table is fact table that does not contain fact.They contain only dimesional keys and it captures events that happen only at information level but not included in the calculations level.just an information about an event that happen over a period.

A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of factless fact tables include:

• Identifying product promotion events (to determine promoted products that didn’t sell)
• Tracking student attendance or registration events
• Tracking insurance-related accident events
• Identifying building, facility, and equipment schedules for a hospital or university
  

Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information.There are two types of factless fact tables: those that describe events, and those that describe conditions. Both may play important roles in your dimensional models.

Factless fact tables for Events
The first type of factless fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless.Sometimes there seem to be no facts associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.

For eg.

The above fact is used to capture the leave taken by an employee.Whenever an employee takes leave a record is created with the dimensions.Using the fact FACT_LEAVE we can answer many questions like

• Number of leaves taken by an employee
• The type of leave an employee takes
• Details of the employee who took leave

Factless fact tables for Conditions
Factless fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events.It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.

For eg, fact_promo gives the information about the products which have promotions but still did not sell

This  fact answers the below questions:

• To find out products that have promotions.
• To find out products that have promotion that sell.
• The list of products that have promotion but did not sell.

This kind of factless fact table is used to track conditions, coverage or eligibility.  In Kimball terminology, it is called a "coverage table."

Note:

We may have the question that why we cannot include these information in the actual fact table .The problem is that if we do so then the fact size will increase enormously .

Factless fact table is crucial in many complex business processes. By applying you can design a dimensional model that has no clear facts to produce more meaningful information for your business processes.Factless fact table itself can be used to generate the useful reports.

Please Check out our Latest Post: How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL

What is a DATAWAREHOUSE? Advantage of a Datawarehouse

In 1993, the “father of data warehousing”, Bill Inmon, gave this definition of a data warehouse: 

“A datewarehouse is a subject oriented , integrated,non volatile and time variant collection of data in support of managements decisions” 
According to this definition, a data warehouse is different from an opera­tional database in four important ways.

Data Warehouse	Operational Database
subject oriented	application oriented
integrated	multiple diverse sources
time-variant	real-time, current
nonvolatile	updateable

Comparing a Data Warehouse and an Operational Database

An operational database is designed primarily to support day to day opera­tions.  A data warehouse is designed to support strategic decision making.

Data warehouse assembles data dispersed in various data sources across the enterprise and helps business stakeholders manage their operations by making better informed decisions. Most organizations have multiple data stores: relational databases, spreadsheets, mainframes, mail systems or even paper files. Each of these data stores tends to serve a subset of the enterprise. Data warehouse attempts to overcome this limitation by combining all relevant data and by allowing managers to view their business from many different angles.
Data warehouses are built using dimensional data models which consist of fact and dimension tables. Dimension tables are used to describe dimensions; they contain dimension keys, values and attributes. For example, the time dimension would contain every hour, day, week, month, quarter and year that has occurred since you started your business operations. Product dimension could contain a name and description of products you sell, their unit price, color, weight and other attributes as applicable.

Benefits of Data Warehousing

Data warehousing is being hailed as one of the most strategically significant developments in information processing in recent times.  One of the reasons for this is that it is seen as part of the answer to information overload. 

Some of the benefits of data warehousing that were seen as relevant to the Avondale College project are listed here.  The points highlighted in the Bill Inmon’s definition give some of the reasons why data warehousing is regarded as important.
 

1.      Has a subject area orientation

2.      Integrates data from multiple, diverse sources

3.      Allows for analysis of data over time

4.      Adds ad hoc reporting and enquiry

5.      Provides analysis capabilities to decision makers

6.      Relieves the development burden on IT

7.      Provides improved performance for complex analytical queries

8.      Relieves processing burden on transaction oriented databases

9.      Allows for a continuous planning process

10.  Converts corporate data into strategic information

Before building a data warehouse you should become familiar with the terminology used to describe various parts of the warehouse.

1.Dimensions 

Dimension tables are typically small, ranging from a few to several thousand rows.Occasionally dimensions can grow fairly large, however.For example, a large credit card company could have a customer dimension with millions of rows. Dimension table structure is typically very lean, for example customer dimension could look like following: 

Customer_key
Customer_full_name
Customer_city
Customer_state
Customer_country

Although there might be other attributes that you store in the relational database, data warehouses might not need all of those attributes. For example, customer telephone numbers, email addresses and other contact information would not be necessary for the warehouse. Keep in mind that data warehouses are used to make strategic decisions by analyzing trends. It is not meant to be a tool for daily business operations. On the other hand, you might have some reports that do include data elements that aren't necessary for data analysis.
Most data warehouses will have one or multiple time dimensions. Since the warehouse will be used for finding and examining trends, data analysts will need to know when each fact has occurred. The most common time dimension is calendar time. However, your business might also need a fiscal time dimension in case your fiscal year does not start on January 1st as the calendar year.
Most data warehouses will also contain product or service dimensions since each business typically operates by offering either products or services to others. Geographically dispersed businesses are likely to have a location dimension.

2.Facts
Fact tables contain keys to dimension tables as well as measurable facts that data analysts would want to examine. For example, a store selling automotive parts might have a fact table recording a sale of each item. The fact table of an educational entity could track credit hours awarded to students. A bakery could have a fact table that records manufacturing of various baked goods.

Fact tables can grow very large, with millions or even billions of rows. It is important to identify the lowest level of facts that makes sense to analyze for your business this is often referred to as fact table "grain". For instance, for a healthcare billing company it might be sufficient to track revenues by month; daily and hourly data might not exist or might not be relevant. On the other hand, the assembly line warehouse analysts might be very concerned in number of defective goods that were manufactured each hour. Similarly a marketing data warehouse might be concerned by the activity of a consumer group with a specific income-level rather than purchases made by each individual.

Data Staging Area

A storage area and set of processes that clean, transform, combine, de-duplicate,household, archive, and prepare source data for use in the data warehouse.The data staging area is everything in between the source system and the presentation server.Although it would be nice if the data staging area were a single centralized facility on one piece of hardware, it is far more likely that the data staging area is spread over a number of machines. The data staging area is dominatedby the simple activities of sorting & sequential processing and, in some cases, the data staging area does not need to be based on relational technology.

Below are the few other terms which comes mostly in OLAP side of Datawarehousing

1.Hierarchy 
It defines parent-child relationships among various levels within a single dimension. For instance in a time dimension, year level is parent of four quarters, each of which is a parent of three months, which are parents of 28 to 31 days, which are parents of 24 hours. Similarly in a geography dimension a continent is a parent of countries, country could be a parent of states, and state could be a parent of cities.

2.Level 
Level is a column within a dimension table that could be used for aggregating data. For example, product dimension could have levels of product type (beverage), product category (alcoholic beverage), product class (beer), product name (miller lite, budlite, corona, etc).

3.Drill-down
Drill-down is the repetitive selection and analysis of summarized facts, with each repetition of data selection occurring at a lower level of summarization.  An example of drill-down is a multiple-step process where sales revenue is first analyzed by year, then by quarter, and finally by month.  Each iteration of drill-down returns sales revenue at a lower level of aggregation along the period dimension.

4.Roll-up 
Roll-up is the opposite of drill-down.  Roll-up is the repetitive selection and analysis of summarized facts with each repetition of data selection occurring at a higher level of summarization.

5. Aggregation 
Aggregation is a key attribute of a data warehouse.  Summarization and consolidation are other words that used to convey the same meaning.  In the case of a multi-dimensional database, the summarizations are pre-computed for all the various combinations of the dimensions.  This allows for very fast response to slice and dice operations at any level of drill-down, and also allows for fast drill-down and roll-up operations.

6.Granularity 
Granularity is a term that is used to describe the level below which no supporting details are stored, only the summaries.  Good judgment needs to be exercised to determine granularity.  If the granularity is set to be too fine, unused data will be stored, wasting processing time during replication steps, and wasting disk space to hold it.  On the other hand, if the granularity is set too coarse, the detailed data will not be available if it is needed at some point in the future.

What are Dimension and Fact?

Dimensions are categories by which summarized data can be viewed. E.g. a profit summary in a fact table can be viewed by a Time dimension (profit by month, quarter, year), Region dimension (profit by country, state, city), Product dimension (profit for product1, product2).
A fact table is a table that contains summarized numerical and historical data (facts) and a multipart index composed of foreign keys from the primary keys of related dimension tables.
In data warehousing, a dimension is a collection of reference information about a measurable event. These events are known as facts and are stored in a fact table. Dimensions categorize and describe data warehouse facts and measures in ways that support meaningful answers to business questions. They form the very core of dimensional modeling.
Dimension tables are referenced by fact tables using keys. When creating a dimension table in a data warehouse, a system-generated key is used to uniquely identify a row in the dimension. This key is also known as a surrogate key. The surrogate key is used as the primary key in the dimension table. The surrogate key is placed in the fact table and a foreign key is defined between the two tables. When the data is joined, it does so just as any other join within the database.