In a System Architect database, the type number lookup table contains a mapping of definition, diagram, and symbol type numbers and their associated type names. If you plan on querying System Architect's tables directly then you will not have access to type names (for example, Data Element, Entity, and Business Process) but rather only the associated type numbers. This can problematic when using the query results for reporting purposes as most users will not know that a class number of 3 and type number of 15 actually refers to a data element definition. The type number lookup table allows you to query System Architect’s tables directly and return both type names and numbers.
When you have selected the Create Lookup Table menu item System Architect will start and load to the encyclopedia that is currently selected in SAEM. The encyclopedia's meta-model will be read and a new table named TypeNum_Lookup will be added to the database. If a lookup table is already present it will be dropped and then recreated using the current meta-model. You should realize that the lookup table is only a snapshot of the meta-model. If you change the encyclopedia meta-model (that is, add a new definition type) then you will need to recreate the lookup table.
The lookup table has these columns:
Column
Description
ClassNum
The class number as used internally by System Architect where 1 is a diagram, 2 is a symbol, and 3 is a definition.
TypeNum
The type number as used internally by System Architect. Valid values vary depending upon the class number.
TypeName
The type name as displayed to the user for the given class/type number pair.
Note If you do not have rights (db_ddladmin or greater) to alter the schema of the database then the creation of the lookup table will fail.
Creating a lookup table
1 If System Architect is running, close it.
2 On the Windows Start menu, click All Programs > UNICOM Systems > UNICOM Systems Lifecycle Solutions Tools > System Architect > SAEM (SQL Server).
3 Click Encyclopedias, and then select the encyclopedia for which you want to create a Lookup Table.
4 Click Tools, and then click Create Lookup Table.
System Architect runs, loads the selected encyclopedia, and then shuts down after it has retrieved the necessary information.
The SAEM message box confirms if the Lookup Table was created successfully for the currently selected database.
5 Run a query which selects records from the Entity table.
Example query
The lookup table is used by joining it with the Entity table on the Type and Class columns. The following example illustrates how to use the lookup table in a query designed to list the number of definition instances for each definition type.
SELECT
Entity.Type AS Type, COUNT ( Entity.Type) AS COUNT_of_Type,
TypeNum_Lookup.TypeName
FROM
Entity INNER JOIN TypeNum_Lookup ON Entity.Type = TypeNum_Lookup.TypeNum
AND Entity.Class = TypeNum_Lookup.ClassNum
WHERE
(
( Entity.Class = 3)
)
GROUP BY Entity.Type, TypeNum_Lookup.TypeName
ORDER BY 1 ASC
The Query Builder feature is aware of the lookup table. If a lookup table is present when using the Query Builder you can select the `Include type names' option to have the join syntax automatically created for you. The above example was generated by SAEM's Query Builder.
You can also use the lookup table in a flattened database. The Flattener feature in SAEM will copy the table to the destination (flattened) database. The following example illustrates how to use the lookup table in a flattened database.