Welcome to PC Teach Me...

Free Computer Training Videos.

Although you do not need to register to access this site. Registering has its benefits. Such as:

  • Access to training material associated with the video you are watching [if applicable]
  • Automatic email updates when new training videos are available.
  • Ask for a training video.
  • Exclusive access to pose questions to me [limited availability as I am doing this for free :-)].
  • Free contribution of your own training topics, this includes referencing your videos outside of PC Teach Me.
  • Its Free!

Member Login

Lost your password?

Not a member yet? Sign Up!

By registering with this blog you are also agreeing to receive email notifications for new posts but you can unsubscribe at anytime.

25021 Registered Users
71 Number Of Articles
Free Video Tutorials (Email) Free Video Tutorials (RSS) Free Video Tutorials (Twitter) Free Video Tutorials (Flickr)

BI Perspective: SQL 2008 R2 Editions

BIPerspectiveSQL2008R2 BI Perspective: SQL 2008 R2 Editions

SQL Server 2008 R2 BI Perspective

The following post shows the differences between Standard and Enterprise Editions of SQL 2008 R2.  What I thought which was important to note is what from a BI perspective are the key advantages that can be made.

I have taken the comparison guide located on the Microsoft SQL Server 2008 R2 site (here) and overlaid it with descriptions as to what benefits can be derived from a BI Perspective.

PLEASE NOTE:There are additional advantages which can be made when combining with Windows 2008 Server (due to O/S architecture changes).  The hot new features include  “PowerPivot” (SharePoint 2010) & “Master Data Management (MDM)”.  These will be discussed via video at a later date.

So grab a coffee and lets get started…

This post is one of many SQL tutorials on this site click here for more.

SQL Scalability And Performance

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Number of CPUs 8 CPU 4 CPU
Memory Utilization 2 TB Ram 64 GB Ram Better scalable memory architecture
Database Size 524PB 524PB
x32 Hardware Support Yes Yes
x64 Hardware Support Yes Yes
Data Compression Yes Allows for large reduction in disk space without significant performance drain.
Resource Governor Yes
IA64 hardware support Yes
Table and Index Partitioning Yes Allows for optimal placement of data on different drives.  E.g Data is on c: but index on d:  This would then invoke parallel I/O
Parallel Index Operations Yes
Parallel Consistency Checks (DBCC) Yes
Scalable Shared Databases Yes Deploy read-only reporting databases across multiple servers.  This will become more important with the larger BI piece
Indexed views Yes Allows for faster retrieval of data via a view.  In standard edition views are seldom used due to this and other constraints.
Enhanced Read-ahead and Scan Yes Faster data reading with joined up approach of CPU and disk IO. It predicts the data ahead of schedule.

 

SQL High Availabilty

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Database Mirroring Yes (full) Yes (Single Threaded, Safety Full Only)
Online System Changes Yes Yes
Log Shipping Yes Yes
Clustering 16-node failover clustering 2-node failover clustering Better for the larger enterprise.  This is more of a DBA aspect to consider…
Automatic Corruption Recovery from Mirror Yes Yes
Log Stream Compression Yes Yes
Online Indexing Yes
Mirrored Backups Yes Better for the larger enterprise.  This is more of a DBA aspect to consider…
Backup Compression Yes Allows for large reduction in disk space without significant performance drain.
Hot-add Memory and CPU Support Yes
Database Snapshots Yes
Fast Recovery Yes
Online Page and File Restore Yes

 

SQL Repliction

Detailed Feature Comparison Core Editions Description
Enterprise Standard
SQL Server Change Tracking Yes Yes
Merge Replication Yes Yes
Transactional Replication Yes Yes
Snapshot Replication Yes Yes
Heterogeneous Subscribers Yes Yes
Oracle Publishing Yes If Oracle (v9i or above) is publishing its databases for replication.  SQL server can also be a participant allow direct data transfer to SQL and alleviate the extraction requirements on ETL

 

SQL Enterprise Security

Detailed Feature Comparison Core Editions Description
Enterprise Standard
C2 Complaint Tracing Yes Yes
SQL Audit Yes Better for the larger enterprise although not a specific reason to use in reporting but has the potential to assist with  security compliance. This is more of a DBA aspect to consider…
Transparent Database Encryption Yes Better for the larger enterprise although not a specific reason to use in reporting but has the potential to assist with  security compliance. This is more of a DBA aspect to consider…

 

SQL Manageability

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Extensible Key Management Yes
Hypervisor Support Yes Yes
Database Migration Tools Yes Yes
Policy-Based Management Yes Yes For AD integration security groups can be easily configured.  For example Read-only, admin access etc…
SQL Server Management Studio Tools Yes Yes
Database Mail Yes Yes
Performance Data Collection Yes Yes
SQL Server Agent Yes Yes
Database Tuning Advisor Yes Yes
Plan Guides Yes Yes
SQL Profiler Yes Yes
Standard Performance Reports Yes Yes
Distributed Partition Views Yes
Automatic Query-to-index-View Matching Yes This is the major performance gain with views which currently is not a performance advantagee on Standard edition.
Unlimited Virtualization Yes

 

SQL Data Warehousing

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Create Cubes Without a Database Yes Yes
Auto-generate Staging and Data warehouse Schema Yes Yes
Attribute Relationship Designer Yes Yes
Efficient Aggregation Designers Yes Yes
Scalable read-only AS Yes
Partitioned Cubes Yes Major benefit as this allows us to store historical aggregated data on other less effect drives which leaves current and user focused aggregations within the high performance realm. This is a MAJOR performance justification for Cubes being on enterprise edition.
Distributed Partitioned Cubes Yes
Data Compression Yes The bigger the cube the more space required.  As before data compression will provide longevity to existing hardware I/O.
Star Join Query Optimizations Yes If creating a cube which comprises of different cube data.  This will provide performance benefits.  It will work without this but depending on setup is considerably slower.
Change Data Capture (CDC) Yes If changes occur on base database tables such as an edit, insert and delete.  CDC will automatically(if configured) update the cube.  Traditionally this is performed by Triggers or scanning for timestamp record changes which are costly to performance.

 

SQL Integration Services SSIS

Detailed Feature Comparison Core Editions Description
Enterprise Standard
SQL Server Import and Export Wizard Yes Yes
Log Providers and Logging Yes Yes
XML Source Yes Yes
SSIS Run-time Yes Yes
Basic Data Profiling Tools Yes Yes
SSIS Package Designer & Service Yes Yes
Data Mining Query Transformation Yes Allows data mining models to be used within SSIS.  This allows a merger of technologies DMX and SQL.
Data Mining Model Training Destination Adapter Yes
Fuzzy Grouping Transformation Yes Provides character algorithms to resolve spelling mistakes or present options as to where the records should be grouped by.  I.e. via its complex algorithm it provides a prediction on fields which are empty.  E.g. employee number missing in extracted table but is present in HR system.  Using Fuzzy Lookups on Employee name and department could provide the recommended Employee ID number.This is VITAL on identifying and correcting data from source data systems (part of normalisation) .
Fuzzy Lookup Transformation Yes As above on a row by row inspection
Term Extraction Transformation Yes
Dimension Processing Destination Adapter Yes Provides the ability to process SSAS object within the SSIS package.  Very useful on overnight runs of data(ETL).
Partition Processing Destination Adapter Yes As above

 

SQL Reporting Services (SSRS)

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Reporting Services Memory Limits OS Max OS Max
Report Designer Yes Yes
Report Manager Yes Yes
Role-based Security Yes Yes
Basic Data Profiling Tools Yes Yes
Export to Excel, Word, PDF and Images Yes Yes
Report Server Application Embedding Yes Yes
Enhanced Gauges and Charting Yes Yes
Custom Authentication Yes Yes
Ad-hoc Reporting (Report Builder) Yes Yes
SharePoint Integration Yes Yes
Email and File Share Delivery Yes Yes
Report History, Scheduling, Subscriptions and Caching Yes Yes
Data Source, Delivery and Rendering Extensibility Yes Yes
Scale-out Operational Report Configuration Yes
Data-driven Report Subscriptions Yes VITAL component to allow mass reporting based on dataset results.  Example, imagine invoices sent to a large list of customers.  Traditional reporting would require you to run the report for each customer, with Data-driven subscriptions this allows a subscription to be set ONCE but refers to the customer name, address and email details and iterate one customer at a time through a dataset development have defined.  This is a Massive administrative overhead if not used, without this you would have to create x number of subscriptions where 1 subscription could do.  Think mail-merge and your on the right track.
Report Scale-out Deployment Yes Share reporting services across multiple servers.  Geographic server locations can assist with speed.  i.e. could have a server in UK to cater for those users and one in the US.
Infinite Click-through in Ad-hoc Reports Yes Allows reports generated in Report builder to have the capacity to “jump” to other reports/tables to keep drilling the data .

 

SQL Analysis Services (SSAS)

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Analysis Services Backup Yes Yes
Dimension, Attribute Relationship, Aggregate & Cube Design Yes Yes
Personalization Extensions Yes Yes
Analysis Services Scalable Shared Databases Yes
Account Intelligence Yes Provides built in Intelligence to work our Asset/Liability accounts income and expense etc.  This is a potential huge timesaver where finance cubes are conserned
Linked Measures and Dimensions Yes Allows the same dimension to be used on multiple cubes.  Sound advantage to development time as one dimension could cater for multiple cubes thus saving developers time.
Perspectives Yes
Semi-additive Measures Yes Very important if you are aggregating over timeFor example, Sales and costs are fully additive; if you sell 100 yesterday and 50 today then you’ve sold 150 in total. You can add them up over time.Stock levels however are semi additive; if you had 100 in stock yesterday, and 50 in stock today, you’re total stock is 50, not 150. It doesn’t make sense to add up the measures over time, you need to find the most recent value.
Writeback Dimensions Yes Very important if we are using budgeting, reforecasting & working capital analysis.  This allows the interactivity of financial trends instantaneously as data is stored to the cube.
Partitioned Cubes and Distributed Partitioned Cubes Yes If Cube(s) are large in size, you can proportion the cube over multiple server which take the brunt of each partitioned area.  Example One server deals with GL data another deals with AP data etc…
Custom Rollups Yes Parent-child relationships in dimensions are more effective managed in enterprise and provides additional MDX commands for interrogation

 

SQL Data Mining

Detailed Feature Comparison Core Editions Description
Enterprise Standard
Excel 2007 and Visio 2007 Add-in Support Yes Yes
Comprehensive Set of DM Algorithms Yes Yes
Integrated Data Mining Tools (Wizards, Editors, Model Viewers, Query Builder) Yes Yes
Parallel Model Processing Yes Processing of data mining models can be a significant drain on resources.  The ability to parallel process more or less doubles the capacity of processing models as CPU’s are synced to work together.
Cross Validation Yes As multiple models can be created to produce data mining result (e.g. neural networks, decision tress etc…) There is a need to validate the results to accurately map the correct model.  This facility assists the developer in deciding the correct model for each situation.
Support for Plug-in Algorithms Yes You have several mining models at your disposal.  However, you can create or purchase dedicated mining models which are bespoke for a business.  With this feature it will allow the business to have a dedicated mining model.
Advanced Configuration and Tuning Options for Data Mining Algorithms Yes
Support for Pipeline Data Mining and Text Mining with Integration Services Yes As discussed earlier, SSIS has the ability to merge / control Mining models.  Ideal, during the ETL process.
Sequence Prediction Yes This is the model that AMAZON use when predicting what to show the end user on the “other items of interest” section.  It uses advanced algorithms which identify paths in data such as drill through to other items.  This is the final part of development on models as it provides control over the results that users can follow within reports, SQL ASP front ends etc.

Related Posts

1 Comment

  1. ESAYAS says
    21 Nov 13 at 3:49pm

    I THANK YOU! AS BEGINNER I GOT SHORT PRECISE AND VALUABLE KNOWLEDGE FREE THANKS AGAIN

Leave a Comment

You must be logged in to post a comment.

Valid XHTML 1.0 Transitional website security