Transcript Business Intelligence based on PI-Systems at BWB Berliner Wasserbetriebe (BWB)

Business Intelligence based on
PI-Systems at BWB
Johannes Broll, Ulrich Blöhm, Thomas Bierwirth
Berliner Wasserbetriebe (BWB)
Berlin, Germany
PI User Conference 2002
1
Agenda
•
•
•
•
•
BWB - who we are
Using PI at BWB now
The Business Intelligence project
How to connect BI to PI?
Using PI at BWB in the future
PI User Conference 2002
2
BWB - The core business
„Make“ water
Collect wastewater + rain
rain
Supply water
Customer
PI User Conference 2002
Clean wastewater + rain
3
The drinking water process
PI User Conference 2002
4
Sewerage treatment
PI User Conference 2002
5
BWB - a brief outline (2000)
Drinking water
• 222 M
• 7787 km supply lines
m3/year
• 11 waterworks
• 978 wells
Waste water
• 241 M m3/year
• 9093 km sewers
• 1035 km pressure
pipe-lines
• 7 purification plants
• 145 pumping stations
5900 employees; turnover 1,9 billion DM
PI User Conference 2002
6
PI System at BWB now
• Many differnet DCS in the plants
• First PI installation in 2 purification plants
1997 - 1999
• Good acceptence of PI Processbook and PI
DataLink to perform local reports
• Full integration of display functions in the
DCS via Active X
PI User Conference 2002
7
Example Configuration
W_RUH_58
W_RUH_57
W_RUH_54
W_RUH_55
W_RUH_59
W_RUH_56
Zentralwarte (Wartennebenraum)
LWL
RJ45
RJ45
RJ45
RJ45
RJ45
RJ45
Switch
S_RUH_60
NT-Server
S_RUH_80
Melde-Server
DFC 77
11
12
1
10
2
9
3
8
4
7
6
5
W_RUH_50
Archivrechner
RAIDSystem
(Festplatte)
RAID
ES_RUH_70
Projektierungsrechner
S_RUH_51
Wasserteil/NA
S_RUH_53
SV West
S_RUH_52
SV Ost
CD-Brenner
Hub
SV SV
Switch
ISDN Leitung
1
2
Hub
LWL
LWL
LWL
LWL
LWL
RJ45
RJ45
RJ45
RJ45
RJ45
RJ45
Hub
Fernwartung
Router
optional
LWL-Leitung
LWL-Leitung
100 Mbit/s
100 Mbit/s
PI Server
Hub
Bus 1 /
Tln 51
W_RUH_64
W_RUH_65
Hub
Hub
S_RUH_61
Wasserteil/NA
PI User Conference 2002
Bus 3 /
Tln 51
Bus 2 /
Tln 51
W_RUH_66
S_RUH_62
SV Ost
W_RUH_67
8
S_RUH_63
SV West
DCS Picture Select
PI User Conference 2002
9
DCS Plot
PI User Conference 2002
10
PI - Picture Select within DCS
PI User Conference 2002
11
PI - DCS Integration
PI User Conference 2002
12
PI - DCS Integration
PI User Conference 2002
13
The business itelligence project
• A new reporting system for
the wastewater process
• including all relevant plants
• including business data
• including Laboratory data
• easy to use (one click),
scalable, no client
administration, intranet
application, etc. ...
PI User Conference 2002
14
BI - the requirement
current situation
• Different systems for data
storage
• Different security concepts
• Different isolated reporting
solutions
• Every new report leads to
stress of the operative staff
objective
• One Database for the
storage of relevant data
• One security concept
• One reporting solution
• independent from
operative systems
PI User Conference 2002
15
At the beginning
ROLAP
ETL
MIS
MOLAP
Data
Mining
Slice and
Dice
Churn
Query
CRM
Drill
PI User Conference 2002
DOLAP
16
Different BI Tools
Summarized reports
EIS
EIS
Executive
Information
Systems
Anzahl Teilnehmer
1,000,000
800,000
600,000
400,000
200,000
0
Minimumtarif
Standardtarif
Geschäftstarif 1
Geschäftstarif 2
Multidimension
analysis, ad hoc
reporting Tools
Standard reporting
relationale Ad Hoc
reporting Tools
Big Button User
e.g.: target/actual comparision,
benchmarks, balanced scorecard analysis
Intensiv Analysis of multivaribale models
(OLAP) trough Power User,
Analyst
Predefined standard reports in regular
intervals or Ad Hoc reports
e.g.: monthly report of quality aspects
PI User Conference 2002
17
Different Users
Web User
Specialist
Database
Sources
(PI System, LIMS, SAP, Other...)
PI User Conference 2002
18
classification of users
complexity
Aggregated data,
charcteristics
management
Detailed data,
reports
Process
responsables
Business-Analyst
(e.g. controller)
Detail-Analyst
(e.g. statistician)
Dynamic of request
static
interactiv dynamic
PI User Conference 2002
19
Classification of BI Tools
complexity
EIS
OLAP Tools (MOLAP/HOLAP)
Desktop OLAP
Relational
OLAP
Relational Query Tools
Dynamic of request
PI User Conference 2002
20
Requests to the reporting tool
•
•
•
•
•
•
Drill, e.g. drill through monthly reports
filter by location, e.g. treatment plant or effluent
calculate new data, e.g. load = flow(PI Data) *
concentration (LIMS Data)
monthly broadcasting of reports (*.pdf-format)
user specific profiles and security related to the
report
prompting
PI User Conference 2002
21
The decision
• Consolidation of process data with PI, this
means to build up the PI System in all
plants
• Build up an ETL process
• „Business Objects“ as reporting tool
PI User Conference 2002
22
Structure
Secialist
Sources
SAP R/3
local
middleware
PI-Client
ETL
Database
Extract
Publication
Reporting
business objects
Transform
PI System
Web-Server
Load
LIMS
(ETL)
Database
PI User Conference 2002
23
Business Intelligence
PI User Conference 2002
24
Query, Reporting, and Analysis
PI User Conference 2002
25
BusinessObjects
• Easy to use
– Single interface for query, reporting, and analysis
– Use your own business terms
– Drag and drop report creation
• Powerful
– Powerful one-click functions (pivot, variance, ranking, alerts)
– Multiple data sources (DW, ERP, OLAP, Excel, internet…)
– Highly customizable through open object model
• Deployable
– “Zero admin” (self-install & update, no client middleware)
– C/S or 3-tier web mode with component-based architecture
PI User Conference 2002
26
BusinessObjects C/S
Toolbar
functions
Mix tables,
graphics,
and charts
Several reports
in one
document
PI User Conference 2002
27
Web-mode
Save and
publish
documents
Create new
documents
from within
the browser
Drill directly
on charts and
tables
Reformat
chart with a
single click
PI User Conference 2002
28
Portal and Broadcasting
PI User Conference 2002
29
BusinessObjects InfoView
• A centralized point for all BI information
– Including non-Business Objects documents
• Personalized interface
– Users can define their own environment
• Fully integrated into the BusinessObjects suite
– Leverages the WebIntelligence infrastructure
– Shares the Business Objects repository
• Also a BI content provider to EIPs
– Partnerships with major Enterprise Information Portal vendors
PI User Conference 2002
30
InfoView
Favorite
report
Multiple
document
types
Excel, or
other file
PI User Conference 2002
31
Broadcast Agent
Serve web and
Windows users
Distribute to
multiple device
types
PI User Conference 2002
Access
multiple data
sources
32
Administration
PI User Conference 2002
33
BusinessObjects Designer
• Universe creation
– In a few mouse clicks
• Leverage existing
metadata
– Dynamic access to
Informatica, Ardent, and
others
– Universe linking
• Exploit DW power
– Aggregate awareness
PI User Conference 2002
34
Supervisor

Protect information resources
 Data
access (row-level)
 Resource utilization
 Product functions

Streamline user administration
 User
profile inheritance
 Nested groups of users
 Reuse NT authentication

Maintain resources easily
 Central
repository
PI User Conference 2002
35
PI tables in the semantic layer
PI ODBC tables
Joining the tagname
PI User Conference 2002
36
Direct connection BI-PI
PI ODBC tables
SQL Editor
PI User Conference 2002
37
The result
Pretty nice, but ....
can I trust the
results ?
and what is that ?
PI User Conference 2002
38
Connecting BI to PI directly
• Action of the user causes in uncontrolled
SQL-statements
• It is possible to shut down the PI SQL
Server by „select * from *“-statement
• Data are not validated
PI Datalink for Data consolidation, used
by the „data responsibles“ (Excel User)
PI User Conference 2002
39
The PI-Datastream
Sources
PI Server
Validation
ETL
Database
Reporting
business objects
PI Datalink
Web-Server
ETL
FlatFile
Publication
Database
PI User Conference 2002
40
PI User Conference 2002
41