8

With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard.

The full comparison between the three: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

The business intelligence edition implies that the purpose of it is for data warehousing and covers what seems to be key concerns for that:

  • Self-Service Business Intelligence (Alerting, Power View, PowerPivot for SharePoint Server)
  • Advanced Corporate BI (Tabular BI Semantic Model, Advanced Analytics and Reporting, VertiPaq™ In-Memory Engine)
  • Advanced Data Integration (Fuzzy Grouping and Lookup, Change Data Capture, Advanced Data Mining)
  • Enterprise Data Management (Data Quality Services, Master Data Services)

However the enterprise edition is the only version that has:

Data Warehousing (ColumnStore Index, Compression, Partitioning)

What functionality does this entail that is seperated between the BI and Enterprise editions?

1
  • This information holds for SQL Server 2014, with one noteworthy (IMHO) addition: 2014 EE includes Attunity drivers for SSIS, which are supposed to dramatically increase throughput with Oracle databases. Also, in 2014 the memory limit for Standard and BI editions was increased to 128 GB. Commented Aug 18, 2014 at 16:12

2 Answers 2

18
+50

Business Intelligence Edition

Business Intelligence edition has some useful features, like Master Data Services and non-additive aggregations (i.e. anything but sum/count). EE has partitioning and the rest of the large database features. The EE features are mostly relevant to users with large data volumes. If you have less than (say) 100GB of data then you can probably get by with BI edition. B.I. edition also has a limit on the number of CPU cores and memory that can be used by the database server, although this does not appear to apply to Analysis Services or Reporting Services.

A more detailed breakdown of the S.E., B.I. and E.E. features can be found here.

Some things that will be fine with B.I. edition

  • Most OLAP applications - B.I. edition seems to give you the clever aggregates (last non empty etc.) and other features that SE doesn't on the OLAP server. By the look of the link, all SSAS features present in EE are present in B.I. edition, which makes it a bit more of a contender for data marts.

  • MDM applications - B.I. edition comes with Master Data Services.

  • Moderate data volumes. You can probably get away with (say) 100GB or so on BIE by applying brute force at the hardware level (fast disks).

  • B.I. edition supports distributed partitioned views, which gives you a basic read-only sharding capability. However, the additional hardware and licensing may be no cheaper than biting the bullet and getting EE.

  • SSRS seems to be the same across B.I. and Enterprise editions.

  • Memory and CPU core limits do not apply to SSAS and SSRS servers.

Some things you will need Enterprise Edition for

  • If you have compliance requirements for physically secure data then the encryption and audit facilities of EE may be desirable. Note that this is new in 2012.

  • Table partitioning is an EE only feature. If you want to use table partitions to manage large data volumes you will need EE.

  • Star join transformations are only supported in EE. If you have an application with lots of highly selective (<1% coverage) queries on a very large fact table you might get a win from star transformations. This feature isn't really well documented in SQL Server circles, though, so it's hard to tell how well it works in practice.

  • Columnstore index - if you want to use this for fast ROLAP applications (using report builder or a 3rd party ROLAP tool such as Business Objects) then you may get significant mileage from this feature on EE.

  • Table compression may be useful for archiving old data.

  • B.I. edition only supports servers of a certain size - 64GB RAM, 4 sockets or 16 cores for the database server. If you want to scale above a two socket machine then you will probably need EE.

  • B.I. edition is only licensed on a 'Server + CAL' basis.

  • Parallel DBCC and index builds are only supported in EE. If you want to drop and recreate indexes for ETL loads this may reduce your run-times, particularly on incremental loads onto large incumbent data sets.

  • EE has a query re-write facility (called 'automatic use of indexed view by query optimiser'). If you want to use these to boost ROLAP performance you may want EE. However, although this is quite a mature feature on Oracle I can't really vouch for how well it works on SQL Server in practice - although SQL Server does have a CUBE operator on GROUP BY, which is mostly indended for this application.

  • EE has fast Oracle and Teradata adaptors for SSIS, and adaptors for various other 'enterprisey' sources such as SAP BW.

  • Some of the MDM-ish featues of SSIS - e.g. fuzzy lookups - are only available in EE.

  • Change Data Capture is an Enterprise Edition only feature.

6
  • +1 ... Thanks for the info. So essentially for a shop with tremendous amounts of data, you'd run EE for the ODS and then separate BI instance(s) for the analytics. Is that a fair application to take from your info?
    – swasheck
    Commented Feb 3, 2012 at 15:44
  • 1
    Depending on the size of the data marts, you might need EE for those too. Column store indexes won't do anything to speed up your ETL - they're only good for fast queries. I'd say that B.I. edition would be good for smaller data volumes rather than data marts on a larger warehouse system - unless the data marts were highly aggregated. In practice, once you're stumping up for EE then the cost savings of B.I. edition for data marts might be a bit of a drop in the bucket. Commented Feb 3, 2012 at 15:47
  • 1
    @swascheck - I found a more detailed breakdown Here Commented Feb 3, 2012 at 16:01
  • 1
    @swascheck - BI Edition does support partitioned SSAS cubes, so it might be more useful for data marts than previously suggested. Commented Feb 3, 2012 at 16:23
  • you must either love this stuff or are extremely helpful. Perhaps it's both/and. Either way - thank you so much.
    – swasheck
    Commented Feb 3, 2012 at 16:36
5

"Business Intelligence" covers everything from database design to Excel monkeying.

Based on this, my interpretation of the new BI edition is "whizzier" features on the OLAP/cube/analysis/mining side compared to the RDBMS side.

Arguably, only "column store" is really relevant to BI. Partitioning itself may be Enterprise edition only, but the ALTER TABLE..SWITCH can be run in Standard edition.

The BI edition also has no memory use limit for SSAS and SSRS

2
  • 1
    Could you elaborate on the column store? Especially along the lines of why would I miss it in the BI version, could that feature alone be reason to justify using enterprise? Commented Jan 23, 2012 at 14:23
  • 5
    The columnstore index implements a 'column' data structure that stores data in a more compact format and is much faster to query a single column than a table scan across a table holding that column. Essentially, a column-store data structure was Sybase IQ's principal gimmick, and IIRC the native data structure used by SSAS to persist data is a column store type format. Commented Feb 3, 2012 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.