4.4a Stage 4a – Performance Reports
4.4a.1
Overview
Two articles on Wikipedia provide an Overview on
- Business Intelligence – http://en.wikipedia.org/wiki/Business_intelligence
- Performance Reports - http://en.wikipedia.org/wiki/Performance_reports
Performance Reports and
Business Intelligence are very similar in their interpretation.
Question : Does your Chief Exec have Report requirements that you cannot meet
?
In order to respond to this
situation appropriately, it is necessary to have an Information Catalogue, a
Data Architecture and Data Lineage.
The solution then involves
the following Steps :-
Step 1) Produce a draft
Report for the Chief Execs approval
Step 2) Trace the lineage and
perform a ‘gap analysis’ for all new data items.
Step 3) Talk to the Data
Owners and establish when and how the data can be made available.
Step 4) Produce a Plan and
timescale
Step 5) Review your Plan with
the Chief Exec and obtain this agreement and formal sign-off.
Step 6) Deliver
!!!
4.4a.2
Best Practice
Performance Reports take data
from Data Marts and many of the same considerations apply when it comes to
determining Best Practice.
One difference is that is
necessary to have a clearer understanding of the business
operations and how the right kind of Performance Reports can provide insight to
the business users.
This leads to the need for a
management education process to be in place so that the evolution of
Performance Reports can be planned in a logical
manner, from basic summaries, to KPIs, Dashboards and
so on.
Question : How do I produce Integrated Performance Reports for senior
management ?
The key action here is to
establish a unified Reporting Data Platform.
This will involve aspects previously
discussed, including MDM, CMI and will certainly involve Data Lineage.
Senior Management will want
to take a view of the integrated data and not focus on details of derivation.
Therefore, we have to follow
the MDM approach with Data Lineage for each item in the
Integrated Performance Reports.
Key Performance Indicators
(‘KPIs’)
Question : What are Key Performance Indicators (‘KPIs’)
Key Performance Indicators (‘KPIs’) are in common use and represent one aspect of Best
Practice.
A variation of this approach
are Key Quality Indicators,(‘KQIs’)
which are used to monitor and manage Data Quality.
Dashboards and Scorecards are often used in association with KPIs.
4.4a.3
Templates
Report Templates are
available showing Content and Layout for standard Ad-Hoc and Off-the-Shelf
Reports.
4.4a.3.1 Data Mart in a
Word document
This
diagram shows a Data Model for a Data
Mart to hold data about Parking Tickets issued by a Local Authority in the
It was produced
in a Word document from early discussion with the End-User and was very helpful
in establishing communication and a collaborative method of working.
End-users find to easier to
understand and agree to this kind of Data Model than a formal ERD.
This approach is therefore recommended.
Each Fact is associated with
a number of Dimensions.
The ‘FACTS’ Table contains the list of data items which is
available.
The other Tables are called
‘Dimensions’ and define how the Facts can be analysed.
4.4a.3.2 Data Mart as a
Data Model
This
diagram produced by a Data Modelling Tool and is the
complete analysis of all the data required.
4.4a.3.3 Map showing KPIs
This Map shows Key
Performance Indicators (KPIs) for the Wards in a
Local Authority
Each Ward is displayed in either Red, Amber or Green, depending in whether the KPIs Threshold values are reached or exceeded.
Red indicates a situation that
requires urgent management attention, amber is a warning and green is within
acceptable limits.
4.4a.3.4 Reports at the
Regional Level
This Report shows the total
count of Customers gained and lost in an imaginary South-East Region
|
RPt.1 Total
Customers Gained and Lost by Week |
|||||||
|
Date selected:
Month of January, 2010 |
|||||||
|
Week Ending |
|
Location |
|
Total Gained |
|
Total Lost |
|
|
March 6th
09 |
|
SE Region |
|
10 |
|
10 |
|
|
March 13th
09 |
|
SE Region |
|
20 |
|
20 |
|
|
March 20th.
09 |
|
SE Region |
|
30 |
|
30 |
|
|
March 27th.
09 |
|
SE Region |
|
40 |
|
40 |
|
|
April 3rd/
09 |
|
SE Region |
|
50 |
|
50 |
|
|
April 10th.
09 |
|
SE Region |
|
30 |
|
30 |
|
|
April 17th.
09 |
|
SE Region |
|
20 |
|
20 |
|
|
April 24th.
09 |
|
SE Region |
|
10 |
|
10 |
|
.
4.4a.3.5 Reports at the
City Level
This Report shows the total
count of Customers gained and lost for
|
RPt.1 Total
Customers Gained and Lost by Week |
||||||
|
Date selected:
Month of January, 2010 |
||||||
|
Week Ending |
|
Location |
|
Total Gained |
|
Total Lost |
|
March 6th
09 |
|
|
|
1 |
|
1 |
|
March 13th
09 |
|
|
|
2 |
|
2 |
|
March 20th.
09 |
|
|
|
3 |
|
3 |
|
March 27th.
09 |
|
|
|
4 |
|
4 |
|
April 3rd/
09 |
|
|
|
5 |
|
5 |
|
April 10th.
09 |
|
|
|
3 |
|
3 |
|
April 17th.
09 |
|
|
|
2 |
|
2 |
|
April 24th.
09 |
|
|
|
1 |
|
1 |
4.4a.3.6 Reports for
Parking Tickets
This table shows a sample
Template of unrealistic data for Parking Ticket Reports.
The Template is available on
this page of the Database Answers Web Site :-
http://www.databaseanswers.org/Parking_Rpts/PK06_TotalPaidPCNs_withPaymentMethod_demo_rpt.xls
|
PK.6 -
Report on Total PCNs Paid with Payment Methods |
||||||||||
|
Date selected:
Month of January, 2010 |
||||||||||
|
PCN Type |
|
Source |
|
Payment
Method |
|
PCNs Paid |
|
Amount Paid |
||
|
PCN - BLE |
|
H |
|
Credit Card |
|
5 |
|
£300.00 |
||
|
PCN - BLE |
|
O |
|
Cheque |
|
186 |
|
£11,160.00 |
||
|
PCN - BLE |
|
O |
|
Credit Card |
|
1 |
|
£60.00 |
||
|
PCN - BLE |
|
O |
|
Postal Order |
|
4 |
|
£240.00 |
||
|
PCN - BLE |
|
U |
|
Auto Phone
Payment |
|
594 |
|
£35,700.00 |
||
|
PCN - CCTV |
|
H |
|
Credit Card |
|
3 |
|
£150.00 |
||
|
PCN - CCTV |
|
H |
|
Debit Card |
|
5 |
|
£250.00 |
||
|
PCN - CCTV |
|
O |
|
Cheque |
|
171 |
|
£8,700.00 |
||
|
PCN - CCTV |
|
O |
|
Postal Order |
|
2 |
|
£100.00 |
||
|
PCN - CCTV |
|
U |
|
Cash |
|
50 |
|
£2,500.00 |
||
|
PCN - CCTV |
|
U |
|
Cheque |
|
5 |
|
£250.00 |
||
|
PCN - DTE |
|
H |
|
Credit Card |
|
28 |
|
£1,680.00 |
||
|
TOTAL |
|
|
10,000 |
|
£500,000 |
|||||
4.4a.4
Tools
A wide range of vendors have
realized that many User requirements for Performance Reports have a similar
style for layout and content and that it is therefore possible to define
Templates and general-purpose reporting software. Products available from Microsoft
are described in a separate document.
4.4a.5
Tutorials
Here’s a Tutorial from
Database Answers on Integrated Performance Reporting –
http://www.databaseanswers.org/tutorial4_integrated_performance_reporting/index.htm
In broad terms, there are
three areas involved :-
i)
Determine
the Data Sources from the Data Marts
ii)
Choose
the commercial Report-Writer
iii)
Create
Data Validation and Transformation procedures
4.4a.6
How do I ?
4.4a.6.1 Get Certified as
a Microsoft BI Specialist ?
Certification is discussed in
Section 4.1.6.1 in these terms :-
Certification can be
described as ‘Necessary but not sufficient’. In other words, some employers
consider it as evidence that you have the necessary technical knowledge and
skills to be a Database Administrator, but without any experience, it will not
guarantee you a job.
If you take your profession
seriously and are committed to self-improvement, then you should certainly
consider getting certified in the DBMS of your choice.
Here is a Web Link discussing
the role of Microsoft Certified Technology Specialist in SQL Server Business Intelligence :-
http://www.microsoft.com/learning/mcp/mcts/bi/default.mspx
4.4a.6.2 Manage Requests
for Changes ?
When you are planning to
produce Reports, it is vital to plan for changes to avoid disappointment.
The most common response when
Users get their much-anticipated Reports for the first time,
is for them to say – “Oh dear, that isn’t really what I wanted’.
Even when the Reports meet
their Requirements, which will have been well-documented,
and probably signed-off by the Users, they still want changes made.
There are some technical
things you can do, including specifications for Report Templates
which capture the features in families of similar Reports.
From a procedural point of
view, you can discuss with the Users, how they see the patterns of future
changes, and try to understand the operational environment. This will help you
see how the Reports fit into their management style and
You can identify a progression
from KPIs (Key Performance Indicators), Traffic Light
Reports (using Red, Amber and Green to indicate the seriousness of situations
being reported on), Dashboards, Scorecards
This will help you to arrange
for the appropriate management education so that you and your Users are always
in step, with your planning for what is just around the corner.
4.4a.7
Qualities for Success in Performance
Reporting
To be competent in this area
of Performance Reporting it is useful to be able to see things from the User’s
perspective and formulate the layout and content of the Reports accordingly
People who are successful
working in this area are happy to work with End-Users and formulate Report
requirements in a style that can be easily understood and
implemented by the developers who might be the Report specialist.
They are subsequently able to
implement the inevitable changes requests by the End-user and manage the
expectations of the End-user and developers.
4.4b Stage 4b – Internet Mashups
4.4b.1
Overview
Question : What are
We can define Enterprise Mashups simply as a Web 2.0 technique that allows data to be integrated by using APIs that deliver the data to the
page or screen.
This greatly simplifies the
tasks involved, compared to the conventional techniques of data integration
closer to the database sources.
Question : How can I check if anyone is using Mashups
in my organisation ?
This question is important
because it provides a measure of unsatisfied demand within your organization
for new reports and analysis of data.
The best way to address this
question is to use the Intranet to build a Knowledge Management ‘Community of Professionals’ for Mashups.
Wikipedia provides a useful introduction to
Enterprise Mashups –
Wikipedia defines Enterprise Mashups as :-
“a Web
application that combines data from one or more sources … thereby creating
a new and distinct Web service that was not originally provided by either
source.
A Mashup [is] frequently . by APIs, [for example] data
from Google
Maps combined with location real estate
data.”
4.4b.2
Best Practice
Best Practice will discuss
how to prepare a Business Case and create Mashups
Mashups are most frequently created by Developers, but they are increasingly being produced using Web
Sites like Boomi, that offer high-level Tools that
can be used by non-professional developers.
Mashups are frequently associated with Widgets that are placed on PC Pages or
4.4b.3
Templates
Templates cover specification
of User Requirements and compliance with Data Governance.
4.4b.4
Tools
Tools for creating Mashups are provided by a number of commercial Suppliers, for
example :-
- Boomi – http://www.boomi.com/
- Google Mashup Editor - http://en.wikipedia.org/wiki/Google_Mashup_Editor
- Jackbe - http://en.wikipedia.org/wiki/JackBe
- Microsoft Popfly - http://en.wikipedia.org/wiki/Microsoft_Popfly
4.4b.5 Tutorials
A simple Tutorial which
provides an insight contains the following steps :-
- 1) Identify the Data Sources, such as RSS Feeds
or Google Maps
- 2) Obtain the APIs for each Source
- 3) Map the API data items to a common data
structure.
- 4) Using the appropriate programming language
such as Apex, code the User Interface (UI)
- 5) Finally, behind the UI, code the integration of the data.
Additional Tutorials will be derived from a review of Mashup
Data Architectures.
Microsoft’s highly respected
Scott Guthrie mentions a Mashup Tutorial in his Blog :-
Microsoft has an excellent
page on Enterprise Mashups in their Architecture Journal :-
which says “… all mashups are RESTful in nature
(they conform to the Representational State Transfer principles)” and contains
this very helpful diagram of the Architecture of a typical Mashup
:-
Here is the highly
recommended Microsoft Architecture Center :-
4.4b.6
How do I ?
4.4b.6.1 Demonstrate the
potential value of Mashups ?
The best way to demonstrate
the potential is to produce something quickly that meets a genuine business
requirement.
The best way to identify the
requirement is to engage with Stakeholders to determine what kind of data they
need that they are not currently getting, especially data that comes from more
than one source .
4.4b.7
Qualities for Success in creating Mashups
To be successful in creating Mashups it is necessary to possess a number of qualities :-
- To have some level of experience as a developer
- to like to work with user people
- to enjoy seeing results produced quickly that meet
a clearly defined User requirement.
- to enjoy keeping up-to-date with innovations in
fast-moving technology
- to be able to recognize innovations that are
important and have lasting power.
4.4b.8
Useful Web Links
Martin Fowler is a
highly-respected writer and has a Catalog of Enterprise Architectures – http://martinfowler.com/eaaCatalog/
Mike Walker’s Blog is worth a look –
- http://blogs.msdn.com/mikewalker/archive/2009/03/11/mapping-current-state-architectures-across-the-enterprise.aspx
Modelling Tool and Consulting for IT Architects
with download Trial -
http://www.mega.com/index.asp/l/en/c/ea
MEGA Database Builder –
http://www.mega.com/index.asp/l/en/c/product/p/mega-modeling-suite/p2/mega-database-builder
Modelling Suite for
http://www.mega.com/index.asp/l/en/c/product/p/mega-modeling-suite
- Organisation’s
Baseline Architecture –
Blog for Architects - http://chiefarchitect.squarespace.com/ea/
- Popfly on Architectures
From this Blog - http://togaforblunder.blogspot.com/2007/10/how-to-develop-complete-picture-of.html