BI applications send their queries to a cube hosted on the AtScale engine. This cube metadata is used to interpret the SQL queries sent by the BI tools, optimize them for the best performance, and then execute them directly against the corresponding data warehouse.
AtScale's cost-based query planner and optimizer dynamically builds and maintains aggregates (summary tables) based on the queries issued by BI users. Once aggregates exist, future queries can run against the aggregated data instead of the raw data, dramatically improving query performance.
Design Tips
Always review the latest documentation for the best tips on the design of your model. We are highlighting some common issues below:
Common Symptoms:
-
You see different results when the query hits an aggregate (aggs) table.
-
Query errors when deduping a dimension table.
-
Duplicate listing of dimension members or double counting measure values for some dimension members
Common Mistakes
-
Using the same key field for different hierarchy levels (there are use cases for this, but in most cases, this is a red flag)
-
Using level key columns that don't uniquely identify each level member (this can cause incorrect results depending on the order of inbound queries and thus the aggregates that exist on the system)
-
Specifying level name columns that have a different scope than their key columns (can look right in the BI tool, but like #2, can deliver wrong results in unpredictable ways)
-
Using "unique" for fields that are not unique in the dataset (this should almost always be checked for only the lowest level of the hierarchy)
-
The secondary attribute key column and value column have different scopes.
Modeling Guidelines
-
Level Keys: A good question to ask when modeling level keys is this, "for each member of the level, are the values re-used across multiple values of the parent level?" If so, you must usually add the parent key field(s) to the child level or use a different column that uniquely identifies each level member in the hierarchy. If level keys don't uniquely identify each member in the level, then the engine will produce the wrong results in non-deterministic ways.
-
"Uniqueness": only choose this level key option if the key really is globally unique in the dataset (like a primary key) - if this is incorrectly set, you could see double counting of dimension members.
-
Level Values: Excel does name-based lookups, so some incorrectly scoped hierarchies look right in Excel, but the engine will not use the aggregates correctly. Please ensure your level keys have the same desired scoping as your level names. You can not rely on the AtScale preview feature or the BI tool hierarchy rendering to determine if a hierarchy is modeled correctly. You have to sample/probe the data in the underlying tables to discover the scoping characteristics of each column used in each level.
-
Secondary Attributes: Despite their rendering in the dimension panel, they exist in their hierarchy so you have to decide if you want them to match the level design conceptually. In most cases, the secondary attribute value and key fields will NOT match the scope of the level keys. The main thing to watch out for is that the value field's scope matches the key field's scope. If in doubt, using the same field for both the secondary attribute value and key fields is usually safe.