Working with the SACS Reports Database


SACS supports exporting results to a SQLite database for enhancing Precede results visualization and for additional post-processing and reporting capabilities. In this post I will explain what the SACS Reports Database is and illustrate a few ways to interact with the database.

Enabling SACS Reports Database

To enable the SACS Reports Database output, select the SACS Reports Database option in the Analysis Options section of the Analysis Generator.

An additional output file, SACS Reports Database, will be created. If a Postvue Database is created, a copy of the SQLite database will be saved in the Postvue Database folder (PSVDB.*).

The SQLite database is used by Precede to query the results data and can be faster for larger datasets (large models with many load cases). The SQLite database is also required for the Combined Results Database feature.

Viewing the Reports Database

Since SQLite is an open source program and widely used, there are many ways to open and view SQLite databases. A command-line shell can be downloaded from the SQLite webpage, but I prefer to use an open source database viewer called DB Browser for SQLite.

When opening the SQLite database in DB Browser, you can view the table information in the Database Structure tab. The definition of the database structure is commonly referred to as the schema.

Each table contains a list of columns which define the name and data type. This page can help you see where the relevant data you are looking for is located in the database.

On the Browse Data tab, we can view each table as if it were a spreadsheet. Some tables may not be populated if the relevant analyses were not performed (e.g. a joint can analysis is required for JointCanUC to be populated).

This information can be filtered and modified just like a spreadsheet and exported to CSV or copied and pasted into other spreadsheet programs.

The Execute SQL tab allows you to run your own custom SQL queries on the database. SQL has its own syntax which can be used to write powerful database queries.

Here I will just show a simple query on the Member results. Say that I want a list of the members with unity checks greater than 1.0 for the operating load conditions. I can just write:

SELECT MemberName, MemberGroup, LoadConditionName, MaxUC
FROM R_POSTMEMBERRESULTS
WHERE MaxUC > 1.0
AND LoadConditionName in ('OPR1', 'OPR2', 'OPR3');


SELECT defines the columns that I want to view, FROM defines which table I am querying and WHERE defines the selection criteria. With this query I get the following output from the Sample02 database:


 


There are many more things that can be done with SQL queries like summations, averages, grouping, sorting and joining of data from multiple tables. If you are interested in learning more about the SQL language, you can read the SQLite documentation or find one of the many tutorials on the web. Keep in mind that there are different “flavors” of SQL and so there may be some specific language differences between MYSQL, MS SQL, SQLite, etc. but the vast majority of the language will be the same and those differences should be well documented.


Viewing the Reports Database with Excel


Excel does not support SQLite by default. A SQLite ODBC driver is required to connect Excel to the Reports Database.


You can download either the 32-bit or 64-bit ODBC Driver for Sqlite here: http://www.ch-werner.de/sqliteodbc/


The version of the driver you should use is dependent upon the version of Office you are using. If you are unsure which version of Office you are using and you have Windows 10 then most likely you should use the 64-bit version. The version information for Excel can be found in any of the programs under File > Account > About Excel.


  1. Once the driver is downloaded, install the executable with admin privileges by right-clicking the executable and selecting Run as Administrator.
  2. Go to the Control Panel -> System and Security -> Administrative Tools -> ODBC Data Sources (64-bit).


 


 


  1. Create a New Data Source, click Add, select SQLite3 ODBC Driver, click Finish.


 


  1. Give it a Data Source Name and select the Sqlite DB to associate it with then Click OK.


 


  1. Verify that the User DSN you just created is listed in the Data Sources and click OK.


 


  1. Now, open Excel and go to the Data Tab. From there, select Get Data -> From Other Sources -> From ODBC.


 


  1. Select the DSN that you created earlier from the list and click OK.


 


  1. The DSN requires that you enter a User Name. Enter your name (or any name) and click Connect. You may omit the Password.


 


  1. The Navigator window should appear, and it is in here that you can select which tables and fields you wish to import.


 


When you are done selecting the data you want to import, you can click Load to open the data into Excel. You can use the Transform Data button to rename fields and such. There are resources available on the internet to help with working with the ODBC Navigator Pane.


Viewing the Reports Database with Python


SQL is a powerful tool for queries and can perform some basic operations, but if you need a more powerful post-processing solution or want to integrate the results with other processes like the SACS Batch Processing feature, you might want to consider a scripting language with Python. I recommend Python for a few reasons, it is one of the most widely used programming languages, it has a built-in SQLite library and we are considering adding more support for Python integration in SACS.


You can download and install Python from their website, but I’d recommend Anaconda which is a distribution of Python which comes with some additional commonly used packages like Numpy, Pandas and Matplotlib.


If you are not familiar with Python, there are many resources available online to learn the language, so I won’t be covering basic syntax in this post, but I will show you how to set up a basic SQLite query in Python.


Here is the same SQLite query we wrote in DB Browser in Python:


import sqlite3

conn = sqlite3.connect('sacsdb.sample02')
c = conn.cursor()
query = c.execute('''
                SELECT MemberName, MemberGroup, LoadConditionName, MaxUC
                FROM R_POSTMEMBERRESULTS
                WHERE MaxUC > 1.0
                AND LoadConditionName in ('OPR1', 'OPR2', 'OPR3');
                ''')
for row in query:
    print(row)


The output should look similar to the output that we saw in DB Browser.


('701-801', 'LG7', 'OPR1', 1.0861318111)
('701-801', 'LG7', 'OPR2', 1.0957342386)
('701-801', 'LG7', 'OPR3', 1.1260851622)
('703-803', 'LG7', 'OPR1', 1.1025291681)
('703-803', 'LG7', 'OPR2', 1.085442543)
('703-803', 'LG7', 'OPR3', 1.0535115004)
...
('803-807', 'W02', 'OPR2', 1.1349545717)
('803-807', 'W02', 'OPR3', 1.1408293247)
('835-838', 'W02', 'OPR1', 1.7950137854)
('835-838', 'W02', 'OPR2', 1.7918047905)
('835-838', 'W02', 'OPR3', 1.7845262289)


Now if you wanted to stop here, there wouldn’t be much benefit in using Python over DB Browser. However, we can do many interesting things with the data like write our own functions for post-processing, create plots to visualize the data, or set up more complex workflows.


Demo14 which comes installed with SACS is an example of an iterative redesign.


Here is the code from the python script for Demo14:


import sys, string, os
import subprocess
import sqlite3
import fileinput

#SACS program information
defaultSACSDir = "C:\\Program Files (x86)\\Bentley\\Engineering\\SACS 13 CONNECT Edition - CL"
enginePath = defaultSACSDir + "\\AnalysisEngine.exe"
#Project Files
projectFiles = defaultSACSDir + "\\Demos\Demo14"
SACSInputFile = projectFiles + "\\sacinp.demo14"
runFilePath = projectFiles + "\\demo14.runx"

def RunSACS():
    #Execute the SACS engine. 
    res = subprocess.run([enginePath,runFilePath,defaultSACSDir], shell=True)
    return res

def ReadMemberResults():
    conn = sqlite3.connect(projectFiles+ "\\sacsdb.db")
    cur = conn.cursor()
    #Get all members with a MaxUC greater than 0.9
    cur.execute('SELECT MemberName,MAX(MaxUC),SegmentNumber FROM R_POSTMEMBERRESULTS WHERE MaxUC > 0.9 GROUP BY MemberName')
    results = cur.fetchall()
    conn.close()
    return results


def EvaluateDesign(results):
    #If the results list is empty it means that there are no members with a MaxUC greater than 0.9.
    if len(results) == 0:
        return True

def GetGRUPforMember(member):
    grup=""
    joints = member.split("-")  
    #Assemble serach line
    memberLine  = "MEMBER {:<4}{:<4}".format(joints[0],joints[1]) 
    memberLine2 = "MEMBER1{:<4}{:<4}".format(joints[0],joints[1]) 
    infile = open(SACSInputFile,"r")
    for line in infile:
        if line.find(memberLine) >= 0:
            mlist=line.split(" ")
            grup = mlist[4]
            grup = grup.replace('\n', ' ').replace('\r', '')
            break
        elif line.find(memberLine2) >= 0 :
            mlist=line.split(" ")
            grup = mlist[3]
            grup = grup.replace('\n', ' ').replace('\r', '')
            break
    infile.close()
    return grup

def ModifyGRUP(grup,segNum):
    grupLine="GRUP {:<3}".format(grup)
    nSeg=0
    #Search for the GRUP Label line in the input file
    for line in fileinput.FileInput(SACSInputFile, inplace=1):
        line = line.replace('\n', ' ').replace('\r', '')
        if line.find(grupLine) >= 0: 
            if nSeg==segNum:
                fields = line.split(" ")
                wt=fields[11]
                #Increase the Wall Thickness by 0.125 inches
                newWT='{:05.3f}'.format(float(wt)+0.125)
                #modify the line
                line=line.replace(wt,str(newWT),1)
        print(line)


def ModifyInputFile(results):
    for row in results:
        member = row[0]
        MaxUX = row[1]
        SegmentNumber = row[2]
        #Get the member GRUP Label from the input file
        grup = GetGRUPforMember(member)
        if grup != "":
            #Modify the GRUP Label
            ModifyGRUP(grup,SegmentNumber)
    


############## Main LOOP
#iterate until Design Criteria is met
while True:
    #Run SACS
    RunSACS() 
    #Read Results from resutls Database
    results = ReadMemberResults()
    #determine if desig criteria has been met
    if EvaluateDesign(results):
        break
    #if criteria is not met, modify the input file and repeat
    ModifyInputFile(results)


Note that we are importing the sqlite library and querying the database with this function:


def ReadMemberResults():
    conn = sqlite3.connect(projectFiles+ "\\sacsdb.db")
    cur = conn.cursor()
    #Get all members with a MaxUC greater than 0.9
    cur.execute('SELECT MemberName,MAX(MaxUC),SegmentNumber FROM R_POSTMEMBERRESULTS WHERE MaxUC > 0.9 GROUP BY MemberName')
    results = cur.fetchall()
    conn.close()
    return results


If the unity check for any element is larger than [UC Limit], then the model file is modified to increase the wall thickness. The analysis is then re-run using the batch processing feature with this function:


def RunSACS():
    #Execute the SACS engine. 
    res = subprocess.run([enginePath,runFilePath,defaultSACSDir], shell=True)
    return res


This script will run until the unity check requirement is satisfied.


With a little bit of knowledge of Python and SQLite, there are many different things that you can do with SACS Results Database. Be sure to comment with your ideas in Communities!