Reporting Framework

This section describes how to access model outputs with SQL as well as the model's integration with greenhouse gas emission analysis.

Overview

All relevant ABM output is loaded into a Microsoft SQL Server Enterprise 2014 database. Reporting is currently handled in the database via programmability objects and ad-hoc queries. In order to access the output database the user should have Microsoft SQL Server Management Studio 2014 installed. A data warehouse and reporting suite leveraging Microsoft SQL Server Analysis and Reporting services is still in development.

Database

ABM outputs are loaded into a SQL Server database. As improvements are made to the ABM, the database evolves too. The current database schema can be found here.

EMFAC Integration

SANDAG staff developed a procedure to integrate ABM with EMFAC2014 for greenhouse gas emission analysis. First a user needs to run a Python-SQL based procedure to generate EMFAC2014 input files using ABM outputs. Once the inputs are generated, the user then needs to run EMFAC2014 software to create greenhouse gas emission measures.

The Python-SQL procedure relies on two SQL Functions in the ABM database, emfac.fn_emfac_2014_vmt and emfac.fn_emfac_2014_vmt_speed. The first function creates VMT by EMFAC2014 vehicle and technology group. The second function creates percent of VMT by 5 mile speed bins between 0 and 70 mph. These functions rely entirely on tables preloaded in the database, including the default EMFAC2014 inventory tables, the mapping table between EMFAC2014 vehicle types and SANDAG model vehicle types, and model assignment and network output from ABM runs.

To run the Python-SQL based procedure, these software/libraries should be installed on a user’s computer:

• Install pymssql. For example in C:\Anaconda\Lib\site-packages.
• Install python excel (xlwt) packages. For example in C:\Anaconda\Lib\site-packages\xlwt.
• Install pyodbc. For example in C:\Anaconda\Lib\site-packages\sqlalchemy.

To run the EMFAC2014 input builder:

1. Load ABM outputs into the database for a given model run.
2. Open a DOS window, navigate to \python folder, execute emfac2014_abm.py with this usage:
   • Python emfac2014_abm.py <Scenario ID> <Season: Annual | Summer | Winter> <SB 375: On | Off> <Output Path>
3. The EMFAC2014 input files are written to the \output folder as EMFAC2014-SANDAG-[YEAR]-[SEASON]-[YEAR] -<sb375>.xlsx

Go To Top