Analytics Semantic Layer (Cube)
The Analytics Semantic Layer provides a consistent interface between RIO's ClickHouse analytics database and downstream consumers such as dashboards, applications, and AI assistants.It defines 35 analytical cubes covering opportunities, quotes, orders, accounts, products, territories, contracts, entitlements, leads, activities, and related business data. Each cube provides predefined measures, dimensions, and segments, ensuring that commonly used business metrics are calculated consistently across consumers.
What this service does
RIO stores its analytical data in ClickHouse, where raw tables contain the underlying business data. Querying these tables directly requires consumers to understand table relationships, joins, historical records, and business definitions.
The Analytics Semantic Layer addresses this by using Cube as a semantic modeling layer over ClickHouse.
Cube provides:
- Measures — predefined calculations such as counts, sums, and amounts.
- Dimensions — fields used to group, filter, and analyze data.
- Segments — reusable filters for common query scenarios.
- Consistent business definitions — ensures the same metric produces the same result across consumers.
This allows dashboards and AI-powered applications to query standardized business concepts instead of working directly with raw ClickHouse tables.
The 35 cubes
The cubes cover opportunities, quotes, orders, products, accounts, territories, contracts, entitlements, leads, activities and more. They split into two patterns:
Dimension cubes represent the current state of a business object (e.g. the current details of an account or product). Examples: dim_opportunity, dim_account, dim_product, dim_territory, dim_contract, dim_campaign.
Fact cubes store historical snapshots — one row per object per point in time. Examples: fact_opportunity_history, fact_quote, fact_sales_order, fact_quote_detail_history, fact_salesorder_detail_history, fact_bpf_history, fact_quota_history.
There are also two special cubes:
- agg_product_kpi — pre-aggregated product KPIs built from quote and order line items, used for product-first dashboards.
- bridge_product_account — links products to accounts with lifetime totals (e.g. total spend, quote count).
The full list of deployed cubes:
| Group | Cubes |
|---|---|
| Opportunities | dim_opportunity, fact_opportunity_history, fact_opportunity_product, fact_opportunity_stage_history |
| Quotes & Orders | fact_quote, fact_quote_detail_history, fact_sales_order, fact_salesorder_detail_history |
| Accounts | dim_account, dim_account_history, fact_account_team_history |
| Products | dim_product, dim_product_history, agg_product_kpi, bridge_product_account |
| People & Territories | dim_person_history, dim_territory, dim_territory_history, dim_user_group_history, fact_deal_team_history |
| Tenants | dim_tenant_history, dim_tenant_role_history, dim_tenant_status_history |
| Contracts & Entitlements | dim_contract, dim_contract_history, fact_entitlement_header, fact_entitlement_header_history, fact_entitlement_line, fact_entitlement_line_history |
| Other | dim_campaign, fact_activity_log, fact_bpf_history, fact_lead_snapshot, fact_quota_history, fact_quota_audit_history |
MCP server for AI assistants
The MCP server is a lightweight API that allows AI assistants (such as Claude or other large language model tools) to interact with the semantic layer programmatically.
The MCP server provides the following tools:
| Tool | What it does |
|---|---|
| List tables | Returns the names and descriptions of all available cubes |
| Get table details | Returns all measures, dimensions, and segments for specific cubes |
| Run a query | Sends a query to Cube and returns the results |
| Get analyst instructions | Returns guidelines that tell the AI how to correctly query RIO data |
| Get my details | Returns the authenticated user’s identity (tenant, role, person ID) |
| Search knowledge base | Searches emails, meeting transcripts, and calendar notes stored in AWS Bedrock (separate from the analytical cubes) |