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.
On this page:
Exporting data from the E2C Hub
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.
Getting started with API
Each dataset on Data & Insights has a corresponding API document. This section contains details on utilizing the API for a particular dataset. The API endpoint and documentation can be found from the dataset's About page.
Locating API docs for a dataset
Access the API documentation for each a given dataset on its About page. Click the Actions button on the top right and select "API" from the dropdown.
API documentation and developer portal
With the Export button you can:
- Download the data
- Retrieve an API endpoint in CSV or JSON format
- Access API documentation with the link below the endpoint window. Example: Next Generation MCAS Achievement Results API
- Access the developer portal
What is SODA API?
The Socrata Open Data API (SODA API) allows you to directly connect to datasets hosted on the E2C Hub. It provides a standardized way to access, query, filter, and retrieve data from various public datasets using simple HTTP requests.
The endpoint of a SODA API is simply a unique URL that represents an object or collection of objects. Every Socrata dataset, and every individual data record, has its own endpoint. The endpoint is what you’ll point your HTTP client at to interact with data resources.
All resources are accessed through a common base path of /resource/ along with their dataset identifier. For example, i9w6-niyt is the identifier for the Next Generation MCAS Achievement Results. This identifier can then be appended to the /resource/ endpoint to construct the API endpoint.
For more information on API, please refer to this Tyler support article.
Benefits of connecting to our data
- Streamline data retrieval and automation: The SODA API simplifies data retrieval by offering a standardized way to query and filter datasets using simple HTTP requests and the SoQL query language. This eliminates the need for complex data scraping or manual downloads.
- Integrate with existing systems: You can easily integrate the SODA API into your existing applications, workflows, and data analysis tools, enabling seamless incorporation of open data into your projects.
- Develop applications: Build innovative applications and tools that leverage real-time open data, enhancing functionality and providing valuable services to users.
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.
- In Excel, under the Data tab, click the From Text/CSV icon.
- Select the file and click Open.
- A window will appear. Click the Transform Data button to open Power Query.
- 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.
- 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.
- Select the Copy button to copy the OData V4 Endpoint.
- In Excel, under the Data tab, click the bottom of the Get Data icon and select From Other Sources > From OData Feed.
- Paste the OData Endpoint and click OK. If you get an error, try using the OData V2 Endpoint instead.
- Click Load to import the full dataset into an Excel worksheet, or click Transform Data to open Power Query.
- 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:
- In the E2C Hub, after transforming a dataset, click the Export button.
- Select the API Endpoint tab, then select CSV from the Data Format dropdown menu.
- Click the Copy to Clipboard button to copy the CSV API endpoint.
- In Excel, under the Data tab, click the From Web icon.
- Paste the CSV API endpoint and click OK.
- Click Load to import the full dataset into an Excel worksheet, or click Transform Data to open Power Query.
- 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:
- 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.
- Click the Copy button to copy the OData V4 Endpoint.
- In Power BI, under the Home tab, click the bottom of the Get Data icon and select OData Feed.
- Paste the OData Endpoint and click OK. If you get an error, try using the OData V2 Endpoint instead.
- Click Load to import the full dataset as is, or click Transform Data to open Power Query.
- When you are done, click the Close & Apply button in the top left corner.
To connect to a transformed dataset:
- In the E2C Hub, after transforming a dataset, click the Export button.
- Select the API Endpoint tab, then select CSV from the Data Format dropdown menu.
- Click the Copy to Clipboard button to copy the CSV API endpoint.
- In Power BI, under the Home tab, click the Web icon.
- Paste the CSV API endpoint and click OK.
- Click Load to import the transformed dataset as is, or click Transform Data to open Power Query.
- 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:
- 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.
- Select the API Endpoint tab, then click the Copy to Clipboard button to copy the JSON API endpoint.
- In R, use the RSocrata package to load the data.
- Install: install.packages("RSocrata")
- Load: library(RSocrata)
- Use: read.socrata(“endpointURL”), e.g., read.socrata("https://educationtocareer.data.mass.gov/resource/787a-3wen.json")
To connect to a transformed dataset:
- In the E2C Hub, after transforming a dataset, click the Export button.
- Select the API Endpoint tab, then click the Copy to Clipboard button to copy the JSON API endpoint.
- In R, use the RSocrata package to load the data.
- Install: install.packages("RSocrata")
- Load: library(RSocrata)
- Manually alter the JSON API endpoint:
- Remove the text “query=”, after the question mark.
- Add an equals sign (=) after SELECT.
- For example, this URL https://educationtocareer.data.mass.gov/resource/787a-3wen.jsonquery=$SELECT becomeshttps://educationtocareer.data.mass.gov/resource/787a-3wen.json?$SELECT=
- Use: read.socrata(“modifiedEndpointURL”)
- read.socrata("https://educationtocareer.data.mass.gov/resource/787a-3wen.json$SELECT=%0A%20%20%60sy%60%2C%0A%20%20%60dist_code%60%2C%0A%20%20%60dist_name%60%2C%0A%20%20%60org_code%60%2C%0A%20%20%60org_name%60%2C%0A%20%20%60org_type%60%2C%0A%20%20%60subj_cat%60%2C%0A%20%20%60subj%60%2C%0A%20%20%60stugrp%60%2C%0A%20%20%60tests_taken%60%2C%0A%20%20%60score_1%60%2C%0A%20%20%60score_2%60%2C%0A%20%20%60score_3%60%2C%0A%20%20%60score_4%60%2C%0A%20%20%60score_5%60%2C%0A%20%20%60pct_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:
- 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.
- To the right of the OData endpoint, click the dropdown and select OData V2.
- Click the Copy button to copy the OData V2 Endpoint.
- In Tableau, click the top left icon to go to the start page.
- In the Connect pane, find the To a Server group and click More..., then click OData.
- 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:
- In Google Sheets, connect to your data via API.
- In Looker Studio, click the Create button and select Data Source.
- Select the Google Sheets connector from the list. You may have to authorize the connector.
- 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.
Developer Portal
More advanced users can visit Tyler's Socrata developer portal to learn more about using open data with extensive documentation, SDKs (software development kits), and support for most popular programming languages.
Programming languages
Support is available for most popular programming languages and platforms:
Tyler support resources
For further research and exploration, please consult the following references and external links.