[1st viz] Research data preparation in backend for first user story about boxtribute HOT 3 CLOSED

User story: beneficiary demographics

We'd like to obtain the number of beneficiaries (fact) for all sets of gender, age(-bin), and creation date (dimensions).

BE implementation

Such data can be obtained by first querying beneficiary rows, then transforming it into a pivot table (e.g. using the pandas framework; id as values, gender, created_on, age as index, count as aggfunc), and finally transforming this table into single data points again.

However due to the simplicity of the aggregation, the data can be obtained directly from the DB via

    bin_width = 5
    gender = fn.IF(Beneficiary.gender == "", "D", Beneficiary.gender)
    created_on = db.database.truncate_date("day", Beneficiary.created_on)
    age = fn.FLOOR((date.today().year - Beneficiary.date_of_birth.year) / bin_width)

    demographics = (
        Beneficiary.select(
            gender.alias("gender"),
            created_on.alias("created_on"),
            age.alias("age"),
            fn.COUNT(Beneficiary.id).alias("count"),
        )
        .where(Beneficiary.deleted.is_null())  # add base IDs
        .group_by(SQL("gender"), SQL("age"), SQL("created_on"))
    )

The final implemenation will contain a filter for base IDs, too.

Data format

The resulting data format is an object with a facts attribute that contains a list of objects with keys age, gender, createdOn, tagIds, and count; and with a dimensions attribute that contains a tag field.

GraphQL interface

To return the data via GraphQL the following extension is proposed:

type Query {
  ...
  beneficiaryDemographics(baseIds: [Int]): BeneficiaryDemographicsData
}

type BeneficiaryDemographicsData {
  facts: [BeneficiaryDemographicsResult]
  dimensions: BeneficiaryDemographicsDimensions
}

type BeneficiaryDemographicsResult {
  age: Int
  gender: HumanGender
  createdOn: Datetime
  tagIds: [Int!]
  count: Int
}

type BeneficiaryDemographicsDimensions {
  tag: [ResultIdName]
}

type ResultIdName {
  id: ID
  name: String
}

from boxtribute.

@MaikNeubert @HaGuesto please see the above proposal. Experimental implementation lives in this branch

from boxtribute.

works from my side

from boxtribute.