Introduction
Kepion supports different editions of SQL Server. The features and functionality available to use in Kepion does depend on your SQL Server edition. This article explains how different SQL editions affect designing Kepion applications.
Differences in functionality
| Function | 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 Time Aggregation By Account feature of the Account dimension is not available in SQL Standard edition. However, this functionality can be emulated by writing your own MDX logic on the cube. Kepion supports all valid MDX that SSAS supports; therefore, anything missing from the built-in ByAccount logic can be emulated as an 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, the Q1 member of the Time dimension should not aggregate the first 3 periods of Q1 but should instead show the data from the last period. For most standard account logic, the ByAccount logic is unnecessary. P&L/Income Statement calculations primarily rely on unary operators, which are fully supported across all editions of SQL Server.
Multiple partitions: This feature is constrained in SQL Standard by limiting you to 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.
In Kepion, you can create multiple measure groups with the same dimensionality. That means, you can work around this limitation by creating multiple measure groups to support the additional partitions.
Proactive caching: Kepion emulates this feature for SQL Standard Edition by leveraging the built-in feature for Enterprise and Business Intelligence editions. End users are able to experience the full ROLAP capabilities across all versions of SQL.
Perspectives: This feature is a bonus for those with the Enterprise and Business Intelligence versions of SQL Server. SQL Standard does not support this feature. You can work around this with better training documentation on which fields should be used for reporting in 3rd party tools.
Differences in scalability
Regarding scalability differences between SQL Server Enterprise and Standard editions, both can handle large-scale global deployments of Kepion applications. SQL Server Standard Edition has an upper limit of 24 CPU cores for database engine or analysis services. 24 CPU cores have been more than sufficient even for high-performing production environments.