PowerBI API Integration

Columns used:

Name of the DB table	Columns Used	Is this column used for join? (Key)

Name of the DB table	Columns Used	Is this column used for join? (Key)
Story	ID, Change ID, Project ID, created_at, updated_at, User ID, charity ID, Story Category ID, Latitude, Longitude, Custom Fields, Send Survey ID, Event ID, Know Beneficiaries Count, Unkown Beneficiaries Count, Ward ID	ID (Story ID)
Story Indicator Beneficiaries	Beneficiary ID, indicaor ID, Indicator Type, Outcome ID, Sub Ratio ID,
Story Change Beneficiaries	Beneficiary ID, Change ID
Sub Ratios	Name, User ID
Indicators	Name, Indicator ID, Ratio Set ID, Owner ID, Owner Type, Success Direction
Changes	Name	ID (Change ID)
Beneficiary	Name	ID (beneficiary ID)
Custom Fields	Name	ID (Custom Field ID)

Columns not used:

Name of the table	Columns not used

Name of the table	Columns not used

JSON Structure:

Way 1: (Story as unique value perspective)

{
{
story {id:1
},
indicator { id10,id20,id30
},
change { id100,id200
},
beneficiary {001,0012,00123
},
custom fields {
}
},
{
story {id:2
},
indicator { id10,id20,id33
},
change { id100,id200
},
beneficiary {001,0012,00123
},
custom fields {
}
}
}

Way 1: (Story as unique value perspective) (Different Structure)

{
{
{id:360001, text: "", story caregory id:4745, indicator { 
5666: { }, 5667, 5668
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "The rainy season is taking its toll on our ambulance! We got stuck in the mud on our way to our clinic yesterday!", story caregory id:7574, indicator { 
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "i", story caregory id:7574, indicator { 
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "i", story caregory id:7574, indicator { 
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
}
}

Example of single box

{
  "id": 360001,
  "text": "",
  "story_category_id": 4745,
  "project_id": 3171,
  "user_id": 5652,
  "charity_id": "",
  "send_survey_id": "",
  "event_id": "",
  "known_beneficiaries_count": 1,
  "unknown_beneficiaries_count": 0,
  "beneficiaries": [
    {
      "id": 132017,
      "indicators": [
        {
          "id": 5667,
          "name": "Young Person programme/service Status",
          "indicator_type": 2,
          "ratio_set_id": 3383,
          "indicator_type_id": 2,
          "success_direction": "none",
          "outcome_id": 2283,
          "owner_type": "charity",
          "owner_id": 793,
          "sub_ratio_id": 14151,
          "sub_ratio_name": "On Progression"
        },
        {
          "id": 5666,
          "name": "Young Person NEET/EET Status",
          "indicator_type": 2,
          "ratio_set_id": 3383,
          "indicator_type_id": 2,
          "success_direction": "none"
        },
        {
          "id": 5668,
          "name": "Evidence of Young person status",
          "indicator_type": 2,
          "ratio_set_id": 3383,
          "indicator_type_id": 2,
          "success_direction": "none"
        }
      ],
      "change": {},
      "custom_fields": {}
    }
  ],
  "latitude": 110,
  "longitude": 130
}

Way 2: (Individual response (It could be indicator or change) as unique value (One story can have multiple responses))

Downside: The same data needs to be sent multiple times