Sample - Data Warehouse Requirements

Document Sample
Sample - Data Warehouse Requirements Powered By Docstoc
					Data Management & Warehousing




                                                                            SAMPLE


                      Requirements Examples
                                                      DAVID M WALKER
                                                                           Version: 0.1
                                                                      Date: 01/01/2006




                      Data Management & Warehousing

   138 Finchampstead Road, Wokingham, Berkshire, RG41 2NU, United Kingdom

                          http://www.datamgmt.com
                                                  Sample - Requirements Examples




Table of Contents

Table of Contents ...................................................................................................................... 2
Synopsis .................................................................................................................................... 3
Intended Audience .................................................................................................................... 3
About Data Management & Warehousing ................................................................................. 3
Business Requirements ............................................................................................................ 4
Data Requirements ................................................................................................................... 9
   Dimensions ........................................................................................................................... 9
   Performance Measures ....................................................................................................... 11
Query Requirements ............................................................................................................... 15
Interface Requirements ........................................................................................................... 16
Copyright ................................................................................................................................. 17




         © 2006 Data Management & Warehousing                                                                                         Page 2
                                  Sample - Requirements Examples




Synopsis
This document has a number of samples of completed sections from the four sets of
requirements (Business, Data, Query and Interface) to aid analysts in completing their own
templates. The requirements are a merger of actual requirements taken from Telcos around
the world. They are not consistent nor should anything be read into any requirements as
coming from any particular operator. There are no examples from the Technical
Requirements as they do not have the same form of template table.



Intended Audience
Reader                                            Recommended Reading
Executive                                         Synopsis through to Background
Business Users                                    Synopsis through to Background
IT Management                                     Entire Document
IT Strategy                                       Entire Document
IT Project Management                             Entire Document
IT Developers                                     Entire Document




About Data Management & Warehousing
Data Management & Warehousing is a specialist consultancy in data warehousing, based in
Wokingham, Berkshire in the United Kingdom. Founded in 1995 by David M Walker, our
consultants have worked for major corporations around the world including the US, Europe,
Africa and the Middle East. Our clients are invariably large organisations with a pressing need
for business intelligence. We have worked in many industry sectors but have specialists in
Telco’s, manufacturing, retail, financial and transport as well as technical expertise in many of
the leading technologies.

For further information visit our website at: http://www.datamgmt.com




      © 2006 Data Management & Warehousing                                                 Page 3
                                    Sample - Requirements Examples




Business Requirements

                                BUSINESS REQUIREMENT
Business Requirement Number                1
Business Requirement Name                  Organisations & Individuals (Customers)
Priority                                   High
Currently available in the Data Warehouse  Yes
Description

There is a requirement to hold details and build a history of customers’ activities with Telco,
tracking them through their Telco relationship ‘life cycle’, from initial marketing prospect,
through sales, service provisioning, billing, etc.

Organisations and individuals include customers and prospects for Telco products and
services and all others for which information can be obtained. Organisations may also be
OLO (Other Licensed Operators) network customers, third-party dealers, the customers of
third-party dealers, and other organisations such as Telco own business units, OFTEL,
contractors, suppliers, etc.

Note: prospects details are only required for prospects that have become customers or
prospects that form part of the Target Account List.

The information held for organisations and individuals must include name, address,
postcode, etc. and a standardised version the customer name into a common search
format.

For organisations, other details should include:

    •    Basic company information - business and trading names, company registration
         number.

    •    A detail of all organisations sites e.g. addresses and service locations of installed
         equipment.

    •    Parent company, subsidiaries, international ownership and partners

    •    An indication of serviced customer or third party customer.

The relationships between organisations and individuals must be maintained, e.g. multiple
contacts for customer organisations. This should include the individual’s full contact details
and what they do, such as influencers, decision makers, finance/procurement contact, IT
director or contact, telephony contact, billing contact, data network contact, marketing
contact, etc.

These is also a need to hold enhanced profile information about organisations such as:

    •    Turnover, numbers of sites and number of employees per site.

    •    Telecomm profile - Comms spend (not just with Telco), telecomm products/services
         used, equipment used, other suppliers used (and products), PBX supplier.

    •    OFTEL information on licensed operators.

There is a need to maintain a history of the relationship between a customer and other
associated information. This information includes contracts, orders, products and services,
accounts, usage, non-usage, billing, payments, contacts, faults, sites, telephone numbers,
sales channels, sales account manager, market segmentation, etc.



        © 2006 Data Management & Warehousing                                                 Page 4
                                    Sample - Requirements Examples




It is required to be able to record and analyse organisations by a number of different
organisational hierarchies. These hierarchies will change over time and customers may
also be in multiple hierarchies at any one time. For example :

    •    Customer defined hierarchies e.g. business and trading names of an organisation
         and all its subsidiaries and parent companies. This is how the customer sees
         himself or herself - this might be for revenue reporting purposes.

    •    Legal hierarchies, e.g. as the government or tax authorities see a customer
         organisation - i.e. the official company structure.

    •    Dun and Bradstreet method of customer hierarchies.

    •    Wholesale customers may be made up of intermediary organisations that actually
         deal with Telco end user customers - where possible details of these levels should
         be held, i.e. from wholesaler to end user.

    •    Telco defined hierarchies or groupings, such as:

              o   Holding Company, Group Customer, Customer Account, Customer Site
                  and Customer Line Identity.

              o   Sales channel related hierarchies, i.e. how sales view their customers.

              o   ‘Forced hierarchies’, e.g. car dealerships with different names.

              o   Limited grouping of customers by customer name.

              o   Marketing sectors, segments, geography.

Over time, as history information is accumulated, these hierarchies will allow detailed
analysis of behaviour to be performed by relating the revenue, product, account, orders etc.
together for an organisational structure.

There is a requirement to have the ability to monitor and analyse -

    •    Customer profitability. Initially this will only include interconnect in-payments / out-
         payments against products and services delivered. When available, sources of
         actual cost information should be included.

    •    Product mix by revenue within customer.

    •    Customer details, e.g. monthly numbers and revenue, by customer life cycle status,
         customer hierarchy level, SOV (Sales Order Value), product, price package,
         discount plan, actual and potential comms spend, installed equipment, geography,
         campaign response or marketing activity, market segment, sector.

    •    Customer churn by product, reason, location, market segment, sector, sales
         channel, campaign code, etc. This will enable the proactive identification of revenue
         drop and provide customer churn KPI.

    •    Number and names of customers lost, their worth and billing trends.

    •    Other patterns in the customer base, e.g. customer lifecycle, customer historic
         trends, customer buying cycle, customer revenue, cost profiles, profitable/non-
         profitable, customer site moves, etc.

    •    The dealer and reseller relationship, including revenue and payments, by call type,


        © 2006 Data Management & Warehousing                                                   Page 5
                                   Sample - Requirements Examples



        national, mobile, international, etc. Also, aggregate third party customers into their
        respective dealers / suppliers.

   •    Existing resellers and dealers to identify potential reseller and dealer companies.

   •    OLO customer revenue (in-payments and out-payments) and usage patterns and
        trends, by routes/points of interconnect, tail costs, time of day, inbound, outbound,
        etc.

Definition of Key Terms

   •    Customer: An organisation or individual of interest to Telco in the provision of Telco
        products and services. Multiple views of a customer hierarchy may exist e.g. the
        customer own view of their organisation, Telco sales view, the billing systems view
        etc.

   •    Prospect: A prospect is a customer that Telco intends to attempt to sell Telco
        products and services to. ‘Prospect’ is usually treated as a status of the customer
        as a whole, or of the customer for particular products, services or campaigns.

   •    Organisation: An organisation is a grouping of one or more individuals, or of one or
        more companies, or other business activities, or for some other reason are required
        to be known about. An organisation may be a legal entity or just some recognised
        group. Examples of organisations include customers and prospects of Telco
        products and services, network customers, third-party dealers, the customers of
        third-party dealers, OLO (Other Licensed Operators), competitors, contractors,
        suppliers, government bodies, Telco own business units, etc.

   •    Individual: An individual is a person for which information can be obtained. An
        individual may be associated with an organisation, for example as a contact for an
        organisation (e.g. influencers, decision makers, finance/procurement contact, IT
        director or contact, telephony contact, billing contact, data network contact,
        marketing contact, etc.). An individual may also be a customer or prospect for Telco
        products and services or the customer of a third-party dealer.

   •    OLO (Other Licensed Operators): An organisation that is licensed by OFTEL to
        provide telecomm network services.

   •    Customer Churn: Customer Churn is defined as the number of customers leaving
        Telco from a given level of the customer hierarchy as a proportion of the total
        customer base at that level (presented either as a number or a percentage and
        calculated at monthly, quarterly and yearly intervals) - churn is also calculated by
        the value of the customer leaving Telco as a proportion of overall revenue.

   •    Dealer: A dealer is a third party organisation that has an existing business base
        through which it can sell standard Telco Branded Products. Telco owns the
        customer and consequently the Customer benefits from 24hr customer care and
        management reports etc. In return for acquiring customers on Telco’s behalf the
        dealer receives an ongoing 'revenue share’ this is a proportion the customers
        billing.

   •    Reseller: The Reseller purchases Telco products at wholesale rates and 'resells'
        them under their own brand. The contractual relationship is between the reseller
        and the customer, not Telco. The reseller sets his own retail pricing, bills the
        customer, arranges premise routing and provides customer care. Telco will provide
        the reseller with a monthly wholesale bill and provide customer care for bill queries
        and network faults to the reseller only, contact is not made between Telco and the
        reseller's customer.

        The reseller can be further subdivided into two groups: those who aggregate carrier


       © 2006 Data Management & Warehousing                                                 Page 6
                                    Sample - Requirements Examples



         minutes (an Aggregator) and those who merely resell (Direct Reseller) from one or
         two carriers.


    •    Aggregator: By using 'smart' MLU technology e.g. black box or via sophisticated
         PBX software, customer premise routing is made over a number of carriers
         according to price.

         Aggregators are able to maximise their buying power to secure the best possible
         rate for each route. Aggregators approach customers as an independent telecomm
         provider. The offer is to remove the purchase and pricing decision from the
         customer by pooling rates from all carriers and therefore always offering the best
         possible combination of rates. Effectively they will manage a customer’s telephony
         needs.

Description of limitations of current data warehouse

    •    The solution should not support lead tracking, contact management - Not all the
         information that the sales force will require is likely to be held in the warehouse as
         this will not be a suitable repository.

    •    Prospect and Customer hierarchies - will need to be given as manual or automated
         input details of group customer s and holding companies.

    •    Customer profitability - Initially this will only include interconnect payments/out-
         payments against products and services delivered. Sources of actual cost
         information to be investigated and reported on when available.

    •    No payment information will be available until increment 4

                                         Data Overview
Performance Measures                            Dimensionality / Ordered By / Described By

    •    Turnover                                     •   Customer (organisation, individual,
    •    Number of sites                                  TAL customer, OLO, supplier,
    •    Number of employees                              dealers, resellers, aggregators etc.)
    •    Telco spend (actual and potential)           •   Customer ‘life cycle’ status
    •    Account measures                             •   Organisation structure/hierarchy level
    •    Revenue                                      •   Geography
    •    Payment amount                               •   Contract
    •    Term                                         •   Orders
    •    Number of accounts                           •   Product
    •    Customer measures                            •   Account (status, date, etc.)
    •    Profitability                                •   Billing
    •    Worth                                        •   Payment
    •    Churn                                        •   Fault
    •    Number of customers                          •   Telephone number
    •    SOV (Sales order value)                      •   Call Distance
    •    Network event measures (usage)               •   Network
    •    Network tail cost                            •   Sales channel hierarchy
    •    Price, charge, discount (usage,              •   Market segment and sector
         non-usage)                                   •   Installed equipment
                                                      •   Campaign (response, activity)
                                                      •   Churn reason
                                                      •   Time


Technical Assessment




        © 2006 Data Management & Warehousing                                                    Page 7
                                 Sample - Requirements Examples




Data Retention Requirements




Other Comments




     © 2006 Data Management & Warehousing                         Page 8
                               Sample - Requirements Examples




Data Requirements

   Dimensions
                                                 DIMENSION
    Dimension Number                                  1
    Dimension Name                                    Party
    Description

    The Party dimension represents individuals, their parent organisations and the higher-level organisation
    structure.

    Hierarchy Structure


                                             Holding
                                            Company

                Customer Reporting          Customer
                     Hierarchy               Group


                                            Customer
                                             Account


                                            Customer
                                              Site


                                            Customer
                                           Line Identity



    Additional Descriptive Information

    This dimension is intended to represent individuals and organisations of interest to Telco – so,
    for instance, this may represent organisations with which Telco have a customer, supplier or
    interconnect relationship.

    The diagram above depicts one of the specific hierarchies that is required – the customer
    reporting hierarchy.

    Other requirements for the representation of organisation hierarchies may include :

         •   Business and trading names of an organisation and all its subsidiaries and parent
             companies.

         •   Legal hierarchies, e.g. as the government or tax authorities see a customer
             organisation - i.e. the official company structure.

         •   Dun and Bradstreet method of customer hierarchies.

         •   Wholesale customers may be made up of intermediary organisations that actually
             deal with Telco end user customers - where possible details of these levels should be
             held, i.e. from wholesaler to end user.




   © 2006 Data Management & Warehousing                                                     Page 9
                             Sample - Requirements Examples




      •   Telco defined hierarchies or groupings, such as:

              o    Sales channel related hierarchies, i.e. how sales view their customers.

              o    ‘Forced hierarchies’, e.g. car dealerships with different names.

              o    Marketing sectors, segments, geography.

              o    TAL method of grouping customers for a sales account manager.

              o    Customer hierarchies defined on an ad-hoc basis for Telco internal reporting.

 Organisations also include third-party dealers, the customers of third-party dealers, OFTEL,
 contractors and suppliers. No hierarchical structure within any of these is required.


 Data Steward
 Status Changes (Slowly Changing Dimensions)

 The customer reporting hierarchy is likely to change at a rate between quarterly and annually.

 The Telco sales organisation is likely to change at a rate between monthly for small changes
 and annually for large changes.


 Currently available in the Data Warehouse            Yes
 Limitations of current availability




 Description of Known Data Quality Problems




 Data Retention Requirements

 This information would be required indefinitely.

 Data Refresh Requirements

 It is important that this information stays as up to date as possible for those organisations with which
 Telco carries out transactions. For other organisations, such as prospects, the data refresh requirement
 is more lenient.

 Implementation Notes

 Customers are the holders of (active or deactivated) billing system accounts.




© 2006 Data Management & Warehousing                                                    Page 10
                             Sample - Requirements Examples




Performance Measures
                        PERFORMANCE MEASURE
Performance Measure Number        1
Performance Measure Name          Vanilla Rated Call Revenue Amount
Measure Type                      Basic
Definition

The Vanilla Rated Call Revenue is the amount of revenue generated by a call, and is attached to
a Call Detail Record (CDR) by the first pass of the rating engine.

Additional Commentary

As such, this Vanilla Rated Call Revenue Amount is the basic revenue value attached
to the call record, and includes factors such as the time of day the call was made, the
day of the week, the message source code, the charge band and the specific rating
group.

It excludes any subsequent rating changes that are applied by the billing system, which
turn Vanilla Rated Call Revenue into the Re-Rated Call Revenue.

The Vanilla Rated Call Revenue Amount could also loosely be referred to as unbilled
usage revenue, and should therefore have a revenue status type of unbilled usage
revenue.

Data Steward
Dimensionality / Ordered By / Described By

    •   Party – Customer Organization
    •   Account
    •   Customer Site (Service Location)
    •   Product / Service
    •   Product / Service Instance (Product / Service + Customer Site)
    •   Message Source Code
    •   Time – charge time (at the individual charge level) and billed time.
    •   Telephone Number and its Geography (where applicable)
    •   Account Address and its Geography
    •   Sales Channel
    •   Market Segment
    •   Campaign
    •   Distance Band
    •   Time Band
    •   Revenue Status Type
    •   Scheme
    •   Volume Band
    •   Duration Band

Currently available in the Data Warehouse        Yes
Limitations of current availability

For unbilled usage: Vanilla Rated Call Revenue Amount will be accumulated alongside
the Call Duration on a daily basis by telephone number per bill cycle, and will be zeroed
at the start of each bill production run for that telephone number. A later increment
may load individual unbilled usage revenue at the CDR level for both Vanilla Rated Call
Revenue and Call Duration.




© 2006 Data Management & Warehousing                                                   Page 11
                              Sample - Requirements Examples




For billed usage: Vanilla Rated Call Revenue Amount will be stored at the individual
CDR level alongside the Call Duration and the Re-Rated Call Revenue Amount.


Description of Known Data Quality Problems




Data Retention Requirements

Individual Call Detail Records are required for a rolling three months, which therefore implies that
Vanilla Rated Call Revenue Amounts will be available for a rolling three months.

Data Refresh Requirements

See above comments in Limitations in Increment 1.

The cumulative unbilled usage (Vanilla Rated Call Revenue Amount and Call Duration)
by telephone number by bill cycle will be a daily feed into the system.

This should be replaced, in a later increment, by a daily feed of individual unbilled
usage CDR records.

At the billed usage level, this information should be loaded into the system once per bill
production run. That is, there are currently estimated to be six bill production runs per
month.

Implementation Notes




© 2006 Data Management & Warehousing                                                        Page 12
                            Sample - Requirements Examples




                        PERFORMANCE MEASURE
Performance Measure Number        2
Performance Measure Name          Number of Services
Measure Type                      Count
Definition

The Number of Services is an aggregated performance measure that is a simple count
of the number of occurrences of at least one instance of a product/service code at a
particular chosen level of interrogation

Additional Commentary

For example – we may wish to know the number of different service codes and their
descriptions by account number and service location, in order to explore service mix.

Data Steward
Dimensionality / Ordered By / Described By

    •   Party – Customer Organization
    •   Account
    •   Customer Site (Service Location)
    •   Product / Service
    •   Product / Service Instance (Product / Service + Customer Site)
    •   Account Address and its Geography
    •   Market Segment
    •   Scheme

Currently available in the Data Warehouse       Yes
Limitations of current availability




Description of Known Data Quality Problems




Data Retention Requirements




Data Refresh Requirements




Implementation Notes




© 2006 Data Management & Warehousing                                             Page 13
                            Sample - Requirements Examples




                        PERFORMANCE MEASURE
Performance Measure Number        3
Performance Measure Name          Bill Balance Brought Forward Amount
Measure Type                      Derived
Definition

The Bill Balance Brought Forward Amount is the balance brought forward from the
previous bill cycle's bill


Additional Commentary

The Bill Balance Brought Forward Amount should be the same as the previous bill
cycle’s Bill Balance Carried Forward Amount.

The Bill Balance Brought Forward Amount will be sourced directly from the source
billing systems.

Data Steward
Dimensionality / Ordered By / Described By

    •   Party – Customer Organization
    •   Account
    •   Account Address and its Geography
    •   Time – billed time
    •   Sales Channel
    •   Market Segment
    •   Campaign

Currently available in the Data Warehouse       Yes
Limitations of current availability

The speed of response will depend on the available level of pre-calculated aggregates.

This information could be made available from the source systems should the Systems
Roadmap point to this being an area of high priority.

Description of Known Data Quality Problems




Data Retention Requirements




Data Refresh Requirements




Implementation Notes




© 2006 Data Management & Warehousing                                            Page 14
                                    Sample - Requirements Examples




Query Requirements
                                       Query Requirement
Query Requirement Number                        1
Query Requirement Name                          Credit/aged report
Description

A report that show the outstanding debt by account and credit group

Definition of Key Terms



Currently available in data warehouse            YES
Description of limitations within current implementation



Requested by
Data Overview

Debts by account and how long that debt has been outstanding

Performance Measures                                Dimensionality / Ordered By / Described By

    •    credit limit                                   •     credit class
    •    current balance                                •     account number
    •    balance 30 days                                •     bill cycle/run
    •    balance 60 days
    •    balance 90 days
    •    balance 120+ days


Technical Assessment



Data Retention Requirements



Other Comments



Frequency                                           Weekly
Type                                                Regular




        © 2006 Data Management & Warehousing                                             Page 15
                                 Sample - Requirements Examples




Interface Requirements
                                           Interface
Interface Number                  1
Interface Name                    Customer Segmentation Groups
Target System Name                CRM System
Output Type                       File
Output Location                   /BI/ftp/outgoing/CRM
Output File Name                  csg.csv
Output File Type                  CSV
Output Format                     ASCII
Output Character Set              US-ASCII
Output Frequency                  Daily
CSV Specifics
Field Separator                   Comma (,)
Record Separator                  Carriage Return – Line Feed
Quoting Character                 Double Quote (“)
Escaping Character                Double Quote (“)
XML Specifics
Validation
Validation Location
Description/Purpose of File

This file is used to update the CRM system with the current market segment group and
score information


Data Definition
Field Name                    Source Field Name                   Mandatory?
Customer_id                   Customer.customer_id                Yes
Market_Segment                MarSeg.segment_name                 Yes
Segment_Score                 Custom_MarSeg_History.score         Yes
                                                                  Yes / No
                                                                  Yes / No
                                                                  Yes / No
                                                                  Yes / No
                                                                  Yes / No
                                                                  Yes / No
                                                                  Yes / No
Currently available from the Data Warehouse       Yes
Limitations of current availability




Description of Known Data Quality Problems




Implementation Notes




     © 2006 Data Management & Warehousing                                      Page 16
                                 Sample - Requirements Examples




Copyright
© 2006 Data Management & Warehousing. All rights reserved. Reproduction not permitted
without written authorisation. References to other companies and their products use
trademarks owned by the respective companies and are for reference purposes only.




     © 2006 Data Management & Warehousing                                     Page 17

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:62
posted:4/24/2012
language:English
pages:17