As many programming languages have come and gone, Structured Query Language is still around and will be... for quite a while. Using SQL (sequel) to develop triggers, stored procedures, and reports to name a few, one is able to extend the functionality of their mission critical data. Call them what you will...'procs', 'sprocs', or 'stored procedures'… to a SQL user they all mean the same. There are those power-users that find them to be of tremendous value and then there are those that take the extreme most opposite position. This post is not to take a position either way or to teach how to write stored procedures but rather to share how one may leverage MS-SQL stored procedures within your Insight Workbench data map designs. I will graciously leave the stored procedure positional debate to those true data architects.
Ok... so how and where can Scribe Insight leverage MS-SQL stored procedures? Let's begin by showing where they can be 'called' for execution via the Insight Workbench application (Fig.1).
Figure 1
As you can see in the above graphic (Fig.1), with the 'Stored Procedures' node selected there are no procedures exposed even if they did exist within your MS-SQL database. For your stored procedures to be exposed there will be the requirement to initially configure your Insight deployment. However before I detail those steps, know that you can still 'call' your stored procedures via the 'Custom Query' button option in your Workbench source and/or target 'configure' dialog (Fig.2) without the need for the initial configuration. Make sure that you preface your stored procedure with the 'exec' command. Using this method would obviously require that you know the procedures name syntax so as to enter it correctly within the SQL Query dialog tab.
Figure 2
For the purposes of reference and clarification, the stored procedure shown throughout my examples has been 'noted' below.
use scribesample
go
create procedure get_currency_xchange_by_type @type varchar(10)='EUR'
as
select * from scribe.currency_rate where from_currency = @type
This procedure simply returns the currency exchange record from the database table that is of 'EUR' type (by default) which can be used as source data for the Workbench data map. This stored procedure was saved under the 'programmability' node within the ScribeSample database.
To follow are the steps to graphically expose your stored procedures (Fig. 3) without requiring the need for you to know the procedure's specific naming syntax. After connecting to your MS-SQL database, you can now visually see your stored procedure/s under the Workbench's primary 'Stored Procedures' node navigation tree. Note that within the 'SQL Query' dialog tab, the stored procedure usage syntax is identical as discussed earlier (i.e. exec
'stored procedure name') in the un-configured mode.
Figure 3
Step #1
It is highly recommended that you backup your database/s before attempting to perform the below.
Within the Program Files\Scribe home directory on your Scribe server, a file named ScribeMetadata.sql exists. Launch/Open this file within SQL Server Management Studio as shown below (Fig.4). The default database being executed against is SCRIBEINTERNAL and if managing your stored procedures here, an edit is required before running the script. That edit is highlighted below (Fig.4) where you need to either comment or delete the 'drop table SCRIBE.KSYNC' statement from the script. If databases other than the SCRIBEINTERNAL are being used, this edit does not need to be made.
Figure 4
Once the ScribeMetadata.sql script has successfully executed against your database, you will now be able to see your stored procedures within the Insight Workbench source and/or configure dialog windows.
Step #2
With your edit complete, open up your Workbench application and connect with either your source and/or target to make the appropriate connection to your stored procedure for data mapping purposes. Note that the executing of the stored procedure on the source will present you with all the fields (Fig.5) that the stored procedure was designed to operate against. With the source stored procedure fields presented to you, you can now data link the specific source fields to your target connection as you would with any other source connection.
Figure 5
Now that the data map has been properly configured, a simple test run will show the results to validate the successful operation of the source stored procedure as well as the data flowing to your target data store. That's all there is to it!
Figure 6
I hope that this has been of value to you and welcome your feedback with regards to your usage and applications when working with stored procedures. For those that have experience in using Workbench stored procedures connecting to an Oracle RDBMS, I encourage you to share your findings.
From all of us here at Scribe Software, we wish you a healthy and prosperous New Year!
Best Regards.
Rich Radaci
