Versions Compared
Key
- This line was added.
- This line was removed.
- Formatting was changed.
Overview
This documentation covers the steps required for loading the Dashboard v1.3.1 for implementationsWarning |
---|
SQL Server Update Warning Recent versions of SQL Server contain a breaking bug in SSIS. Do not install any version of SQL Server newer than SQL Server 2012, Service Pack 2, Cumulative Update 6. Cumulative Update 7 introduced a bug in SSIS that was not fixed in Service Pack 3. Microsoft has acknowledged the error, but has not yet released a timeline for fix. |
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: Restore ODS Database
- Step 3: Configure SQL Server Integration Services
- Step 34: Build SSIS Projects and Deploy to the SSIS Catalog
- Step 45: Setup the SQL Server Agent Job
- Step 56: Load 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
- Follow the instructions in the Ed-Fi
- Dashboards v1.3 - Getting Started Guide.
- 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
- 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”
Image Removed
- 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.
Image Removed
- SSISDB will be created for the new project deployments.
Image Removed
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
Info | ||
---|---|---|
| ||
2007 Office System Driver: Data Connectivity Components is required for executing the SSIS packages that load data from Excel spreadsheets. Installation may not be necessary if Microsoft Office is already installed. |
- SSIS Multiple Hash v1.4.1 (2012), http://ssismhash.codeplex.com/releases/view/84748
Info | ||
---|---|---|
| ||
For convenience, this document uses SQL Server 2012 for both the Ed-Fi Operational Database and SQL Server Integration Services. You can also use SQL Server 2008 R2 for the database if you choose; however ETL support requires the Integration Services delivered as part of SQL Server 2012. |
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:
Image Removed
- 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:
Code Block | ||
---|---|---|
| ||
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.
Image Removed
Info | ||
---|---|---|
| ||
Note: You can also install The EdFiException handler through an installer, by building and executing the “EdFiExceptionInstaller” project. |
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 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.
Image Removed
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.
Image Removed
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. 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 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. For this walk through, use "." 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. For this walk through, use "." 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. For this walk through, use "." 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.
Image Removed
- 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.
Image Removed
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. For this walk through, use "." 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. For this walk through, use "." 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. For this walk through, use "." 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.
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-Samples. Right click Properties > Security > Edit > Add and select the user account.
Image Removed
- Give the user account Full control permissions.
Image Removed
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.
Image Removed
- 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 ~\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 ~\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-Samples\Sample Interchange XML (update for v2.0)
Load the ODS Database
TBD
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
Right-click on the job named EdFiDashboard and select Start Job at Step...
Image Removed
The Start job screen will now open. Select Step ID 6
Image Removed
Info | ||
---|---|---|
| ||
Note: Steps 1-5 are for loading a v1.2 ODS database using SSIS packages. |
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:
Note | ||||||||
---|---|---|---|---|---|---|---|---|
After applying the dashboard compatibility views, you will need to apply a bug fix. See ODS
|
Step 2: Restore ODS Database
Restore a backup of an Ed-Fi v2.0 ODS that is populated with sample data and has the dashboard compatibility views applied.
Download the sample database attached to this page (EdFi_ODS_Populated_Template.zip) and extract the contents to a folder on your local machines (C:\Ed-Fi-ODS-Implementation\Database\Data\EdFi)
Restore the downloaded database to replace EdFi_ODS_Populated_Template
Code Block | ||
---|---|---|
| ||
USE [master]
GO
RESTORE DATABASE [EdFi_Ods_Populated_Template]
FROM DISK = N'C:\Ed-Fi-ODS-Implementation\Database\Data\EdFi\EdFi_Ods_Populated_Template.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO |
Note |
---|
It is important to follow this step. The EdFi_ODS_Populated_Template DB required for powering dashboards is different to the database backup restored during the "initdev" process. |
Step 3: 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”
Image Added
- 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.
Image Added
- SSISDB will be created for the new project deployments.
Image Added
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
Info | ||
---|---|---|
| ||
2007 Office System Driver: Data Connectivity Components is required for executing the SSIS packages that load data from Excel spreadsheets. Installation may not be necessary if Microsoft Office is already installed. |
- SSIS Multiple Hash v1.4.1 (2012), provided as a .zip in the Downloads Panel
Info | ||
---|---|---|
| ||
For convenience, this document uses SQL Server 2012 for both the Ed-Fi Operational Database and SQL Server Integration Services. You can also use SQL Server 2008 R2 for the database if you choose; however ETL support requires the Integration Services delivered as part of SQL Server 2012. |
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.
- Download the EdFiComponent zip folder from the download section.
- Unzip the file and check if it has been marked as blocked. If it has been blocked, click Unblock and Apply.
Image Added
- Copy EdFiException.dll to the following location: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents
Install EdFiException.dll using Developer Command Prompt (Running as Administrator) found under Visual Studio Tools by executing the following commands:
Code Block | ||
---|---|---|
| ||
gacutil -i "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\EdFIException.dll" |
Image Added
At this point the assembly is now installed, providing a data flow component, EdFiException, in the Visual Studio SSIS Toolbox.
Image Added
Info | ||
---|---|---|
| ||
Note: You can also install The EdFiException handler through an installer, by building and executing the “EdFiExceptionInstaller” project if you are using Visual Studio 2010. For newer versions of Visual Studio, the solution is not compatible. |
Step 4: 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.Dashboard\ EdFi.Etl.Dashboard.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.
Image Added - 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.
Image Added
- 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:
Code Block language powershell #*********File Variables********************************************************************************************************* $ProjectFilePath = "C:\Ed-Fi-Core\Etl\EdFi.Etl.Dashboard\bin\Development\EdFi.Etl.ispac" #The path to the ispac file from the SSIS project. Must build the project solution first. $ProjectName = "EdFi.Etl.Dashboard" #Project name from the SSIS Project $FolderName = "Dashboard" #The folder name in the SSIS Catalog $EnvironmentName = "Dashboard" #The name of the environment the packages will use $ISServerName = "(local)" #The name of the server where the SSIS Catalog has been created $EnvDatabaseName = "InitialCatalog" #Name of the property that the environment will store the EdFi Database name in $Server = "(local)" #The server where the EdFi Database is stored $EdFi = "EdFi_Ods_Populated_Template" #The EdFi (ODS) database name. For this how-to, we will be using the Ed-Fi v2.0 ODS Populated Template $Dashboard = "EdFi_Dashboard" #The Dashboardboard Database name $DashboardDW = "EdFi_DashboardDW" #The Dashboard DW name $Application = "EdFi_Application" #The Application Database name $DBUserName = "edfiAdmin" $DBUserPassword = "edfiAdmin" #********************************************************************************************************************************
- 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.
Image Added
- 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.
Image Added
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:
Code Block language powershell #********** File Variables***************************************************************************************************** $ProjectFilePath = "C:\Ed-Fi-Core\Etl\EdFi.Etl.DW\bin\Development\EdFi.Etl.ispac" #The path to the ispac file from the SSIS project. Must build the project solution first. $ProjectName = "EdFi.Etl.DW" #Project name from the SSIS Project $FolderName = "DashboardDW" #The folder name in the SSIS Catalog $EnvironmentName = "DashboardDW" #The name of the environment the packages will use $ISServerName = "(local)" #The name of the server where the SSIS Catalog has been created $EnvDatabaseName = "InitialCatalog" #Name of the property that the environment will store the EdFi Database name in $Server = "(local)" #The server where the EdFi Database is stored $EdFi = "EdFi_Ods_Populated_Template" #The EdFi (ODS) database name. For this how-to, we will be using the Ed-Fi v2.0 ODS Populated Template $Dashboard = "EdFi_Dashboard" #The Dashboardboard Database name $DashboardDW = "EdFi_DashboardDW" #The Dashboard DW name $Application = "EdFi_Application" #The Application Database name $DBUserName = "edfiAdmin" $DBUserPassword = "edfiAdmin" #********************************************************************************************************************************
- 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 5: Setup SQL Server Agent Job
Create the SQL Server Agent Job
- Download EdFiSQLAgentv2.0.sql
- In SQL Server Management Studio, open the downloaded file.
- 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 ODS v2.0) |
Owner | The SQL User that will be the owner of the job: edfiAdmin |
DashboardDatabaseName | The name of the Dashboard Data Store database, (EdFi_Dashboard) |
DashboardDWDatabaseName | The name of the Dashboard Data Warehouse database, (EdFi_DashboardDW) |
ApplicationDatabaseName | The name of the Application Database, (EdFi_Application) |
RootDirectory | The root directory of the extracted repository, (C:\) |
DatabaseServer | The SQL Server Instance the Ed-Fi Dashboards Databases will be created on, (local) |
DTExecLocation | C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe |
- 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.
Image Added
- After the SQLAgent script has been updated, execute the script to create a new SQL Server Agent Job.
Step 6: Load Dashboard Databases
Once the SQL Server Agent script has been executed (in Step 5 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 ODS v2.0 and select Start Job at Step...
- The Start job screen will now open. Select Step ID 1
Image Added
- 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:
Table of Contents | ||||
---|---|---|---|---|
|
Panel | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||
The following links are required to complete installation: EdFiException Component Assembly SSIS Multiple Hash v1.4.1 (2012) SetupMultipleHash2012_V1.4.1.zip EdFiDashboard ODS v2.0 Job Creation Script EdFi ODS v2.0 Database Backup |