Export Analysis Utility

From CaisisWiki

(Difference between revisions)
Jump to: navigation, search
(Features)
(added writeDB fields source code for Chiragh)
 
(10 intermediate revisions not shown)
Line 16: Line 16:
* Place the Microsoft Access file (MDB file) for the Export Analysis Utility in this secure network folder.
* Place the Microsoft Access file (MDB file) for the Export Analysis Utility in this secure network folder.
* From Export Analysis Utility, open the Linked Table Manager (Tools, Database Utilities, Linked Table Manager) to link all tables to the warehouse copy of Caisis on SQL Server.
* From Export Analysis Utility, open the Linked Table Manager (Tools, Database Utilities, Linked Table Manager) to link all tables to the warehouse copy of Caisis on SQL Server.
 +
 +
* Localization Issues
 +
** For Longitudinal Followup will need to modify WHERE clause in the following queries to remove IN() statement or modify to your own OpCaseSurgeon and OpInstitution values: qryFU_NeedLetter_Kidney, qrptFU_LastNephrectomy, qrptFU_LastRPLND, qryFU_NeedLetter_Testis, qryFU_NeedLetter_Prostate, qryFU_NeedLetter_Bladder, qryFU_NeedLetter_LapProstateIMM, qryFU_NeedLetter_Survivorship, qryFU_NeedLetter_Survivorship_Jul08_2005
== Features ==
== Features ==
* Splash Screen. This form opens automatically when you open the Export Analysis Utility. It displays the location of the MDB file and the database your tables are linked to, and it provides buttons to launch each of the following features.
* Splash Screen. This form opens automatically when you open the Export Analysis Utility. It displays the location of the MDB file and the database your tables are linked to, and it provides buttons to launch each of the following features.
* Data Integrity tool. This form is also available in Caisis system admin (Administer the Syste, System Config, Data Integrity). It allows users to browse contents of every table and every field in the MDB file or the linked SQL Server tables in the warehouse. It also allow the user to view lookup codes associated with each field in the database (through Metadata tables) The recode feature is exclusively available through the Caisis system admin tool.
* Data Integrity tool. This form is also available in Caisis system admin (Administer the Syste, System Config, Data Integrity). It allows users to browse contents of every table and every field in the MDB file or the linked SQL Server tables in the warehouse. It also allow the user to view lookup codes associated with each field in the database (through Metadata tables) The recode feature is exclusively available through the Caisis system admin tool.
-
* Nomograms. Links to [http://nomograms.org web nomograms] on [http://www.mskcc.org MSKCC web site]. Also includes functions for calling nomogram library to calculate nomogram predictions for sets of patients in a tables or spreadsheet.
+
* [[Nomograms]]. Links to [http://nomograms.org web nomograms] on [http://www.mskcc.org MSKCC web site]. Also includes functions for calling nomogram library to calculate nomogram predictions for sets of patients in a tables or spreadsheet.
 +
* [[Longitudinal Followup]]. Wizards and partially automated tools to help manage the process of mailouts to patients to find out how they are doing after treatment if they are not coming back regularly to your practice. NOTE: You will need to keep your own local copy of the Signatures table after you have started using the Longitudinal Followup process to maintain custom letters to patients from your local physicians.
 +
 
* Tables
* Tables
** DistributableObjects. A list of tables, queries, forms, etc that can be publically distributed. Used when preparing a copy of the Export Analysis Utility to post here on Caisis.org. Does not include user-defined or temporary tables, queries and reports.
** DistributableObjects. A list of tables, queries, forms, etc that can be publically distributed. Used when preparing a copy of the Export Analysis Utility to post here on Caisis.org. Does not include user-defined or temporary tables, queries and reports.
 +
== SAMPLE CODE ==
 +
 +
* How do I find out which fields are most/least populated?
 +
 +
<pre>
 +
Function WriteDBFields()
 +
On Error GoTo Error_Handler
 +
 +
    Dim db As DAO.Database
 +
    Dim tbl As DAO.TableDef
 +
    Dim fld As DAO.Field
 +
    Dim fldType As String
 +
    Dim outfilename As String
 +
    Dim strTableDescription As String
 +
    Dim strFieldDescription As String
 +
   
 +
    Dim rs As DAO.Recordset
 +
    Dim lngTableRecords As Long
 +
    Dim lngFieldRecords As Long
 +
    Dim lngZLS As Long
 +
    Dim strCount As String
 +
   
 +
    Set db = CurrentDb()
 +
    outfilename = Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & " Data Dictionary.txt"
 +
   
 +
    Debug.Print outfilename
 +
   
 +
    Open outfilename For Output As #1
 +
   
 +
    Write #1, "TableName", "TableDesc", "FieldName", "DataType", "FieldDesc", "TableRecords", "FieldRecords", "ZeroLengthStrings"
 +
    Debug.Print """TableName"",""TableDesc"",""FieldName"",""DataType"",""FieldDesc"",""TableRecords"",""FieldRecords"",""ZeroLengthStrings"""
 +
   
 +
    For Each tbl In db.TableDefs
 +
       
 +
        'To prevent system tables from printing, include: Left(tbl.Name, 4) <> "MSys"  - or for specific tbls only: Left(tbl.Name, 6) = "CSMM_S"
 +
        If Left(tbl.Name, 4) <> "MSys" And tbl.Connect = vbNullString Then
 +
            Set rs = tbl.OpenRecordset(dbOpenSnapshot)
 +
            If rs.EOF Then
 +
                lngTableRecords = 0
 +
            Else
 +
                rs.MoveLast
 +
                lngTableRecords = rs.RecordCount
 +
            End If
 +
            rs.Close
 +
            For Each fld In tbl.Fields
 +
                    strCount = "SELECT [" & tbl.Name & "].[" & fld.Name & "] " _
 +
                    & "FROM [" & tbl.Name & "] " _
 +
                    & "WHERE (([" & tbl.Name & "].[" & fld.Name & "]) Is Not Null);"
 +
                    Set rs = db.OpenRecordset(strCount, dbOpenSnapshot)
 +
                    If rs.EOF Then
 +
                        lngFieldRecords = 0
 +
                    Else
 +
                        rs.MoveLast
 +
                        lngFieldRecords = rs.RecordCount
 +
                    End If
 +
                    rs.Close
 +
                    If fld.Type = 10 Or fld.Type = 12 Then
 +
                        strCount = "SELECT [" & tbl.Name & "].[" & fld.Name & "] " _
 +
                        & "FROM [" & tbl.Name & "] " _
 +
                        & "WHERE (([" & tbl.Name & "].[" & fld.Name & "]) = """");"
 +
                        Set rs = db.OpenRecordset(strCount, dbOpenSnapshot)
 +
                        If rs.EOF Then
 +
                            lngZLS = 0
 +
                        Else
 +
                            rs.MoveLast
 +
                            lngZLS = rs.RecordCount
 +
                        End If
 +
                        rs.Close
 +
                    Else
 +
                        lngZLS = 0
 +
                    End If
 +
                    Debug.Print """" & tbl.Name & """,";
 +
                    strTableDescription = vbNullString
 +
                    strTableDescription = tbl.Properties("Description")
 +
                    Debug.Print """" & strTableDescription & """"
 +
                    Debug.Print """" & fld.Name & """,";
 +
                    Select Case fld.Type
 +
                    Case 1
 +
                        fldType = "Yes/No"
 +
                    Case 10
 +
                        fldType = "Text:" & fld.Size
 +
                    Case 12
 +
                        fldType = "Memo"
 +
                    Case 8, 22, 23
 +
                        fldType = "Date/Time"
 +
                    Case 2
 +
                        fldType = "Number:Byte"
 +
                    Case 3
 +
                        fldType = "Number:Integer"
 +
                    Case 4
 +
                        fldType = "Number:Long"
 +
                    Case 5
 +
                        fldType = "Number:Currency"
 +
                    Case 6
 +
                        fldType = "Number:Single"
 +
                    Case 7
 +
                        fldType = "Number:Double"
 +
                    Case Else ' 9, 11, 15, 17, 19, 20, 21
 +
                        fldType = "Numeric"
 +
                    End Select
 +
                    Debug.Print """" & fldType & """,";
 +
                                       
 +
                    strFieldDescription = vbNullString
 +
                    strFieldDescription = fld.Properties("Description")
 +
                    Debug.Print """" & strFieldDescription & """,";
 +
                    Debug.Print lngTableRecords & ",";
 +
                    Debug.Print lngFieldRecords & ",";
 +
                    Debug.Print lngZLS
 +
               
 +
                Write #1, tbl.Name, strTableDescription, fld.Name, fldType, strFieldDescription, lngTableRecords, lngFieldRecords, lngZLS
 +
               
 +
                Next
 +
        End If
 +
    Next
 +
   
 +
    Debug.Print "*****************  END OF RUN ********************"
 +
    Close #1
 +
   
 +
Exit_Procedure:
 +
    Set fld = Nothing
 +
    Set tbl = Nothing
 +
    Set db = Nothing
 +
    Exit Function
 +
Error_Handler:
 +
    If Err.Number = 3270 Then
 +
        Resume Next
 +
    Else
 +
        MsgBox Err.Number & Err.Description
 +
        Resume Exit_Procedure
 +
    End If
 +
    Resume
 +
End Function
 +
</pre>
== TO DO ==
== TO DO ==
This is a list of tasks we need to do to prepare the most current version of the Export Analysis Utility for public distribution
This is a list of tasks we need to do to prepare the most current version of the Export Analysis Utility for public distribution
-
* Data Integrity Tool - compare with colorectal database version
+
* Longitudinal follow-up process
-
 
+
** Integrate all differences in follow-up process MDB file (UroShared\_CaisisDB\follow up project\CaisisDB.mdb) into Export Analysis Utility
-
* Integrate all differences in follow-up process MDB file (UroShared\_CaisisDB\follow up project\CaisisDB.mdb) into Export Analysis Utility
+
* With fresh install of Caisis 4.0 from caisis.org download, test setup to be sure no linked tables or steps are MSK-specific.
* With fresh install of Caisis 4.0 from caisis.org download, test setup to be sure no linked tables or steps are MSK-specific.

Current revision as of 16:15, 8 September 2008

Translations: italiano


The Export Analysis Utility is a Microsoft Access 2000 (convert to 2003 format?) database that contains many add-on features for Caisis, including.

  • Algorithms for generating analysis datasets for prostate cancer research
  • Longitudinal follow-up process wizard for semi-automated mailouts
  • Data Integrity Tool, a simple browser for looking into every table and field in the database
  • Nomograms, a way to run nomograms on sets of patients
  • Reports, quality assurance, operations and common research reports

Contents

Setup Instructions

  • Create a warehouse copy of Caisis on SQL Server
  • Create a Windows security group with permissions to read data on your Caisis SQL Server warehouse
  • Create a secure network folder with read/write permissions for ONLY the few people who will be gatekeepers for the data. These users will be responsible for ensuring that all data requests meet IRB and privacy board requirements and will work with users to generate datasets.
  • Place the Microsoft Access file (MDB file) for the Export Analysis Utility in this secure network folder.
  • From Export Analysis Utility, open the Linked Table Manager (Tools, Database Utilities, Linked Table Manager) to link all tables to the warehouse copy of Caisis on SQL Server.
  • Localization Issues
    • For Longitudinal Followup will need to modify WHERE clause in the following queries to remove IN() statement or modify to your own OpCaseSurgeon and OpInstitution values: qryFU_NeedLetter_Kidney, qrptFU_LastNephrectomy, qrptFU_LastRPLND, qryFU_NeedLetter_Testis, qryFU_NeedLetter_Prostate, qryFU_NeedLetter_Bladder, qryFU_NeedLetter_LapProstateIMM, qryFU_NeedLetter_Survivorship, qryFU_NeedLetter_Survivorship_Jul08_2005

Features

  • Splash Screen. This form opens automatically when you open the Export Analysis Utility. It displays the location of the MDB file and the database your tables are linked to, and it provides buttons to launch each of the following features.
  • Data Integrity tool. This form is also available in Caisis system admin (Administer the Syste, System Config, Data Integrity). It allows users to browse contents of every table and every field in the MDB file or the linked SQL Server tables in the warehouse. It also allow the user to view lookup codes associated with each field in the database (through Metadata tables) The recode feature is exclusively available through the Caisis system admin tool.
  • Nomograms. Links to web nomograms on MSKCC web site. Also includes functions for calling nomogram library to calculate nomogram predictions for sets of patients in a tables or spreadsheet.
  • Longitudinal Followup. Wizards and partially automated tools to help manage the process of mailouts to patients to find out how they are doing after treatment if they are not coming back regularly to your practice. NOTE: You will need to keep your own local copy of the Signatures table after you have started using the Longitudinal Followup process to maintain custom letters to patients from your local physicians.


  • Tables
    • DistributableObjects. A list of tables, queries, forms, etc that can be publically distributed. Used when preparing a copy of the Export Analysis Utility to post here on Caisis.org. Does not include user-defined or temporary tables, queries and reports.

SAMPLE CODE

  • How do I find out which fields are most/least populated?
Function WriteDBFields()
On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim fldType As String
    Dim outfilename As String
    Dim strTableDescription As String
    Dim strFieldDescription As String
    
    Dim rs As DAO.Recordset
    Dim lngTableRecords As Long
    Dim lngFieldRecords As Long
    Dim lngZLS As Long
    Dim strCount As String
    
    Set db = CurrentDb()
    outfilename = Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & " Data Dictionary.txt"
    
    Debug.Print outfilename
    
    Open outfilename For Output As #1
    
    Write #1, "TableName", "TableDesc", "FieldName", "DataType", "FieldDesc", "TableRecords", "FieldRecords", "ZeroLengthStrings"
    Debug.Print """TableName"",""TableDesc"",""FieldName"",""DataType"",""FieldDesc"",""TableRecords"",""FieldRecords"",""ZeroLengthStrings"""
    
    For Each tbl In db.TableDefs
        
        'To prevent system tables from printing, include: Left(tbl.Name, 4) <> "MSys"  - or for specific tbls only: Left(tbl.Name, 6) = "CSMM_S"
        If Left(tbl.Name, 4) <> "MSys" And tbl.Connect = vbNullString Then
            Set rs = tbl.OpenRecordset(dbOpenSnapshot)
            If rs.EOF Then
                lngTableRecords = 0
            Else
                rs.MoveLast
                lngTableRecords = rs.RecordCount
            End If
            rs.Close
            For Each fld In tbl.Fields
                    strCount = "SELECT [" & tbl.Name & "].[" & fld.Name & "] " _
                    & "FROM [" & tbl.Name & "] " _
                    & "WHERE (([" & tbl.Name & "].[" & fld.Name & "]) Is Not Null);"
                    Set rs = db.OpenRecordset(strCount, dbOpenSnapshot)
                    If rs.EOF Then
                        lngFieldRecords = 0
                    Else
                        rs.MoveLast
                        lngFieldRecords = rs.RecordCount
                    End If
                    rs.Close
                    If fld.Type = 10 Or fld.Type = 12 Then
                        strCount = "SELECT [" & tbl.Name & "].[" & fld.Name & "] " _
                        & "FROM [" & tbl.Name & "] " _
                        & "WHERE (([" & tbl.Name & "].[" & fld.Name & "]) = """");"
                        Set rs = db.OpenRecordset(strCount, dbOpenSnapshot)
                        If rs.EOF Then
                            lngZLS = 0
                        Else
                            rs.MoveLast
                            lngZLS = rs.RecordCount
                        End If
                        rs.Close
                    Else
                        lngZLS = 0
                    End If
                    Debug.Print """" & tbl.Name & """,";
                    strTableDescription = vbNullString
                    strTableDescription = tbl.Properties("Description")
                    Debug.Print """" & strTableDescription & """"
                    Debug.Print """" & fld.Name & """,";
                    Select Case fld.Type
                    Case 1
                        fldType = "Yes/No"
                    Case 10
                        fldType = "Text:" & fld.Size
                    Case 12
                        fldType = "Memo"
                    Case 8, 22, 23
                        fldType = "Date/Time"
                    Case 2
                        fldType = "Number:Byte"
                    Case 3
                        fldType = "Number:Integer"
                    Case 4
                        fldType = "Number:Long"
                    Case 5
                        fldType = "Number:Currency"
                    Case 6
                        fldType = "Number:Single"
                    Case 7
                        fldType = "Number:Double"
                    Case Else ' 9, 11, 15, 17, 19, 20, 21
                        fldType = "Numeric"
                    End Select
                    Debug.Print """" & fldType & """,";
                                        
                    strFieldDescription = vbNullString
                    strFieldDescription = fld.Properties("Description")
                    Debug.Print """" & strFieldDescription & """,";
                    Debug.Print lngTableRecords & ",";
                    Debug.Print lngFieldRecords & ",";
                    Debug.Print lngZLS
                
                Write #1, tbl.Name, strTableDescription, fld.Name, fldType, strFieldDescription, lngTableRecords, lngFieldRecords, lngZLS
                
                Next
        End If
    Next
    
    Debug.Print "*****************  END OF RUN ********************"
    Close #1
    
Exit_Procedure:
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing
    Exit Function
Error_Handler:
    If Err.Number = 3270 Then
        Resume Next
    Else
        MsgBox Err.Number & Err.Description
        Resume Exit_Procedure
    End If
    Resume
End Function

TO DO

This is a list of tasks we need to do to prepare the most current version of the Export Analysis Utility for public distribution

  • Longitudinal follow-up process
    • Integrate all differences in follow-up process MDB file (UroShared\_CaisisDB\follow up project\CaisisDB.mdb) into Export Analysis Utility
  • With fresh install of Caisis 4.0 from caisis.org download, test setup to be sure no linked tables or steps are MSK-specific.
  • Remove Nomograms code and provide DLL and instructions for using library
    • LifeExpectancies
    • NomogramScores
    • PartinScores_2001
  • Remove Segments code and replace with DLL and instructions?
  • Create feature to export and import algorithm settings so that different users and sites can reproduce analysis datasets using same settings on their own Caisis datasets.
  • Create easy to use feature to export and import user defined queries and reports
Personal tools