System Design Specification - Ohs Statistical Reporting System

Only available on StudyMode
  • Topic: Microsoft SQL Server, SQL Server Integration Services, Microsoft
  • Pages : 12 (3075 words )
  • Download(s) : 308
  • Published : May 2, 2013
Open Document
Text Preview
SYSTEM DESIGN SPECIFICATION
OHS Statistical Reporting System

Document History|
Author| Sutherland Nele| Thursday, 18th October, 2012|
|
Modification History|
| | |
| | |
| | |

Table of Contents

1Introduction2
1.1Purpose2
1.2Intended Audience2
1.3Scope2
2System Overview3
3Design Considerations3
4Assumptions and Dependencies3
5Related software or hardware3
5.1Hardware3
5.2Software4
6General Constraints4
6.1Access to certain secured Data Sources4
6.2Unavailability of certain Data Sources4
6.3Server Hardware Performance4
7Development Methodology5
7.1Project Setup Plan5
7.2Plan Develop & Test Feedback – Iterations5
7.3Develop and Test – Release Product6
8System Architecture7
9Detailed System Design8
9.1Identify Data Sources8
9.2Extraction of Data8
9.3Transformation and Loading of Data9
9.4Designing and Uploading of Reports to the Report Server10
9.5User Front-End Report Viewer10
9.6System Security11
10Glossary11
11Bibliography12

Introduction
Purpose
The primary purpose of this document is to describe the design of the OTML OHS Statistical Reporting system. Intended Audience
The information contained in this document builds on the Business Requirements Specification document and is technical in nature. It is therefore intended for audiences that have an understanding of Microsoft SQL Server BI (SSIS, SSAS and SSRS) and Microsoft SP2010. Scope

This document will cover the following areas:
* Provide an Overview of the BI System
* Describe the Design Considerations
* Outline the Assumptions and Dependencies
* Describe the general constraints involved
* Describe the Development Methodology
* Describe the overall BI System Architecture
* Detailed Description of the BI System Architecture
* Identify the main data sources from where information will be extracted * Describe how the information will be extracted from the data sources and what technology will be used for the data extraction * Identify and describe the Transformations in the Staging database * Describe how data will be loaded to the Reporting Database * Describe how the reports will be designed and hosted on the Reporting Server * Describe how SP2010 will be integrated with SSRS to provide a front-end interface for users to access the reports * Describe the how the security features will be built into the BI System

System Overview
The System to be designed is a reporting system that will provide the OTML OHS Statistical reports on a monthly and quarterly basis. The system will be designed and built using the Microsoft BI Technology stack. This includes the following: a) SQL Server 2008 Database as the main data store

b) SQL Server Integration Services (SSIS) as the main tool to perform all ETL operations c) SQL Server Analysis Services to copy relational data into OLAP cubes, if and when necessary d) SQL Server Reporting Services integrated with SharePoint 2010 to host reports based on data in the data warehouse e) SharePoint 2010 to provide the front-end report viewer for users to view the reports Design Considerations

Based on the current business requirement SSIS, SSRS and SharePoint 2010 will be the only BI technologies used to build the BI solution. If there are requirements in the future where by OTML management would require multidimensional views of the information for analysis and decision making, SSAS will be used to build OLAP cubes. Moreover, since the BI solution will be built using SQL Server 2008 and not SQL Server 2008 R2 or SQL Server 2012, most of the extra SSAS BI features like PowerPivot which are available in those later versions cannot be used. SQL Server 2008 R2 will not be used because a separate license will need to be purchased and also for the current business...
tracking img