SQL Tip —The Data Request Number

As we’ve discussed before in webinars, white papers and in our SQL training classes, we recommend that all DR-based reporting and extracting be driven by stored procedures. Those stored procedures be used or consumed by either SQL Server Reporting Services reports, or SQL Server Integration Services packages, or Crystal Reports.

This splits the work into two focused areas – data retrieval and then data presentation – and lets you use each “tool” in an optimized way — which is good.

But unlike a classic NPR, your work is now split into two different sets of code, using two (or more) sets of tools, and physically stored in at least three places! And that can get confusing.

One excellent solution to manage this new environment is to implement a Data Request Number (DRN) and some structure around the process of building and deploying reports and extracts.

In the Data Request Number approach, every single data request you receive is given a unique number. This number is tracked, along with key data about who made the request in a centralized way. (For some of our customers, that is a custom web app they built, or an Access database, or an Excel spreadsheet… an example layout of the tracking tool – in Excel – is attached to this tip).

Note that every request is tracked, from every system, even if we don’t build a report or extract in response to the request. And the Data Request Number is just an identifier to let us identify things were created in response to a request – it is not necessarily structured in such a way as to identify a specific module or application – though you could do so.

The DRN Workflow

In the specific case of a data request that is met by building a stored procedure and then an SSIS extract and a matching SSRS report, we would get something like:

• Data Request Number assigned is: M0241 (in this implementation, “M” stands for a MEDITECH report, “K” for Kronos reports, etc.)
• The request is for a BAR-based text-file extract of UB-04 data to be submitted to the state.
• The request includes an error report, showing issues with the data that should be corrected before the final file is created and sent to the state.
• We create one Stored Procedure to get the data from the DR and it gets the DRN embedded in the stored procedure name like so:

• And the stored procedure code you’re keeping in an offline copy of the database object has exactly the same name, with a .sql added:

• Then we use that stored procedure to drive an SSRS report, where the report has multiple sections, pulling out records that meet specific error criteria. We can embed the error criteria either in the sp (adding an Error Flags field) or in the report. The key idea here, however, is that the report .RDL name also includes the DRN:

• When you set the description property of that report, you also embed the DRN, so that it flows over into the report title the end-user sees in Report Manager or Sharepoint:

• Finally, when you create the SQL Server Integration Services package to automate the creation of the formatted text-file, the .dtsx file also embeds the number:

We can now track all of the parts of the response to the request just by the DRN. This makes maintenance vastly easier for the programmer; gives the end-user an easy way to refer to a specific report (“I need a change to the M0241 report”); and lets you positively identify each request and what happened to it in response.

Ah, but wait!, you say. What about database objects that are generic? What if we create a VIEW in the database, for example, to ease reporting? Should they have DRN’s? And we say… no, they do not. They have names like:

zcus.dbo.BAR_CLAIMS_VIEW

And…

BAR_CLAIMS_VIEW.sql

With the exception of a non-generic supporting object. Say you need a SQL function which is specific to one data request, then you might have something like:

M0241_BAR_UB4_CPT_CODE_FN.dtsx

A real-life example of a request-specific function would be a multiple-file/multiple-stored procedure extract set where state-specific CPT Codes needed to be suppressed in the files sent to a specific vendor. Then you could properly build a DRN-number function because it is intimately linked to that request.

Organization of Code

We noted above that you’re keeping an off-line copy of each database object, (because you are, aren’t you?) for the day that something happens to the database and you have to recreate a single object (stored procedure, custom index, custom table, function, view, etc.) without a database restore.

This happens. It’s painful if you haven’t taken some care beforehand…

What we suggest is that you build, in a network share visible to your report developers and DBA’s, a structure like:

\DR
           \Live
                     \Procedures
                     \Functions
                     \Views
                     \Indexes
                     \Reports

And everything that you put into the database you do via a SQL script, which you save into the offline folder first, then run to create (or ALTER, later) the object(s).

When you need to change an object, you change the offline .sql script first, then run it to change the object in the database. Work diligently to keep these two structures in sync! Someday it will save your… well, you will be happy you have it.

Extracts are a bit more complicated, because SSIS wants to create its own folder structure, and that tends to get deep — so deep, in fact, that SSIS can create a folder path you can’t access! Because of that, you want to shorten the path as much as possible.

\Extracts
             \M0241-Bar-Claims
                          \SSIS
                          \SQL
                          \Documentation

The \SSIS folder contains both the .sln, .dtproj and .dtsx files all in one bucket. The \SQL folder, in this case, contains all of the extract’s supporting code. Conceptually, this takes us away from the structure above, where we have more generic folders, but is much easier to manage.

RD and NPR

Nothing prevents you from using the same tracking system for new NPR(s) and RD reports, as you can set their internal name and title. You won’t be storing them in the offline storage area, however, unless you have some scripted process to dump the object versions of those report formats out to disk on a schedule.

Finally…

If you’re just starting with the DR, that’s a great time to implement an organizational system like this.

If you’re migrating to MT6, that’s a great time to implement an organizational system like this.

If you have disorganized folders full of a mass of code, it’s… well, you get the idea!

(end)

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact our NPR report writing team at reportwriting@iatric.com.