Technical Solution Architecture
The solution architecture outlines the integration of data sources into the Data Warehouse for the purpose of generating reports using the Business Intelligence reporting tool.
The core objectives of the Data Warehouse technical solution are:
- Develop a global reporting solution utilizing Business Intelligence reports
- Develop the Data Warehouse to cater the global reporting needs by utilizing Microsoft SQL Server Integration Services for the extraction, transformation and loading processes (the diagram uses Microsoft SQL server as an example)
- Develop Interfaces with varied data sources, Excel Files, Flat Files, Source Systems by utilizing Microsoft SQL Server Integration Services (example) in building the Data Warehouse
- Develop a single version of truth available in the Data Warehouse for business decisions
The various interfaces that will be built as part of the solution will link each of the different data source and target systems with the Data Warehouse. This Technical Solution Architecture intends to:
- Provide a high level overview of the various data flow processes behind these interfaces
- Highlight the data extraction, transformation and loading stages which are part of these interfaces
The infrastructure solution will be made of three environments:
- Development
o This environment can be built in a virtual environment as permitted by the application software, Database, ETL and Business Intelligence services
o Development and unit testing is performed in this environment
- QA (Quality Assurance Environment)
o This should reflect the Production environment as much as possible
o Data requirements are the same as in Production which will allow performance and stress testing
- Production
o Live environment, highly available
The diagrams below provide an example of how the environments should be built.
Data Warehousing environments need to be built to accommodate the business requirements for reporting and growth. Not having the infrastructure built out correctly will result in performance degradation (long running reports), slow response time and overall user dissatisfaction. I have had overwhelming success with building out Data Warehousing environments and have also made calculation errors when estimating future business growth patterns. The amounts of support it will take to keep the environment operational when these types of mistakes are made are substantial, but can be avoided when building out a back-end infrastructure in a pre-cautious manner.
I would really be interested in hearing from those of you that have had successes and challenges in this area.
