D’MART: A Tool for Building and Populating Data Warehouse Model from Existing Reports and Tables

Abstract

As companies grow (organically or inorganically), Data Administration (i.e. Stage 5 of Nolan’s IT growth model) becomes the next logical step in their IT evolution. Designing a Data Warehouse model, especially in the presence of legacy systems, is a challenging task. A lot of time and effort is consumed in understanding the existing data requirements, performing Dimensional and Fact modeling etc. This problem is further exacerbated if enterprise outsource their IT needs to external vendors. In such a situation no individual has a complete and in-depth view of the existing data setup. For such settings, a tool that can assist in building a data warehouse model from existing data models such that there is minimal impact to the business can be of immense value. In this paper we present the D’MART tool which addresses this problem. D’MART analyzes the existing data model of the enterprise and proposes alternatives for building the new data warehouse model. D’MART models the problem of identifying Fact/Dimension attributes of a warehouse model as a graph cut on a Dependency Analysis Graph (DAG). The DAG is built using the existing data models and the BI Report generation (SQL) scripts. The D’MART tool also uses the DAG for generation of ETL scripts that can be used to populate the newly proposed data warehouse from data present in the existing schemas. D’MART was developed and validated as part of an engagement with Indian Railways which operates one of the largest rail networks in the world.