Query model data for analytics

You can query model data for analytics when you need counts, sums, filtered result sets, or data shaped for tables and pivots in a ViewModel.

Analytics starts with a model that has facts to measure and dimensions to group by. A fact is a recorded event or value, such as a sale amount. A dimension is data used to group or filter facts, such as customer, day, month, or year.

Model a measurable event

For example, model sales with these classes and associations:

Model element	Purpose in the example
Customer	Identifies who made the sales.
SalesEvent	Represents one sale.
SalesEvent.Value	Stores the sale amount to aggregate.
SalesEvent.Date	Stores when the sale occurred so that you can group or filter by time.
Customer.Sales	Navigates from a customer to that customer's sales events.

With this structure, an analytics view can answer questions such as:

• What is the total value of sales for each customer?
• How many sales occurred in a selected period?
• Which dates, weeks, months, or years contain the sales being reported?

Use OCL to navigate model associations, filter collections, and calculate values for a ViewModel. OCL is optimized for querying the model and works with objects and collections.

Calculate aggregates without loading every object

Use PSEval and PSEvalValue when an aggregate would otherwise require loading a large collection into memory. PSEvalValue selects and manipulates data in the database without loading the selected objects into memory.

For example, an article can have many comments. To show the number of comments with a like, do not load every comment, filter the loaded objects, and then count them. Use PSEvalValue to select the comments with a like and calculate the count in the database.

The same approach applies to the Customer and SalesEvent example. When you need a sales total, design the query so that the database performs the aggregate instead of loading every SalesEvent object only to calculate a result.

Keep analytics values current

A calculated value must be re-evaluated when its underlying data changes. In the comment example, a PSEvalValue like count does not update in the user interface when a user likes or unlikes a comment unless something triggers re-evaluation. Apply the same check to every analytics value: verify that changing a sales value, date, or association causes the displayed result to be evaluated again.

Add time dimensions for reporting

Use the Calendar package model pattern when your reports group facts by dates, weeks, months, or years. The package prepopulates the database with days, months, years, and weeks so that you can efficiently find data for a diagram or table.

For the sales example, link each SalesEvent to the applicable calendar data. You can then prepare ViewModel data for questions such as "sales by month" or "sales by week" instead of deriving every grouping only at display time.

Shape results for a pivot or grid

Use pivot-table patterns when you need to present analytical data in a Turnkey table. Create a ViewModel and use OCL expressions to add objects to the list that supplies the pivot.

For a monthly sales pivot, prepare data in this order:

1. Create one root object, such as CurrentMonthView.
2. Add the ViewModel object that represents each row.
3. Provide column headers in the intended order, for example January through December.
4. Provide every cell for every row and keep the cells in column order.

You can use transient classes to organize calculated analytics data before rendering it. For example, use a transient month-view cell class for a sales total and subclass it when different cell types need different logic or actions.

Use third-party visualization with model-provided data

You can use a third-party grid or pivot add-in when it meets a presentation need. Provide the data from the model and ViewModels; searching, sorting, filtering, and pagination can instead be handled on the server within the model and ViewModels. Keeping these operations in the model reduces extra work when moving the functionality to other platforms.

Google Analytics in a Turnkey app is separate from model-data analytics. It configures Google Analytics tracking in a Turnkey application's AssetsTK folder and does not describe exporting or querying model data for a BI tool.

See also

• Documentation:Learn OCL
• HowTos:OCLps Example
• Documentation:Pivot tables
• Documentation:MDriven Turnkey Series