SQL cube: GROUP BY CUBE option

5 views
Skip to first unread message

Q&A Bot

unread,
Apr 2, 2021, 10:03:47 AMApr 2
to seektab...@googlegroups.com

Q:
i'm looking for documentation about UseGroupByCube feature
Is that feature available also for SQL Server DB?

this option allows to display sub-totals / grand total in the pivot table for a measure that is calculated with a custom SQL aggregate expression.
This is a measure with Type=FirstValue where SQL aggregate expression is specified in "Params"; maybe most typical case for this kind of measure is 'count-unique':

image.png

(type "FirstValue" means that SeekTable just 'takes' the first value from DB query result for each unique dimensions 'tuple')

SeekTable doesn't "know" the nature of this measure, and it cannot calculate sub-totals/grand total on its side (in most cases this is technically impossible - like for "COUNT(distinct)"), and these pivot table cells will be empty. 
It is possible to calculate & load these sub-totals if the database supports "GROUP BY CUBE" syntax, and "UseGroupByCube" option enables this syntax in the generated SQL.

SQL Server supports "GROUP BY CUBE" (actually, many popular DBs like PostgreSQL support "GROUP BY CUBE" as well).
However, MySql doesn't support this SQL syntax and this option cannot be used with MySql.
Reply all
Reply to author
Forward
0 new messages