A Model for Guiding Consumption of Information

Health care organizations have arrived late to the use of other tools that retrieve data, highlight information, and afford interactivity.

Look at the tools available from Microsoft, listed in the order of power and degree of interactivity.

Reports (Reporting Services) variants:

• Static Reports
• Parameter Driven
• Simulated Drill Down

Report Builder

Analysis Services

Data Mining Toolkit

The Reporting Cycle

Hospital IT outfits start with Reporting Services. Often, however, the crush to meet the endless need results in the cutural assumption that the need must be met with "reports": They become an end unto themselves.

Reports require that a programmer/designer/business analyst be employed to design and vet the structures. The organization simply can't produce fast enough to meet the expectations of the subject matter experts. Many of these experts wish to "surf" on the data to uncover hidden patterns - but the development cycle simply does not permit reports to be tailored quickly enough.

Some of the other tools in the above arsenal permit the experts to develop their own tools to consume information. As IT managers, when and how do you unleash each tool? An overall framework is necessary.

One taxonomy, the CIF (Corporate Information Factory), that is widespread was published several years ago by Claudia Imhoff and her colleagues. The groups briefly are:

1. Farmers (financial analysts) have well defined requirements. Queries are clear and concise. Data marts and OLAP are often used.
2. Tourist (executives) search large amounts of data to anticipate and respond to events. Data marts and OLAP are often used.
3. Operators require current detailed information on a scheduled bases. Reports largely suffice to meet this need in the health care vertical.
4. Explorers search for new patterns and relationships. Random queries are often generated. Results can lead to observations that range from the mundane to the spectacular. Ad hoc tools are employed.

Each group can be matched up with each retreival tools. Each determines the type of load on the database servers (ad hoc vs. schedule reports). In the early days of SQL 7, Microsoft published a large wall poster with this classification.

Further information on the Corporate Information Factory can be found at http://download.101com.com/pub/tdwi/files/Critical_Shift_to_Flex_BI_Imhoff.pdf .

Reporting Services: Tip # 1

Establish a standard that all Data Connections, especially Shared Data Sources, must include the application name parameter of the connection string. An example is below:

Data Source=epicsql;Initial Catalog=Reporting_Srvs;application name=SDS_Clarity_PROD_Reporting_Srvs;

Our standard calls for placement of the name of the Data Source as the application name.

Why? It will come in handy when the DBA monitors activity generated by the RS server against the SQL server.

The image shows the Processes option of the new Current Activity window in SQL Manager. Now the DBA can see clearly activity has been sent by a specific Data Source of the Reporting Services server. This helps to troubleshoot performance problems.

The Developer’s Platform

What’s the best permission scheme that permits developers to fabricate reports, but protects a clinical database?

Fine-tuning the permissions under SQL 2000 was difficult. As mentioned in a previous post, the Clarity module shipped prior to SQL 2005, when permissions became more granular. Also, Clarity was developed with two other vendors’ databases also in mind.

We needed a solution long before our upgrade to SQL 2008. In the following, I’ll describe the steps we took under SQL 2000 and those that we took to extend the capability under SQL 2008.

A new database on the same server, Reporting_SRVS, was created. Two groups were created in the Active Directory, RptDev and RptBrowse. The latter was used extensively in the permissions of the production Reporting Services server.

In the main vendor’s database, Clarity, RptBrowse and RptDev were made members of the db_datareader role. RptBrowse was made a member of the db_denywriter role.

In the Reporting_SRVS database, RptDev was made a member of the db_owner role.

The developers created all views in the Reporting_SRVS database, referring with a three part name to an object in the Clarity database, for example, a view Report_SRVS.dbo.TestView was created with the command Select fld1,fld2 FROM Clarity.dbo.PAT_ENC.

Once we were under SQL 2008, I searched repeatedly for references on this topic. It was only long afterwards that one pertinent reference was found: SQL Server Best Practices – Implementation of Database Object Schemas, at http://msdn.microsoft.com/en-us/library/dd283095.aspx . However, Epic had not created the Clarity database using multiple schemas and we did not wish to open that can of digital worms. Fortunately, SQL 2008 permitted a number of enhancements to be added. The following assignments were made after the upgrade:

For the MSDB database, RptDev made a role member of the relatively new DatabaseMailUserRole. This allowed the report developers to craft TSQL scripts to generate email. Furthermore, RptDev was made a member of the SQLAgentReaderRole, to allow the developers to monitor the SQL Jobs.

SHOWPLAN permissions were granted to RptDev on the Clarity database (this permission was implicitly assigned to Reporting_SRVS database due to the db_owner role permissions). The granting of SHOWPLAN does create a possible security hole. Please read the caveats on Books on Line.

One last problem remained. The developers gradually transited from placing SQL batches to stored procedures in their Reporting Services reports. They soon discovered that they could run a stored procedure in SQL Manager, but it would not run in a Reporting Services report. The developers were instructed to grant Execute permissions for each stored procedure specifically to the account in Reporting Services authorized to access the data. We decided not to implement an automated assignment (as outlined in http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ ).


Now the developers have nearly complete rights to their own developed objects in their database, yet the data is secured in the vendor’s database. Only vendor objects (and a few custom indexes) are in the Clarity database. The developers can monitor SQL jobs pertinent to the databases (although they can’t modify any). They can also examine execution plans and become aware early in the development process of queries that may perform poorly. SQL Manager 2008 also suggests indexes that may be missing and the developers are instructed to keep a log of these for the DBA to review.

There is one downside to this approach. We’ve been informed that Indexed views are not supported spanning one database to another but I haven’t confirmed this by attempting to create one. The benefits far outweigh the problems, however and this has worked well for some time.

Clarity Corner: Tip # 1

First, a brief description of the Clarity module used in the Epic Systems electronic medical record system. Clarity's primary task is to read data from the clinical information stored in over 800 files in the Unix environment and transform this data into a relational format. Currently, Clarity supports three databases: Oracle, Teradata, and SQL Server. There are over 6500 relational tables and data easily grows rapidly.

Hospital environments are inherently conservative. Many will not contemplate SQL 2008, even though Service Pack 1 became available last month; SQL 2005 and even SQL 2000 are mainstream.

However, Epic submitted the Clarity module to Microsoft Labs and it was subjected to rigorous testing. Results became available earlier this year in a monograph titled Clarity-SQL Server 2008 Enterprise. The document is available from the Clarity Performance Team at Epic.

Basically, the team recommended that users move directly from SQL 2000 to SQL 2008, skipping 2005 entirely. The case was too compelling.

This document is crucial if you are contemplating a selection of a database vendor for a new implementation.

Introduction to My Blog

Welcome to my blog, with the improbable title "MSSQL HealthIT". This blog deals with the use of Microsoft SQL Server (MS SQL) and the ancillary tools and services applied to health care organizations, particularly hospitals.

Licensing restrictions apply, but these tools are available on the same DVD as the database server software and include:

1. SQL Server (the database engine)
2. SSIS - SQL Server Integration Services, which moves and cleans information SSRS - SQL
3. Server Reporting Services - a web based reporting platform. ReportBuilder - an adjunct to SSRS, which permits users to design their reports.
4. SSAS - SQL Server Analysis Services - a service that permits users to discover patterns and relationships on their data by interactively slicing and dicing on the data.

There are other capabilities, notably Data Mining. As this blog progresses, I'll be outlining how these can be effectively applied.

This blog has 4 identified target groups:

Technically proficient practitioners of MS SQL that are new to the Health vertical.

1. Hospital Technical staff that are currently evaluating Electronic Medical Record systems (EMRs), who also wish to assess the use of MS SQL.
2. Hospitals that have installed one of the most prominent EMR's, Epic by Epic Systems of Madison, Wisconsin. There is one module in particular, Clarity, which provides trending information to management.
3. Hospitals and other health care institution which may be using other products (Oracle, Teradata, DB2) but wish to use the MSSQL BI stack for further analytical support.
   

Why would I attempt this ambitious endeavor? I have in my career (outlined in the About Me portion of the Blog Profile) long lamented that the techniques of Business Intelligence so prevalent in other verticals (manufacturing, supply chain, financial) have not percolated to health care - an endeavor that currently consumes 17% of the GNP of United States.

Stimulus programs are forthcoming which will provide financial carrots to install computerized health care software. Penalties (in the form of reduced Federal reimbursements) are also anticipated should a health care organization fail to implement a system in the next few years. This blog will in future installments show concrete use of the SQL products in this vertical, particularly the use of Business Intelligence techniques.

I'll be showing how the features new to SQL 2008 can be applied to an Epic Clarity environment, including the following:

1. Resource Governor
2. Row compression
3. Permissions tailored for report and software developers
4. New indexing solutions - filtered indexes
   

One of the immediate other topics will be the use of Reporting Services to report on Clarity and other data sources. Two dozen topics are planned, including the scaling of a Reporting Services (RS) server, the pitfalls of multi-valued parameter reports and remedies, and techniques needed to monitor reports and improve the quality of data.

So stick around as this gets into gear.