For a normalized schema, the multidimensional model is optional and purely logical. Star schema databases, on the other hand, are specifically designed to be modeled in a multidimensional, hierarchical fashion. The dimensions and measures are defined at the physical layer.
A star schema organizes measures into a fact table that is typically quite large. Dimensions are grouped into separate dimension tables, with dimensions that form a natural hierarchy grouped into a single table. Therefore, the logical multidimensional relationships are built into the physical design, with a single join between a dimensional table and the fact table. This design delivers a simpler view of the data and makes it easier to avoid pitfalls.
The snowflake schema is a variation of the star schema wherein dimensional tables are normalized. In some cases, redundancy in dimensional tables becomes excessive; the normalization in a snowflake schema improves performance.
View more examples in the InetSoft visualization gallery
In general, the star schema and snowflake schema are referred to as ROLAP technology. Because most design decisions are shifted to the design of the schema itself, modeling ROLAP in the Data Modeler is extremely straightforward. Each physical view readily maps into a fact table and its dimension tables. The multidimensional hierarchy model is created by mapping the built-in hierarchical relationships within each dimension table and exposing the measures from the fact table.
A physical view’s design should transform a generic schema into a business intelligence-friendly schema. Even though the physical view is not directly accessed by end users, its design impacts the logical model, which is accessed by end users.
The join relationships among tables form the foundation for a physical view. Physical views are independent; that is, a relationship in one physical view will not impact another.
A subject contains a main business topic and related information. For example, a CRM database includes company information, individual contact information, and sales opportunity information. It is best to segment the schema into three separate physical views, but each physical view can contain some redundant information and interlink keys. For example, the sales opportunity view may include some company information but not the company change history, which is available only in the company view.
Properly exposed interlink keys allow power users to dynamically combine subject information using Data Block technology. Even though subject-based design reduces the potential for problems, the traps within each subject still need to be identified and resolved.
Copyright © 2024, InetSoft Technology Corp.
