Get documents about "Data Warehouse Bus Architecture Matrix
Document Sample
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
