Normalized Table Builder transforms a System Architect encyclopedia into a normalized relational database table structure that can be queried using standard structured query language (SQL). Normalized Table Builder provides an environment that the user can query using standard SQL with a response time that is not hindered by the parsing activity of the System Architect “memo” field
Normalized Table Builder is accessed through SAEM (System Architect Encyclopedia Manager). It maps the System Architect repository to a flattened form, enabling you to run SQL queries against this static copy of the encyclopedia in SAEM or using third-party tools such as Crystal Reports.
What the normalized table builder does
1 If the destination database does not exist it will be created.
2 The entity and relationship tables from the source database will be copied to the destination database. If the entity and relationship tables already exist in the destination database they will be dropped.
3 A new table, Property, will be created in the destination database. If this table already exists it will be dropped.
The Property table has four columns:
PropID: The ddid of the flattened object.
PropName: The name of the flattened property.
PropValueShort:The first 2000 characters of the property value.
PropValueFull: The complete property value.
The PropValueShort column is needed to support queries using ORDER BY and LIKE.
Each row in the Entity table will have its Properties column (memo field) broken up such that every property will have a row in the Property table.
Rows in the Entity and Property table can be linked by ddid (Entity.Id = Property.PropID).
To use the normalized table builder
1 Start SAEM.
2 Connect to the desired SQL Server, and then select the database (encyclopedia) that you want to flatten.
3 Optionally create a lookup table for the encyclopedia, to make query writing more intuitive in the flattened database. In SAEM, select Tools, Create Lookup Table. If the database chosen in SAEM is not open in System Architect, you will automatically prompted to open the encyclopedia in System Architect.
4 Flatten the database: Select Tools, Flattener. This option is only enabled if the current database is a valid System Architect encyclopedia. You must have exclusive access to the database that you want to flatten.
5 In the Flatten Database dialog, click Click here or press ALT+O to see additional options to reveal additional options.
Use the Local Batch Size field to control how much local memory the flattener will use. By default it is set to 500000 which roughly equates to 250MB. If your system has more available memory, you should increase this number as it will improve performance. The value for this field will be remembered for subsequent Flattener sessions.
Use the Update Batch Size field to control how many INSERT statements are bundled together when updating the destination database. Larger values help reduce network traffic and thus improve performance but too large a value will hinder performance. The ideal number seems to vary from system to system. The default of 60 seems to help performance on all systems tested. The value for this field will be remembered for subsequent Flattener sessions.
6 In the Flatten Database dialog, specify the name of the flattened database that will be created within the Destination Database field. The Destination Database field will be set to the current database plus a “_Flat” suffix. You can change the destination database name to any valid database name. The destination database name will be remembered for subsequent Flattener sessions. The Source Database in Flatten Database dialog will be set to the current database and is read only. If you want to change the current database you must close this dialog and change the current database.
7 Click OK.
When the database has been flattened, the destination database can be queried using standard SQL.
Example SQL queries
The following SQL query was written by hand to report on who created all of the data elements in the encyclopedia:
select entity.name, property.propname, property.propvalueshort from entity, property where (entity.class = 3 and entity.type = 15) and entity.id = property.propid and property.propname = 'Initial Audit'
The following SQL will report on all objects whose description property is exactly 255 characters and does not end in a period:
select class, type, name from entity where id in (select propid from property where propname = 'Description' and len(propvalueshort) = 255 and right(propvalueshort, 1) <> '.')
To run these queries
1 If the destination database is created by the Flattener you will need to reconnect to the server in order for SAEM to see this new database.
2 In SAEM, select the flattened database.
3 Select Query, New.
4 Paste the code above into the Query text field.
5 Select Query, Execute.
Result
Performance
If the destination database is newly created (Run 1) performance will be slower as the database will have to be extended for the new data. Subsequent runs (Run 2) into the same database are generally faster.
