MS Reporting Services – IT Requirements - Cfs-international.net

chunkyscreechServers

Dec 4, 2013 (3 years and 9 months ago)

120 views



CFS


International


Reporting Framework


Page
1

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004









REPORTING

FRAMEWORK













CFS


International


Reporting Framework


Page
2

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

Index


Overview of CFS
-
International Reporting Framework


Server Specifications


Report Development


Client Access Licenses


IT Maintenance


Steps for Implementation




































CFS


International


Reporting Framework


Page
3

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

Overview


The CFS

Reporting Framework defines

a complete turn key solution for

delivering
automated reports to CFS Customers

and is build around the Microsoft SQL 2000
R
eporting Services
software that forms part of the Microsoft SQL Server 2000 software
suite.


The Reporti
ng Framework consists of the following components

that interact

closely in
delivering
a robust and scalable reporting solution
:



IT Infrastructure Services

o

Hosting Server

o

Reporting Server

o

E
-
Mail Server

o

LAN Connectivity

o

Internet Connectivity

o

Backup Facilitie
s

o

IT Support personnel (Systems Engineer and/or DBA

(Database
Administrator
)



Software

o

MS Windows Server 2003

o

MS SQL Server 2000 with SP3

o

MS SQL Analysis Server 2000 with SP3 (Data Analysis)

o

MS Reporting Services with SP1

o

MS Visual Studio .net 2003 (optiona
l)

o

ProClarity Professional Version 6 (Data Analysis)

o

Service Tracker Version 5 (Report Delivery Manager)



CFS Reporting Library (Consisting of the Building Blocks per Customer per
Report)

o

Report RDL

o

SQL script for creating SQL Stored Procedures used by the
report

o

SQL script to use for Data Driven Subscriptions

o

Empty Cube backup file (if applicable)

o

DTS

(Data Transformations Services)

packages to update Cube (if
applicable)

o

Report description and implementation instructions



Analysis Platform (a “Know All’ Cub
e can be build per Customer to do data
analysis)

o

Empty “Know All” Cube backup file

o

DTS packages to update the “Know All” Cube


Reports complexity will determine

which framework components will be
involved
to
compile, render and deliver a specific
report

to

the customer
.

Less complex reports might
not require a cube and DTS packages.


Report development will be done by the CFS
-
International reporting team in South
Africa. The CFS
-
International Reporting Library will contain all developed reports and


CFS


International


Reporting Framework


Page
4

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

will con
sist of the all building blocks needed to implement a specific customer report.
Developed reports will be made to CFS
-
International Distributors via the Report Library.


Service Tracker Version 5 will contain a module called the CFS Report Delivery
Manager

that will link reports, deployed on the Reporting Server, to CFS customer report
recipients, as well as specifying the report rendering format and other report parameters
needed in delivering automated reports to a Report Recipient. These report delivery
parameters will be stored in the CFS Customer Database. Data Driven subscriptions will
use these Report Delivery parameters when rendering and delivering reports.

A report
specific SQL Script will be provided as one of the Report Building Blocks

-

to use

w
hen
s
cheduling
a report

as a Data Driven Subscription

-

to link a report to the report delivery
parameters.

Reports and Recipients can be de
-
activated via the Report Delivery Manager
as well. De
-
activated recipients will not receive a report and de
-
activat
ed reports will not
be delivered to any recipient.


Standard Reporting Filters like:



Incomplete Answer S
ets



Business

Hours



Public Holidays



TTA threshold



Blank Report
-

No Data No Report



Remote Activation / Numpad



Etc


will be applied when data is written i
nto the database. Only data that adhere to all
filters will be reported on. These filters can be configured and set from Service Tracker
Version5.


Report specific logic, filters, calculation and other coding that are not defined in the
report definition (
Report RDL) will be available in the
CFS
Customer Database as
Stored
Procedures. These Stored Procedures can be used by multiple reports define
d for a
customer and are exec
uted from the Report RDL when the report is run.

A SQL Script
file, that will create

these Stored Procedures on the CFS Customer Database, will be one
of the Report Library building block
s

for the specific customer and/or report.


















CFS


International


Reporting Framework


Page
5

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

Server

Spec
ification
s


Hosting Server Spec:



Hardware:




Pentium 4 Technology



Redundant Hard Dr
ive configuration

o

RAID 1 or RAID 5



1GB RAM



Multi RS232 Port Card (for modem connections)



US Robotics 56K External Fax Modems (start with at least 2 and
add more as required)



Software:



Microsoft Windows Server 2003



Microsoft SQL Server 2000

Standard Editi
on



SQL Server 2000 Service Pack 3a



CFS
Service Tracker Version 3.1

Int


o

Database Builder Scripts

o

HoSync

o

HoSync Instance Manager

o

SCModem Service


Analysis

and Reporting Server Spec:


Hardware:




Intel XEON 4 Processor Server Platform



Minimum of 2 x XEON 2GHz

Processors



Redundant Hard Drive configuration

o

RAID 5

o

Min 5 x 40GB SCSI
-
3 HDD



2GB RAM




Software:




Microsoft Windows Server 2003




Microsoft SQL Server 2000 Enterprise Edition




SQL Server 2000 Service Pack 3a



Microsoft SQL Analysis Services



SQL Analysis Se
rvices Service Pack 3a



Microsoft .net Framework Version 1.1



Microsoft
Frontpage 2003 Server Extension
s



Microsoft Reporting Services



ProClarity Professional Version 6 (for analysis)



CFS


International


Reporting Framework


Page
6

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

Report De
velopment and Deployment

(CFS
-
International
Report

Development Te
am

in SA
)
:


All reports will be developed by the CFS
-
International Report Development Team (RDT)
in South
-
Africa to integrate with the CFS
-
International Reporting Framework.


Comprehensive knowledge of the Microsoft Visual Studio.net 2003 environment and t
he
following programming syntaxes are required to develop reports:



MDX Coding



SQL Scripting



Visual Basic



Reporting Services RDL Scripting



DTS



Microsoft SQL Server and Analysis Server


Development of new reports will commence after the RDT has received the
new report
specifications. Report specifications must be given to the RDT at least two weeks before
the report is required to go live. New reports will be developed by
either adjusting
existing reports from the Reporting Library or by
combining components
from the Report
Development Library. If the Report

Develop
ment Library do not contain the components
needed
to build a report according to the repo
rt specification, new components will be
coded and added to the Reporting Development Library.

A copy of the
CFS customer
database is required with the report specification for the RDT to comprehensively test
and run the report prior delivery for deployment on the live Reporting Framework.


Developed reports will be delivered in a bundle containing some or all of

the following:



Report RDL



SQL Scripts to create needed Stored Procedures on CFS Customer database



SQL Script to use for data driven subscriptions



A copy of the CFS Customer Reporting Cube backup



DTS packages needed top update the CFS Customer Reporting C
ube



Client Access Licenses (CALS):


i.

Microsoft Windows Server 2003 CAL

ii.

Microsoft SQL Server 2000 CAL


CALS of both i and ii are needed for every user that will log on to the

Hosting and
Reporting Server, t
his include CFS employees and Customer t
hat will log onto the
Reporting Server via the web.






CFS


International


Reporting Framework


Page
7

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

IT Maintenance:


S
killed IT personnel are

required to setup and maintain the Repo
r
ting Services
Environment. Well revised knowledge
of the following is essential:



Microsoft Windows Server 2003



Microsof
t TCP/IP
Networking



Microsoft IIS



Microsoft SQL Server



Microsoft Analysis Services



Microsoft Reporting Services



Email
-

Microsoft Exchange, SMTP



Microsoft Visual Studio Development Environment (Optional)

o

SQL Scripts

o

VB Cod
e

These personnel will be responsi
ble for deploying new reports, setting up and
maintenance of the Reporting Framework as well as performing daily checks on the
Reporting Framework to ensure the 24 x 7 availability and delivery of reports.



Steps

for Implementation
:

1.

Purchase Hardware and
Software (as mentioned above)

2.

Arrange Internet presence for Reporting Server

3.

CFS
-
International
-

Development of Reports

based on the Reporting Framework

and delivered in CFS Reporting Library building blocks.

4.

Renier visit
-

assistance, setup

and tra
ining o
f CFS
-
SEA personnel (5
-
27
)

5.

Overview of IT Infrastructure

1.

Hosting Server

2.

E
-
Mail Service

3.

Internet access and websites

4.

Reporting Server

6.

Installing and Configuration of Operating Systems on Servers

7.

Securing the Reporting Server

8.

Setup and Configuration of Remot
e Desktop Management

9.

Installation and Configuration of SQL Server 2000

10.

Installation and Configuration of SQL Analysis Services

11.

Install
ation

of SQL Server 2000 SP3

12.

Setup and configuration of IIS

13.

Setup Configuration of Frontpage Server Extensions

14.

Setup

and C
onfiguration of Microsoft .net Framework 1.1

15.

Installation and Configuration of MS SQL 2000 Reporting Services

16.

Installation of MS SQL 2000 Reporting Services Service Pack 1

17.

Configuration of SMTP web services for Report delivery via email

18.

Configuration of we
b access to Reporting Services reports

19.

Training on Service Tracker Version 5.0



CFS


International


Reporting Framework


Page
8

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

20.

Training on DBA using Enterprise Manager and Analysis Manager

1.

Create DB

2.

Backup and Restore DBs

3.

Running Scripts

4.

Stored Procedures

5.

Cubes

6.

DTS

7.

SQL Server Agent and Scheduled Jobs

21.

Tr
aining on MS SQL 2000 Reporting Services

1.

Web interface

2.

Data Sources, Data Sets and RDLs

3.

Deploying Reports

4.

Access rights to Reports

5.

Report Scheduler

6.

Subscriptions and Data Driven Subscriptions

7.

Rendering formats

22.

Training on CFS Reporting Framework

1.

Data Sourc
es

2.

Data Sets

3.

Deploy RDL

4.

DB setup, running scripts for Stored Procedures

5.

Report Subscription Manager (Service Tracker Version 5)

6.

Report Scheduling and Subscriptions

7.

Cube setup

8.

DTS setup

23.

Setup and Configuration of CFS Reporting Framework

24.

Deploying of Reports

25.

Report scheduling

and data driven subscriptions

using Service Tracker Version
5.0

and Reporting Services Scheduler

26.

Testing of Reporting Platform

27.

Setup and Configuration of Analysis Framework

1.

Creating Know
-
All cube

2.

DTS for updating cube

3.

Scheduling automati
c execution of DTS packages

4.

Using ProClarity to analyse data

28.

IT Maintenance and Contingency

1.

Backup Policy

2.

Contingency Plan

3.

Daily DBA and IT maintenance



CFS


International


Reporting Framework


Page
9

Author:
Renier Botha


2004/11/17

Copyright: CFS
-
International 2004

CFS
-
International
Devices
System
Controller
SCModem
Report
Development
Report Libraries
World Wide Web
PSTN
.
net Developer
Workstation
Customer

Reports
Via email or Web
SCModem
Bank
CFS Reporting Framework
-

Distributors IT Infrastructure
PABX
DTS updates
Customer Cubes
in SQL Analysis
Services
Reporting
Services build
reports from
Customer
Databases and
Cubes
Reports are
devlivered as
scheduled via
Email to Customer
Reporting
Services send
reports to Mail
Server for delivery
Customer receive reports via
email
Or
Pull reports via Reporting
Services WebServer
Web Server
,
Services reporting
requests
Report
Deployment
and Maintenance
Internet Server
DBA Workstation
E
-
Mail Server
Analysis
&
Reporting Server
Web Reporting
request send to
Reporting Server
for Processing
SA Hosting Server
&
Report Test Bed
Hosting Server
SA Internet Server
FTP Data Files to
SA Hosting Server via HostLink