Adam Rauch Partner, LabKey Software Extending LabKey Server Part 1: Retrieving and Presenting Data

(1)

Extending LabKey Server Part 1:

Retrieving and Presenting Data

Adam Rauch

Partner, LabKey Software [email protected]

(2)

Extending LabKey Server

 _{LabKey Server is a large system that combines an extensive set}

of tools and applications for managing biomedical research data

 _{But the single most powerful feature is the ability to extend and}

customize LabKey Server to meet your specific needs

 _{LabKey Server is not a product with a fixed set of functionality….}

it’s a platform that you (and we) can extend in many different ways via code and customization

(3)

A Few Examples of Extending LabKey Server

 _{Customize the look and feel, navigation model, and data}

presentation of your projects, studies, and assays

 _{Assemble LabKey and external components into mash-ups,}

dashboards, and other custom views

 _{Encapsulate your “process logic” in custom data entry}

applications and workflows

 _{Augment existing features with processing steps,}

transformations & analyses matching your science

(4)

In two presentations I’ll cover six key extension mechanisms:  _{Custom queries}

 _{External schemas / data sources}  _{Script reports}

 _{Server API / client libraries}  _{File-based modules}

 _{Java modules}

Retrieve & present data

Create applications Extension Mechanisms Easy Hard Limited Powerful Primary Use

(5)

Security and Permissions

 _{All extension mechanisms respect access privileges}

 _{Custom code typically “impersonates” the requesting user}

 _{Your code can’t subvert permissions (e.g., retrieve forbidden data)}  _{It’s critical that you test your code/queries as a non-admin user}

 _{Caveats & warnings}

 _{Exception: Java code in a module can subvert security; be careful!}  _{Custom R code running on your web server can do nasty things}

 _{Custom JavaScript running in your users’ browsers can do nasty things}  _{You damn well better trust people who add code to your server!}

 _{Special Developers group determines who can write code}

 _{Site administrators are added automatically}  _{Other users must be added explicitly}

(6)

Key extension mechanisms:  _{Custom queries}

 _{External schemas / data sources}  _{Script reports}

 _{Server API / client libraries}  _{File-based modules}

 _{Java modules}

Retrieve & present data

Create applications

(7)

LabKey Query Terminology

 _{Query: a named, saved SQL SELECT statement}

 _{Like a VIEW in a SQL database}

 _{LabKey translates SQL to underlying implementation & enforces security}

 _{Schema: a named collection of related tables and queries}  _{Table/query metadata:}

 _{Column names, types, formats, etc.}

 _{Custom view}

 _{Subset of query functionality (column list, sort, filter)}  _{Intended for UI definition (not defined in SQL)}

(8)

Custom Queries

 _{Use the full power of SQL to do custom analysis of your data}  _{Allow joins, grouping, aggregates, complex filters, pivots,}

functions, expression columns, etc.

 _{Access tables & queries exposed by modules and external}

schemas

 _{Respect the executing user’s permissions}

 _{Use meta data to configure display properties such as}

lookups, labels, URLs, standard and conditional formats, etc.

(9)

(10)

(11)

(12)

Custom Queries: Tips & Tricks

 _{SQL is a good tool for narrowing down the data you really}

want to see or preparing data for other analysis

 _{The query schema browser is your friend}

 _{Use it to explore the data sources, schemas and queries (built-in and} user-defined) available in a folder

 _{Documentation on labkey.org}

 _{LabKey SQL reference}  _{Meta data XML reference}

(13)

External Schemas & Data Sources

 _{Provide access to data that is not managed by LabKey}  _Scenarios

 _{Use LabKey to publish, report & visualize any data in your institution}  _{Build LabKey applications without relocating the data}

 _{Join LabKey data with user-defined tables (same database)}  _{Publish SAS data sets securely and dynamically via LabKey}

(14)

External Schemas & Data Sources

 _{Currently support:}

 _{Microsoft SQL Server (2005 & 2008)}  _{PostgreSQL (8.3 – 9.1)}

 _{MySQL (5.1 & 5.5)}  _{SAS (9.1 & 9.2)}  _{Oracle (11g)}

 _Steps

1. Add DataSource <Resource> elements to labkey.xml

2. Expose external schemas from these data sources (per-folder) Tables and views are then visible like any other query

(15)

DataSource <Resource> Example

Example <Resource> element that enables a MySQL data source. LabKey documentation includes examples for all supported databases.

<Resource name="jdbc/mySqlDataSource" auth="Container" type="javax.sql.DataSource" username="root" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/?autoReconnect=true &useUnicode=true&characterEncoding=utf8" maxActive="15" maxIdle="7" useInformationSchema="true" accessToUnderlyingConnectionAllowed="true"

(16)

Script Reports

 _{Reports (“views”) provide alternate ways of displaying data,}

beyond a simple grid

 _{Time chart and crosstab are two built-in examples}

 _{Use script reports to create custom transformations &}

visualizations of datasets and queries  _{Write script reports in R or JavaScript}

 _{Once created, users can invoke the custom report}

 _{LabKey executes the code, providing query data & meta data}  _{Artifacts created by the code are then sent to the browser}

(17)

Script Reports: Tips & Tricks

 _{Configure external script engines from the admin console}  _{Script reports are created with functional placeholder code}  _{The “help” tab in the script report designer is… helpful}

(18)