Building a data warehouse

Building a data warehouse is a project that requires serious work and effort on the part of business and information technology provider. The best approach here would be a joint project of the company and a company specializing in this area. The global experience shows that a data warehouse is to be created for a specific customer. A major advantage is the availability of qualified personnel, model data marts, as well as industry data model.

The plan of a data warehouse implementaion project:

Development of the Charter of the Project;
Creating the Project site on the corporate web-site (or any other web-system) and the formation of the web- structure:
- library for business requirements and methodologies;
- section for protocols on the workshops, the current tasks;
- discussion of the operational issues (perhaps in the form of a forum, tracking);
- sections of the documentation for the storage of data marts, OLAP-cubes;
- knowledge System (wiki);
- library for regulations;
- section of training, web-caste;
- and other topics..
Collection of fragmentary knowledge about business processes (BP), the company and the metrics by conducting series of interviews with key business personnel and experts. The formalization of BP in the form of the integrated graphics circuits (for example, BPMN-notation);
Getting access to your system (preferably copies, access for reading data at the database, and view the data in a graphical user interface);
Collecting and discussion of the techniques implemented in the existing regulatory / management reports;
Formulation of requirements / discussion of the techniques for new / desired regulatory / management reports;
Organizing business requirements (based on pp 3, 5, 6) the composition of the attribute data, which should be reflected in the data store;
Building / updating and description of the logic models in accounting systems - data sources for DWH. (Perhaps the model is already available, although in most cases it is not);
Description / actualization of physical models (the construction of ER-diagrams) for the accounting systems - data sources for DWH. (Perhaps the model is already available, although in most cases it is not);
Analysis and formalization of business requirements to the data attributes (for items 7, 8, 9), which should be reflected in the data warehouse;
Preparation of the technological platform for BI: server development, testing, production, installation of the server and the application software;
Profiling data (item 12), derived from the accounting systems, systematization of statistics on metadata and data record-keeping systems;
Development of the procedures(possibly re-engineering of the existing ones) to extract the necessary data (item 10) from the accounting systems in the buffer tables (stage area); filling the buffer tables;
Designing the logical data warehouse;
Designing the structure of a physical model of the data warehouse;
Development of a conceptual framework, approaches, ETL-process by loading data from the accounting systems into the data warehouse;
Development of the mappings (field source -> field target);
Technological implementation (software development) ETL / ELT- data transfer processes, data loading from the directories of accounting systems into the dimension table (dimensions) repository. (Performed by stages in the subject areas of business);
Development of the procedures for primary / critical cleaning / deduplication of the directories (with p.18) [Draft NSI (MDM) is performed as a separate project / sub-project];
The technological implementation (software development) ETL / ELT-process of data transfer from accounting systems in the fact table (fact table, factless table) storage. (Performed by stages in the subject areas of business);
Testing:
- control the outcome of the convergence on the data in the accounting system with the results according to store tables;
- the execution speed of the full ETL-cycle;
Refinement pp 18, 19, 20 on the identified errors, comments on the results of operations section 21;
The development of structures of data marts (aggregation of denormalized tables / views). Carried out in stages in the subject areas of business;
Developing ETL / ELT-procedures for updating data marts, the calculation of derived indicators (enrichment data marts);
Testing:
- control the outcome of the convergence of data marts on the outcome according to the accounting systems and the results according to store tables;
- the execution speed of the refresh cycle of data marts;
Development of the documentation on the data storage;
Developing documentation for data marts;
The development and harmonization of requirements for analytical OLAP cubes (the list of measurements, metrics, ext. Distribution of the access rights);
Development of analytical structures of OLAP cubes, procedures, updating data in the cubes, which is carried out in stages in the subject areas of business;
Testing analytical cubes;
Preparation, deployment of infrastructure for the publication of reports (REPORTING, ad-hoc reporting, OLAP) on the web-site (eg, MS Sharepoint 2010 EE 64x + MS Reporting Services, possibly Gognos 10.x);
Development of the documentation on the analytical cubes, publication of the documents on the web-portal system of knowledge;
The development of procedural / administrative reports (according to claims 5, 6);
Publication and ordering routine / management reports on the corporate web-site;
Training business users on how to use interactive OLAP-cubes, identification / building proactive users (power users);
Business users (if possible) independently develop reports (based on § 35); acceptance and publication of the reports carried out in coordination with the IT-analytical department (perhaps in the beginning).