Database Answers

 

 

4.4a Stage 4a – Performance Reports

4.4a.1 Overview

Two articles on Wikipedia provide an Overview on

 

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 UK.

 

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 London in the 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

 

London             

 

1

 

1

March 13th 09

 

London

 

2

 

2

March 20th. 09

 

London

 

3

 

3

March 27th. 09

 

London

 

4

 

4

April 3rd/ 09

 

London

 

5

 

5

April 10th. 09

 

London

 

3

 

3

April 17th. 09

 

London

 

2

 

2

April 24th. 09

 

London

 

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 Enterprise Mashups ?

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 Mobile screens to run the Mashup.

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 :-

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 :-

 

Click here for larger image

 

 

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 –

 

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 Enterprise Architecture –

http://www.mega.com/index.asp/l/en/c/product/p/mega-modeling-suite

 

 

Blog for Architects - http://chiefarchitect.squarespace.com/ea/

 

 

From this Blog - http://togaforblunder.blogspot.com/2007/10/how-to-develop-complete-picture-of.html