Data Warehouse Bus Architecture Matrix

Document Sample
Data Warehouse Bus Architecture Matrix Powered By Docstoc
					                          Data Warehouse Datamart Design
                         DRAFT Version 0.2, 22 February 2008

Datamart-Report matrix
In this matrix we list potential datamarts and dimensions the reports that will use them to derive their
reporting information. Here we loosely define a datamart as a clump of (mostly numeric) facts that look
like they could be used together.




                                                                                                                                                                          Detailed Aging of Portfolio at at
                                                                                                                                     Active Loans by Loan Officer



                                                                                                                                                                          Branch Cash Confirmation



                                                                                                                                                                                                                                        Clients without Loans




                                                                                                                                                                                                                                                                                                   Delinquent Portfolio
                                                                                                       Consolidated Report
                                                                         Financial Indicators




                                                                                                                                                                                                                                                                Loans by Gender
                         Collection Sheet

                                              Branch Progress




                                                                                                                                                                                                                                                                                  Client Dropout
                                                                                                                                                                                                                    Loan Status
                                                                                                                                                                          risk

Client snapshot                              X                       X                              X                                                                                                                                X                                            X
Loan officer snapshot                        X                                                      X
Loan snapshot           X                    X                       X                              X                            X                                     X                                         X                                              X
Savings snapshot                                                                                                                 X

Datamart-Dimension matrix
This table shows the dimensions that will be used to browse, aggregate, or group information from the fact
tables for each datamart. The table was derived by inspecting samples or specifications of current reports or
those needed for release 1.1.
                                                                Client Hierarchy



                                                                                                                             Staff Hierarchy
                                                                                                Poverty Level



                                                                                                                                                                    Loan Product




                                                                                                                                                                                                                                  Loan Product
                                                                                                                                                                                            Client Status

                                                                                                                                                                                                              Loan Status
                                            Time




Client snapshot                             X                   X                               X                            X
Loan snapshot                               X                   X                                                            X                                      X
Loan officer snapshot                       X                                                                                X
Client Status Transactions                  X                   X                                                                                                                        X




                                                                                                                                         Page 1 of 6
Data Warehouse Datamart Design
DRAFT Version 0.2, 22 February 2008


Dimensions
These dimensions are needed to produce the sliced/diced data for the above reports.

Time_Dim
The time dimension allows browsing at the daily level of granularity -- no business rules have been
identified that might demand a finer grain than the day.

          Field            Standard SQL Type              Values                   Comments
id (PK)                    INT                      1, 2, …              Start at 1. Does not need to be
                           AUTO_INCREMENT                                 sequential by date.
date                       DATE
day_num_in_week            SMALLINT                 1=Monday,            Locale-specific? Do different
                                                    … 7=Sunday            locales have different
                                                                          conventions for first day of
                                                                          week?
                                                                         Is the concept of a “week”
                                                                          locale-specific?
week_num_in_year           SMALLINT
week_num_overall           SMALLINT                 1..                  See comment for
                                                                          day_num_overall
day_of_week                VARCHAR(100)             “Monday”,            Must be locale-specific.
                                                    etc.                 Need to determine max possible
                                                                          length (50 is arbitrary)
day_num_in_month           SMALLINT                 1..31                Do different locales have
                                                                          different calendars?
day_num_overall            INT                      1..                  A Julian-type day number
                                                                          starting at some beginning date
month                      VARCHAR(100)             “January”, etc       Must be locale-specific
month_num_in_year          SMALLINT                 1..12
month_num_overall          SMALLINT                 1..                  See comment for
                                                                          day_num_overall
quarter_num_in_year        SMALLINT                 1..4                 Must we allow for fiscal year
                                                                          differing from calendar year?
quarter_name               VARCHAR(100)             “First               Local-specific
                                                    quarter”, etc
year                       YEAR                     2007, etc
weekday_flag               SMALLINT                 1=TRUE,
                                                    0=FALSE
last_day_in_month_flag     SMALLINT                 1=TRUE,
                                                    0=FALSE
holiday_flag               SMALLINT                 1=TRUE,
                                                    0=FALSE



                                                Page 2 of 6
Data Warehouse Datamart Design
DRAFT Version 0.2, 22 February 2008

holiday_name               VARCHAR(100)


Client Hierarchy
Reports use this dimension to browse clients by any level in the hierarchy. Foreign keys link to
subdimensions representing coarser-grained organizational levels. Coarser-grained dimensions can be used
either to constrain client_id when browsing tables linked to client, or to browse directly tables whose grain
is at their level (e.g. a branch snapshot table). This is illustrated here:




ISSUE: The hierachy shown below is static. However, organizational hierarchy is not necessarily static, but
may vary from MFI to MFI in two different ways:
 The office hierarchy can include from 2 to 5 levels – see http://www.mifos.org/knowledge/functional-
    specs/offices?searchterm=office+hierarchy,
             o A possible workaround is to include fake organizations at the missing levels of the
                  hierarchy.
 Although it is mandatory that clients be organized into groups, an MFI may choose not to form centers
    of groups.
             o A possible workaround is to create a fake center that includes all groups in a branch.


Client_Dim

         Field              Standard SQL Type              Values                   Comments
id                         INT                       1..                   Surrogate key used by fact tables
                           AUTOINCREMENT




                                                 Page 3 of 6
Data Warehouse Datamart Design
DRAFT Version 0.2, 22 February 2008

client_num            INT                                   the id defined by the operational
                                                             system. Extracted from
                                                             CUSTOMER.CUSTOMER_ID
global_cust_num       VARCHAR(100)                          defined in the operational tables
                                                             Extracted from CUSTOMER.
                                                             GLOBAL_CUST_NUM
group_id              INT                                   foreign key linking to
                                                             Group_Dim
center_id             INT                                   foreign key linking to
                                                             Center_Dim
branch_id             INT                                foreign key linking to Branch_Dim
full_name             VARCHAR(200)                          extracted from
                                                             CUSTOMER.DISPLAYNAME



Group_Dim
            Field     Standard SQL Type         Values               Comments
id                    INT                 1..               Surrogate key used by fact tables
                      AUTOINCREMENT                          or Client_Dim
group_num             INT                                   the id defined by the operational
                                                             system. Extracted from
                                                             CUSTOMER.CUSTOMER_ID
global_cust_num       VARCHAR(100)                          defined in the operational tables.
                                                             Extracted from CUSTOMER.
                                                             GLOBAL_CUST_NUM
center_id             INT                                   foreign key linking to
                                                             Center_Dim
name                  VARCHAR(200)                          extracted from
                                                             CUSTOMER.DISPLAYNAME

Center_Dim
            Field     Standard SQL Type         Values               Comments
id                    INT                 1..               Surrogate key used by fact tables
                      AUTOINCREMENT                          or Client_Dim
center_num            INT                                   the id defined by the operational
                                                             system. Extracted from
                                                             CUSTOMER.CUSTOMER_ID
global_cust_num       VARCHAR(100)                          defined in the operational tables.
                                                             Extracted from CUSTOMER.
                                                             GLOBAL_CUST_NUM
branch_id             INT                                   foreign key linking to
                                                             Center_Dim




                                      Page 4 of 6
Data Warehouse Datamart Design
DRAFT Version 0.2, 22 February 2008

name                       VARCHAR(200)                                    extracted from
                                                                            CUSTOMER.DISPLAYNAME



Branch_Dim
         Field              Standard SQL Type              Values                   Comments
id                         INT                       1..                   Surrogate key used by fact tables
                           AUTOINCREMENT                                    or Client_Dim
branch_num                 INT                                             the id defined by the operational
                                                                            system. Extracted from
                                                                            OFFICE.OFFICE_ID
global_branch_num          VARCHAR(100)                                    defined in the operational tables.
                                                                            Extracted from OFFICE.
                                                                            GLOBAL_OFFICE_NUM
name                       VARCHAR(200)                                    extracted from
                                                                            OFFICE.DISPLAYNAME
created_date               DATE                                            not yet populated by MifOS but
                                                                            needed for Financial Indicators
                                                                            report
dissolved_date             DATE                                            not yet populated by MifOS but
                                                                            needed for Financial Indicators
                                                                            report



Poverty Level
Browse clients by poverty level. Include “none” for clients whose poverty level has not yet been
established.

id
level_num
level_name

Loan Status
Browse loans by status (none, active, closed, awarded) ). Note that the status “none” must be included in
order to populate the Client_Status_Trans_Facts table with the first entry for a client.
id
status_name

Client Status
Browse clients by status (none, cancel, partial application, pending approval, approved/active, hold,
closed). Note that the status “none” must be included in order to populate the Client_Status_Trans_Facts
table with the first entry for a client.

The set of possible client status values can be found at http://mifos.org/knowledge/functional-
specs/clients?searchterm=client+status.

Two of the status values require specifying a reason for the status change. See the above link.




                                                 Page 5 of 6
Data Warehouse Datamart Design
DRAFT Version 0.2, 22 February 2008

            Field            Standard SQL Type                Values                  Comments
id                          INT                        1..                  Surrogate key used by fact tables
                            AUTOINCREMENT
name                        VARCHAR(100)
reason                      VARCHAR(100)                                    is null if status does not require a
                                                                             reason


Loan Officer
id
full_name
employee_num
branch_name
join_date

Loan Product
id
full_name
Loan_product_num



Fact Tables
Client_Status_Trans_Facts
This fact-less table captures each day that a client’s status changes.

ISSUE: Can the status of a client change more than once on the same day? If so, we have a problem…
            Field             Standard SQL Type               Values                  Comments
client_id                    INT                        1..                 Foreign key linking to
                             AUTOINCREMENT                                   Client_Dim
date_id                      INT                                            Foreign key linking to
                                                                             Time_Dim
new_client_status_id         INT                                            Foreign key linking to the new
                                                                             status in the Client_Status_Dim
previous_client_status_id    INT                                            Foreign key linking to the old
                                                                             status. May be useful for
                                                                             reporting transition facts




                                                  Page 6 of 6