MSSQL HealthIT

When GEO meets SQL: Hotwiring Data to Locations

2010-04-28T17:53:00.001-04:00

I gave this presenation last Saturday at SQLSaturday41, in Atlanta. This was conducted by the local SQL Server user group and PASS, the international association of SQL Server professionals.

The talk was ambitious: Show the geospatial characteristics (distance, drive time) for members of an ice cream gourmet society related to three ice cream facilities. Substitute patients for members and clinics for facilities as you please. Key methods, properties, and queries were shown, as well as several tricks and traps. All of this was done in the context of enhancing an existing data mart/warehouse with geo-aware data. More details, including the slide deck and samples, can be found here .

Michael

Session on SQL Server Geospatial Features to be presented

2010-04-11T17:50:00.000-04:00

On April 24th, I'll be presenting the topic: When GEO meets SQL: Hotwiring Data to Locations at SQLSaturday in the Atlanta regional Microsoft office.

This is a beginner's level session on geospatial for the DBA and TSQL developer. We will focus on the adaptation of the spatial capabilities to a data warehouse and how to acquire resources with little or no money ( a key requirement in the health care field). The description is below:

Spatial fields, operators, and functions were introduced in SQL 2008. I will detail how these can be integrated with location-based information in existing data warehouses and marts - and on the cheap. We will briefly discuss the basics (geometry versus geography, geocoding), then move on to the common problems with this task and the remedies.

------------------
Stay tuned. I'll let you know how it went.

When Queries Zone Out in RS - The Unknown Element

2010-03-06T11:27:00.000-05:00

Frequently the report developers will approach you with a problem: A query that runs lightening fast in SQL Server Management Studio completely tanks when the query is hosted by a Reporting Services report.

RS 2008 makes calls to the Data Source in a slightly different fashion than a call by other clients. One major difference is that parameters are called using Unicode. This can result in table scans if the destination objects are not in Unicode.

Besides checking for the usual issues (table scans, indexes, etc.), I've had good results with the inclusion of the Optimize for Unknown optimizer hint. This has restored performance for many queries, from minutes to seconds. Give it a try. See details below:

http://blogs.msdn.com/mssqlisv/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

Back in the Swim

2010-03-06T11:08:00.004-05:00

This blog has been woefully neglected. Look for blogs on the following subjects in the new few weeks:

Dimensional modeling tips for a Mart based upon Epic Clarity data.
Use of SQL Server's Spatial fields - adding geospatial to a data mart.
Geocoding data - on the cheap.

A New Job, (almost) the Same Environment, and a New Perspective

2009-09-20T12:38:00.005-04:00

I'm now at a different employer. Yes, it's still the health field, but it's a well known health plan rather than a pediatric hospital. It's still an Epic shop and Clarity is used, but the source is Teradata. SQL Server is used as the mart data store and query engine.

Unlike the former shop, I now work in the business and analytics section rather than IT. Both the former and latter have difficulties with Epic's over-normalization of the 7000 Clarity tables and all that implies - although the size of the data in the new environment is far larger than the former. In response, the new employer started a couple of years ago to identify demensional models of the core business. Extracts were in turn developed to feed Clarity data into star structures in MSSQL. All of this was farmed out to a third party server hosting firm. The result: We focus on the business need (trends in membership, payments, etc.) rather than the SQL maintenance and other back room tasks.

Performance in the data warehouse is terrific. Stay tuned on other observations as I pursue this leg of my career.

Back from Microsoft's Health User Group

2009-09-20T12:28:00.003-04:00

This two day exploration of the use of MS technology meets annually on the MS campus in Redmond, WA. It's attended by a cross section of health IT people. There's a similar meeting in the Spring for one day.

What’s new? I’ve been attending since 2006 with a particular interest in Business Intelligence, but can say that it had not been a significant HUG interest until now. The sessions on BI, analytics, or Clinical Informatics were packed. One session, mentioned below, was SRO until more chairs could be obtained.

One slide for a keynote (which isn’t available) cited a Gartner statistic that BI would be the top interest in Health IT in both 2009 and 2012.

A theme that repeatedly surfaced was the paramount role of business and clinical lines rather than IT. One session in particular was packed, Leveraging SharePoint’s Business Intelligence Capabilities to Drive Process Improvements in Healthcare.

Don’t dwell to much on the Sharepoint theme. The other theme was how to structure a successful BI operation in a health care organization. Basically, the hospital for which the presenter worked embarked for years on a BI effort led by IT, which failed. Success only occured when a BI Governance system was put in place to transform the process - one which placed control by the business stakeholders.

The other key presentation to note was Next Generation Decision Support for Hospitals: Using Microsoft Technologies You Already Own to Drive New Levels of Performance, delivered by Bob Lokken, CEO, WhiteCloud Analytics. Bob founded and developed Proclarity, a company which consulted this last decade on building solutions with MS Analysis services (“cubes” and data mining). Furthermore, his company developed software which became perhaps the pre-eminent software to “surf” on the cubes.

He sold the company to Microsoft in 2006 and worked there briefly as world-wide head of the BI consulting arm. Now he’s back with his own new company that specializes on providing analytics to the health care vertical. I had met him years ago and introduced him at his section.

Bob mentioned that 5 out of 6 (successful?) projects are now initiated not by the IT shop, but by the business and clinical lines. He focused on the difference between reporting and analytics and offered six examples.

I noticed that his samples documented outliers that exceeded a certain number of standard deviations. Also, he keenly documented how necessary it was for designers to be aware of navigation (drill down, drill to a separate “report”). Some of the data was run through data mining before the user “surfed” on the data.

The other big item was “self-serve” BI, whereas power users have the tools to discover patterns without the need to have the Report Team develop something per a work order request. Bob was particularly critical of the issue of limiting analysis to those trained in SPSS and other statistical power packages. He outlined size examples where analytics (as opposed to operational reporting) uncovered relationships which yielded significant savings and/or put a health care institution back on track with a sound footing.

RS Parameter Problems - Part I

2009-07-25T12:59:00.004-04:00

Microsoft issued a "Fast Publish" notice about a problem with parameters issued by Reporting Services
(http://support.microsoft.com/default.aspx?scid=kb;en-us;2000524&sd=rss&spid=13165).

A Microsoft consultant told me about this in mid-May. Parameters are coerced into Unicode and applied in the SQL query to the SQL Server.

The problem is query optimization. As suggested in the notice:

This may result in performance issues on the backend SQL server. For example SQL server may perform a table scan instead of a seek.

The interim workarounds are:

Use an expression based command text with Join().
Use stored procedures with parameters passed in e.g. as comma-separated string.
Implement custom data extensions with your own implementation of multi-value query parameter rewrite.
Change database schema column definition to NVarchar

None of them are particularly appealing if your shop has invested in designing hundreds of reports with multi-parameter drop down options against thousands of tables in the Clarity database, many of which are so large that access using a table scan is of concern.

The first two are the most feasible and are being explored. However, now the issue is public and some clever approaches will likely be forthcoming.

Stay tuned on this issue.

There is an even larger issue of parameters in RS and the optimization of queries. That will be covered in a subsequent posting.

Edit Update: One of the MS field engineers provides more detail on the remedies at http://blogs.msdn.com/sql_pfe_blog/archive/2009/06/17/possible-performance-implications-when-using-string-parameters-in-reporting-services.aspx

I'll be applying these next week to some of our problem queries issued by RS and see if index scans can be turned into index seeks.

A Model for Guiding Consumption of Information

2009-06-27T14:33:00.004-04:00

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:

Farmers (financial analysts) have well defined requirements. Queries are clear and concise. Data marts and OLAP are often used.
Tourist (executives) search large amounts of data to anticipate and respond to events. Data marts and OLAP are often used.
Operators require current detailed information on a scheduled bases. Reports largely suffice to meet this need in the health care vertical.
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

2009-06-21T15:20:00.009-04:00

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

2009-06-21T15:14:00.004-04:00

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

2009-06-14T16:17:00.001-04:00

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

2009-06-14T16:02:00.000-04:00

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:

SQL Server (the database engine)
SSIS - SQL Server Integration Services, which moves and cleans information SSRS - SQL
Server Reporting Services - a web based reporting platform. ReportBuilder - an adjunct to SSRS, which permits users to design their reports.
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.

Hospital Technical staff that are currently evaluating Electronic Medical Record systems (EMRs), who also wish to assess the use of MS SQL.
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.
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:

Resource Governor
Row compression
Permissions tailored for report and software developers
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.