| OHDSI Home | Forums | Wiki | Github |
Ha, yes, that’s an interesting issue. # is the preface for a temporary
table in multiple database environments, and as part of the SQLRender
process, to allow for temp tables to work in oracle, we have to preface the
character in the middle of their username or password…
The new Oracle require the common username must start with ‘C##’.
You will get an error if trying to create a common user without ‘C##’ prefix, now.
SQL Error: ORA-65096: invalid common user or role name
65096. 00000 - “invalid common user or role name”
*Cause: An attempt was made to create a common user or role with a name
that was not valid for common users or roles. In addition to
the usual rules for user and role names, common user and role
names must start with C## or c## and consist only of ASCII
characters.
Please let me go on record for saying that Oracle is really the dumbest database platform ever for requiring special characters in schema names (which are then called ‘users’ to add to the confusion).
@yonghui, I’ve created a workaround in SqlRender that will keep the double-hash intact. Could you please try rerunning with the latest version? Make sure that all R instances are closed, open only one, and type
library("devtools")
install_github("ohdsi/SqlRender")
to update the necessary package.
No problem. Trying now…
Yonghui
Hi Martijn,
It almost works. The cohort can be created. But there is an error in analysis stage:
Executing SQL took 10.4 secs
Fetching cohorts from server
Fetching cohorts took 1.66 secs
Constructing default covariates
^M | ^M | | 0%^M | ^M |= | 1%^M | ^M |= | 2%^M | ^M |== | 2%^M | ^M |== | 3%Error executing SQL: Error in .local(conn, statement, …): execute JDBC update query failed in dbSendUpdate (ORA-00942: table or view does not exist
)
An error report has been created at /data2/user_data/ywu4/project/angioedema/run/errorReport.txt
cat /data2/user_data/ywu4/project/angioedema/run/errorReport.txt
DBMS:
oracle
Error:
execute JDBC update query failed in dbSendUpdate (ORA-00942: table or view does not exist
)
SQL:
CREATE TABLE m742w0facov_gender
AS
SELECT
cp1.row_id AS row_id,
gender_concept_id AS covariate_id,
1 AS covariate_value
FROM
m742w0facohort_person cp1
INNER JOIN C##NERCDMW.person p1
ON cp1.subject_id = p1.person_id
WHERE p1.gender_concept_id IN (SELECT concept_id
FROM C##NERCDMW.concept
WHERE LOWER(concept_class_id) = ‘gender’
)
R version:
R version 3.2.5 (2016-04-14)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
Other attached packages:
Hi @yonghui,
It seems like no oracleTempSchema was specified. Could you confirm that you specified the oracleTempSchema when calling execute?
(Explanation: Oracle doesn’t really support temp tables, so to simulate them we create static tables with randomized names in a schema the user has to provide. You must have read, write, and create privileges in this schema).
Hi @schuemie
I specified the oracleTempSchema and re-run the protocol.
Here is the new error:
*** Fitting shared propensity score models ***
Removing subject that are in both cohorts (if any)
No outcome specified so skipping removing people with prior outcomes
Removing subjects with less than 1 day(s) at risk (if any)
No outcome specified so not creating outcome and time variables
High correlation between covariate(s) and treatment detected:
covariateId
1 19079279401
2 19079279402
3 19079279501
4 19079279502
5 19079279503
6 19079279504
7 19079280401
8 19079280402
9 19079280501
10 19079280502
11 19079280503
12 19079280504
covariateName
1 Drug exposure record observed during 365d on or prior to cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
2 Drug exposure record observed during 30d on or prior to cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
3 Drug era record observed during 365d on or prior to cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
4 Drug era record observed during 30d on or prior to cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
5 Drug era record observed concurrent (overlapping) with cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
6 Drug era record observed during anytime on or prior to cohort index: 19079279-Phenytoin 100 MG Extended Release Capsule [Dilantin]
7 Drug exposure record observed during 365d on or prior to cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
8 Drug exposure record observed during 30d on or prior to cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
9 Drug era record observed during 365d on or prior to cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
10 Drug era record observed during 30d on or prior to cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
11 Drug era record observed concurrent (overlapping) with cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
12 Drug era record observed during anytime on or prior to cohort index: 19079280-Phenytoin 100 MG Extended Release Capsule
analysisId conceptId
1 401 19079279
2 402 19079279
3 501 19079279
4 502 19079279
5 503 19079279
6 504 19079279
7 401 19079280
8 402 19079280
9 501 19079280
10 502 19079280
11 503 19079280
12 504 19079280
Error in createPs(stopOnHighCorrelation = TRUE, prior = list(priorType = “laplace”, :
High correlation between covariate(s) and treatment detected. Perhaps you forgot to exclude part of the exposure definition from the covariates?
Calls: execute … -> lapply -> FUN -> do.call -> createPs
Execution halted
Hi @yonghui,
There seems to be some problems with your ETL and resulting data in the Common Data Model (CDM). Specifically, it seems two CDM rules have been broken:
Only standard concepts are allowed to be used in the CDM. In your case, I can see that concepts 19079279 and 19079280 appear in the drug_exposure and drug_era tables. Both concepts are non-standard and have in fact been declared invalid.
The drug_era table should only contain concepts at the ingredient level. In your case, concept 19079280, a clinical drug, appears in the drug_era table.
The reason why especially rule 1 is important is that only standard concepts have a well-defined place in the vocab hierarchy. For example, we used the vocab hierarchy to list all drug concepts having the ingredient phenytoin, but we missed the two non-standard concepts mentioned above. (In this case we therefore misclassified prevalent users as new users and their past use of phenytoin became a near perfect predictor in the propensity model, hence the error message above).
An easy fix would be to add the two offending concepts to our list defining phenytoin use. But I hesitate to go that route since the same problem could also affect other aspects of the study such as the outcome definitions, but with no clear warning signals as the one you ran into.
I recommend you review your ETL, specifically the logic for mapping your local codes to standard concepts. I would be happy to help with that if you want.
Hi @schuemie,
It brings out the question: how to deal with the drug records that could not be converted to the CDM concepts.
Should we simply drop them if there is no valid CDM concept?
We observed a lot of high frequent drug records that cannot be mapped to the CMD concept or ingredient level drug concept in the Cerner data. So we changed the ETL to use the source concepts instead for some high frequent drugs. Later, we can update them if a new concept was assigned.
Otherwise, a lot of drug records will be missing.
For example, we kept 19079280 as there is no standard ingredient level drug concept and the frequency is high.
SQL> select count(*) from C##NERCDMW.drug_exposure where drug_concept_id=19079280;
258681
I think it should not bother the queries as you can always to limit using ‘invalid’ flag.
I will re-populate the drug_era table using the standard ETL and let you know the result.
Thanks,
Yonghui
You don’t drop them. instead, you put into the drug_concept_id the value of 0 (unknown) and in the source_drug_concept_id the value of the NDC you cannot map. However, I doubt that’s necessary:
Not sure what this is. It’s neither an NDC, nor a GPI code that the Cerner data use, but an RxNorm Concept. And a deprecated one. You can see that by the valid_end_date=‘01-AUG-09’ and invalid_reason=‘U’, which stands for “Upgraded”. If you go into the table CONCEPT_RELATIONSHIP you can find the proper and valid Concept through the relaitonship_id=‘Concept replaced by’, and you will get 40163208 “Phenytoin sodium 100 MG Extended Release Oral Capsule”.
Yes, that sounds like the rigth approach. Use the latest Vocabulary tables.
@yonghui, when I look into the latest version of the vocab (release date June 20, 2016), there are no NDC codes that map to 19079279 or 19079280. Instead, they all now seem to map to valid standard concepts.
Can you recompute the number of NDC codes you have that do not map to valid concepts, using this latest version? (Make sure you ignore concept_relationship records which have an invalid flag).
Woudl it be possible to provide a list of NDC codes that are problematic?
I traced the mapping procedure and found where 19079280 came from.
As I found that half of the NDC codes cannot map to the source concept ID if using the ‘invalid_reason is null’, I modified the procedure to ignore the invalid flag for all tables, including the concept_relationship table. Some of the deleted ‘Maps to’ relations brought it in.
This can be fixed by only ignoring the invalid flag for NDC codes and reserve the flag for all others.
Martijn, I will send you the list of problematic NDC codes, shortly.
Thanks,
Yonghui
SQL> select * from c##nercdmw.concept where concept_code=‘00378156001’ and vocabulary_id=‘NDC’;
DOMAIN_ID VOCABULARY_ID CONCEPT_CLASS_ID S
CONCEPT_CODE VALID_STA VALID_END I
44942719
Phenytoin sodium 100 MG Extended Release Oral Capsule
Drug NDC 11-digit NDC
00378156001 01-AUG-09 31-DEC-99
SQL> select * from c##nercdmw.concept_relationship where CONCEPT_ID_1=44942719 and relationship_id=‘Maps to’;
CONCEPT_ID_1 CONCEPT_ID_2 RELATIONSHIP_ID VALID_STA VALID_END I
44942719 19079280 Maps to 01-JUN-07 15-OCT-15 D
44942719 40163208 Maps to 03-AUG-09 31-DEC-99Stop. Not the latter. Use any concepts, but only concept_relationship records where invalid_reason is null. We will probably remove the others from the distribution file.
Exactly as I said. The top of the two is wrong. Only use the second one.
Hi all,
I am trying to run the analysis on IMS data base, but unfortunately it’s is in CDM4 format and KeppraAngeoidema package supports only CDM5. So what I am trying to do is to adapt package for CDM4.
I’ve updated SQL scripts for createCohors part, but failed to path runCmAnalysis. Actually scripts fails at getDbDefaultCovariateData (FeatureExtraction package) when trying to execute SQL request based on
https://github.com/OHDSI/FeatureExtraction/blob/master/inst/sql/sql_server/GetCovariates.sql
An error “Invalid object name #cov_m_below” occurs at line 3200 FROM #cov_m_below.
Can somebody help with this?
Thanks,
Yuriy
Could you run the cdmv4->cdmv5 etl conversion that’s available on github ,
then run the method without modifying it?
Hi @Patrick_Ryan,
I am going to take this way (cdm4 to cdm5 conversion), but it probably won’t be quickly. So in parallel I also try to adapt Keppra package to CDM4. Actually the error I’ve mentioned, is stange for me, because it occurs in FeatureExtraction package which is compitable CDM4.
Actually fails this part of GetCovariates.sql
{(@cdm_version == ‘4’ & @use_covariate_observation) | @use_covariate_measurement} ? {
{@use_covariate_observation_below} ? {
UNION
SELECT row_id, covariate_id, covariate_value
FROM #cov_m_below
}
Can anybody explain what this section is responsibale for? May be for Keppra research it can be just ignored?
Hi @YuriK, perhaps to be more clear and directive: it should be VERY quick
to do a CDMv4–>CDMv5 conversion, as the script is already available in
various database dialects, and would just need to be run on your end
without much intervention. Here’s the link on github to give it a shot:
https://github.com/OHDSI/CommonDataModel/tree/master/Version4%20To%20Version5%20Conversion.
Certainly, if you are re-building your ETL to ETLv5 from teh ground up, I
can imagine that’d be a fair bit of work, but running this script as a
simple work-around is by far your most efficient mechanism to get this
study done.
Assuming you still don’t want to do this despite the nudge, then to your
question: CohortMethod and FeatureExtraction have been extensively
developed on since CDMv5 was ratified, and while we initially tried to make
it backward-compatible for CDMv4, that is not being supported or tested,
and so we can provide any assurances on if it’d work. You could try to
avoid this specific block of code by setting use_covariate_observation and
use_covariate_measurement = FALSE, which may not have a huge impact if your
data is only administrative claims. But if you do modify the analysis
script, please be sure to send that back with the resutls, since any
differences will need to be documented to explain how results may differ
from other sites who ran the script as-is.
Hi all,
I have an error running Keppra package on IMS database (CDM5).
Running analyses
*** Creating cohortMethodData objects ***
*** Creating study populations ***
*** Fitting shared propensity score models ***
Removing subject that are in both cohorts (if any)
No outcome specified so skipping removing people with prior outcomes
Removing subjects with less than 1 day(s) at risk (if any)
No outcome specified so not creating outcome and time variables
Error in createPs(stopOnHighCorrelation = TRUE, prior = list(priorType = “laplace”, : unused argument (stopOnHighCorrelation = TRUE)
The traceback is following
createPs(stopOnHighCorrelation = TRUE, prior = structure(list(
priorType = “laplace”, variance = 1L, exclude = 0L, graph = NULL,
neighborhood = NULL, useCrossValidation = TRUE, forceIntercept = FALSE), .Names = c(“priorType”,
"variance", “exclude”, “graph”, “neighborhood”, “useCrossValidation”, …
10 do.call(“createPs”, args)
9 FUN(X[[i]], …)
8 lapply(x, fun, …) at Cluster.R#120
7 OhdsiRTools::clusterApply(cluster, modelsToFit, fitSharedPsModel)
6 CohortMethod::runCmAnalyses(connectionDetails = connectionDetails,
cdmDatabaseSchema = cdmDatabaseSchema, exposureDatabaseSchema = workDatabaseSchema,
exposureTable = studyCohortTable, outcomeDatabaseSchema = workDatabaseSchema,
outcomeTable = studyCohortTable, outputFolder = cmOutputFolder, … at Main.R#108
The error actually occurs at CohortMethod:: runCmAnalyses (line 417), because invalid parameters have been transferred to the function createPs.
fitPsModel <- function(params) {
cohortMethodData <- loadCohortMethodData(params$cohortMethodDataFolder, readOnly = TRUE)
studyPop <- readRDS(params$studyPopFile)
args <- params$args
args$cohortMethodData <- cohortMethodData
args$population <- studyPop
ps <- do.call("createPs", args)
saveRDS(ps, params$psFile)
}
Does anyone have an idea what is the reason for such behavior and how to fix it?
Thanks,
Yuriy
Hi Yuriy,
Sorry about that. This is caused by a slightly newer version of CohortMethod. Please make sure you get the latest version of the KeppraAngioedema package, where this problem is fixed. Please close all instances of R, open only one, and type:
install.packages("devtools")
devtools::install_github("ohdsi/StudyProtocols/KeppraAngioedema")
If you run the execute command with createCohorts = FALSE it will continue right where it left off.
Cheers,
Martijn
Hi Martijin,
you solution helped. Thank you.
Yuriy