Prior to embarking on detailed design and implementation, it makes sense to assess the actual technology that will embody our conceptual design. Doing so clarifies the feasibility of the project and also may help to identify any areas of the design that have not been fully considered. In this lesson, we use the Microsoft SQL Server environment as a starting point for our technical requirement checklist.
After this lesson, you will be able to:
- Categorize the technical issues that must be addressed prior to a detailed implementation
- List the features of Microsoft SQL that resolve technical requirements
Estimated lesson time: 15 minutes
Before proceeding to implementation, it is a good idea to gain perspective on the feasibility of the project by sketching the technical requirements. At this point, the list does not need to be final, but analysis and conceptual design have progressed to the point where you can get a fairly realistic picture of the necessary technology. This allows you to identify any gaps in the supporting technology.
The technical requirements are what allow the data warehouse to exist physically and be of use to the business community. The following four categories of technical requirements will guide you in selecting the tools and procedures suitable to everyone who will have access to the data warehouse. Failure to adequately address the technical requirements may result in a data warehouse that is unstable, inaccessible, or unusable.
The infrastructure represents the environment in which the data warehouse exists. It includes the database, transformation tools, reporting and query tools, the processor, network connectivity, and all the operational utilities that are necessary for the health of a modern database environment.
In the Microsoft SQL Server environment, the technical requirements are met by the features shown in the following table.
Requirement | Met by |
---|---|
Database | SQL Server database |
Transformation tools | DTS, BCP, bulk insert, replication, linked servers, ad hoc queries |
Reporting and query tools | Pivot table service, English query, cube browser |
The processor | Alpha or Intel |
Network connectivity | Windows NT |
Operational utilities | DBCC, backup, restore |
In the process of acquisition and population, data is moved from the source systems to the data warehouse. It may involve both internally and externally sourced data with differing refresh frequencies. The following table slows the SQL Server features that support acquisition and population.
Requirement | Met by |
---|---|
Data acquisition | DTS, BCP, bulk insert, replication, linked servers, ad hoc connector, ODBC |
Data population | OLAP services |
The backup and recovery processes ensure that the information in the data warehouse is safe from destruction, as shown in the following table. There may also be a routine process to perform a rollback of the data warehouse when problems occur in a load event.
Requirement | Met by |
---|---|
Backup | SQL server backup, OLAP services archive add-in |
Recovery | SQL server restore, OLAP services archive add-in |
The security process is used to prevent unauthorized access to information in the data warehouse.
Requirement | Met by |
---|---|
Security | Windows NT user and groups, SQL Server security, OLAP services access security, DTS owner and operator passwords, COM security |
Analysis of business requirements leads to a conceptual design. A conceptual design will eventually lead to an actual implementation. Identifying technical requirements is the final step toward that implementation.