Overview

In this exercise, we're going to show how to add assessment information to the Student and School pages of the Power BI reports.  In particular, we're going to look at PSAT data in the Grand Bend dataset.  There are two separate steps we need to complete in order to accomplish this:

  1. Modify the Tabular Model to include Assessment data and measurements.
  2. Modify the Power BI file to include visualizations for the newly created measurements.

Modfying the Tabular Model

Requirements

Instructions

  1. Open the Tabular Model solution in Visual Studio.
    1. You will be prompted to select which environment type you want to work on - an integrated workspace or remote workspace.  An integrated workspace uses your local machine's analysis engine to validate all of the changes to the model.  A remote workspace creates a small model on an Analysis Services server related to your instance, and will use that remote compute power to validate all of the changes.  For ease of testing, use the integrated workspace.



  2. Open the Model.bim file in Solution Explorer.
  3. By default, the Ed-Fi Exchange solution is pointing to a test ODS and Analysis Services.  The first thing we need to do is make sure our solution is pointing at our production resources.
    1. In Solution Explorer, right-click on the project name and select Properties.  Change the Server string to your production Analysis Services server.




    2. In the Tabular Model Explorer pane, open the Data Sources folder.  Right-click on the Enterprise ODS data source and change the connection information to your production ODS.




  4. Now that our connection strings are updated, we'll be able to process and deploy the model whenever we want.
  5. Let's process the model in our development workspace.  Right-click on the model name in Tabular Model Explorer, choose Process, and then Process All.  This may take a while depending on the size of your ODS.



  6. Once the model on your workspace has been processed, you'll start seeing data in Visual Studio.
  7. Now it's time to add the necessary tables for our Assessment feature.  Right-click on the data source and choose Import New Tables.
  8. Select the edfi.StudentAssessment, edfi.StudentAssessmentScoreResult, and edfi.AssessmentReportingMethodType tables.  This will pull the tables in and process them automatically.
  9. Let's remove unnecessary columns on these new tables.  Tabular Models run entirely in-memory, and while they are fairly optimized, we need to be frugal with our space.
    1. An example of unnecessary columns are the Id, LastModifiedDate, and CreateDate columns.

  10. On the edfi.StudentAssessmentScoreResult table, add a new column entitled "ReportingMethodCodeValue".  We're going to look up the CodeValue associated with the ReportingMethodType and append it to this table.

    = LOOKUPVALUE( AssessmentReportingMethodType[CodeValue],
			   AssessmentReportingMethodType[AssessmentReportingMethodTypeId],
			   StudentAssessmentScoreResult[AssessmentReportingMethodTypeId] )

  11. Now, on the edfi.StudentAssessment table, let's pull in the Score for each row of this table.  This will allow us to work with this table, rather than relying on a relationship between multiple tables.  This will increase our initial processing time, but improve overall query performance.

    = LOOKUPVALUE( StudentAssessmentScoreResult[Result],
			   StudentAssessmentScoreResult[StudentUSI], StudentAssessment[StudentUSI],
			   StudentAssessmentScoreResult[AssessmentTitle], StudentAssessment[AssessmentTitle],
			   StudentAssessmentScoreResult[AcademicSubjectDescriptorId], StudentAssessment[AcademicSubjectDescriptorId],
			   StudentAssessmentScoreResult[AssessedGradeLevelDescriptorId], StudentAssessment[AssessedGradeLevelDescriptorId],
			   StudentAssessmentScoreResult[Version], StudentAssessment[Version],
			   StudentAssessmentScoreResult[AdministrationDate], StudentAssessment[AdministrationDate],
			   StudentAssessmentScoreResult[ReportingMethodCodeValue], "Raw Score" )
  12. Now that we've groomed our edfi.StudentAssessment table to have the necessary information, we need to relate it to our key domain tables (Student, School, Staff, etc).  Let's create a relationship between the edfi.StudentAssessment and the edfi.Student table.
    1. Right-click on the Relationships folder in the Tabular Model Explorer window.  Create a new relationship between these two tables, with StudentUSI being the key column.



  13. We now have all of the assessment information added and related to our existing model.  Now let's move on to creating some aggregate measures.

  14. On the edfi.School table, select an empty cell in the measurement pane.  Let's first create a "Number of PSATs Taken" measure.

    Number of PSATs Taken := CALCULATE(DISTINCTCOUNT(StudentAssessment[StudentUSI]),
								   FILTER(StudentAssessment, CALCULATE( COUNTROWS(StudentSchoolAssociation)) > 0),
								   FILTER(StudentAssessment, StudentAssessment[AssessmentTitle] = "PSAT")
						 )
  15. Now that our measure is created, we want to save the model and re-deploy it to our Analysis Services server.
    1. Right-click on the project name in Solution Explorer and select Deploy.  This will deploy the column and measure metadata and re-process the model on our remote server.
  16. All set! We can now move on to the Power BI file.

Modifying the Power BI File

Requirements

Instructions

  1. Open the .PBIX file in Power BI Desktop.
  2. You will immediately be prompted to log in with an Office 365 / Azure Active Directory account.
    1. Log in with the summit@edfidev.onmicrosoft.com account.
  3. Notice that some new tables are now showing up in Power BI Desktop - these are all of the tables we added in the Tabular Model.



  4. Select the School tab at the bottom of Power BI Desktop.  Let's make room for a card visualization that shows the "Number of PSATs Taken" measurement that we created earlier.
  5. Select some whitespace on the canvas, ensuring that no visualizations are selected.  Click the Formatting icon.



  6. Change the Page Size drop-down to Custom.  Increase the height to 900 pixels.  Notice how the canvas is now taller.
  7. Add a card visualization to the page.



  8. Select the "Number of PSATs Taken" measurement from the fields panel, and drag it to the Fields for the visualization.



  9. You should now be seeing data on the card visual.  Try changing the School Name filter at the top of the page to test the results.
  10. Now, let's move on the Student page.  Let's add a list of all assessments a student has taken, and the score they received.
  11. Similar to the School page, let's increase the height of this report.
  12. Add a table visual to the page.
  13. Add the AdministrationDate, AssessmentTitle, and Score columns from the StudentAssessment table to the visual.



  14. Change the Student Name filter at the top of the page to start seeing results in the table.
    1. e.g. Adam Spears



  15. All done! You can format the visualizations as needed!
  • No labels