Oct 30

Logical and Enterprise Data Warehouse Strategy

Does your organization have multi-department, multi-dimensional, multi-structured or a combination of structured and unstructured data assets.

This multi-org, multi-tenant scenario will bring forth a diverse set of information assets, business rules that need to be collected, analyzed, and reported on for business intelligence. Traditionally in large enterprises such efforts have been addressed using a single Enterprise Data Warehouse (EDW) approach where a collection of operational data stores and data marts that are formally developed, well vetted and setup for data analysis. However, as technology in the enterprise evolved the business entity footprint is now represented in multiple formats such as structured (e.g. RDMBS data), un-structured (e.g. pdfs) and semi structured (XML documents, Excel CSV). Establishing and changing EDW (extensive modeling of interdependent hierarchical models) with constantly changing inputs is cumbersome, time consuming and expensive. Using a Logical Data Warehouse (LDW) is a more economical and quick turnaround approach for data that is subjected to change and heterogeneous. LDW is an architecture pattern that federates and virtualizes data from multiple data sources (independent at their taxonomical entity level) for gaining business intelligence.



KPSoft has successfully implemented both LDW and EDW for our large enterprise clients and will apply these methods for enabling D2D. KPSoft recommends establishing and governing interoperability between EDW and LDW and thus providing a unique data access layer to the integrating BI applications. Please refer to the diagram below for our recommended reference architecture.

Please find below the list of implementation steps and best practices that KPSOFT recommends for establishing D2D architecture for the long term and increased ROI:

  • Conduct metadata and logical modeling – Use a bottom up analysis of business functions, existing definitions of business entities and OLTP databases to arrive at a comprehensive set of information assets that will ultimately define the predictive and analytical BI space.
  • Develop physical models for EDW and LDW – Apply industry best practices for installation and configuration of operational data stores, star schema, fact tables for EDW and heterogeneous data stores that are in 3rd Normal Form (3NF) for LDW.
  • Develop ETL for establishing data movements between the sources to EDW and LDW as required. These ETLs will also facilitate data movement between EDW and LDW for their interoperability toward providing a unified data source to other tiers of D2D.
  • Establish Service Level Agreements (SLAs) – Conduct a thorough JAD style sessions with various end user communities, software vendors and infrastructure stakeholders to establish a hierarchical set of SLAs. Top layers will include a common core set of standards and SLAs across the customer agencies, while the bottom layers will include SLAs specific to the agency. SLAs will then be mapped to reliability, fault tolerance, scalability, tuning and performance of the D2D components (MicroStrategy, Fuse etc.).
  • Configure User Access Layer – Create a single user interface layer for all BI applications to be connecting to. Security policies that are in compliance with your organizational information security regulations, personalization and customizations will be applied to this layer.