Planning a Data Warehouse - System Design
(Page 5 of 6 )
The systems design phase is important because it determines how the requirements will be met. The main focus of this phase is to convert the systems requirements into a set of system-level specifications through deriving logical and physical data models for the data warehouse (i.e., E-R Diagrams and MetaModels).
Dimensional modeling is the design concept used by many data warehouse designers to build their data warehouse. Dimensional model is the underlying data model used by many of the commercial OLAP products available today in the market. In this model, all data is contained in two types of tables called Fact Table and Dimension Table.
Fact Table
Fact table contains the measurements or metrics or facts of business processes. If your business process is Sales, then a measurement of this business process such as "monthly sales number" is captured in the fact table. In addition to the measurements, the only other things a fact table contains are foreign keys for the dimension tables.
Context of the measurements is represented in dimension tables. You can also think of the context of a measurement as the characteristics such as who, what, where, when, how of a measurement (subject). In your business process Sales, the characteristics of the 'monthly sales number' measurement can be a Location (Where), Time (When), Product Sold (What).
The Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country='USA'. The dimension attributes also contain one or more hierarchical relationships.
Before designing your data warehouse, you need to decide what this data warehouse contains. Say if you want to build a data warehouse containing monthly sales numbers across multiple store locations, across time and across products then your dimensions are:
Location
Time
Product
Each dimension table contains data for one dimension. In the above example you get all your store location information and put that into one single table called Location. Your store location data may be spanned across multiple tables in your OLTP system (unlike OLAP), but you need to de-normalize all that data into one single table.
The specifications are then used to generate the data warehouse extractors and transformation, integration, summarization, and aggregation software. Next, a "Build versus Buy" trade-off analysis is performed. Decisions are complicated by technical, economic, and political considerations. A poor decision can ruin an otherwise successful analysis and design. On the other hand, an appropriate selection of vendor-supplied component or incorporation of existing investment can make it possible to build a quick and effective data warehouse.
Finally, processes are identified to connect the data sources, the data warehouse, and the end user access tools together.
Next: System Integration >>
More ASP.NET Articles
More By Jagadish Chaterjee
/ 19 
