Overview
This documentation covers the steps required for loading the Dashboard v1.3.1 for implementations using the Ed-Fi ODS v2.0 (Beta). 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 Dashboard Data
Step 1: Install and Configure Required Software
Install and configure the Ed-Fi ODS v2.0 (Beta)
Follow the instructions in the Ed-Fi ODS / API v2.0 (Beta) - Getting Started Guide.
Download and Install the Dashboard Compatibility Views from the Ed-Fi Exchange
Download the Dashboard Compatibility Views for Ed-Fi ODS v2.0 from the Ed-Fi Exchange, and follow the instructions for applying them to your Ed-Fi ODS v2.0 (Beta) instance.
Step 2: Configure SQL Server Integration Services
Configure the SQL Server Integration Services Catalog
- Ensure that "Integration Services" are installed for SQL Server. If not, then re-run SQL Server 2012 Setup, and selected "Integration Services
- Start SQL Server Management Studio
- If the SQL Server Agent is not started, right click and select “Start”
- Right click on the “Integration Services Catalog” and select “Create Catalog”
- The Create Catalog prompt will appear. Select “Enable automatic execution of Integration Services stored procedures at SQL Server startup” and enter a password for the encryption key, then select OK.
- SSISDB will be created for the new project deployments.
Install Additional Software Components
Install the following software components (used during SSIS ETL):
- 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), http://ssismhash.codeplex.com/releases/view/84748
Build and Install the EdFiException Component
The EdFiException component is a compiled assembly that is part of the Dashboards ETL Reject Strategy. The instructions below describe how to build and install this component manually.
- Use SQL Server Data Tools (Visual Studio 2010 Shell) to open the solution c:\Ed-Fi-Core\Etl\EdFi.Etl.EdFi\EdFiException2012\EdFiException.sln
- Once the solution is open in Visual Studio, the “Solution Explorer” window will display the following:
- Right click EdFiException (highlighted above) and choose “Build”. This will create the EdFiException.dll file in the following location: C:\Ed-Fi-Core\Etl\EdFi.Etl.EdFi\EdFiException2012\EdFiException\bin\Debug
- Copy EdFiException.dll to the following location: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents
- Install EdFiException.dll using Powershell by executing the following commands in a PowerShell session:
Import-module C:\Ed-Fi-Core\logistics\scripts\modules\utility\gac-utility.psm1 Install-Gac “C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\EdFIException.dll”
- At this point the assembly is now installed, providing a data flow component, EdFiException, in the Visual Studio SSIS Toolbox.
Step 3: Build SSIS Projects and Deploy to the SSIS Catalog
Build the SSIS Projects
To use the SSIS Catalog, a project file (.ispac) must be created for the EdFi.Etl.Dashboard and EdFi.Etl.DW projects. This step 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.Dashboard\ EdFi.Etl.Dashboard.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.Dashboard\bin\Development folder is the .ispac file for the XML Loaders.
- Repeat these steps for the C:\Ed-Fi-Core\Etl\EdFi.Etl.DW\ EdFi.Etl.DW.sln project.
Deploy the .ispac files to the SSIS Catalog
The two .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. These scripts will also create environment variables used to store the necessary connections strings to manage the project. The packages will reference the new environment variables and will execute with those connection strings. The PowerShell script has variable fields that must be populated for the deployment.
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:
| 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). |
Variable | Description |
- Save and execute the PowerShell script.
- After DeployDashboardPackages.ps1 has completed executing, 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 -> Dashboard -> Projects -> EdFi.Etl.Dashboard -> Packages. When you expand the Packages your view should look similar to the image below.
- Navigate to SSISDB->Dashboard ->Environments and expand to see the Dashboard environment.
- Double-click the Dashboard environment and select Variables in the “Select a page” pane to the left. The data in the Value column will be used as the connections strings. It should look similar to the image below.
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:
| 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). |
Variable | Description |
- 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 Dashboard packages" above.
Step 4: Setup SQL Server Agent Job
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:
Parameter | Value |
|---|---|
SQLAgentJobName | The name of the SQL Agent Job, (EdFiDashboard) |
Owner | The SQL User that will be the owner of the job. For this purpose, we will use the edfiAdmin SQL Login that was created earlier |
EdFiDatabaseName | The name of the Ed-Fi ODS Database that will be created and loaded, (EdFi) |
RootDirectory | The root directory of the extracted repository, (C:\) |
DatabaseServer | The SQL Server Instance the Ed-Fi ODS Database will be created on, ((local)) |
DashboardDatabase | The name of the Dashboard Data Store database, (EdFi_Dashboard) |
DashboardDWDatabase | The name of the Dashboard Data Warehouse database, (EdFi_DashboardDW) |
ApplicationDatabase | The name of the Application Database, (EdFi_Application) |
- 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 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 Dashboard databases.
- To execute the job, navigate to the SQL Server Agent in SQL Server Management Studio and drill down into Jobs and right-click on the job named EdFiDashboard and select Start Job at Step...
- The Start job screen will now open. Select Step ID 6
- Click Start. This will start the process to load and create the databases to power the Ed-Fi Dashboards.
- Upon successful execution of the SQL Agent job, data will have been loaded into the Dashboard databases.
On This Page: