Data Import Error Report - Clinical Experience and Performance - VDG

Owned by Steven Arnold
Aug 15, 2023
6 min read

Solution Overview

This guide describes how set up and use the Data Import Error Report as a companion report to your Educator Preparation Starter Kit. The Data Import Error Report is a simple Power Bi report meant to display errors from the Ed-Fi Alliance's Data Import. This allows for a read-only view of the errors that happen after an import w/o requiring non-technical people to log into the Data Import administration console.


The error report has several columns, all of which can be configured to show our hide based on your specific needs and preferences, these columns include:

  • Date - The date that the import took place
  • Source - The source file where the error occurred
  • Row - The row number in the source file where the bad data is located
  • Resource - The API endpoint where the data was sent
  • Status - The HTTP Status message from the import attempt
  • Message - The message returned from the import attempt
  • Data - The JSON payload that was sent to the ODS/API


We generally recommend that at least Date, Source, Row, Status and Message be included. Resource might be helpful if your source csv are not named in a way that makes it clear what data you are loading. The Data column will show the JSON representation of the data that was sent to the ODS/API and while that might be useful in some cases, it makes each entry much larger, which means more scrolling, so hiding this column is prefered.

Limiting the amount of data in the report

As part of it's normal operation, Data Import sends a lot of data to it's database. If we were show all data it would quickly become overwhelming to parse through and find useful information. To combat this issue we took a couple of steps:

  • We do not retrieve messages from successful imports
  • We limit the data retrieved to the past 90 days.

All of the above can be adjusted to fit your needs by adjusting the queries within the Power Bi report.


Install and Test the Data Import Error Log Report

In this step, you'll download and run the Program Diversity and Persistence Dashboards. 

Prerequisites

  • At this point, you should have an operational Ed-Fi ODS / API with data loaded through Data Import.
  • You will also need a machine that has access to the ODS database and has Power BI Desktop installed.

Download and Configure the Data Import Error Log Dashboard

The latest version of the Data Import Error Log Power BI Dashboard file is located in a GitHub repository.

Use the machine that has Power BI and create the following folder structure: C:\Ed-Fi\QuickStarts\Error_Log\.

Save this file onto that location by clicking this link: Data Import Error Log .

Once the download completes, navigate to the folder where you downloaded the file and double-click it. This should open Power BI and load the Data Import Error Log as depicted below:

Connect your Power BI Dashboard to your Ed-Fi Data Import

The data that is pre-populated is a set of data that mimics real world data in a educator preparation program.

To connect to your Ed-Fi Data Import Database, use the top menu and navigate to Home and then Transform Data. Click the dropdown arrow and select "Data source settings".

When the modal opens click on the "Change Source..." button.

A second modal will popup with inputs to configure your SQL Server server connection. Use the IP address or the hostname of the server that contains the database. Then input the name of your Ed-Fi Data Import database. Usually the name of your database will be EdFi_DataImport.

Depending on your organization's security configuration, you may use integrated or SQL server security. 

Once you click ‘OK’, you will see SQL statements. Click ‘OK or ‘Run’

Additionally, it might show you the following screen:

This is where you can switch from Windows Integrated Security to MsSQL security if you desire.

Proceed to click on connect and accept the defaults.

You will see a screen that will load data from your Data Import database like the one below. Note that it might take a minute or two to finish loading. This depends on the amount of data and students you have in the database.

Once it loads, the report will show the Data Import log data.

Step 5. Publish the Program Diversity and Persistence Dashboard and Plan Your Rollout

The final step is to publish the Program Diversity and Persistence Dashboards to Power BI Online. This release is targeted to your internal test users, allowing them to log in, view, use, and test the data visualizations. At the end of this step, you will be able to plan your rollout and deployment.

Basic Power BI Online Setup 

We will guide you through some of the basics around Power BI Online.

For this dashboard to work you will need:

  • A subscription to Power BI Online
  • A few users created in Office 365 
  • A Workspace to be able to publish the dashboard

Creating Users in Power BI Online

If you already have users created in your Power BI account you can skip to the next section.

To create users follow these simple steps:

  • Login to Power BI Online
  • Click on the  menu on the top left of the screen and select  as depicted below.

  • This will open the 365 admin center. From here click on  button indicated on the image below.

  • This will open a modal window that will allow you to add a user. Follow the instruction on that modal to create a user.
  • It is important to note that the user's email address in Power BI should be the same as the ones in you SIS and in the Ed-Fi ODS. This is how we match the user that is logged in with a role in the Ed-Fi ODS.
  • You can repeat this process to add all the users that will be accessing the Program Diversity and Persistence Dashboard on Power BI

Creating a Workspace to Hold the Dashboard

Back on the main screen of Power BI Online, click on the menu icon and then on the "Workspaces" subsection, which expands a side modal. At the bottom, click on the "Create a workspace" button to toggle a slide-out form on the right. This slide out form allows you to create and name the workspace. For this demo we named it "Ed-Fi EPP Starter Kits".

At this point you are done with this step.

Publishing the Dashboard

Open Power BI Desktop and Sign in with the account that has access to publish to Power BI online.

Proceed to locate and click on the "Home → Publish" menu entry as depicted below.

This will open a modal popup window. Select the workspace you created in the section above and click on the "Select" button.

In a few seconds you will see the modal popup indicating that it is publishing. When complete you will see a "Success!" message.

To confirm, we recommend that you login to Power BI Online and ensure you can see the Dashboard.

To do this, open your favorite browser and navigate to https://powerbi.microsoft.com/ and login.

Click on your workspace and you should see the "Data Import Error Log" in the list.