Queries
Queries in the SanteDB BI services infrastructure allow the definition of a single identified query which can be referenced by reports or views. Queries abstract the underlying query mechanic of the RDBMS and present a logical view of data fed from that data source.
Queries should:
  1. 1.
    Always return consistent column names between invariant SQL definitions.
  2. 2.
    Be reusable where possible, representing a logical construct (patients, providers, sessions, etc.)
  3. 3.
    Define at least npgsql and sqlite definitions
Queries loosely translate to fact sources.

Query Definition Files

The BI infrastructure definition of queries allow multiple invariant representations of a query. For example, one query definition may define SQL for Firebird, PostgreSQL and SQLite . The runtime environment will determine which invariant SQL query to run. For example, if we wanted to reference data for PostgreSQL and Firebird as org.custom.query.ageWeight
1
<BiQueryDefinition xmlns="http://santedb.org/bi" id="org.santedb.bi.core.query.session" label="ui.santedb.bi.core.query.session">
2
<meta status="active">
3
<authors>
4
<add>Sample Organization</add>
5
</authors>
6
<annotation>
7
<div xmlns="http://www.w3.org/1999/xhtml">
8
<p>Age ranges with healthy weights</p>
9
</div>
10
</annotation>
11
</meta>
12
<dataSources>
13
<add ref="#org.custom.dataSource.references"/>
14
</dataSources>
15
<parameters>
16
<add ref="#org.custom.parameter.version" name="version-name" type="string" required="false"/>
17
</parameters>
18
<definitions>
19
<add>
20
<providers>
21
<invariant>npgsql</invariant>
22
</providers>
23
<![CDATA[
24
select
25
min_age,
26
max_age,
27
zneg1,
28
z,
29
zpos1
30
from
31
ref_weights
32
where
33
version = ${version-name}
34
]]>
35
</add>
36
<add>
37
<providers>
38
<invariant>fbsql</invariant>
39
</providers>
40
<![CDATA[
41
select
42
m_age as min_age,
43
x_age as max_age,
44
zn1 as zneg1,
45
z,
46
zp1 as zpos1
47
from
48
ref_weights
49
where
50
version = ${version-name}
51
]]>
52
</add>
53
</definitions>
54
</BiQueryDefinition>
Copied!
Queries can pull data from multiple data sources, however this function is only supported if the underlying data source provider supports attaching additional data sources (for example: SQLite)

Materialized Queries

You can store the result of queries in a materialized view. This view will be refreshed whenever the Refresh BI Materialized Views job is run. To materialize a view, use the <materialize> element in your query definition.
1
<BiQueryDefinition xmlns="http://santedb.org/bi" id="org.santedb.bi.core.query.session" label="ui.santedb.bi.core.query.session">
2
<meta status="active">
3
<authors>
4
<add>Sample Organization</add>
5
</authors>
6
<annotation>
7
<div xmlns="http://www.w3.org/1999/xhtml">
8
<p>A complex query</p>
9
</div>
10
</annotation>
11
</meta>
12
<dataSources>
13
<add ref="#org.custom.dataSource.main"/>
14
</dataSources>
15
<parameters>
16
<add ref="#org.custom.parameter.ageFilter" name="age-in-years" type="string" required="false"/>
17
</parameters>
18
<definitions>
19
<add>
20
<providers>
21
<invariant>npgsql</invariant>
22
</providers>
23
<materialize name="sample_query">
24
<!-- Note: Parameters cannot be used in the materialized view -->
25
<![CDATA[
26
SELECT *, age(psn_tbl.dob, CURRENT_DATE) AS age
27
FROM
28
ent_tbl
29
INNER JOIN ent_vrsn_tbl USING (ent_id)
30
INNER JOIN psn_tbl USING (ent_vrsn_id)
31
WHERE
32
obslt_utc IS NULL
33
]]>
34
</materialize>
35
<![CDATA[
36
select
37
*
38
from
39
sample_query
40
where
41
age <= ${age-in-years}
42
]]>
43
</add>
44
</definitions>
45
</BiQueryDefinition>
Copied!
Last modified 2mo ago