config = configparser.ConfigParser()
config.read(CONFIG)
with TM1Service(**config['tm1srv01']) as tm1:
# create dimensions
dimension = Dimension(
name="Py Project")
hierarchy = Hierarchy(
name="Py Project",
dimension_name="Py Project",
elements=[
Element("Project1", "Numeric"),
Element("Project2", "Numeric"),
Element("Project3", "Numeric")])
dimension.add_hierarchy(hierarchy)
if not tm1.dimensions.exists(dimension.name):
tm1.dimensions.create(dimension)
dimension = Dimension(
name="Py Quarter")
hierarchy = Hierarchy(
name="Py Quarter",
dimension_name="Py Quarter",
elements=[
Element("2018-Q1", "Numeric"),
Element("2018-Q2", "Numeric"),
Element("2018-Q3", "Numeric"),
Element("2018-Q4", "Numeric"),
Element("2019-Q1", "Numeric"),
Element("2019-Q2", "Numeric"),
def build_dimensions(tm1: TM1Service, loans: list):
dimension_name = "Time"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for year in range(2005, 2020, 1):
hierarchy.add_element(element_name=str(year),
element_type="Consolidated")
hierarchy.add_edge(parent=total_element, component=str(year), weight=1)
for month in ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec"):
hierarchy.add_element(element_name="{}-{}".format(
month, str(year)),
element_type="Numeric")
hierarchy.add_edge(parent=str(year),
component="{}-{}".format(month, str(year)),
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Loan"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
dimension.add_hierarchy(hierarchy)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
element_names = [loan.loan_id for loan in loans]
# send elements and edges separately to avoid strange firewall connection abortion from server side
for element_name in element_names:
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
update_or_create_dimension(tm1, dimension)
for element_name in element_names:
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
update_or_create_dimension(tm1, dimension)
dimension_name = "LC Rating"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for rating in ("A", "B", "C", "D", "E", "F", "G"):
element_name = rating
hierarchy.add_element(element_name=element_name,
element_type="Consolidated")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
for sub_rating in ("1", "2", "3", "4", "5"):
element_name = rating + sub_rating
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=rating, component=element_name, weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "FICO Score"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in range(300, 851, 1):
hierarchy.add_element(element_name=str(element_name),
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=str(element_name),
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Employment"
if tm1.dimensions.exists(dimension_name=dimension_name):
dimension = tm1.dimensions.get(dimension_name=dimension_name)
else:
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
if hierarchy_name in dimension:
hierarchy = dimension.get_hierarchy(hierarchy_name=hierarchy_name)
else:
hierarchy = Hierarchy(name=hierarchy_name,
dimension_name=dimension_name)
dimension.add_hierarchy(hierarchy)
total_element = "Total " + dimension_name
if total_element not in hierarchy.elements:
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
employments = CaseAndSpaceInsensitiveSet()
for loan in loans:
employments.add(loan.emp_title)
employments.add("None")
for employment in employments:
if employment and employment not in hierarchy.elements:
employment = employment.strip()
hierarchy.add_element(element_name=employment,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=employment,
weight=1)
update_or_create_dimension(tm1, dimension)
dimension_name = "Term"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("36 months", "60 months"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Income"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("10000", "20000", "30000", "40000", "50000", "60000",
"70000", "80000", "90000", "100000", "110000",
"120000", "130000", "140000", "150000", "160000",
"170000", "180000", "190000"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Purpose"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("other", "debt_consolidation", "medical",
"major_purchase", "home_improvement", "credit_card",
"vacation", "house", "car", "small_business",
"moving", "renewable_energy", "wedding",
"educational"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Loan Status"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("Current", "Fully Paid", "Late (31-120 days)",
"Late (16-30 days)", "Charged Off", "In Grace Period",
"Default"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "State"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
states = CaseAndSpaceInsensitiveSet()
for loan in loans:
states.add(loan.addr_state)
states.add("None")
for state in states:
if state:
state = state.strip()
hierarchy.add_element(element_name=state, element_type="Numeric")
hierarchy.add_edge(parent=total_element, component=state, weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Home Ownership"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("Rent", "Own", "Mortgage", "Any", "None"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Application Type"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in ("Individual", "Joint App"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Income To Loan Ratio"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for counter in range(0, 100000, 1):
dti = counter / 100
element_name = "%.2f" % dti
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=element_name,
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = "Delinquency Events"
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
total_element = "Total " + dimension_name
hierarchy.add_element(element_name=total_element,
element_type="Consolidated")
for element_name in range(0, 51):
hierarchy.add_element(element_name=str(element_name),
element_type="Numeric")
hierarchy.add_edge(parent=total_element,
component=str(element_name),
weight=1)
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
dimension_name = " ".join([CUBE_NAME, "Measure"])
dimension = Dimension(name=dimension_name)
hierarchy_name = dimension_name
hierarchy = Hierarchy(name=hierarchy_name, dimension_name=dimension_name)
for element_name in ("loan_amnt", "int_rate", "installment", "out_prncp",
"total_pymnt", "last_pymnt_amnt",
"total_pymnt_by_loan_amnt", "emp_length",
"num_personal_inquiries", "inquiries_in_last_12m",
"mths_since_last_delinq", "mths_since_recent_bc_dlq",
"mths_since_recent_inq",
"mths_since_recent_revol_delinq", "defaulted",
"defaulted_or_delayed"):
hierarchy.add_element(element_name=element_name,
element_type="Numeric")
for element_name in ("last_pymnt_d", ):
hierarchy.add_element(element_name=element_name, element_type="String")
hierarchy.add_element_attribute(name="Description", attribute_type="Alias")
dimension.add_hierarchy(hierarchy)
update_or_create_dimension(tm1, dimension)
attributes = {
("loan_amnt", "Description"): "Listed amount of the loan",
("int_rate", "Description"): "Interest rate",
("installment", "Description"): "Monthly payment",
("out_prncp", "Description"): "Remaining outstanding amount",
("total_pymnt", "Description"): "Payments received to date",
("total_pymnt_by_loan_amnt", "Description"):
"Total Payments devided by total Loan",
("last_pymnt_amnt", "Description"): "Last payment amount received",
("last_pymnt_d", "Description"): "Last month payment was received",
("defaulted", "Description"): "Loan is defaulted"
}
tm1.cubes.cells.write_values(cube_name="}ElementAttributes_" +
dimension_name,
cellset_as_dict=attributes)