Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unable to use Patient List #423

Closed
artgoldberg opened this issue Jun 11, 2021 · 7 comments
Closed

Unable to use Patient List #423

artgoldberg opened this issue Jun 11, 2021 · 7 comments
Assignees
@ndobb
Labels
enhancement New feature or request

Comments

@artgoldberg
Copy link

Hi Guys

Following the instructions at Defining the Basic Demographics Query, I configured Leaf to extract datasets. I used the "Try this Basic Demographics SQL query". This is the query being entered:
image
I was able to save it without error and all fields had green checkmarks.

This query ran, and found 844 patients:
image

But selecting Patient List fails
image

Unfortunately, Chrome's Developer Tools and the server logs do not provide helpful information.

The leaf API log only contains "Level":"Information" messages

{"Timestamp":"2021-06-10T23:02:43.7545598-04:00","Level":"Information","MessageTemplate":"Refreshed Access Token. Token:{Token}","Properties":{"Token":"eyJhbGciOiJodHRwOi8vd3d3LnczLm9yZy8yMDAxLzA0L3htbGRzaWctbW9yZSNyc2Etc2hhNTEyIiwia2lkIjoiMkQ3NjczMjJDMDY5RUMxQUEwNjM4MEM2RUMzMDFFMUE4RkI2MzZGMCIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiYXJ0aHVyLnAuZ29sZGJlcmdAbXNzbS5lZHUiLCJhdWQiOiJ1cm46bGVhZjppc3N1ZXI6bGVhZi5tc3NtLmVkdSIsImlkLW5vbmNlIjoiYWRlYTYwNzYtYzA4NC00YWEzLTg2ZDMtMGY2ZWRmMDRiYTdmIiwiYXV0aC10eXBlIjoiU2FtbDIiLCJsZWFmLXZlcnNpb24iOiIzLjguMi4wIiwiaHR0cDovL3NjaGVtYXMubWljcm9zb2Z0LmNvbS93cy8yMDA4LzA2L2lkZW50aXR5L2NsYWltcy9yb2xlIjpbImFkbWluIiwicGhpIl0sImlzcyI6InVybjpsZWFmOmlzc3VlcjpsZWFmLm1zc20uZWR1IiwiYWNjZXNzLW5vbmNlIjoiZWNmZTM2NjctMTAyOS00NmJkLTg2YWMtNzE4NzAyODQwNzdhIiwic2Vzc2lvbi10eXBlIjoicWkiLCJkYXRhLWNsYXNzIjoibm9pZCIsImlhdCI6IjE2MjMzODA1NjMiLCJ0b2tlbi10eXBlIjoiYWNjZXNzLXRva2VuIiwiZXhwIjoxNjIzMzgwOTIzfQ.e0w5Kzpo5iGEk6U2qLyQl-RyzYk8jIRVAuh5u1-0kEQMW3PUd8FTj4pPUhNmkQbKDOK-xGSI4AySSAZWF9cMbKmAX7yDZ5nWG3tHrQODycXXtXnqytQSA-SVCTCgAGM8bZ14gchVJGBSWhaBvFvs-7aQhsYT0Mg2bcio1zjmH1ucT8caaYj1hn3BczMF0Xs6NO8oFJXnMoMYbyB30kI60142EPoQSYgmHvP6Xpb4lkFQPGlh-wn93MOcX53p0yuGOAJNp1ldpSmKm6rMJUuQte9KtHfRYoLZ7F4ZGRo2j0r6jihnGoqFRrBeMOUXGQM7iWxOyuAQ0Y9CA2E-J8ClbQ","SourceContext":"API.Controllers.UserController","ActionId":"b1e13344-f50a-43ec-ad9d-4b7b52f194c7","ActionName":"API.Controllers.UserController.Refresh (API)","RequestId":"0HM97F9COPKEG:00000002","RequestPath":"/api/user/refresh","SpanId":"|1ed16ec1-418a8f7798649317.","TraceId":"1ed16ec1-418a8f7798649317","ParentId":"","ConnectionId":"0HM97F9COPKEG","SessionId":"ecfe3667-1029-46bd-86ac-71870284077a","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-10T23:02:43.7547674-04:00","Level":"Information","MessageTemplate":"Executing ObjectResult, writing value of type '{Type}'.","Properties":{"Type":"API.DTO.User.AccessTokenDTO","EventId":{"Id":1,"Name":"ObjectResultExecuting"},"SourceContext":"Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor","ActionId":"b1e13344-f50a-43ec-ad9d-4b7b52f194c7","ActionName":"API.Controllers.UserController.Refresh (API)","RequestId":"0HM97F9COPKEG:00000002","RequestPath":"/api/user/refresh","SpanId":"|1ed16ec1-418a8f7798649317.","TraceId":"1ed16ec1-418a8f7798649317","ParentId":"","ConnectionId":"0HM97F9COPKEG","SessionId":"ecfe3667-1029-46bd-86ac-71870284077a","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-10T23:02:43.7549890-04:00","Level":"Information","MessageTemplate":"Executed action {ActionName} in {ElapsedMilliseconds}ms","Properties":{"ActionName":"API.Controllers.UserController.Refresh (API)","ElapsedMilliseconds":7.8723,"EventId":{"Id":2,"Name":"ActionExecuted"},"SourceContext":"Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker","ActionId":"b1e13344-f50a-43ec-ad9d-4b7b52f194c7","RequestId":"0HM97F9COPKEG:00000002","RequestPath":"/api/user/refresh","SpanId":"|1ed16ec1-418a8f7798649317.","TraceId":"1ed16ec1-418a8f7798649317","ParentId":"","ConnectionId":"0HM97F9COPKEG","SessionId":"ecfe3667-1029-46bd-86ac-71870284077a","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-10T23:02:43.7551044-04:00","Level":"Information","MessageTemplate":"{HostingRequestFinishedLog:l}","Properties":{"ElapsedMilliseconds":9.2347,"StatusCode":200,"ContentType":"application/json; charset=utf-8","HostingRequestFinishedLog":"Request finished in 9.2347ms 200 application/json; charset=utf-8","EventId":{"Id":2},"SourceContext":"Microsoft.AspNetCore.Hosting.Diagnostics","RequestId":"0HM97F9COPKEG:00000002","RequestPath":"/api/user/refresh","SpanId":"|1ed16ec1-418a8f7798649317.","TraceId":"1ed16ec1-418a8f7798649317","ParentId":"","ConnectionId":"0HM97F9COPKEG"},"Renderings":{"HostingRequestFinishedLog":[{"Format":"l","Rendering":"Request finished in 9.2347ms 200 application/json; charset=utf-8"}]}}
{"Timestamp":"2021-06-10T23:02:56.5083313-04:00","Level":"Information","MessageTemplate":"Refreshed TokenBlacklistCache","Properties":{"SourceContext":"API.Jobs.BackgroundTokenBlacklistSynchronizer"}}
{"Timestamp":"2021-06-10T23:03:26.5107999-04:00","Level":"Information","MessageTemplate":"Refreshed TokenBlacklistCache","Properties":{"SourceContext":"API.Jobs.BackgroundTokenBlacklistSynchronizer"}}
{"Timestamp":"2021-06-10T23:03:56.5127494-04:00","Level":"Information","MessageTemplate":"Refreshed TokenBlacklistCache","Properties":{"SourceContext":"API.Jobs.BackgroundTokenBlacklistSynchronizer"}}

and although the Apache log captures the 500 error, doesn't provide me with any insight:

10.254.163.242 - [email protected] [10/Jun/2021:23:15:24 -0400] "GET /api/admin/demographics HTTP/1.1" 200 1622 "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 1938 1877
10.254.163.242 - [email protected] [10/Jun/2021:23:15:24 -0400] "GET /api/admin/datasetcategory HTTP/1.1" 200 2 "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 1941 254
10.254.163.242 - [email protected] [10/Jun/2021:23:15:24 -0400] "GET /api/admin/network/endpoint HTTP/1.1" 200 2 "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 1942 254
10.254.163.242 - [email protected] [10/Jun/2021:23:15:29 -0400] "GET /api/concept/417a5da8-bfc4-eb11-9c1f-000c29db1cc8/children HTTP/1.1" 200 2329 "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 2547 2722
10.254.163.242 - [email protected] [10/Jun/2021:23:15:45 -0400] "POST /api/cohort/count HTTP/1.1" 200 645 "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 4413 1069
10.254.163.242 - [email protected] [10/Jun/2021:23:15:51 -0400] "GET /api/cohort/78210e50-63ca-eb11-9c1f-000c29db1cc8/demographics HTTP/1.1" 500 - "https://leaf.mssm.edu/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36" 1976 159

Meanwhile, the servers appear to be running correctly

    $ systemctl status -l leaf_api httpd shibd
    ● leaf_api.service - Leaf API Service
       Loaded: loaded (/var/opt/leafapi/services/leaf_api.service; linked; vendor preset: disabled)
       Active: active (running) since Thu 2021-06-10 23:14:53 EDT; 4min 49s ago
     Main PID: 41837 (dotnet)
       CGroup: /system.slice/leaf_api.service
               └─41837 /usr/bin/dotnet API.dll

    Jun 10 23:15:53 omop-leaf dotnet[41837]: {"Timestamp":"2021-06-10T23:15:53.0626071-04:00","Level":"Information","MessageTemplate":"Executed action {ActionName} in {ElapsedMilliseconds}ms","Properties":{"ActionName":"API.Controllers.CohortController.Demographics (API)","ElapsedMilliseconds":1889.4512,"EventId":{"Id":2,"Name":"ActionExecuted"},"SourceContext":"Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker","ActionId":"8763e2e2-fa62-4e14-af1a-8610325c6d28","RequestId":"0HM9CGSDK9GB8:00000007","RequestPath":"/api/cohort/78210e50-63ca-eb11-9c1f-000c29db1cc8/demographics","SpanId":"|59bd1878-4274b672abcc6a11.","TraceId":"59bd1878-4274b672abcc6a11","ParentId":"","ConnectionId":"0HM9CGSDK9GB8","SessionId":"e6a0b3c9-76a9-476d-b53e-93488fda24ee","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
    Jun 10 23:15:53 omop-leaf dotnet[41837]: {"Timestamp":"2021-06-10T23:15:53.0633412-04:00","Level":"Information","MessageTemplate":"{HostingRequestFinishedLog:l}","Properties":{"ElapsedMilliseconds":1904.3591,"StatusCode":500,"ContentType":null,"HostingRequestFinishedLog":"Request finished in 1904.3591ms 500 ","EventId":{"Id":2},"SourceContext":"Microsoft.AspNetCore.Hosting.Diagnostics","RequestId":"0HM9CGSDK9GB8:00000007","RequestPath":"/api/cohort/78210e50-63ca-eb11-9c1f-000c29db1cc8/demographics","SpanId":"|59bd1878-4274b672abcc6a11.","TraceId":"59bd1878-4274b672abcc6a11","ParentId":"","ConnectionId":"0HM9CGSDK9GB8"},"Renderings":{"HostingRequestFinishedLog":[{"Format":"l","Rendering":"Request finished in 1904.3591ms 500 "}]}}
    Jun 10 23:15:53 omop-leaf dotnet[41837]: {"Timestamp":"2021-06-10T23:15:53.8426615-04:00","Level":"Information","MessageTemplate":"Refreshed TokenBlacklistCache","Properties":{"SourceContext":"API.Jobs.BackgroundTokenBlacklistSynchronizer"}}

    ● httpd.service - The Apache HTTP Server
       Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)
       Active: active (running) since Thu 2021-06-10 23:14:54 EDT; 4min 48s ago
         Docs: man:httpd(8)
               man:apachectl(8)
      Process: 41845 ExecStop=/bin/kill -WINCH ${MAINPID} (code=exited, status=0/SUCCESS)
      Process: 47728 ExecReload=/usr/sbin/httpd $OPTIONS -k graceful (code=exited, status=0/SUCCESS)
     Main PID: 41882 (httpd)
       Status: "Total requests: 41; Current requests/sec: 0; Current traffic:   0 B/sec"
       CGroup: /system.slice/httpd.service
               ├─41882 /usr/sbin/httpd -DFOREGROUND
               ├─41883 /usr/sbin/httpd -DFOREGROUND
               ├─41884 /usr/sbin/httpd -DFOREGROUND
               ├─41885 /usr/sbin/httpd -DFOREGROUND
               ├─41886 /usr/sbin/httpd -DFOREGROUND
               └─42007 /usr/sbin/httpd -DFOREGROUND

    Jun 10 23:14:54 omop-leaf systemd[1]: Starting The Apache HTTP Server...
    Jun 10 23:14:54 omop-leaf systemd[1]: Started The Apache HTTP Server.

    ● shibd.service - Shibboleth Service Provider Daemon
       Loaded: loaded (/usr/lib/systemd/system/shibd.service; disabled; vendor preset: disabled)
       Active: active (running) since Thu 2021-06-10 23:14:54 EDT; 4min 47s ago
         Docs: https://wiki.shibboleth.net/confluence/display/SP3/Home
     Main PID: 41988 (shibd)
       CGroup: /system.slice/shibd.service
               └─41988 /usr/sbin/shibd -f -F

    Jun 10 23:14:54 omop-leaf systemd[1]: Starting Shibboleth Service Provider Daemon...
    Jun 10 23:14:54 omop-leaf systemd[1]: Started Shibboleth Service Provider Daemon.

I tried the query that I expect Leaf to run directly on the database

SELECT
      personId          = CONVERT(NVARCHAR(10),P.person_id)
    , addressPostalCode = L.zip
    , addressState      = L.[state]
    , birthDate         = P.birth_datetime
    , deceasedDateTime  = D.death_datetime
    , ethnicity         = c_ethnicity.concept_name
    , gender            = c_gender.concept_name
    , deceasedBoolean   = CONVERT(BIT, CASE WHEN D.person_id IS NULL THEN 1 ELSE 0 END)
    , hispanicBoolean   = CONVERT(BIT, CASE WHEN c_ethnicity.concept_name = 'Hispanic or Latino' THEN 1 ELSE 0 END)
    , marriedBoolean    = CONVERT(BIT, 0)
    , [language]          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , maritalStatus     = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , mrn               = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , name              = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , race              = c_race.concept_name
    , religion          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    FROM [omop].[cdm_std].[person] AS P
        LEFT JOIN omop.cdm_std.concept AS c_gender
            ON P.gender_concept_id = c_gender.concept_id
        LEFT JOIN omop.cdm_std.concept AS c_race
            ON P.race_concept_id = c_race.concept_id
        LEFT JOIN omop.cdm_std.concept AS c_ethnicity
            ON p.ethnicity_concept_id = c_ethnicity.concept_id
        LEFT JOIN omop.cdm_std.[location] AS L
            ON P.location_id = L.location_id
        LEFT JOIN omop.cdm_std.death AS D
            ON P.person_id = D.person_id
    WHERE DATEDIFF(DAY, P.birth_datetime, GETDATE()) / 365.25 >= 123;

and it worked

image

What do you think?

Regards and thanks

Arthur
@ndobb
Copy link
Member

ndobb commented Jun 11, 2021 •
edited
Loading

Hi @artgoldberg,

Mostly for reasons of de-identification (e.g., if Leaf needs to date-shift a date column but receives a string instead, what to do?) the Leaf API is very strict about column types returned matching those expected. We need to do a better job of helping admins debug this - in fact I've just made an issue to make this easier to test in the Leaf Admin Panel - but for now the best place to look in this case is the Leaf API logs, as this kind of issue is almost always due to a subtle column type mismatch.

My suggestion would be to grep for "Demographic Schema Validation Error" (which should be Error level). If you find a row in the logs, it should have information similar to

"Messages": [ "'zip' expected type 'String' but received 'Numeric'" ]

Immediately after seeing the error in the UI, can you try that?

Best,
-nic

@ndobb
Copy link
Member

ndobb commented Jun 11, 2021

One additional observation: in the example suggested OMOP query the database is omitted, and the schema is dbo, while in final example query run directly on the SQL Server the database and a different schema are explicitly stated - i.e., dbo.person vs omop.cdm_std.person.

Any chance the database & schema mismatch could be the issue? In that case I would also expect an error in the API logs.

@artgoldberg
Copy link
Author

artgoldberg commented Jun 22, 2021 •
edited
Loading

Hi Leaf folks, @mh2727 and @jprosser

I'm obtaining a reproducible error message when executing the "Patient list" feature: "Demographic Schema Validation Error" ... "'addressState' expected type 'String' but received 'None'". Here are all of the Leaf API log messages generated when "Patient list" is clicked:

{"Timestamp":"2021-06-22T10:41:02.0279782-04:00","Level":"Information","MessageTemplate":"{HostingRequestStartingLog:l}","Properties":{"Protocol":"HTTP/1.1","Method":"GET","ContentType":null,"ContentLength":null,"Scheme":"http","Host":"10.95.46.180:5001","PathBase":"","Path":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","QueryString":"","HostingRequestStartingLog":"Request starting HTTP/1.1 GET http://10.95.46.180:5001/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics  ","EventId":{"Id":1},"SourceContext":"Microsoft.AspNetCore.Hosting.Diagnostics","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ"},"Renderings":{"HostingRequestStartingLog":[{"Format":"l","Rendering":"Request starting HTTP/1.1 GET http://10.95.46.180:5001/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics  "}]}}
{"Timestamp":"2021-06-22T10:41:02.0290663-04:00","Level":"Information","MessageTemplate":"Successfully validated the token.","Properties":{"EventId":{"Id":2,"Name":"TokenValidationSucceeded"},"SourceContext":"Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ"}}
{"Timestamp":"2021-06-22T10:41:02.0294418-04:00","Level":"Information","MessageTemplate":"Route matched with {RouteData}. Executing controller action with signature {MethodInfo} on controller {Controller} ({AssemblyName}).","Properties":{"RouteData":"{action = \"Demographics\", controller = \"Cohort\"}","MethodInfo":"System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.ActionResult`1[Model.Cohort.Demographic]] Demographics(System.String, Model.Cohort.DemographicProvider, System.Threading.CancellationToken)","Controller":"API.Controllers.CohortController","AssemblyName":"API","EventId":{"Id":3,"Name":"ControllerActionExecuting"},"SourceContext":"Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0296758-04:00","Level":"Information","MessageTemplate":"Authorization was successful.","Properties":{"EventId":{"Id":1,"Name":"UserAuthorizationSucceeded"},"SourceContext":"Microsoft.AspNetCore.Authorization.DefaultAuthorizationService","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0303566-04:00","Level":"Information","MessageTemplate":"Demographics starting. QueryRef:{QueryRef}","Properties":{"QueryRef":"Model.Compiler.QueryRef","SourceContext":"Model.Cohort.DemographicProvider","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0306031-04:00","Level":"Information","MessageTemplate":"Getting DemographicQueryCompilerContext. QueryRef:{@QueryRef}","Properties":{"QueryRef":{"_typeTag":"QueryRef","Id":"729d03be-67d3-eb11-9c20-000c29db1cc8","UniversalId":null},"SourceContext":"Model.Compiler.DemographicCompilerValidationContextProvider","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0307849-04:00","Level":"Information","MessageTemplate":"Getting DemographicQueryCompilerContext by QueryId","Properties":{"SourceContext":"Services.Search.DemographicCompilerContextProvider","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0404648-04:00","Level":"Information","MessageTemplate":"Demographics compiler validation context. Context:{@Context}","Properties":{"Context":{"_typeTag":"CompilerValidationContext`1","Context":{"_typeTag":"DemographicCompilerContext","Shape":"Demographic","DemographicQuery":{"_typeTag":"DemographicQuery","Shape":"Demographic","SqlStatement":"SELECT\n      personId          = CONVERT(NVARCHAR(10),P.person_id)\n    , addressPostalCode = L.zip\n    , addressState      = L.[state]\n    , birthDate         = P.birth_datetime\n    , deceasedDateTime  = D.death_datetime\n    , ethnicity         = c_ethnicity.concept_name\n    , gender            = c_gender.concept_name\n    , deceasedBoolean   = CONVERT(BIT, CASE WHEN D.person_id IS NULL THEN 1 ELSE 0 END)\n    , hispanicBoolean   = CONVERT(BIT, CASE WHEN c_ethnicity.concept_name = 'Hispanic or Latino' THEN 1 ELSE 0 END)\n    , marriedBoolean    = CONVERT(BIT, 0)\n    , [language]          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , maritalStatus     = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , mrn               = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , name              = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , race              = c_race.concept_name\n    , religion          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    FROM [omop].[cdm_std].[person] AS P\n        LEFT JOIN omop.cdm_std.concept AS c_gender\n            ON P.gender_concept_id = c_gender.concept_id\n        LEFT JOIN omop.cdm_std.concept AS c_race\n            ON P.race_concept_id = c_race.concept_id\n        LEFT JOIN omop.cdm_std.concept AS c_ethnicity\n            ON p.ethnicity_concept_id = c_ethnicity.concept_id\n        LEFT JOIN omop.cdm_std.[location] AS L\n            ON P.location_id = L.location_id\n        LEFT JOIN omop.cdm_std.death AS D\n            ON P.person_id = D.person_id"},"QueryContext":{"_typeTag":"QueryContext","QueryId":"729d03be-67d3-eb11-9c20-000c29db1cc8","Pepper":"d0f8987f-5360-4b60-b970-eaa4bd3ad8fb","Definition":null,"Found":true}},"State":"Ok"},"SourceContext":"Model.Cohort.DemographicProvider","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:02.0413969-04:00","Level":"Information","MessageTemplate":"Compiled demographic execution context. Context:{@Context}","Properties":{"Context":{"_typeTag":"DemographicExecutionContext","FieldSelectors":[{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"addressPostalCode","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"addressState","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"ethnicity","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"gender","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"language","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"maritalStatus","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"race","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"religion","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"marriedBoolean","Type":"Bool"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"hispanicBoolean","Type":"Bool"},{"_typeTag":"SchemaFieldSelector","Phi":false,"Mask":false,"Required":true,"Name":"deceasedBoolean","Type":"Bool"},{"_typeTag":"SchemaFieldSelector","Phi":true,"Mask":true,"Required":false,"Name":"birthDate","Type":"DateTime"},{"_typeTag":"SchemaFieldSelector","Phi":true,"Mask":true,"Required":false,"Name":"deceasedDateTime","Type":"DateTime"},{"_typeTag":"SchemaFieldSelector","Phi":true,"Mask":false,"Required":false,"Name":"name","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":true,"Mask":false,"Required":false,"Name":"mrn","Type":"String"},{"_typeTag":"SchemaFieldSelector","Phi":true,"Mask":true,"Required":true,"Name":"personId","Type":"String"}],"Shape":"Demographic","CompiledQuery":"DECLARE @IsIdentified BIT = 0; DECLARE @IsResearch   BIT = 0; DECLARE @IsQI         BIT = 1; WITH cohort as ( SELECT __personId__ = PersonId, Exported, Salt FROM LeafDB.app.Cohort WHERE QueryId = @queryid ), dataset as ( SELECT\n      personId          = CONVERT(NVARCHAR(10),P.person_id)\n    , addressPostalCode = L.zip\n    , addressState      = L.[state]\n    , birthDate         = P.birth_datetime\n    , deceasedDateTime  = D.death_datetime\n    , ethnicity         = c_ethnicity.concept_name\n    , gender            = c_gender.concept_name\n    , deceasedBoolean   = CONVERT(BIT, CASE WHEN D.person_id IS NULL THEN 1 ELSE 0 END)\n    , hispanicBoolean   = CONVERT(BIT, CASE WHEN c_ethnicity.concept_name = 'Hispanic or Latino' THEN 1 ELSE 0 END)\n    , marriedBoolean    = CONVERT(BIT, 0)\n    , [language]          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , maritalStatus     = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , mrn               = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , name              = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    , race              = c_race.concept_name\n    , religion          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */\n    FROM [omop].[cdm_std].[person] AS P\n        LEFT JOIN omop.cdm_std.concept AS c_gender\n            ON P.gender_concept_id = c_gender.concept_id\n        LEFT JOIN omop.cdm_std.concept AS c_race\n            ON P.race_concept_id = c_race.concept_id\n        LEFT JOIN omop.cdm_std.concept AS c_ethnicity\n            ON p.ethnicity_concept_id = c_ethnicity.concept_id\n        LEFT JOIN omop.cdm_std.[location] AS L\n            ON P.location_id = L.location_id\n        LEFT JOIN omop.cdm_std.death AS D\n            ON P.person_id = D.person_id ), filter as ( SELECT * FROM dataset ) SELECT Exported, Salt, filter.* FROM filter INNER JOIN cohort on filter.personId = cohort.__personId__","Parameters":[{"_typeTag":"QueryParameter","Name":"@queryid","Value":"729d03be-67d3-eb11-9c20-000c29db1cc8"}],"QueryContext":{"_typeTag":"QueryContext","QueryId":"729d03be-67d3-eb11-9c20-000c29db1cc8","Pepper":"d0f8987f-5360-4b60-b970-eaa4bd3ad8fb","Definition":null,"Found":true},"DatasetQuery":null},"SourceContext":"Model.Cohort.DemographicProvider","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:03.2055533-04:00","Level":"Error","MessageTemplate":"Demographic Schema Validation Error. Messages:{Messages}","Properties":{"Messages":["'addressState' expected type 'String' but received 'None'"],"SourceContext":"Services.Cohort.DemographicsExecutor","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:03.6144919-04:00","Level":"Information","MessageTemplate":"Executing HttpStatusCodeResult, setting HTTP status code {StatusCode}","Properties":{"StatusCode":500,"EventId":{"Id":1,"Name":"HttpStatusCodeResultExecuting"},"SourceContext":"Microsoft.AspNetCore.Mvc.StatusCodeResult","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","ActionName":"API.Controllers.CohortController.Demographics (API)","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:03.6147246-04:00","Level":"Information","MessageTemplate":"Executed action {ActionName} in {ElapsedMilliseconds}ms","Properties":{"ActionName":"API.Controllers.CohortController.Demographics (API)","ElapsedMilliseconds":1585.0986,"EventId":{"Id":2,"Name":"ActionExecuted"},"SourceContext":"Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker","ActionId":"0edd6015-3446-4845-b2c0-94ddad1bd5cb","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ","SessionId":"1fee6aba-8b42-4391-b7f0-8af58af93a63","User":"[email protected]@urn:leaf:issuer:leaf.mssm.edu"}}
{"Timestamp":"2021-06-22T10:41:03.6149348-04:00","Level":"Information","MessageTemplate":"{HostingRequestFinishedLog:l}","Properties":{"ElapsedMilliseconds":1586.9883,"StatusCode":500,"ContentType":null,"HostingRequestFinishedLog":"Request finished in 1586.9883ms 500 ","EventId":{"Id":2},"SourceContext":"Microsoft.AspNetCore.Hosting.Diagnostics","RequestId":"0HM9KUC5F8TEQ:00000001","RequestPath":"/api/cohort/729d03be-67d3-eb11-9c20-000c29db1cc8/demographics","SpanId":"|68b8726e-4a69b10f7836cf92.","TraceId":"68b8726e-4a69b10f7836cf92","ParentId":"","ConnectionId":"0HM9KUC5F8TEQ"},"Renderings":{"HostingRequestFinishedLog":[{"Format":"l","Rendering":"Request finished in 1586.9883ms 500 "}]}}

The error is reported at "Timestamp":"2021-06-22T10:41:03.2055533-04:00".

It's not clear to me whether this error is caused by executing the SQL or by analysis of the results of the query. I do wonder whether NULL values in cdm_std.location.state would cause this error. In our CDM 2,897,597 of the 14,181,381 location records are NULL in location.state and the patient list being obtained here is large enough that it almost certainly contains some of these NULLs.

Please let me know what I can do to help debug this. For example, I could try to isolate the SQL and execute it.

Regards
Arthur

@mh2727
Copy link
Contributor

mh2727 commented Jun 23, 2021

Hi @artgoldberg.

From the error, it seems one of the columns addressState is supposed to be a string, but was not provided that.

In the line at "Timestamp":"2021-06-22T10:41:02.0404648-04:00", can you try running the query listed there as is? That way we can check if the query is throwing an error by checking whether or not the addressState column accepts null values.

If it is the case that we can run the query, it could mean that the analysis of the result is throwing an error because it cannot take null values. In which case, we can try to add the condition where addressState is not null in the query and see if Leaf API still throws the error.

Hope this helps.

--
Mehadi Hassan

@artgoldberg
Copy link
Author

Hi Leaf folks, @mh2727 and @jprosser

Yes, that's what seems to be going on Mehadi.

However, I think that the query at "Timestamp":"2021-06-22T10:41:02.0413969-04:00" is the one that needs to be executed, as it's the one that immediately precedes the error.

Extracting the SQL and formatting it for people, I get this (I made the reference to @queryid in the query a literal, as it was giving a "Must declare the scalar variable" error in DBeaver.):

DECLARE @IsIdentified BIT = 0;
DECLARE @IsResearch   BIT = 0;
DECLARE @IsQI         BIT = 1;
DECLARE @queryid      NVARCHAR(50) = '729d03be-67d3-eb11-9c20-000c29db1cc8';
;WITH
  cohort as ( SELECT __personId__ = PersonId, Exported, Salt FROM LeafDB.app.Cohort WHERE QueryId = '729d03be-67d3-eb11-9c20-000c29db1cc8' ),
  dataset as ( SELECT
    personId          = CONVERT(NVARCHAR(10),P.person_id)
    , addressPostalCode = L.zip
    , addressState      = L.[state]
    , birthDate         = P.birth_datetime
    , deceasedDateTime  = D.death_datetime
    , ethnicity         = c_ethnicity.concept_name
    , gender            = c_gender.concept_name
    , deceasedBoolean   = CONVERT(BIT, CASE WHEN D.person_id IS NULL THEN 1 ELSE 0 END)
    , hispanicBoolean   = CONVERT(BIT, CASE WHEN c_ethnicity.concept_name = 'Hispanic or Latino' THEN 1 ELSE 0 END)
    , marriedBoolean    = CONVERT(BIT, 0)
    , [language]        = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , maritalStatus     = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , mrn               = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , name              = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    , race              = c_race.concept_name
    , religion          = CONVERT(NVARCHAR(1),NULL) /* Not in OMOP */
    FROM [omop].[cdm_std].[person] AS P
      LEFT JOIN omop.cdm_std.concept AS c_gender
          ON P.gender_concept_id = c_gender.concept_id
      LEFT JOIN omop.cdm_std.concept AS c_race
          ON P.race_concept_id = c_race.concept_id
      LEFT JOIN omop.cdm_std.concept AS c_ethnicity
          ON p.ethnicity_concept_id = c_ethnicity.concept_id
      LEFT JOIN omop.cdm_std.[location] AS L
          ON P.location_id = L.location_id
      LEFT JOIN omop.cdm_std.death AS D
          ON P.person_id = D.person_id ),
  filter as ( SELECT * FROM dataset )
SELECT Exported, Salt, filter.* FROM filter INNER JOIN cohort on filter.personId = cohort.__personId__

This query runs and produces the table below.
full_patient_list_query_results.csv

My hypothesis is that the NULL values in addressState, of which there are 254, cause downstream software that processes the query to generate the "'addressState' expected type 'String' but received 'None'" error.

Thanks
Arthur

@ndobb ndobb self-assigned this Jun 28, 2021
@ndobb
Copy link
Member

ndobb commented Jun 28, 2021

Hi @artgoldberg, thanks for your patience while I was out, and thank you @mh2727 for helping advise.

The issue is likely an unknown SQL column type that we don't have a proper "Leaf Type" for (see https://github.com/uwrit/leaf/blob/master/src/server/Model/Schema/LeafType.cs#L31). As you can see, we map nvarchar and varchar column types to LeafType.String, but I suspect the addressState column is neither of those, perhaps, Arthur. Can you please verify?

As a workaround, you can change addressState = L.[state] to addressState = CONVERT(NVARCHAR(20), L.[state]) or something similar. We can also create an issue to add an additional column type mapping to address this if that is indeed the issue.

@artgoldberg
Copy link
Author

Hi @ndobb

I hope you enjoyed your vacation.

Your diagnosis is correct. In our clinical DW, location.state is an NCHAR(2), which isn't in your type map. I modified the assignment to addressState as you recommend, and Patient List now works! I further verified that Export Data works.

I've also updated my version of 8_other.sql to reflect this change, as the update to "Basic Demographics" in the Leaf's admin did not change the contents of app.DemographicQuery.

Thanks
Arthur

@ndobb ndobb added the enhancement New feature or request label Jun 28, 2021
@ndobb ndobb closed this as completed Oct 19, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ndobb ndobb

Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@artgoldberg @ndobb @mh2727