Kepion SQL Standard Support

By - Jan 23, 2017

Kepion supports SQL Standard edition. However, there are a few differences in functionality between SQL Standard and SQL Enterprise. We will explore below what those differences mean when designing applications in Kepion.

Feature Name Enterprise Edition Business Intelligence Edition Standard Edition
Semi-additive Measures Yes Yes No
Hierarchies Yes Yes Yes
Perspectives Yes Yes No
Account intelligence Yes Yes Yes
Time Intelligence Yes Yes Yes
Custom rollups Yes Yes Yes
Write-back Yes Yes Yes
Drill-through Yes Yes Yes
Advanced hierarchy types (Parent-Child, Ragged Hierarchies) Yes Yes Yes
Multiple Partitions Yes Yes Yes, up to 3
Proactive Caching Yes Yes No
MDX queries and scripts Yes Yes Yes
Role-based security model Yes Yes Yes
Dimension Security Yes Yes Yes
MOLAP, ROLAP storage modes Yes Yes Yes


Semi-additive Measures: The built-in ‘ByAccount’ aggregation logic is not available in SQL Standard edition. However, this functionality can be emulated by writing your own MDX logic on the cube to perform the calculations. Kepion supports all valid MDX that SSAS supports, therefore anything missing from the built-in ByAccount logic can be emulated as MDX script. The ‘ByAccount’ logic typically is used for Balance Sheet calculations, where the data has specific aggregation based on the Time dimension. For instance, ‘Q1’ member of Time dimension should not aggregate the first 3 periods of ‘Q1’, but rather should show the data from the last period. For most standard account logic, the ByAccount logic is not necessary. P&L/Income Statement calculations primarily rely on unary operators and these are fully supported across all editions of SQL Server.

Multiple Partitions: This feature is constrained in SQL Standard by limiting you with up to 3 active partitions per measure group. Kepion models come with a Writeback partition and a Rule partition.  Both count as a partition if they are active.

However, in Kepion you can create multiple measure groups with the same dimensionality. Thus, you can effectively work around this limitation by creating multiple measure groups to support the additional partitions.

Proactive Caching: Kepion emulates this feature for SQL Standard edition, while it leverages the built-in feature for Enterprise and Business Intelligence editions. From an end user perspective, users of Kepion will experience the full ROLAP capabilities across all version of SQL.

Perspectives: This feature is ‘nice to have’ for Enterprise and Business Intelligence version of SQL Server. However, SQL Standard does not support this feature and can be worked around with better training documentation as it relates to which fields should be used for reporting in 3rd party tools.