Adding Analytics Fields

Webcast Details

This webcast covers the process for adding fields to the Analytics Metalayer Tree. This includes existing fields in the database as well as SQL expression fields. SQL fields are discussed from tracing SQL to testing in SQL Server Management Studio to converting it into Analytics syntax before being added to the Metalayer.

Webcast Materials

Presented 18 March, 2015

Slide Deck (PDF)

Common SQL expressions (PDF)

Links

Allscripts Analytics

Questions

How did you get to that screen in analytics? The Analytics metalayer

Answer: This is accessible via Metalayer Engine under the Administration tab. Security settings may need to be adjusted to allow users this access.

How can we modify the ETL to pull more fields?

Answer: This is a far more involved topic, as it requires modification of the ETL job, addition of tables in the Analytics database, and a variety of stored procedures to link up the new data tables properly. At that point, fields can then be added to the Metalayer.

We were told by Allscripts that if we add a field to the Default Metalayer Tree that it will be removed with each update. The way around that is to put the new field into the Custom Fields group of the Tree. Is that not accurate anymore?

Answer: Allscripts is the official source of upgrades and notes around it. Please refer to them for information on any issues that may occur during the upgrade process.

What does the E. specify in the expression?

Answer: In many SQL queries, a table is given an alias to simplify selecting fields. The syntax looks like TableName Alias. In an actual query, the difference is this:

SELECT 
AHS_Encounter.EncounterDTTM
,AHS_Encounter.AppointmentStatusName 
,AHS_Patient.LastName
,AHS_Patient.FirstName
FROM AHS_Encounter
INNER JOIN AHS_Patient ON AHS_Encounter.PatientID = AHS_Patient.ID

SELECT 
enc.EncounterDTTM
,enc.AppointmentStatusName 
,pt.LastName
,pt.FirstName
FROM AHS_Encounter enc 
INNER JOIN AHS_Patient pt ON enc.PatientID = pt.ID

This simplification makes a greater difference when additional tables are joined in and more fields are included in the SELECT. It also makes the query much easier to read as long table names are not written out each time.

What is the meaning of FK Dim date? Answer: FK_Dim_Date is a foreign key link to the DateDim table. This table contains a breakdown of all parts of a given date, including year, quarter, month, week, day, day of week, weekend flag, any of the ago (year, month, etc.) field calculations. It will contain 365 records for each year, or 366 for leap years. This table is used mostly in searching, as it allows for easy querying on the individual parts of a given date.

Will it be possible to change the joins between tables? Answer: Yes, you can change the joins. INNER is the most common, but does have the potential side effect of filtering out data if the source column for the join is NULL. Changing joins should always be tested to ensure the correct number of records are returned.

Will the ELSE part of the [CASE] expression impact the date part?

Answer: A CASE statement is basically an IF THEN statement. The ELSE is what is returned if none of the IF statements evaluate to TRUE. When using something like

 CASE WHEN PatientID IS NOT NULL

the ELSE should never be returned as all patients should have an ID.