Authorization Model Service

In this Topic

Using Microsoft Excel with an OData Data Feed

The Authorization Model Service (AMS) is an externally-accessible version of the model service used internally by AASHTOWare Project. The AMS can be used by programs, applications, and third-party tools that utilize the OData standard to securely retrieve and update system data through the business layer in AASHTOWare Project.

Requests made to the AMS are sent with AASHTOWare Project usernames and passwords via Basic Authentication using SSL. The AMS uses the AASHTOWare Project security model for filtering data requests and processing updates. For example, when a request is made to the AMS to retrieve the list of projects, the set of projects sent back is restricted to include only those to which the requesting user account is assigned access. The security access applied to a given request is determined by the user role marked as the Service Authorization Role for the requesting user. If a large amount of data is requested, the service initially returns the first 1000 records and allows the client to load additional data if needed.

Installation and configuration are required to enable the access of system data by using the AMS. During installation, you must:

Select HTTPS.
Install an SSL certificate on the application server, which the installer uses to set up the bindings for the AuthModelService.

For information about installing the application server, installing an SSL certificate, and using the AMS, refer to the Web-Based AASHTOWare Project Installation Instructions.

To enable a user to access system data externally by using the AMS, navigate to the User Summary, click the User Roles tab, and select the Svc Auth Role check box for the appropriate role. Only one role can be marked as the Service Authorization Role for each user. Repeat this process for each user requiring access.

After the system is configured correctly, you can test the connection to the AMS and retrieve information similar to data dictionary by using the $metadata command in a web browser, similar to the following:

https://[WebSiteName:AMSPort]/AuthModelService.svc/$metadata

The system includes a sample Visual Studio solution file that can be used as an example of how to securely retrieve and update system data by using OData with the AMS. To install the sample file, the AuthModelService Client Sample must be selected during installation. The Sample_AuthModelService_Client.sln file can then be found under the root of the application server installation location in the Samples\AuthModelService_Client directory.

Using Microsoft Excel with an OData Data Feed

Microsoft Excel is one example of the many applications that can be used to securely access system data via the OData protocol and the AMS. You can use Excel to query the database and pull system data into a spreadsheet for further manipulation and analysis. Excel can be used to view a local copy of system data, but it cannot be used to change data in the database.

Microsoft Power Query for Excel is an add-in available for free download that extends the query and analysis capabilities already built into Excel. Power Query provides an interface for building complex queries and enables you to manually edit the query in the Query Editor if needed. You can download and install Power Query here:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

To access system data from Excel, configure an Excel workbook to create a connection to the system's OData data feed. Browse to locate the data feed here (be sure to include the trailing slash):

https://[WebSiteName:AMSPort]/AuthModelService.svc/

When prompted, enter the AASHTOWare Project username (formatted as Domain\UserName) and password that will be used to connect to the system. For information about configuring a workbook to use an OData data feed, refer to the Microsoft Excel Help system.

After the connection is created, the entire structure of system tables is available to view. Although you can view the full list of tables, you can pull data only from those tables for which the connecting user's Service Authorization Role is assigned access.

You can use Power Query to:

Load all rows in a table
Load a subset of rows in a table
Load values from a related child or reference table
Join two tables
Join data from multiple sources

For example, you could construct queries that retrieve all code table names and descriptions, as well as the related child table of code table values.

Keep in mind that the features available in Excel and Power Query vary depending on which version of each program you are using. For additional information about Power Query, refer to the Microsoft Power Query for Excel Help system.