Contents x
    Client Database System Tables
    • 20 May 2022
    • 2 Minutes to read
    • Print
    • Dark
      Light
    Contents

    Client Database System Tables

    • Updated on 20 May 2022
    • 2 Minutes to read
    • Print
    • Dark
      Light
    Article summary

    The fixed tables that power the Ursa Studio application are mostly kept and managed in the Application Database, whereas the tables in the Client Database are dynamic and represent the data that Ursa Studio helps process from raw to finished form. That said, there are a small number of “system tables” that are managed by Ursa Studio in the Client Database, typically because the data within them sometimes needs to be joined in with the other tables in the Client Database to produce analytics.

    Below is a list of the “system tables” managed by Ursa Studio in the Client Database.

    mi_aa_002

    Value Sets

    Managed by the value sets screen in Metadata Manager. One row per value set value.

    mi_aa_002_summary

    Value Set Summary

    One row per value set, optimized for summary queries. Automatically managed by Ursa Studio upon changes to value sets.

    mi_aa_003

    Lookup Tables

    Managed by the lookup tables screen in Metadata Manager. One row per lookup table key/value pair.

    mi_aa_003_summary

    Lookup Table Summary

    One row per lookup table, optimized for summary queries. Automatically managed by Ursa Studio upon changes to lookup tables.

    mi_aa_004

    Calendar Dates

    Helper table for measure creation. One row per day. This table never changes and need only be run once.

    mi_aa_005

    Patient Affiliations

    Managed by the patient affiliations screen in Metadata Manager. One row per patient.

    mi_aa_005_summary

    Patient Affiliation Summary

    One row per patient affiliation group, optimized for summary queries. Automatically managed by Ursa Studio upon changes to patient affiliations.

    mi_aa_007

    Provider Affiliations

    Managed by the provider affiliations screen in Metadata Manager. One row per provider.

    mi_aa_007_summary

    Provider Affiliation Summary

    One row per provider affiliation group, optimized for summary queries. Automatically managed by Ursa Studio upon changes to provider affiliations.

    mi_aa_012

    Sources

    Managed by the sources screen in Integration Manager. One row per source.

    mi_aa_013

    File Import Metadata

    One row per run of an import object. Contains import metadata such as data coverage periods. This data is accessible in the semantic mapping screen via the “Import Metadata” values.

    mi_aa_015

    Result Cache

    Caches certain responses to Analytics Portal queries. A similar cache is also kept in the application database; this cache is used for responses – notably to “aggregate statistics” queries in Case Review – that might contain PHI, which we keep out of the application database. One row per cached response.

    mi_aa_016

    Cohort Data

    Powers to “Save Cohort” feature in Analytics Portal. One row per cohort value.

     Two of these tables have objects associated with them, as can be seen in the list of objects in Object Workshop.

    • Calendar Dates (mi_aa_004)

    This object’s data is the same across all Ursa Studio implementations and never changes. It needs only be run once, at the start of an implementation. Notably, the initial run of this object is what triggers Ursa Studio to build the empty shells of all these system tables.

    • Value Sets (mi_aa_002)

    Running this object will automatically create a number of useful value sets based on the contents of the standard Ursa Reference tables. When included in an ELT, this object will naturally place itself after the Ursa Reference tables that it draws from. This object also typically need only be run once, at the start of an engagement. The value sets created by this object will be shown as read-only in the value sets screen in Metadata Manager.

    Was this article helpful?
    What's Next