Skip to main content

Data Warehousing Objects and its Definitions



Fact tables and dimension tables are the two types of objects commonly used in dimensional data warehouse schemas.

Fact tables are the large tables in your data warehouse schema that store business measurements. Fact tables typically contain facts and foreign keys to the dimension tables. Fact tables represent data, usually numeric and additive, that can be analyzed and examined. Examples include Sales, Cost and Profit.

Dimension tables, also known as lookup or reference tables, contain the relatively static data in the data warehouse. Dimension tables store the information you normally use to contain queries. Dimension tables are usually textual and descriptive and you can use them as the row headers of the result set. Examples are Customers or Products

Fact Table:
A fact table typically has two types of columns: those that contain numeric facts (often called measurements), and those that are foreign keys to dimension tables. A fact table contains either detail-level facts or facts that have been aggregated. Fact tables that contain aggregated facts are often called summary tables. A fact table usually contains facts with the same level of aggregation.

Fact tables are summarized into three types:
1   .   Additive
2   .   Semi-Additive
3   .   Non-Additive
Additive facts can be aggregated by simple arithmetical addition. A common example of this is sales.Semi-additive facts can be aggregated along some of the dimensions and not along others. An example of this is inventory levels, where you cannot tell what a level means simply by looking at it. Non-additive facts cannot be added at all. An example of this is averages.

Requirements of Fact Table:
You must define a fact table for each star schema. From a modeling standpoint, the primary key of the fact table is usually a composite key that is made up of all of its foreign keys.

Dimension Table:
A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.
Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

Commonly dimension tables are categorized into three types:
1      Conformed dimension
2      Degenerated dimension
3.   Junk dimension

If we are saying in simple word, Conformed dimension table is a table which is having at least one or multiple primary key or unique key to join with one or multiple fact table. For example product table is related with the sales fact table
A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.
Dimensions are conformed when they are either exactly the same (including keys) or one is a perfect subset of the other. Most important, the row headers produced in the answer sets from two different conformed dimensions must be able to match perfectly.
A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.
A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators. These kinds of attributes are typically remaining when all the obvious dimensions in the business process have been identified and thus the designer is faced with the challenge of where to put these attributes that do not belong in the other dimensions.
One solution is to create a new dimension for each of the remaining attributes, but due to their nature, it could be necessary to create a vast number of new dimensions resulting in a fact table with a very large number of foreign keys. The designer could also decide to leave the remaining attributes in the fact table but this could make the row length of the table unnecessarily large if, for example, the attributes is a long text string.
Refer Ralph Kimball’s explanation about Junk Dimension
Hierarchies
Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.

Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies—one for product categories and one for product suppliers.

Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.
When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.

Levels
A level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies.

Level Relationships
Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy.
Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.

Comments

Popular posts from this blog

OBIEE 11.1.1.7.0 Step by Step Installation

This post will guide us through the  step by step installation of OBIEE 11.1.1.7.0. Here I don't find any difference in RCU installation between in previous version with this version. Still if you want to know the RCU installation stepsclick here
Step1: Download the OBIEE 11g versionfrom here or from oracle e-delivery site and extract all the four disk into a single folder and click setup.exe from the Disk1
Universal Installer window will start and will check for the initial set up and system requirement. After the successful precheck the first step of our OBIEE 11.1.1.7.0 starts with the below window. Just Click 'Next'
Step2:
If you have oracle Support username and password then update then use that in this step or just select first option 'Skip Software updates' and click 'Next'
Step3:
Select the appropriate installation step here. I am going to install all the component of BI so selected here 'Enterprise Install' and then click 'Next'
Step4:
This …

Step by Step Installation of Oracle 11g 64bit in Windows7 64bit

This Post will give you step by step information of Oracle database 11g 64 bit version installation in windows 7 64 machine. If you prefer with Oracle Database 11g installation in Windows 64bit machine, This post will help you to move forward.
Prerequisites Checks: RequirementMinimum ValuePhysical memory (RAM)1 GB minimum
On Windows 7 and Windows 8, 2 GB minimumVirtual memoryIf physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM
If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk spaceTotal: 5.39 GBProcessor TypeAMD64, or Intel Extended memory (EM64T)Video adapter256 colorsScreen Resolution1024 X 768 minimum
Download oracle Database from oracle site and unarchive the zipped download file.
Step1:  Just double click on setup.exe from the unarchived folder of oracle database source. Step2: Oracle Universal Installer will check the disk space and initiates the installation Step3: If you have a oracle metalink ID then type your em…

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1