DARTS : Data Model

Data Model Folder  

Items to Investigate and/or Add:

• Project_ID is not Business Unit specific. The same ID can be used in the multiple institutions. 

select distinct project_id , count (distinct Business_unit)
from sysadm.ps_um_glq_reprting
group by project_id
having count(distinct Business_unit) > 1

• 2/12/20 from Kate: Statistics_code should be own dimension.
• 10/10/19 from Kate: Class_Dim should be under fact
• 8/12/19: add UM_FUTURE_BUDGET
• 8/8/19: When numeric doesn't have decimal point, change to int.
• 8/7/19: EFFECTIVE_FROM_FYAP_PK and EFFECTIVE_TO_FYAP_PK are int.
• 8/1/19: Change "char" to "varchar"
• 7/26/19 from Darla:
  • Take the most recent data when data changed during the month (i.e. manager's name).
  • Use Accounting_period=12 from the previous fiscal_year for descriptions for Accounting_period=0. 
  • • UM_FUND_DESCR
    • UM_BU_DESCR
    • UM_DEPT_DESCR
    • UM_OPUN_DESCR
    • UM_PROJ_DESCR
    • UM_PROG_DESCR
• 7/25/19:
  • Add Fiscal_Year to Account Dimension, Fact Table 
  • Ask Gary for feedback and partitioning table (Emailed Gary)
    
• 7/17/19:
  • Think about partitioning 
  • Add AK by combining a key field and EFFECTIVE_FROM_FYAP_PK & EFFECTIVE_TO_FYAP_PK  BI-78 - alternate keys for glq data mart Done
• Meeting on 7/2/19 & 7/8/19:
  • Bridge table or straight snowflake for (Department → Account → Class)
    • find some examples that roll up account only 
    • Add Business_Unit to Department_Dim, Program_Dim, Project_Dim
  • Capture change for account descriptions (current indicator, effective FY_Period) 
    • CURRENTINDICATOR INT(1) NOT NULL DEFAULT 0
    • EFFECTIVEFROM_fiscalyearaccountPeriod_pk (EFFECTIVE_FROM_FYAP_PK)
    • EFFECTIVETO_fiscalyearaccountPeriod_pk (EFFECTIVE_TO_FYAP_PK)
    • fiscalyearaccountPeriod_pk == 2019007  → fy*1000 + fy_period
  • Find out if other fields need SCD
    • except fund and operating unit, everything else need SCD (above fields)
  • Add DI_SOR_PK for identifying source table (1 for each table, nullable field)
    • Renamed to SOR_System_SK to reflect all the Rick's work on staging
  • Create custom accounting period description table in staging
  • Naming Convention
    • Keep same name as source on staging and integration
    • Rename in views or Tabular model
  • Replace Business_Unit_Descr with UM_BU_DESCR and UM_BU_DESCRSHORT
    
    
• Project_to_date is fact
• DEPTID is missing
• What is Fiscal_Code and where does it come from?
  • Deleted.  
    
• Is Fiscal_Month a decode of accounting period or is it an actual field from the reporting tables renamed?  If it's meant to be a decode, are we sure it doesn't exist already in FN.  If it doesn't, can we build a decode (i.e. 1, July, 2, August, etc.) into the datamart?
  
  • changed to Accounting_Period_Descr.
• We should double check all Data types 
  • Pavan will work with Gary to follow what's available in SQL Server.
• Questions for FK: How is FK populated? How is it used to join to the dimension tables? 
  • Will create alternate key in fact table that relates to foreign key in dimension table
    
• Mix case and all upper case
  • Unknown User (gary.blake) needs to make sure that SQL server is NOT case sensitive.
    
• Talked to Darla on 5/16: 
  • CLASS_FLD is depend on Account and DEPTID. 
    • Delete UM_CLASS_D since the data is not valid.
    • CLASS_FLD is aligned under Account_Dim
    • Miki Yanagi will check change of data to determine the need of additional attributes → everything except Fund and Operating Unit
    • Use snowflakes (Department → Account → Class)
  • Data refresh: Daily except Sunday at 4am (tentative). Prior FY, Current FY, & Future FY. Occasionally entire refresh. → Use Run control
  • Description for Accounting_period =0 → 'Beginning of Year Balance', 997& 998 → 'Accounting Use Only'
  • Don't need Business_unit= 'MCV09'
• Talked to Darla on 6/13/19:
  • Data for FY 2004 and 2005 in UM_GLQ_REPRTING is not valid. Do not pull the data for datamart.
  • Description will be fixed in the table. (Don't need extra tables for staging)