Connecting to data tools

Work with datasets in your preferred platform for analysis and visualization by downloading and importing data or connecting via API or OData. Tips and instructions for the most popular tools follow. Need additional support? Email E2CHub@mass.gov

Exporting data from the E2C Hub 

Select a dataset in the E2C Hub that you’d like to export (e.g., Advanced Placement Performance). 
To export all data in the dataset:
  • Click the Export button in the top right corner.
  • Click Download to export as a CSV, or choose a different format in the dropdown menu. 
To transform the data before export:
  • Select the Actions button in the top right corner, and then select Query Data.
  • To filter data, select the funnel icon in the Visual Query Editor below the data table. Select a column to filter, choose an operator using the pill dropdown menu and a value for the column. Add additional filters with "and/or" logic. Select the Apply button at the bottom to update the data table. 
  • To group or aggregate data, select the middle icon from the Visual Query Editor. For aggregations, choose a calculation using the pill dropdown menu to the right. Click the Apply button at the bottom. 
  • To manage columns, select the bottom icon from the Visual Query Editor. Rearrange columns by dragging the icons in the Order column. Choose to include or exclude columns by checking the Include column on or off. Sort data using the dropdown menus in the Sort column. Click the Apply button at the bottom to update the data table. 
  • When you are done reshaping the data, select the Export button in the top right corner. Select Download to export as a CSV, or choose a different format in the dropdown menu.

Considerations for connecting via API and OData

Connecting to an E2C Hub dataset via API can make it easy to keep the data in your visualizations up to date. However, there are some things to keep in mind: 
  • You may need to refresh the API connection to load new data into your visualization. That is, just because there is new data in the E2C Hub doesn’t mean your visualization will "see it" automatically, unless you have configured your application to run the API call at intervals.
  • OData feeds are the preferred way to connect to data in the E2C Hub, because they can import an unlimited number of rows by default. JSON and CSV API endpoints only support 1,000 rows by default. If your dataset contains over 1,000 rows (after filtering), you will need to adjust the limit of your API export. Set the limit high so you won’t have to change it later. 
    • If you are adding the limit parameter to a transformed dataset’s API endpoint, you may need to use this adjusted syntax: %20limit%20number (i.e. %20limit%205000).
  • Large datasets will take longer to connect than small datasets. You can make a large dataset smaller by applying filters in the E2C Hub before connecting via API. 
For more information visit Tyler Data & Insights' support page: Getting started with the SODA Consumer API.

Instructions by platform

Excel & Power Query

Open data from the E2C Hub in Excel just like you would open any other data file. If there are too many rows or you wish to do some analysis, you can also load the data using Power Query. 
  1. In Excel, under the Data tab, click the From Text/CSV icon. 
  2. Select the file and click Open. 
  3. A window will appear. Click the Transform Data button to open Power Query. 
  4. Use Power Query to transform the data as desired. For example, change the data type, rename columns, merge columns, split columns, transpose columns, format text, or create a custom calculated column. 
  5. When you are done, click the bottom of the Close & Load button and select Close & Load To. You can load the data to a Table (if within Excel’s row limit), as a PivotTable Report, as a PivotChart, or just create a connection to the data. 
To connect to the full dataset: 
Select a dataset in the E2C Hub that you’d like to export (e.g., Advanced Placement Performance) and click the Actions button in the top right corner, then select Access via OData. 
  1. Select the Copy button to copy the OData V4 Endpoint. 
  2. In Excel, under the Data tab, click the bottom of the Get Data icon and select From Other Sources > From OData Feed. 
  3. Paste the OData Endpoint and click OK. If you get an error, try using the OData V2 Endpoint instead. 
  4. Click Load to import the full dataset into an Excel worksheet, or click Transform Data to open Power Query. 
  5. When you are done, click the bottom of the Close & Load button and select Close & Load To. You can load the data to a Table (if within Excel’s row limit), as a PivotTable Report, as a PivotChart, or just create a connection to the data. 
To connect to a transformed dataset: 
  1. In the E2C Hub, after transforming a dataset, click the Export button. 
  2. Select the API Endpoint tab, then select CSV from the Data Format dropdown menu. 
  3. Click the Copy to Clipboard button to copy the CSV API endpoint. 
  4. In Excel, under the Data tab, click the From Web icon. 
  5. Paste the CSV API endpoint and click OK. 
  6. Click Load to import the full dataset into an Excel worksheet, or click Transform Data to open Power Query. 
  7. When you are done, click the bottom of the Close & Load button and select Close & Load To. You can load the data to a Table (if within Excel’s row limit), as a PivotTable Report, as a PivotChart, or just create a connection to the data. 

Power BI

Import and connect data from the E2C Hub to Power BI just like you would import and connect any other data file. You can also connect to it via API. 
To connect to the full dataset: 
  1. Select a dataset in the E2C Hub to export (e.g., Advanced Placement Performance) and click the Actions button in the top right corner, then select Access via OData. 
  2. Click the Copy button to copy the OData V4 Endpoint. 
  3. In Power BI, under the Home tab, click the bottom of the Get Data icon and select OData Feed. 
  4. Paste the OData Endpoint and click OK. If you get an error, try using the OData V2 Endpoint instead. 
  5. Click Load to import the full dataset as is, or click Transform Data to open Power Query. 
  6. When you are done, click the Close & Apply button in the top left corner. 
To connect to a transformed dataset: 
  1. In the E2C Hub, after transforming a dataset, click the Export button. 
  2. Select the API Endpoint tab, then select CSV from the Data Format dropdown menu. 
  3. Click the Copy to Clipboard button to copy the CSV API endpoint. 
  4. In Power BI, under the Home tab, click the Web icon. 
  5. Paste the CSV API endpoint and click OK. 
  6. Click Load to import the transformed dataset as is, or click Transform Data to open Power Query. 
  7. When you are done, click the Close & Apply button in the top left corner. 

R

You can import data from the E2C Hub in R just like you would import any other data file. You can also connect to it via API using the RSocrata package. Remember to adjust your row limit if needed. 
To connect to the full dataset: 
  1. Select a dataset in the E2C Hub that you’d like to export (e.g., Advanced Placement Performance) and click the Export button in the top right corner. 
  2. Select the API Endpoint tab, then click the Copy to Clipboard button to copy the JSON API endpoint. 
  3. In R, use the RSocrata package to load the data. 
    1. Install: install.packages("RSocrata") 
    2. Load: library(RSocrata) 
    3. Use: read.socrata(“endpointURL”), e.g., read.socrata("https://educationtocareer.data.mass
      .gov/resource/787a-3wen.json")
To connect to a transformed dataset: 
  1. In the E2C Hub, after transforming a dataset, click the Export button. 
  2. Select the API Endpoint tab, then click the Copy to Clipboard button to copy the JSON API endpoint. 
  3. In R, use the RSocrata package to load the data. 
    1. Install: install.packages("RSocrata") 
    2. Load: library(RSocrata) 
    3. Manually alter the JSON API endpoint: 
      1. Remove the text “query=”, after the question mark. 
      2. Add an equals sign (=) after SELECT. 
      3. For example, this URL https://educationtocareer.data.mass.gov/resource/787a-3wen.jsonquery=$SELECT becomes 
        https://educationtocareer.data.mass.gov/resource/787a-3wen.json?$SELECT=
    4. Use: read.socrata(“modifiedEndpointURL”) 
      1. read.socrata("https://educationtocareer.data.mass.gov/resource/787a-3wen.json$SELECT=%0A%20%20%60sy%60%2C%0A%20%20%60
        dist_code%60%2C%0A%20%20%60dist_name%60%2C%0A%20%20%60
        org_code%60%2C%0A%20%20%60org_name%60%2C%0A%20%20%60
        org_type%60%2C%0A%20%20%60subj_cat%60%2C%0A%20%20%60
        subj%60%2C%0A%20%20%60stugrp%60%2C%0A%20%20%60
        tests_taken%60%2C%0A%20%20%60score_1%60%2C%0A%20%20%60
        score_2%60%2C%0A%20%20%60score_3%60%2C%0A%20%20%60
        score_4%60%2C%0A%20%20%60score_5%60%2C%0A%20%20%60
        pct_1_2%60%2C%0A%20%20%60pct_3_5%60%0AWHERE%20caseless_
        one_of(%60sy%60%2C%20%222023%22)") 

Tableau

Import and connect data from the E2C Hub in Tableau just like you would import and connect any other data file. You can also connect to a full dataset using API. You cannot currently connect to a transformed dataset in Tableau. 
To connect to the full dataset: 
  1. Select a dataset in the E2C Hub that you’d like to export (e.g., Advanced Placement Performance) and click the Actions button in the top right corner, then select Access via OData. 
  2. To the right of the OData endpoint, click the dropdown and select OData V2. 
  3. Click the Copy button to copy the OData V2 Endpoint. 
  4. In Tableau, click the top left icon to go to the start page. 
  5. In the Connect pane, find the To a Server group and click More..., then click OData. 
  6. Paste the OData V2 Endpoint and click Sign In. 

Google Sheets

Import and open data from the E2C Hub in Google Sheets just like you would import and open any other data file. For API instructions, please contact the E2C Hub at E2CHub@mass.gov.

Looker Studio (Google Data Studio)

Import and connect data from the E2C Hub in Looker Studio just like you would import and connect any other data file. You can also connect to it via API by way of Google Sheets. 
To connect to the full or transformed dataset: 
  1. In Google Sheets, connect to your data via API using the instructions in the previous section. 
  2. In Looker Studio, click the Create button and select Data Source. 
  3. Select the Google Sheets connector from the list. You may have to authorize the connector. 
  4. Select your spreadsheet and worksheet from the list, then click Connect. 

Flourish

You can import data from the E2C Hub in Flourish just like you would import any other data file. Flourish does not support connecting to data via API at this time. 
For more information visit Tyler Data & Insights' support page: Getting started with the SODA Consumer API.