Skip to content

Query Methods

Define Query

define_query(
    name: str,
    dimensions: set[str] = {},
    metrics: List[str] = [],
    computed_metrics: Optional[List[str]] = None,
    having: Optional[str] = None,
    drop_null_dimensions: bool = False,
    drop_null_metric_results: bool = False
)

Defines a named query with dimensions and metrics for later execution.

Parameters:

  • name: A unique name for the query
  • dimensions: Set of dimension column names to include in the query
  • metrics: List of metric names (as defined with define_metric)
  • computed_metrics: List of names referencing computed metrics defined via define_computed_metric()
  • having: SQL HAVING-like expression to filter rows based on aggregated results
  • drop_null_dimensions: Whether to exclude rows where dimension values are missing
  • drop_null_metric_results: Whether to exclude rows where metric calculations result in null

Example:

# Define metrics
cube.define_metric(name='Revenue', expression='[qty] * [price]', aggregation='sum')
cube.define_metric(name='Units', expression='[qty]', aggregation='sum')

cube.define_computed_metric(name = 'Avg Revenue per Unit', expression = '[Revenue] / [Units]')

# Define a query using those metrics
cube.define_query(
    query_name="sales analysis",
    dimensions={'region', 'category', 'promo_type'},
    metrics=['Revenue'],
    computed_metrics=['Revenue per Unit'],
    having='[Revenue per Unit] > 10'
)

Execute Query

query(query_name: str, return_internal_metrics: bool = False) -> pd.DataFrame

Executes a previously defined query by name.

Parameters:

  • query_name: Name of a previously defined query
  • return_internal_metrics: The query might need metrics and/or computed metrics which are internally used for calculation of computed metrics, having and/or sorting. If this parameter is set to True they will also be retrieved. Can be useful for debugging or validation.

Returns:

  • Pandas DataFrame with the query results

Example:

# Define a query
cube.define_query(
    query_name="sales_analysis",
    dimensions=set(['region', 'category']),
    metrics=['Revenue', 'Units']
)

# Execute the query
result = cube.query("sales_analysis")

# Apply a filter and run the same query again
cube.filter({'region': ['North']})
filtered_result = cube.query("sales_analysis")

Dimensions Only

dimensions(
  columns_to_fetch: List[str],
  retrieve_keys: bool = False, # Internal use, these are autogenerated autonumbered keys each of shared column names have.
  context_state_name: str = 'Default',
  query_filters: Optional[Dict[str, Any]] = None
) -> pd.DataFrame

Fetch unique values for specified dimension columns.

Parameters:

  • columns_to_fetch: List of dimension column names to retrieve
  • retrieve_keys: Whether to include link table keys in the result
  • context_state_name: Which context state to use
  • query_filters: Additional filters to apply just for this query

Returns:

  • Pandas DataFrame with unique combinations of the requested dimensions

Example:

# Get unique region/category combinations
result = cube.dimensions(['region', 'category'])

# Get unique region/category combinations from a specific context state
result = cube.dimensions(['region', 'category'], context_state_name='State1')

Single Dimension Values

Fetch distinct values for a single dimension.

dimension(dimension: str) -> pd.DataFrame

Example:

products = cube.dimension('Product')