Couple of Macro requests? Automated View Generation/Metadata update / Data Model Metrics

Hello,

we are looking for a macro that can do the following:

1. Take each table in turn

2. Create a view based on the physical data model's table exact structure, with a specific pre-fix

Another one:

1. Can we have the Export Meta Data to Excel Version 3.1 extended to include the name of the Domain to which an attribute is bound?

Final One

Data Model Metrics:

Can we have a macro that produces the following metrics for all models in a DM1 file, split by model:

1) # of Entities

1b) # of Entities without definitions

2) # of attributes

2b) # of attributes without definitions

3) # of relationships

4) # of Primary, Alternate & Foreign Keys

Kind regards

Cheryl

Parents
  • Here's the last macro for the metrics.  I wasn't quite sure what you were looking for with the following:

    4) # of Primary, Alternate & Foreign Keys

    I didn't know if you were looking for the number of keys/indexes or the number of columns that are marked as PK/FKs or something else.  What I have done right now is count the number of keys/Indexes and display that.  I've also added the code to count the number of PK/FK columns but it's commented out.  Depending on what you're looking for you can comment/uncomment the following lines:
    For PK/FK column count:

                    ' count the number of key columns
                    'If attr.ForeignKey = True Or attr.PrimaryKey Then
                    '   totalKeys = totalKeys + 1
                    'End If

    For key/index count:

                ' count the number of keys/indexes
                totalKeys = totalKeys + ent.Indexes.Count

    Let me know if you have any questions.

    5531.Export Data Model Metrics to Excel .txt
    ' This macro will produces the following metrics report for all models in a DM1 file.
    ' This will export the following information in Excel where each one is a separate column:
    '
    ' 		Model Name
    '		# of Entities
    '		# of Entities without definitions
    '		# of Attributes
    '		# of Attributes without definitions
    '		# of Relationships
    '		# of Primary, Alternate & Foreign Keys
    
    Dim curRow As Integer
    Dim curCol As Integer
    Dim clrBack As Variant
    Dim clrFore As Variant
    Dim clrTitleBack As Variant
    Dim clrTitleFore As Variant
    
    ' Dim MS Excel variables.
    	
    Dim Excel As Object
    	
    ' Dim ER/Studio variables.
    	
    Dim diag As Diagram
    Dim mdl As Model
    
    Sub Main
    
    	' Init the ER/Studio variables.
    	
    	Set diag = DiagramManager.ActiveDiagram
    	'Set dict = diag.Dictionary
    
    
    	curRow = 1
    	curCol = 1
    
    	Debug.Clear
    
    	' Create Excel workbook.
    
    	Set Excel = CreateObject("Excel.Application")
    	Excel.Workbooks.Add
    	
    	'Excel.Visible = True
    
    	PrintColumnHeader
    	PrintData
    	
    	MsgBox("Export   Complete!",,"ER/Studio")
    	
    	' make Excel spread sheet visible
    	Excel.Visible = True
    
    
    
    End Sub
    
    Sub PrintData
    	Dim ent As Entity
    	Dim	attr As AttributeObj
    	Dim totalAttr As Integer
    	Dim attrNoDef As Integer
    	Dim entNoDef As Integer
    	Dim totalKeys As Integer
    
    	For Each mdl In diag.Models
    
    		totalAttr = 0
    		attrNoDef = 0
    		entNoDef = 0
    		totalKeys = 0
    
    		PrintCell mdl.Name, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		PrintCell mdl.Entities.Count, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		For Each ent In mdl.Entities
    			'count the number that don't have definitions
    			If ent.Definition = "" Then
    				entNoDef = entNoDef + 1
    			End If
    
    			' count the number of attributes
    			totalAttr = totalAttr + ent.Attributes.Count
    			For Each attr In ent.Attributes
    				' count the number of attributes without defs
    				If attr.Definition = "" Then
    					attrNoDef = attrNoDef + 1
    				End If
    
    				' count the number of key columns
    				'If attr.ForeignKey = True Or attr.PrimaryKey Then
    				'	totalKeys = totalKeys + 1
    				'End If
    			Next
    			' count the number of keys/indexes
    			totalKeys = totalKeys + ent.Indexes.Count
    		Next
    		PrintCell entNoDef, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		PrintCell totalAttr, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		PrintCell attrNoDef, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		PrintCell mdl.Relationships.Count , curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		PrintCell totalKeys, curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, False
    		curRow = curRow + 1
    		curCol = 1
    	Next
    
    
    
    
    
    End Sub
    
    
    ' Print the column header.  Only print headers when value is true in options array.
    
    Sub PrintColumnHeader
    
    
    		PrintCell "Model Name", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# of Entities", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# Entities without definitions", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# of Attributes", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# of Attributes without definitions", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# of relationships", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		PrintCell "# of Primary, Alternate, & Foreign Keys", curRow, curCol, 0, 1, clrTitleFore, clrTitleBack, 10, True
    
    		curRow = curRow + 1
    		curCol = 1
    
    End Sub
    
    ' Print a cell
    
    Sub PrintCell(value As Variant, row As Integer, col As Integer, rowInc As Integer, colInc As Integer, clrFore As Variant, clrBack As Variant, szFont As Integer, bBold As Boolean)
    	Excel.Cells(row, col).Value = value
    
    	Excel.Cells(row, col).Font.Bold = bBold
    	Excel.Cells(row, col).Font.Color = clrFore
    	Excel.Cells(row, col).Font.Size = szFont
    
    	curRow = curRow + rowInc
    	curCol = curCol + colInc
    End Sub
    

Reply Children
No Data