The schema of the reporting database may contain stored procedures or functions designed to return a recordset. The procedures may have expected input parameters used in the context of the procedure.
Note:
The ability to import procedures is limited to Sql Server databases.
The Import Procedures wizard allows the System Administrator to import the procedures into the metadata database and manage the results. There are five wizard steps involved in importing procedures into the metadata database.
The initial dialog, “Schema Procedures”, will present a list of stored procedures and functions found in the reporting database. The input parameters of each procedure are also collected and presented when the procedure is highlighted. The initial list of procedures will be filtered based on whether the
procedure contains reserved words that have the potential to modify the reporting database (e.g. insert, delete, drop, etc).
To determine the columns in the returned recordset, each selected procedure will have to be executed.
Based on the data type of the input parameters, if any, default values will be used during the execution.
The default values are 1 for numeric parameters, “a” for strings, and the current date for dates.
Note:
Running the selected stored procedures and functions may take considerable time.
Note:
Columns containing spaces in the column name are not supported in the Procedures Import process. Please review the \Log\ProcedureColumns_Update_Debug_Log.xml file to determine whether specific columns were excluded from the import process.
The second dialog, “Manage Procedures”, will present a list of procedures that have either been run or have already been imported into the metadata. Highlighting the procedure will show the columns available from the procedure. This dialog will allow the System Administrator to manage the procedures.
Procedures may be added, updated or removed based on the interaction with this dialog.
Procedures, both stored procedures and functions, become “data objects” in the Ad Hoc parlance. As with the schema import process, data objects may be given friendly names and descriptions.
Additionally, the data object may be initially hidden from the user community on the “Object Names and Descriptions” dialog.
Similarly, the “Column Names and Descriptions” dialog will allow the System Administrator to provide friendly names and descriptions for the columns returned in the recordset. The columns may also be initially hidden from the user community.
The final Import Procedures dialog, “Finish”, provides a summary of the actions taken on the procedures.
The Import Procedures wizard may be launched by:
The Configuration Wizard as part of creating a new instance by default or through the Advanced Options configuration
Clicking on the Report Database Management action group and then clicking on the Import Procedures action
The following dialog will be presented:
The purpose of the Schema Procedures dialog is to examine the active reporting database and present a list of stored procedures and functions that may be used within Ad Hoc to present data to the user community. The System Administrator can identify the procedures to be imported into the metadata database by enabling the Select checkbox for each procedure.
The initial list of procedures is filtered to exclude those procedure definitions that contain potentially destructive reserved words. The full list of procedures defined in the reporting database can be obtained by enabling the Include Dangerous Procedures checkbox.
Note:
The list of “dangerous” words is included in the <DBKeywords> element (a child of the <AdHoc>
element) in the _Settings.lgx file in the Ad Hoc instance.
The procedures list is also color coded; blue indicates that the procedure was previously imported into the metadata, black indicates that the procedure only exists in the reporting database, orange indicates that the procedure has been previously imported but the metadata and the reporting database
definition do not match, and red indicates that the procedure is only in the metadata database and has likely been removed from the reporting database.
Below the procedures list is a panel that will display information related to the input parameters for the procedure. As rows in the procedure list are highlighted, the parameter panel will change to display the associated input parameters.
The System Administrator should enable the Select check box for each procedure that should be processed through to the next step of the wizard. All displayed procedures may be selected/unselected by clicking on the Select column header. Each procedure will be executed between wizard steps to determine the columns and data types of the columns. When the selected procedure is executed, the Default Value displayed in the lower panel for each parameter will be used.
The System Administrator may set the Default Value by enabling the Select check box on the procedure and then supplying values for each parameter (as well as a Friendly Name). Alternatively, the
procedures may be executed using system default values. The default values are “a” for string/text parameters, 1 for numeric parameters, and the current date for date parameters. These system defaults may be changed by clicking on the Options button.
A default value and friendly name for each parameter are required for each selected procedure before the wizard will allow the user to go to the next wizard step.
Clicking on the Options button also allows the System Administrator to set the naming convention for the Friendly Names.
If the Use Default Options is enabled on the main dialog, the default values shown will be used for the input parameters when the procedure is executed.
The friendly names for the input parameters will also follow the selected/specified convention. The default will be to insert a space on case change. That means that an input parameter called CustomerID would be stored with “Customer ID” as the friendly name. To retain the naming convention found in the reporting database, the Use Database Names option should be enabled.
The procedure/function definition may be reviewed by clicking on the View Source button.
Clicking on the Next button will execute the selected procedures and display the Manage Procedures dialog.
If errors are encountered when executing the selected procedures, an error message will be issued and the user will be directed back to the initial dialog to resolve the problems.
All of the procedures that encountered problems will be flagged with a red asterisk.
For a detailed explanation of the error, click on the procedure row in the list and click on the View Error button. Following is an example of an error dialog:
Once the selected procedures have executed successfully, the Manage Procedures dialog will be
presented. This dialog allows the System Administrator to add, remove and update the Ad Hoc metadata database with procedure/function information.
The three components of the dialog are the procedures list, column information panel, and action buttons. The basic usage scenario is to highlight one or more procedures and click on an action button.
This process can be repeated and the Changes column will show the expected action for the procedure and columns.
The list of procedures and columns is color coordinated.
Items in blue indicate that the information has been previously imported. There is no determination made related the validity of the procedure since the procedure was not executed as part of the wizard process. It simply means that the procedure exists in the metadata.
Items in green indicate that the procedure was executed and the schema in the metadata matches the schema of the procedure in the reporting database.
Items in black indicate that the procedure was executed and there is no corresponding schema in the metadata database.
Items in orange indicate that the procedure was executed and that there are differences between the schema in the metadata database and the reporting database. This usually indicates that the procedure was modified in the reporting database after it was imported into the metadata database.
Items in red indicate that the procedure is only found in the metadata database. This indicates that the procedure was removed or renamed in the reporting database schema.
After setting the desired actions on the procedures, click on the Next button.
The Object Names and Descriptions dialog will be presented that allows the System Administrator to set the user friendly name, description, and visibility of the procedures/functions (data objects in Ad Hoc parlance)
The basic usage scenario is to review and modify the user friendly name and description as necessary for each data object in the list.
The list of data objects may be filtered by entering search criteria in the Filter text area. The Filter value will be applied to the data object list according to the current logic specified in the Options dialog. By
default the Filter value is applied with a “contains” operator to the Object Name, User Friendly Name and Description. Click on the Options but to apply different logic for the filter.
User friendly names may be automatically generated according to the rules defined in the
Auto-generate dialog. Highlight the data objects to be affected and click on the Auto-Auto-generate Names button to display the following dialog:
After specifying the naming convention for the friendly names, click on the OK button to apply the rules to the highlighted objects.
To reset the information provided for the data objects, highlight the objects or any of the editable areas and click on the Clear Changes button.
Click on the Next button to continue to the Column Names and Descriptions dialog.
The Column Names and Descriptions dialog is presented. Creation of Friendly Labels is required as part of the configuration process. Specifying column Descriptions is optional.
Friendly Labels and Descriptions may be manually added by entering information in the appropriate column in the grid. Every column must have a Friendly Label. Columns may also be hidden from end-users by checking the related Hide checkboxes.
For manual entry of Friendly Labels for the columns, clicking on the object name in the top panel will show only the related columns in the bottom panel. For the complete list of columns in the bottom panel click on the All Objects label in the top panel.
The dialog provides a mechanism to generate the Friendly Label for the highlighted columns. Click on the box in the upper left corner of the grid in the bottom panel to highlight all of the columns presented in the panel. Click on the Auto-generate Names button to let the MC set the names automatically.
The Auto-generate Friendly names dialog will be presented. The two most frequently used options are
“Insert spaces at case change” and “Use database names”.
Click on the checkbox for an option, provide any additional information necessary, and click on the OK button.
After reviewing the generated friendly object names, entering Descriptions as necessary, click on the Next button to set the names and descriptions of the columns.
The most common usage of the Column Names and Descriptions dialog is as follows:
1) Click on All Objects to populate the lower panel with all columns
2) Click on the box in the upper left corner of the grid in the bottom panel to highlight all of the columns
3) Click on the Auto-generate Names button to show the automated naming options 4) Click on the OK button
5) Click on the Next button Note:
The dialog also provides a simple filter that may assist in locating columns in larger schemas.
Enter search criteria into the Filter text box and the list will be adjusted based on the filter criteria.
Click on the Options button to set the filter criteria logic.
Click on the Clear button to reset the Filter text box and show all columns in the grid related to the highlighted object.
Click on the upper left corner of the grid in the bottom panel to highlight all of the columns and then click on the Auto-generate Names button to let the MC set the names automatically.
Note:
Columns containing spaces in the column name are not supported in the Procedures Import process. Please review the \Log\ProcedureColumns_Update_Debug_Log.xml file to determine whether specific columns were excluded from the import process.
The Finish dialog in the Procedures Import wizard displays a summary of the actions taken. As an example:
Click on the Finish button to close the Procedures Import wizard.