Difference between revisions of "Standardising Data Analysis and Information Reporting"

From IMSMA Wiki
Jump to: navigation, search
Line 1: Line 1:
 
{{TOC right}}
 
{{TOC right}}
 
__NOEDITSECTION__
 
__NOEDITSECTION__
The primary purpose of any information system must be to support operational use of the information it collects. That’s why IMSMA<sup>NG</sup> was designed with a robust reporting and analysis component that supports various reporting options. These options include:
+
The primary purpose of any information system must be to support operational use of the information it collects. That’s why {{IMSMANG}} was designed with a robust reporting and analysis component that supports various reporting options. These options include:
  
 
<center>
 
<center>
 
{| class="wikitable" width="800"
 
{| class="wikitable" width="800"
 
|-
 
|-
| align="center" colspan="5" | '''Comparison of IMSMA<sup>NG</sup> Reporting Options'''
+
| align="center" colspan="5" | '''Comparison of {{IMSMANG}} Reporting Options'''
 
|-
 
|-
 
| width="20pt" |  
 
| width="20pt" |  
Line 21: Line 21:
 
|-
 
|-
 
| align="left" | '''Uses'''  
 
| align="left" | '''Uses'''  
| align="left" | Navigating data in IMSMA<sup>NG</sup>  
+
| align="left" | Navigating data in {{IMSMANG}}>  
 
General summaries of pre-provided data
 
General summaries of pre-provided data
 
| align="left" |  
 
| align="left" |  
Line 32: Line 32:
 
*Pivot tables and charts based on one item
 
*Pivot tables and charts based on one item
 
| align="left" |  
 
| align="left" |  
*Linking or exporting IMSMA<sup>NG</sup> data for use in external tools
+
*Linking or exporting {{IMSMANG}} data for use in external tools
*Building customised reporting tools for IMSMA<sup>NG</sup> data
+
*Building customised reporting tools for {{IMSMANG}} data
 
*Linking data to ArcGIS Desktop, Crystal Reports and other tools
 
*Linking data to ArcGIS Desktop, Crystal Reports and other tools
 
|-
 
|-
Line 47: Line 47:
 
*Supports charts
 
*Supports charts
 
| align="left" |  
 
| align="left" |  
*Easiest way to get IMSMA<sup>NG</sup> data into Excel
+
*Easiest way to get {{IMSMANG}} data into Excel
 
*Can manipulate data in common applications
 
*Can manipulate data in common applications
 
| align="left" |  
 
| align="left" |  
Line 70: Line 70:
  
 
==Basic Reporting==__NOEDITSECTION__
 
==Basic Reporting==__NOEDITSECTION__
IMSMA<sup>NG</sup> provides several reporting options that allow users to produce basic reports directly from IMSMA<sup>NG</sup>. These include printing data entry forms entered into the system, maps from the main navigation window and item summaries. Any of these reports can be sent directly to a printer or to an intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.
+
{{IMSMANG}} provides several reporting options that allow users to produce basic reports directly from {{IMSMANG}}. These include printing data entry forms entered into the system, maps from the main navigation window and item summaries. Any of these reports can be sent directly to a printer or to an intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.
  
IMSMA<sup>NG</sup> also includes a Map Layout manager that allows users to define map layouts and print high-quality maps with IMSMA<sup>NG</sup> data. Information managers can define map layout templates that include scale bars, annotations and legends or use more sophisticated tools like ArcGIS Desktop to prepare more complicated map layouts for displaying IMSMA<sup>NG</sup> data.
+
{{IMSMANG}} also includes a Map Layout manager that allows users to define map layouts and print high-quality maps with {{IMSMANG}} data. Information managers can define map layout templates that include scale bars, annotations and legends or use more sophisticated tools like ArcGIS Desktop to prepare more complicated map layouts for displaying {{IMSMANG}} data.
  
 
[[Image:AdminGuide_ExampleMapLayoutTemplate.png|center|500px|''Example of a Map Layout Template'']]
 
[[Image:AdminGuide_ExampleMapLayoutTemplate.png|center|500px|''Example of a Map Layout Template'']]
Line 80: Line 80:
  
 
{{note|
 
{{note|
* Map layouts can be printed and scaled to any size paper. This is a simple way of producing IMSMA<sup>NG</sup> maps without needing additional GIS software.
+
* Map layouts can be printed and scaled to any size paper. This is a simple way of producing {{IMSMANG}} maps without needing additional GIS software.
* Map layout templates can be prepared and distributed to individual IMSMA<sup>NG</sup> clients, allowing information managers to prepare templates based on user preference or functional need.
+
* Map layout templates can be prepared and distributed to individual {{IMSMANG}} clients, allowing information managers to prepare templates based on user preference or functional need.
 
}}  
 
}}  
  
Line 90: Line 90:
  
 
==Embedded Reporting==__NOEDITSECTION__
 
==Embedded Reporting==__NOEDITSECTION__
IMSMA<sup>NG</sup> supplements the basic reporting options available to all users with several more sophisticated reporting tools that are embedded within the system. In addition to reporting on individual elements, these embedded tools allow for statistical and aggregate reporting of data within the database. Instead of reporting the contents of a single report, statistical reporting allows information managers to produce reports about multiple items and to use functions such as sum, average and count to summarize data.  
+
{{IMSMANG}} supplements the basic reporting options available to all users with several more sophisticated reporting tools that are embedded within the system. In addition to reporting on individual elements, these embedded tools allow for statistical and aggregate reporting of data within the database. Instead of reporting the contents of a single report, statistical reporting allows information managers to produce reports about multiple items and to use functions such as sum, average and count to summarise data.  
  
There are two options for embedded reporting: iReports and data export. Both functions rely on IMSMA<sup>NG</sup> searches to filter data. Using iReports, information managers can build reporting templates to visualize data in a specific and repeatable format. Using data export, managers can export search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysis.
+
There are two options for embedded reporting: iReports and data export. Both functions rely on {{IMSMANG}} searches to filter data. Using iReports, information managers can build reporting templates to visualise data in a specific and repeatable format. Using data export, managers can export search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysis.
  
 
===iReports===__NOEDITSECTION__
 
===iReports===__NOEDITSECTION__
With the iReport tool, information managers have access to an entire suite of report generation functions. iReport provides the ability to design report templates from the desired data elements; add page layout and formatting information including images, colours and text; and import the reports into IMSMA<sup>NG</sup> for data entry personnel to run. Information managers can also design complex reports to summarize data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into IMSMA<sup>NG</sup>, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with the complexity of designing reports while traditional users simply have to select a report (and any preceding search) and run it.
+
With the iReport tool, information managers have access to an entire suite of report generation functions. iReport provides the ability to design report templates from the desired data elements; add page layout and formatting information including images, colours and text; and import the reports into {{IMSMANG}} for data entry personnel to run. Information managers can also design complex reports to summarise data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into {{IMSMANG}}, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with the complexity of designing reports while traditional users simply have to select a report (and any preceding search) and run it.
 
   
 
   
 
Typical uses for iReport include producing reports commonly required by multiple users within a programme. These reports can include:
 
Typical uses for iReport include producing reports commonly required by multiple users within a programme. These reports can include:
Line 112: Line 112:
 
</div>  
 
</div>  
  
In the first example, no search parameter is applied and all data in IMSMA<sup>NG</sup> is used in the report. But by adding a search parameter, for example, limiting this to land whose status is Open, a different report is generated.  
+
In the first example, no search parameter is applied and all data in {{IMSMANG}} is used in the report. But by adding a search parameter, for example, limiting this to land whose status is Open, a different report is generated.  
  
 
[[Image:AdminGuide_ReportUsingSearchParameters.png|center|500px|''Report Example Using a Search Parameter'']]
 
[[Image:AdminGuide_ReportUsingSearchParameters.png|center|500px|''Report Example Using a Search Parameter'']]
Line 121: Line 121:
 
Moreover, if the land are further limited to minefields (excluding UXO and battle areas), another report is generated, all using the same template. In this way, information managers can build templates and searches to fulfil multiple reporting needs.
 
Moreover, if the land are further limited to minefields (excluding UXO and battle areas), another report is generated, all using the same template. In this way, information managers can build templates and searches to fulfil multiple reporting needs.
  
Reports created with iReport can be printed directly within IMSMA<sup>NG</sup> or saved to an intermediate format such as .RTF, .PDF or .XLS for electronic transmission or additional editing or inclusion in other reports. iReport supports the inclusion of charts, images and headers and footers, and it acts as a complete reporting package similar to Crystal Reports.
+
Reports created with iReport can be printed directly within {{IMSMANG}} or saved to an intermediate format such as .RTF, .PDF or .XLS for electronic transmission or additional editing or inclusion in other reports. iReport supports the inclusion of charts, images and headers and footers, and it acts as a complete reporting package similar to Crystal Reports.
  
 
{{HowTo's
 
{{HowTo's
Line 127: Line 127:
 
}}
 
}}
 
===Data Export===__NOEDITSECTION__
 
===Data Export===__NOEDITSECTION__
IMSMA<sup>NG</sup> also provides data export functionality designed to allow users to export data to external tools such as Microsoft Excel for additional reporting and analysis. This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to format and present data. Users access this functionality by performing a search in IMSMA<sup>NG</sup> and then exporting the results to .CSV format. Users can choose which columns to export, and the data can be manipulated in any external tool that supports .CSV.
+
{{IMSMANG}} also provides data export functionality designed to allow users to export data to external tools such as Microsoft Excel for additional reporting and analysis. This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to format and present data. Users access this functionality by performing a search in {{IMSMANG}} and then exporting the results to .CSV format. Users can choose which columns to export, and the data can be manipulated in any external tool that supports .CSV.
  
Because data export is initiated with a search, it provides a flexible approach for getting data to an external tool. However, once the data is used in a tool outside of IMSMA<sup>NG</sup>, it is impossible to control how this data is manipulated which may limit the consistency of reports produced in this way.  
+
Because data export is initiated with a search, it provides a flexible approach for getting data to an external tool. However, once the data is used in a tool outside of {{IMSMANG}}, it is impossible to control how this data is manipulated which may limit the consistency of reports produced in this way.  
  
{{note|IMSMA<sup>NG</sup> searches return information on only one item at a time, and data export is limited to the data returned by the search. Data from linked items (such as victim data linked to accidents) is not available for export}}
+
{{note|{{IMSMANG}} searches return information on only one item at a time, and data export is limited to the data returned by the search. Data from linked items (such as victim data linked to accidents) is not available for export}}
  
 
{{HowTo's
 
{{HowTo's
Line 138: Line 138:
  
 
==External Reporting Tools==__NOEDITSECTION__
 
==External Reporting Tools==__NOEDITSECTION__
IMSMA<sup>NG</sup> also allows information managers to connect advanced reporting tools such as Crystal Reports or ArcGIS Desktop directly to its relational database for highly advanced report generation. With this capability, information managers can use custom reporting solutions to connect with IMSMA<sup>NG</sup>, allowing for complete control of IMSMA<sup>NG</sup> outputs. Or, managers can modify existing reporting solutions to connect to IMSMA<sup>NG</sup>, which leverages technology already implemented in the programme and limits the need for retraining.  
+
{{IMSMANG}} also allows information managers to connect advanced reporting tools such as Crystal Reports or ArcGIS Desktop directly to its relational database for highly advanced report generation. With this capability, information managers can use custom reporting solutions to connect with {{IMSMANG}}, allowing for complete control of {{IMSMANG}} outputs. Or, managers can modify existing reporting solutions to connect to {{IMSMANG}}, which leverages technology already implemented in the programme and limits the need for retraining.  
  
{{note|Using external reporting tools is a complex activity requiring an advanced understanding of SQL, reporting tools and the IMSMA<sup>NG</sup> data model}}
+
{{note|Using external reporting tools is a complex activity requiring an advanced understanding of SQL, reporting tools and the {{IMSMANG}} data model}}
  
 
===Connecting External Reporting Tools===__NOEDITSECTION__
 
===Connecting External Reporting Tools===__NOEDITSECTION__
The process for connecting external reporting tools is essentially the same for all tools. External tools connect to the IMSMA<sup>NG</sup> relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for MySQL. When this is complete, information managers can connect any ODBC-compliant tool to the IMSMA<sup>NG</sup> database, including reporting tools, other database packages and SQL management tools. Examples include:
+
The process for connecting external reporting tools is essentially the same for all tools. External tools connect to the {{IMSMANG}} relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for MySQL. When this is complete, information managers can connect any ODBC-compliant tool to the IM{{IMSMANG}} database, including reporting tools, other database packages and SQL management tools. Examples include:
  
 
Reporting tools and  
 
Reporting tools and  
Line 161: Line 161:
 
*Heidi SQL
 
*Heidi SQL
  
When connecting an external reporting tool to IMSMA<sup>NG</sup>, it is recommended that information managers build database views within the database to do any necessary data transformations. While it is possible to directly import or connect IMSMA<sup>NG</sup> database tables in external tools and perform transformations within the external tools, the recommended method is to do transformations within the IMSMA<sup>NG</sup> database using database views and then import the resulting views into the external tools.
+
When connecting an external reporting tool to {{IMSMANG}}, it is recommended that information managers build database views within the database to do any necessary data transformations. While it is possible to directly import or connect {{IMSMANG}} database tables in external tools and perform transformations within the external tools, the recommended method is to do transformations within the IMS{{IMSMANG}} database using database views and then import the resulting views into the external tools.
  
 
{{note|Building database views to transform data before importing it into other tools significantly reduces the performance hit associated with connecting external tools}}
 
{{note|Building database views to transform data before importing it into other tools significantly reduces the performance hit associated with connecting external tools}}
  
 
===Building Database Views===__NOEDITSECTION__
 
===Building Database Views===__NOEDITSECTION__
Building database views is the primary way of transforming data to support external reporting. Information managers can use SQL editing tools to build and save database queries as views that can be used to format data for easier reporting. Using database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis tools. Database views act like database tables and can be imported or queried from external tools. Moreover, views automatically refresh as data is added to IMSMA<sup>NG</sup> and provide an up-to-date data source for other applications.
+
Building database views is the primary way of transforming data to support external reporting. Information managers can use SQL editing tools to build and save database queries as views that can be used to format data for easier reporting. Using database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis tools. Database views act like database tables and can be imported or queried from external tools. Moreover, views automatically refresh as data is added to {{IMSMANG}} and provide an up-to-date data source for other applications.
  
Using database views, information managers can perform statistical and other operations on the IMSMA<sup>NG</sup> set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined with search criteria to provide a highly precise mechanism for providing data to reports. For example, information managers can create a view that returns the name of each clearance and a sum of the hours worked, AP mines found and area cleared on all progress reports linked to each clearance.  
+
Using database views, information managers can perform statistical and other operations on the {{IMSMANG}} set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined with search criteria to provide a highly precise mechanism for providing data to reports. For example, information managers can create a view that returns the name of each clearance and a sum of the hours worked, AP mines found and area cleared on all progress reports linked to each clearance.  
  
 
CL-2345 1,920 4 20 1
 
CL-2345 1,920 4 20 1
Line 200: Line 200:
 
</center>
 
</center>
  
This view could then be pasted into Excel, linked with additional IMSMA<sup>NG</sup> data or external data or linked with a map to provide additional analysis. As such, database views provide a powerful way of formatting and analysing IMSMA<sup>NG</sup> data.
+
This view could then be pasted into Excel, linked with additional {{IMSMANG}} data or external data or linked with a map to provide additional analysis. As such, database views provide a powerful way of formatting and analysing {{IMSMANG}} data.
  
 
{{note|
 
{{note|
* Database views created in IMSMA<sup>NG</sup> are backed up by the IMSMA<sup>NG</sup> backup functionality. However, it is important to correctly set the database permissions on views or it will cause problems when restoring the databases.
+
* Database views created in {{IMSMANG}} are backed up by the {{IMSMANG}} backup functionality. However, it is important to correctly set the database permissions on views or it will cause problems when restoring the databases.
 
* Information managers should maintain separate files containing the view creation scripts so that they can be quickly and easily restored or modified. Using the CREATE OR UPDATE VIEW syntax from SQL is an easy way to build scripts for creating or updating views if they already exist.
 
* Information managers should maintain separate files containing the view creation scripts so that they can be quickly and easily restored or modified. Using the CREATE OR UPDATE VIEW syntax from SQL is an easy way to build scripts for creating or updating views if they already exist.
 
}}
 
}}
  
 
{{NavBox Getting started with IMSMA}}
 
{{NavBox Getting started with IMSMA}}

Revision as of 23:10, 5 October 2013

The primary purpose of any information system must be to support operational use of the information it collects. That’s why IMSMANG was designed with a robust reporting and analysis component that supports various reporting options. These options include:

Comparison of IMSMANG Reporting Options
Basic iReport Data export External reporting tool
Description Includes printing data entry forms, summaries and maps Includes building iReport templates for more sophisticated reporting as well as exporting data for use in external tools such as Microsoft Excel Includes exporting data to CSV format for use in Excel as well as cutting and pasting from tables. The most complicated kind of reporting where users connect to backend databases using SQL, Crystal Reports, ArcGIS Desktop, etc., to perform queries and generate reports
Uses Navigating data in IMSMANG>

General summaries of pre-provided data

  • Lists and summary reports of single items
  • Simple cross tabs
  • Monthly progress reports for internal consumption
  • Any report where the data is more important than the formatting
  • Manipulating data using external tools
  • Pivot tables and charts based on one item
  • Linking or exporting IMSMANG data for use in external tools
  • Building customised reporting tools for IMSMANG data
  • Linking data to ArcGIS Desktop, Crystal Reports and other tools
Benefits
  • Fast and easy to use
  • Requires little or no training
  • Can limit data using searches
  • Can be preformatted and available on a network
  • Backed up and stored
  • Users can simply run these reports as opposed to building them
  • Supports charts
  • Easiest way to get IMSMANG data into Excel
  • Can manipulate data in common applications
  • Most powerful and customisable way of accessing data for reports
  • Can manipulate data in advanced or custom reporting applications
  • Can be linked with external databases
Challenges
  • Reports are simplistic
  • Limited to printing and reports provided with the software. No customisation.
  • Limited customisation of resulting data (formats, conditional display, etc.)
  • Not ideal for incorporating multiple reports together within a larger report
  • Limited customisation of resulting data (formats, conditional display, etc.)
  • Not ideal for incorporating multiple reports together within a larger report
  • Requires expert level understanding of SQL

Basic Reporting

IMSMANG provides several reporting options that allow users to produce basic reports directly from IMSMANG. These include printing data entry forms entered into the system, maps from the main navigation window and item summaries. Any of these reports can be sent directly to a printer or to an intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.

IMSMANG also includes a Map Layout manager that allows users to define map layouts and print high-quality maps with IMSMANG data. Information managers can define map layout templates that include scale bars, annotations and legends or use more sophisticated tools like ArcGIS Desktop to prepare more complicated map layouts for displaying IMSMANG data.

Example of a Map Layout Template

Example of a Map Layout Template

Note.jpg
  • Map layouts can be printed and scaled to any size paper. This is a simple way of producing IMSMANG maps without needing additional GIS software.
  • Map layout templates can be prepared and distributed to individual IMSMANG clients, allowing information managers to prepare templates based on user preference or functional need.

Embedded Reporting

IMSMANG supplements the basic reporting options available to all users with several more sophisticated reporting tools that are embedded within the system. In addition to reporting on individual elements, these embedded tools allow for statistical and aggregate reporting of data within the database. Instead of reporting the contents of a single report, statistical reporting allows information managers to produce reports about multiple items and to use functions such as sum, average and count to summarise data.

There are two options for embedded reporting: iReports and data export. Both functions rely on IMSMANG searches to filter data. Using iReports, information managers can build reporting templates to visualise data in a specific and repeatable format. Using data export, managers can export search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysis.

iReports

With the iReport tool, information managers have access to an entire suite of report generation functions. iReport provides the ability to design report templates from the desired data elements; add page layout and formatting information including images, colours and text; and import the reports into IMSMANG for data entry personnel to run. Information managers can also design complex reports to summarise data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into IMSMANG, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with the complexity of designing reports while traditional users simply have to select a report (and any preceding search) and run it.

Typical uses for iReport include producing reports commonly required by multiple users within a programme. These reports can include:

  • clearance: monthly progress reporting
  • education: number of people trained by type of training
  • land: sum of hazardous area by province or type of land
  • accidents: number of accidents by type or by province
  • data quality: number and list of land with incorrect status

Because iReport templates are initiated with a search, they provide a scalable reporting solution that allows one template to be developed for many reports. For example, a report template that shows number of land and size of land by province can produce different reports depending on the search parameters.

Report Example Using No Search Parameters

Report Example Using No Search Parameters

In the first example, no search parameter is applied and all data in IMSMANG is used in the report. But by adding a search parameter, for example, limiting this to land whose status is Open, a different report is generated.

Report Example Using a Search Parameter

Report Example Using a Search Parameter

Moreover, if the land are further limited to minefields (excluding UXO and battle areas), another report is generated, all using the same template. In this way, information managers can build templates and searches to fulfil multiple reporting needs.

Reports created with iReport can be printed directly within IMSMANG or saved to an intermediate format such as .RTF, .PDF or .XLS for electronic transmission or additional editing or inclusion in other reports. iReport supports the inclusion of charts, images and headers and footers, and it acts as a complete reporting package similar to Crystal Reports.

Data Export

IMSMANG also provides data export functionality designed to allow users to export data to external tools such as Microsoft Excel for additional reporting and analysis. This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to format and present data. Users access this functionality by performing a search in IMSMANG and then exporting the results to .CSV format. Users can choose which columns to export, and the data can be manipulated in any external tool that supports .CSV.

Because data export is initiated with a search, it provides a flexible approach for getting data to an external tool. However, once the data is used in a tool outside of IMSMANG, it is impossible to control how this data is manipulated which may limit the consistency of reports produced in this way.

Note.jpg IMSMANG searches return information on only one item at a time, and data export is limited to the data returned by the search. Data from linked items (such as victim data linked to accidents) is not available for export

External Reporting Tools

IMSMANG also allows information managers to connect advanced reporting tools such as Crystal Reports or ArcGIS Desktop directly to its relational database for highly advanced report generation. With this capability, information managers can use custom reporting solutions to connect with IMSMANG, allowing for complete control of IMSMANG outputs. Or, managers can modify existing reporting solutions to connect to IMSMANG, which leverages technology already implemented in the programme and limits the need for retraining.

Note.jpg Using external reporting tools is a complex activity requiring an advanced understanding of SQL, reporting tools and the IMSMANG data model

Connecting External Reporting Tools

The process for connecting external reporting tools is essentially the same for all tools. External tools connect to the IMSMANG relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for MySQL. When this is complete, information managers can connect any ODBC-compliant tool to the IMIMSMANG database, including reporting tools, other database packages and SQL management tools. Examples include:

Reporting tools and

  • Crystal Reports
  • ArcGIS Desktop
  • iReport
  • Microsoft Excel

Database packages

  • Microsoft Access
  • OpenOffice Base
  • Microsoft SQL Server
  • Oracle

SQL management tools

  • MySQL Query Browser
  • Navicat
  • TOAD SQL
  • Heidi SQL

When connecting an external reporting tool to IMSMANG, it is recommended that information managers build database views within the database to do any necessary data transformations. While it is possible to directly import or connect IMSMANG database tables in external tools and perform transformations within the external tools, the recommended method is to do transformations within the IMSIMSMANG database using database views and then import the resulting views into the external tools.

Note.jpg Building database views to transform data before importing it into other tools significantly reduces the performance hit associated with connecting external tools

Building Database Views

Building database views is the primary way of transforming data to support external reporting. Information managers can use SQL editing tools to build and save database queries as views that can be used to format data for easier reporting. Using database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis tools. Database views act like database tables and can be imported or queried from external tools. Moreover, views automatically refresh as data is added to IMSMANG and provide an up-to-date data source for other applications.

Using database views, information managers can perform statistical and other operations on the IMSMANG set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined with search criteria to provide a highly precise mechanism for providing data to reports. For example, information managers can create a view that returns the name of each clearance and a sum of the hours worked, AP mines found and area cleared on all progress reports linked to each clearance.

CL-2345 1,920 4 20 1

Clearance ID Area cleared AP mines found Hours worked Number of progress reports
CL-1022 23,400 45 120 4
CL-1239 22,330 42 160 6
CL-2345 1,920 4 20 1

This view could then be pasted into Excel, linked with additional IMSMANG data or external data or linked with a map to provide additional analysis. As such, database views provide a powerful way of formatting and analysing IMSMANG data.

Note.jpg
  • Database views created in IMSMANG are backed up by the IMSMANG backup functionality. However, it is important to correctly set the database permissions on views or it will cause problems when restoring the databases.
  • Information managers should maintain separate files containing the view creation scripts so that they can be quickly and easily restored or modified. Using the CREATE OR UPDATE VIEW syntax from SQL is an easy way to build scripts for creating or updating views if they already exist.

Template:NavBox Getting started with IMSMA