Transforming data for reporting

You can manipulate data in the Axiom database for reporting purposes by using data transformation definitions. These definitions allow you to define how, when, and where to change values in the database using different calculation types with no SQL knowledge necessary. The results of calculations can then be stored in certain Axiom standard and custom fields. For example, you could create a definition that increases Medicare payments by 5% and stores the results in a new encounter table field.

Examples of types of definitions that you can create include:

  • Perform calculations on encounter data elements and store the results in Axiom fields.

  • Assign a date, numeric, or text constant to a field.

  • Identify outliers.

  • Perform If-Then-Else comparisons.

  • Create basic formulas using constants as well as standard and custom fields for inputs

NOTE: Only users assigned the Axiom EDS Rules Writer role profile can access this feature.

About definitions

A data transformation definition includes calculations that Axiom applies to tables in the database. .

The Data transformation definitions page displays a table with all definitions. From this page, you can add, edit, copy, or delete definitions as well as process them. To search for a definition, you can use the search box in the upper right corner of the page. To filter the results in the table, click the click the ellipsis(...) in any of the available headings. To add a definition or folder, click + Add in the upper right corner of the page.

From the Actions column, click the ellipsis (...) to edit, copy, or delete a definition. You can also process a definition, but definitions must first be active.

Working with data transformation definitions

A data transformation definition is a mathematical formula that describes the data you want to use, how you want to use it, and where you want to store a result. When you create a definition, you choose a type, and Axiom prompts you to "fill in the blanks" by choosing:

  • Calculation variables

  • The table column fields to use in the calculation

  • An operator

  • A constant, if applicable

  • Result variable

  • The table column field in which to store the result

The following table lists the available definition types:

Type Description
Assign Constant Use to store a numeric, text, flag (Boolean), or date constant in an Axiom field. For example, you can store a benchmark or best practices length of stay for hip replacement surgery in an encounter custom field.
Basic Calculation Use to calculate and store a value from Axiom numeric fields and constants. You may combine and group the fields and constants to form an expression using addition, subtraction, multiplication, and division operators.
Concatenate Use to join Axiom fields and constants. You can use the constant as a delimiter.
Comparison Use to create If Then Else formulas that compare Axiom fields to each other or to constants, and store a value based on whether the comparison is true or false.
Copy Field Use to copy the value of one field to another field.

NOTE: A definitions inputs and results must be fields from within the same table.

Understanding inputs and results

Inputs

If there is a custom column in the table, you can use data from the following dimension tables in the Table Library > !Dimensions folder:

  • ACCT

  • FINCLASS

  • CALDATE

  • JOBCODE

  • CDMCODE

  • LOCATION

  • CPT

  • PAYOR

  • DEPT

  • PAYTYPE

  • ENTITY

  

Results

You can store results in standard and custom columns in tables in the EDS folder except columns in custom tables.

You cannot store results in the following column types:

  • Keys

  • Pointers to key column on other tables

  • Data that results from cost model processing (tables under CostModel and CostResults)

  • CM$ tables

  • Encounter total cost columns

  • Columns, even custom, on any table in any folder other than EDS or dimensions

Important notes about transforming data

Keep in mind the following when using and processing definitions:

  • All the fields (inputs and results) in a single definition must be from the same table. Axiom will not allow you to select a field from a different table.

  • You must manage the processing order. If the results of one definition feed into the inputs of another definition, you must ensure the first definition completes successfully before processing the second one.

  • When creating a custom column on a table to store results, we recommend that you:

    • Create only text, numeric, date, or flag (Boolean) types. The system will not stop you from creating an Integer or an Integer32 column, but numeric is our recommendation.

    • Do not set a default value on a custom column.