Building Recipes

Warning

This help isn’t complete. It may even look terrible. If you want to work on it, see How to Contribute. You can also ask for help in the Juice Slack #documentation channel.

Recipes are the heart of data services for slices.

dataservices.generator module

Recipe Ingredients

Ingredients are the raw material for making reusable SQLAlchemy queries. We define ingredients in the data service base class in the dimension_shelf and metric_shelf. These ingredients can then be used across all your data services.

def MyBaseService(RecipeServiceBaseV3):
    dimension_shelf = {
        # A dictionary of reusable dimensions. The key is the "id"
        # of the dimension and the value is a Dimension object
    }

    metric_shelf = {
        # A dictionary of reusable metrics. The key is the "id"
        # of the dimension and the value is a Metric object
    }

    automatic_filter_keys = (...)
    # A tuple or list of values from the dimension_shelf
    # these will automatically be used for filters and show up in global filters

Types of Ingredients

Dimension
Dimensions are used for grouping data.
LookupDimension
A dimension that takes an expression and a python dictionary to look the values up against. For instance, the following would change state abbreviations into full U.S. state names. If the state abbreviation wasn’t found “State not found” would be returned.
LookupDimension(Census.state, {
  "AL": "Alabama",
  "AK": "Alaska",
  ...
}, default="State not found")
IdValueDimension
A dimension that takes two expressions. The first is used as the id of an item and the second as the displayed label of that thing. Imagine you had students. There might be multiple people named “Sally Baker”. These would have the same label but different ids and would show up as different values in the frontend.
EncryptedDimension
A dimension that is encrypted at rest in the database. It will be decrypted using a secret when it is displayed in juicebox. Encrypted dimension injects a formatter in the first formatters position that decrypts the value. Any other formatters will have access to the decrypted value.
Metric
Metrics count and aggregate values.
SumIfMetric(conditional_expression, summing_expression)
Adds up the value in summing_expression ``if the ``conditional_expression is true. For instance, to count up the total amount of sales with status=’complete’, you could use:
SumIfMetric(MyTable.status == 'complete', MyTable.sales
CountIfMetric(conditional_expression, counting_expression)
Counts the number of values in counting_expression where conditional_expression is true.
DivideMetric(numerator_expression, denominator_expression)
Divides two expressions while avoiding problems that can occur when you divide by zero.

Building a recipe

You will use your ingredients to build recipes to supply data for a slice.

self.dimensions = ('age', 'city')
self.metrics = ('sales_dollars', 'sales_count')
self.recipe().metrics(*self.metrics).dimensions(*self.dimensions)\
   .filters(Filter(MyTable.state == 'Georgia'))
.metrics(list of metrics)
Add one or more metrics to the recipe. Use the keys that you defined on the metric_shelf.
.dimensions(list of dimensions)
Add one or more dimensions to the recipe. Use the keys from the dimension_shelf.
.filters(list of filters)
Add one or more filters to the recipe. These can be keys from the filter_shelf or filter objects you create just for the recipe using Filter(expression)
.order_by(list of dimensions or metrics to order by)
A list of dimension or metric keys that determines the order results should appear in. The default is values appear in ascending order, but if you put a ‘-‘ sign before the key, it will sort in descending order.
.apply_user_filters(True or False)
Should user filters be applied to this recipe. The default is True.
.apply_stack_filters(True or False)
Should stack filters be applied to this recipe. The default is True.
.apply_automatic_filters(True or False)
Should automatic filters be applied to this recipe. The default is True.
.include_automatic_filter_keys(a list of keys)
Limit the keys from self.automatic_filter_keys that will apply for automatic_filters to the provided list. There are two synonyms for this function limit_global_filters_to and limit_automatic_filters_to
.exclude_automatic_filter_keys(a list of keys)
Exclude certain keys in automatic_filter_keys from being applied
.compare(recipe, suffix)
Add a comparison recipe. Comparison recipes will be matched to the base recipe by the dimensions in the comparison recipe. The metrics in the comparison recipe will be added to each row with a suffix.
.blend(recipe)
Add a blend recipe. Blend recipes will be matched to the base recipe by looking at the tables used in the two recipes and matching constraints (primary keys and foreign keys). Only rows that are in both the blend recipe and the base recipe will be returned
.full_blend(recipe)
Similar to .blend() but only rows that are in the base recipe will be returned. If there is no match in the blend recipe, the blend recipe values will be None.

Using a recipe

Once you have a recipe you can either look at the values or use it to generate the slice response.

.all()
A list of all rows in the data. The row will have a property for each dimension and metric used in the recipe. The will also have a property {{dimension_key}}_id which is the id for for each dimension. If formatters are used there will be a {{key}}_raw, the unformatted value of that ingredient.
.one(), .first()
An object representing the first row returned.
.render()
Render the recipe in a proper response for this slice type.
.to_sql()
See the SQL that this recipe generated.

Debugging recipes

In juicebox3 apps, a frontend debugging view is available. This view is visible to superusers or if the recipe renders with parameter show_debug=True. Client implementations like HealthStream can override FruitionUser.can_see_dataservice_debug to customize who sees the debug views.

This debugging view is not available in HIPAA environments (where ALLOW_QUERY_CACHING=False in settings).

def build_response(self):
    self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs')
    self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed')
    recipe = self.recipe().metrics(*self.metrics).dimensions(*self.dimensions)
    self.response['responses'].append(recipe.render(show_debug=True))
../../../_images/debug_view_link.png

The debug view is a modal dialog that shows automatic filters, custom filters, ingredients and the generated sql.

../../../_images/debug_view_expanded.png

Supporting pagination

Slices can support pagination. To do this they need to do three things.

  • The data service for the slice must use a recipe renderer.
  • The recipe must include an .order_by() parameter.
  • Pagination must be enabled for the slice in the stack.yaml configuration. For more details see pagination.

Here’s a sample recipe that would work

def build_response(self):
    self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs')
    self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed')
    recipe = self.recipe().metrics(*self.metrics)\
                    .dimensions(*self.dimensions).order_by('name')
    self.response['responses'].append(recipe.render())

When the data service returns the response for the paginated slice, it needs to tell the front-end which page of data it is returning and how many total items exist. This information is injected by the data service into the response’s config.pagination when the data service runs:

{
    'config': {
      'pagination':
        {
           page: 1,
           pageSize: 20,
           totalItems: 78
        },
    'data': [{ 'name': 'items',  'values': [] }],
    'metadata': {},
    'name': 'Untitled',
    'templateContext': {},
    'version': '3'
}

This data service might look like this when displayed in the front end. Here we are on the first page of a total of four.

../../../_images/pagination_widget.png

Searching and sorting in pagination

Pagination may also support searching and sorting. For instance a details table slice allows users to search for a term and to sort each column.

When you use pagination, this searching and sorting must happen in the recipe in the data service. Data services that support pagination will have a paginator property on the data services. You can access the user’s requested search term and sort order as follows. This recipe support custom sorting and searching on all dimensions.

def build_response(self):
    # get the optional search query and sorts from the paginator
    search_term = self.paginator.q
    sorts = self.paginator.sort
    # If no sorts are provided sort by nane
    if not sorts:
        sorts = ('name',)
    self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs')
    self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed')
    recipe = self.recipe().metrics(*self.metrics)\
                    .dimensions(*self.dimensions).order_by(*sorts)
    self.response['responses'].append(recipe.render())

Search will automatically search all dimensions used in the recipe. If you want to limit the search to fewer values change the paginator’s search_keys. Here’s an example that limits searching to only the name and team:

def build_response(self):
    # Limit search to name and team. Don't support custom sorting.
    self.paginator.search_keys = ('name', 'team')
    self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs')
    self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed')
    recipe = self.recipe().metrics(*self.metrics)\
                    .dimensions(*self.dimensions).order_by('name')
    self.response['responses'].append(recipe.render())