search

18F / data-act-pilot

This small DATA Act pilot contains code that translates agency data to a uniform DATA act format.
Other
21 stars 14 forks source link

research questions: mapping document #35

Closed bsweger closed 9 years ago

bsweger commented 9 years ago

Sprint 1 research questions about the SBA to DATA Act mapping document:

  1. The mapping doc says that TAS info isn't currently in use (and that GTAS will be implemented soon). A prior version of the doc maps TAS to Prism itemacct.TAS#. For this sprint, we hard-coded the TAS that corresponds to the single award we matched. Going forward, should we look for TAS in itemacct until GTAS is implemented?
  2. Place of performance city is mapped to Prism faadsciv.census_code. Doesn't look like this column exists, so we used countycityname instead during the first sprint. Can you confirm the correct mapping for place of performance city?
  3. Why are unobligated amount and amount of other budgetary resources marked as NA in the mapping document?
  4. Is there any more information available about Type of Action (it's marked as a computed column in the mapping document)?
  5. There are several fields marked as NA in the mapping document. What are the specific reasons?
    • awardee/recipient parent DUNS Number and awarding/recipient parent legal business name
    • awardee/recipient country name and congressional district
    • period of performance potential end date
    • ordering period end date
  6. The mapping doc doesn't specify a mapping for CFDA program number/title. We used the following for these fields during the sprint. Can you confirm that this is correct?
    • Prism faadsciv.programnumber
    • Prism faadsciv.programtitle
  7. The mapping doc doesn't specify a mapping for Place of Performance County Name and Place of Performance County Code. We used the following for these fields during the sprint. Can you confirm that this is correct?
    • grantheader.sba1222countyname
    • grantheader.sba1222countycode
  8. Several DATA Act elements are mapped to JAAMS po_lines_all.quantity * po_lines_all.unit_prices. Is there additional nuance we should know about?
    • funding action obligation
    • non-federal funding amount
    • current total funding obligation amount on award
    • current total value of award
    • potential value of award
  9. We need some clarification for awardee/recipient address:
    • Will SAM be the authoritative source? The current mapping doc shows SAM for addressline1, but then maps it to a Prism table (VENADDRESS.ADDRESS1). The rest of the address fields are mapped to JAAMS (AP_SUPPLIER_SITES)
    • The mapping doc specifies JAAMS table AP_SUPPLIER_SITES, but our data only has a table called AP_SUPPLIER_SITES_ALL. We used this--is that correct?
    • If we should, in fact, be getting address info from AP_SUPPLIER_SITES_ALL: Wwhen there are multiplier SUPPLIER_SITES records for a single vendor, which one should we use to get the address info?
  10. The mapping doc contains mapping information for county name and code (grantheader.sba1222countyname and grantheader.sba1222countycode), but those fields aren't in the DATA Act schema. Were those fields included because they're especially useful or needed for another process?
bsweger commented 9 years ago

Didn't have time to hit the mapping questions in today's meeting. Am scheduling a follow-up for these.

bsweger commented 9 years ago

A few notes from a phone call we had w/ SBA on 5/19. Sprint 1 research questions about the SBA to DATA Act mapping document:

  1. RESOLVED Once SBA moves to GTAS at the end of this FY, JAAMS will contain the TAS (and BETC). It's technically in JAAMS now but is not readily accessible. Scott sent some additional tables that we can use to pull TAS for the pilot; we'll note that this source will like change after the GTAS work is done.
  2. RESOLVED faadsciv.citycountyname confirmed as source
  3. MOVED TO #49 More research needed--need to better understand the corresponding data element definitions.
  4. MOVED TO #49 More research needed--need to better understand the corresponding data element definitions.
  5. MOVED TO #60 SBA does not capture sub-recipient information. They ask contract recipients to report information about subs in SSRS, but that info doesn't flow back to JAAMS or Prism. Is there a way we can pull this info from SSRS?
  6. RESOLVED Confirmed that the above mappings are correct. SBA pointed out that this info is pulled from a CFDA master table as needed by the application.
  7. RESOLVED Confirmed that the above mappings are correct. SBA pointed out that this info is pulled from a master table as needed by the application.
  8. MOVED TO #31 Got some additional mapping data helps clarify this. Will likely have some follow-up questions.
  9. MOVED TO #75 This led to an interesting conversation. The vendor addresses are checked against SAM when entered into Prism. Once an award is made, that vendor info becomes part of the "base" award record and doesn't change. However, once an award is "modded," this type of base information is updated. So, if you want to get the most current information available, you would pull vendor info from the most recent award mod in Prism. But if you wanted to see where the vendor was located at the time the money was actually awarded, you would use the vendor address on the applicable award/mod record. This raises two questions:
    • will/how this type of base award data be presented when we're showing aggregate financial data numbers like total obligations?
    • where does SAM come in (for now, we're going to hit SAM as a validation mechanism)
  10. SCHEMA QUESTION Unsure.
bsweger commented 9 years ago

A few new mapping questions/comments:

[updates inline below]

General

Prism

JAAMS

bsweger commented 9 years ago

Closing this b/c everything is either resolved, incorporated into a user story, or reflected in a more specific research question.