Sql-server – Sql Server 2012 data warehousing and different versions

data-warehousesql-server-2012

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?

Best Answer

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.

Related Question