Dimension Tables for Textual Context Usage

Dimension tables are integral part of any fact table dimensional modeling schema. Dimension tables contain textual context that are associated with a business process measurements stored in fact tables.

Such a table can contain anywhere between 20 to 100 attributes or columns. These tables are smaller in terms of number of rows but are considered wide with much large text content.  Each dimensional table has to have a primary key through which they can be linked or associated to the fact table. The attributes serve as primary source of grouping, query filters, constraints or labels. Attributes can at times have cryptic or coded names but it is considered good practice to name them according to the description of the content of a column. This makes it easier for user/developer to understand and use rather than memorizing what each coded name means.  Although many time the operational codes have some logic to the naming convention which is aligned with the applications and the source data used. For example, the first four letters could be related to area and the next three could be district and so on.

It is said that the analytical power of any DW/BI is directly proportional to the quality and depth of the attributes.  A dimensional table designed to contain multiple levels ranging from the most granular to the highest possible under the business rules and capabilities will give the most flexibility in usage of the contained data in any Business Intelligence application or tools. These levels being in hierarchical grouping allows different tools to produce resultant data in the form required or as needed according to the specification.  The hierarchical data should have description added as a column in spite of the redundancy of data. This allows ease of use and better overall query performance. Dimensional tables should not be normalized to a degree where descriptive information is stored in another supplemental table in form of snow flaking. Since dimensional tables have significantly fewer rows than a fact table, further normalization would rarely make a difference in database size.