Overview

This documentation covers the steps required for loading the Dashboard v1.3.1 for implementations using the Ed-Fi ODS v1.2. These instructions assume that you have already followed the Dashboards v1.3 - Getting Started Guide.

The steps can be summarized as follows:

• Step 1: Install and Configure Required Software
• Step 2: Configure SQL Server Integration Services
• Step 3: Build SSIS Projects and Deploy to the SSIS Catalog
• Step 4: Setup the SQL Server Agent Job
• Step 5: Load Data

Step 1: Install and Configure Required Software

• Verify that the following Windows components are installed and configured:

  • .NET Framework 4.0 (full install, included with Visual Studio 2010, http://www.microsoft.com/en-us/download/details.aspx?id=17851) or 4.5 (included with Windows 8)
  • PowerShell 3.0 (included with the Windows Management Framework 3.0, http://www.microsoft.com/en-us/download/details.aspx?id=34595)

• Install the following software components:
  • Visual Studio 2010, 2012 or 2013 (Professional or Premium Edition)
  • Microsoft SQL Server 2012 – with Service Pack 2 or higher (Developer Edition, Standard Edition, or Enterprise Edition) * Express Edition is NOT supported.
    • When prompted during setup, select the following features:
  • • • Database Engine Services (replication/text search/data quality services are NOT required)
      • SQL Server Data Tools
      • Integration Services 
      • Management Tools – Complete
      • Use default instance (MSSQLSERVER)
      • Select “SQL Server” and “Windows Authentication Mode”
      • In the “Specify SQL Server administrator” click on “Add Current User”
  • 2007 Office System Driver: Data Connectivity Components, http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734

• • SSIS Multiple Hash v1.4.1 (2012), provided as a .zip in the Downloads Panel

• Create SQL login “edfiAdmin”. Open a new query window and run the following script:

  USE [master]
  GO
  CREATE LOGIN [edfiAdmin] WITH PASSWORD=N'edfiAdmin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  GO
  ALTER LOGIN [edfiAdmin] DISABLE
  GO
  ALTER SERVER ROLE [sysadmin] ADD MEMBER [edfiAdmin]
  GO

To use the SSIS Catalog, a project file (.ispac) must be created for the EdFi.Etl.EdFi, EdFi.Etl.Dashboard andEdFi.Etl.DW projects. This stores the ETL Packages, connection strings and project configurations for the SSIS Catalog.

• Build the C:\Ed-Fi-Core\Etl\EdFi.Etl.EdFi\EdFi.Etl.EdFi.sln project
  • Use SQL Server Data Tools (Visual Studio 2010 Shell) to open the solution C:\Ed-Fi-Core\Etl\EdFi.Etl.EdFi\EdFi.Etl.EdFi.sln.
  • When the project completely opens, go to the Solution Explorer, right click on the project, and select Build.
    

• • After the build completes, you should now have the created .ispac file. This will be in the project folder for each project. For example, in the C:\Ed-Fi-Core\Etl\EdFi.Etl.EdFi\bin\Development folder is the .ispac file for the XML Loaders. 

• Repeat these steps for the C:\Ed-Fi-Core\Etl\EdFi.Etl.Dashboard\ EdFi.Etl.Dashboard.sln and C:\Ed-Fi-Core\Etl\EdFi.Etl.DW\ EdFi.Etl.DW.sln projects.

Deploy the .ispac files to the SSIS Catalog

The three .ispac files created in the previous step must be deployed to the SSIS Catalog that was previously created. To support the ease of deploying to the SSIS Catalog, PowerShell scripts have been provided that setup the SSIS Catalog with the new projects and deploy the SSIS packages. This script will also create an environment variable used to store the necessary connections strings to manage the project. The packages will reference the new environment variable and will execute with those connection strings. The PowerShell script has variable fields that must be populated for the deployment.

•  Deploy the EdFi packages
  • Navigate to C:\Ed-Fi-Samples\Tools\SSIS Catalog Execution folder
  • Open DeployEdFiPackages.ps1 (using Notepad or Windows Powershell ISE)

  • In the File Variables section, populate or modify the variables according to the following table: 
    

    ProjectFilePath	This is the path to the .ispac file that was created previously. The end path has been provided, and only the root path is needed. Replace the following ‘**************\’ with the root (i.e., c:).
    ISServerName	This is the name of the server where the SSIS Catalog was created. Use (local) to designate the local server.
    Server	This is the name of the server where the Ed-Fi ODS is stored. This may be different if the ODS is on a 2008 instance and the SSIS Catalog is on a 2012 instance. Use (local) to designate the local server.
    DBUserName	This is the name of the SQL Login that was created previously (edfiAdmin).
    DBUserPassword	This is the password that was used for the above SQL Login (edfiAdmin).
    XMLDataFileDirectory	This is the directory where the XML files to be processed are stored. The user that is being used must have access to this directory. To start, enter in the location of the sample XML files provided in the Ed-Fi-Samples repository, (C:\Ed-Fi-Standard\v1.2\Samples).
    Variable	Description

  • Save and execute the PowerShell script

  • After the DeployEdFiPackages.ps1 has completed the execution, the project will be deployed in the SSIS Catalog. To verify this, do the following:

    • Navigate to the Integration Services Catalogs in SQL Server Management Studio

    • Expand the Integration Services Catalogs to drill down to SSISDB > EdFi >Projects > EdFi.Etl.EdFi > Packages. When the packages are expanded, the view should look similar to the image below.

• • • Navigate to SSISDB > EdFi > Environments and expand to see the EdFi environment.

    • Double-click the EdFi environment and select Variables in the Select a page pane to the left. The data in the Value column will be used as the connection strings. It should look similar to the image below:

•  The Databases that will be used in this guide and within the scripts are:

   

  Parameter/Variable Name	Database Name
  EdFi	EdFi
  Dashboard	EdFi_Dashboard
  DashboardDW	EdFi_DashboardDW
  Application	EdFi_Application

• Deploy the Dashboard packages
  • Navigate to the C:\Ed-Fi-Samples\Tools\SSIS Catalog Execution folder
  • Open DeployDashboardPackages.ps1 (using Notepad or Windows Powershell ISE)
  • In the File Variables section, populate the variables according to the following table: 

• • Save and execute the PowerShell script.
  • After DeployDashboardPackages.ps1 has completed executing, the project will be deployed in the SSIS Catalog. Verify this using the same approach as described in "Deploy the EdFi packages" above.
    
    
• Deploy the DashboardDW Packages
  • Navigate to the C:\Ed-Fi-Samples\Tools\SSIS Catalog Execution folder
  • Open DeployDWPackages.ps1 (using Notepad or Windows Powershell ISE)
  • In the File Variables section, populate the variables according to the following table:  

• • Save and execute the PowerShell script
  • After DeployDWPackages.ps1 has completed executing, the project will be deployed in the SSIS Catalog. Verify this using the same approach as described in "Deploy the EdFi packages" above.

Once all of the projects have been deployed to the SSIS Catalog, they will be ready to execute.

Step 4: Setup SQL Server Agent Job

For the execution of the ETL packages, which include the Interchange packages as well as the Types and Descriptors, the user account of the SQL Server Agent will need access to the directory where the Ed-Fi Solution has been extracted.  It will also need permissions to create new databases on the SQL Server Instance where the Ed-Fi ODS will be located.

• Give folder permissions to the SQL Server Agent account.
  • Navigate to the location of the sample Interchange XML files, at C:\Ed-Fi-Standard. Right click Properties > Security > Edit > Add and select the user account. 
    

• • Give the user account the SQL Agent is running under Full control permissions.

• Create the SQL Server Agent Job
  • In SQL Server Management Studio, open the EdFiSQLAgent.sql file in the C:\Ed-Fi-Samples\Tools\SSIS Catalog Execution directory.
  • To make the script customizable, parameters have been created and need to be populated. Once open, from the Menu bar, navigate to Query > Specify Values for Template Parameters… (or click CTRL+SHIFT+M)
  • Enter in the values for the parameters:

• • Once the parameter values have been entered, click OK. The values will now be inserted in the SQL Agent script. Below is an example of the parameters filled in. 

• • After the SQLAgent script has been updated, execute the script to create a new SQL Server Agent Job.

Step 5: Load Data

Sample Data

The Ed-Fi Dashboard application is provided with a pre-defined set of data, which includes default enumeration values, Ed-Fi Descriptors, and sample interchange data:

• Default enumeration values
  • Enumeration values must be loaded into the Ed-Fi ODS before any other data
  • Enumeration values are sometimes referred to as “Ed-Fi Types” due to the related ODS table-naming convention
  • These values that are populated for the Ed-Fi ODS can be found in C:\Ed-Fi-Core\Database\Data\EdFi\ Import Ed-Fi Core Types.Data.xls
• Ed-Fi Descriptor values
  • Ed-Fi Descriptors must be loaded after the enumeration values
  • The values that are populated for the Ed-Fi ODS can be found in C:\Ed-Fi-Core\Database\Data\EdFi\Import Ed-Fi Descriptors.Data.xls
• Sample interchange data
  • Sample interchange data includes realistic but fictitious data that can be used to load and exercise the Operational Data Store.
  • The sample data directory is located at: C:\Ed-Fi-Standard\v1.2\Samples

Load ODS and Dashboard Databases

Once the SQL Server Agent script has been executed in (Step 4 above), the SQL Server Agent Job will be available to use to load the Ed-Fi ODS and Ed-Fi Dashboard databases.

• To execute the job, navigate to the SQL Server Agent in SQL Server Management Studio and drill down into Jobs.
• Right-click on the job named EdFiDashboard and select Start Job at Step...

• The Start job screen will now open. Select Step ID 1
  
  

• Click Start. This will start the process to load and create the databases to use the Ed-Fi Dashboards.

• Upon successful execution of the SQL Agent job, data will have been loaded from the XML files, into the Operational Data Store and then into the Dashboard databases.