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.Always return consistent column names between invariant SQL definitions.
2.Be reusable where possible, representing a logical construct (patients, providers, sessions, etc.)
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!

Parameters

Views

Last modified 2mo ago

Copy link

Contents

Query Definition Files

Materialized Queries