Analytic & Sharepoint : Pros and Cons

I've had the pleasure (or torture) of integrating four enterprise class analytic packages with SharePoint.  I'm putting together this post to list out the pros and cons that I've experienced with each tool.  I know it's not an exhaustive list, but I hope you'll find it useful if you're exploring analytics in SharePoint. 

All of these tools use external tools to develop dashboards and reports. Also, they each have the ability to produce many different types of charts and grid reports.  All are interactive in one aspect or another, and are web-enabled (of course).

SQL Server Reporting Services (SSRS)

Licensing:

• Included with SQL Server

Pros:

• Reports can be developed with Visual Studio or Report Builder (Free Download)
• Works equally well with relational or dimensional data sources
• Exports reports to PDF, Excel, and many other platforms
• Produces "pixel perfect" reports for viewing online or printing
• Includes geospatial analytical charting
• Uses standard HTML capabilities to render reports on many browser platforms
• Works well with "touch" devices (tables, phones)
• Most flexible analytic tool listed 
• Data can be "blended" from multiple sources
• Data may be loaded from any ADO.Net data source (SQL Server, Oracle, Excel, Access, etc.)

Cons:

• Requires the SSRS server to produce reports for users
• Does not fully support "touch" interfaces for mouse-hover tool tips
• Developing reports requires thorough knowledge of the data sources
• Development user interface is geared toward experienced developers

Performance Point Services (PPS)

Licensing:

• Included with SharePoint Enterprise

Pros:

• Automatically detects the structure of the multidimensional data, allowing users to explore data via drill through.
• Included in the SharePoint 2013 software distribution, no additional installation media required
• When you drill-through items, only the affected components are refreshed, other KPIs and worksheets remain unaffected.
• PPS dashboards are true dashboards, which consolidate wide data that can either be tied together or desperate.  In this sense it is the only "true" dashboard tool presented here.

Cons:

• Does not include geospatial analytical charting, but can be augmented with SSRS
• Requires SQL Server Analysis Services dimensional data to operate
• Requires two-button mouse support for full capabilities (drill-through and mouse hovers)
• Dashboard development must be conducted on a system that is on the same domain as the SharePoint server.
• Development user interface is geared toward experienced developers

Power View (a.k.a. Power BI)

Licensing:

• Included with SharePoint Enterprise

Pros:

• Microsoft Excel or web-based tools are used to author dashboards

• Supported in both Office 365 and on-premises SharePoint 2010 and 2013
• Includes  geospatial analytical charting
• Utilizes HTML5 for cross browser and platform support
• Works well with "touch" devices (tables, phones)
• Data can be embedded in spreadsheets and automatically hosted by SSAS.

Cons:

• Requires a Multidimensional data source or Tabular data source for data
• Not fully "touch" enabled, popup tool tips require mouse hover
• Although some editing on PowerView dashboards can be done on-line, the best experience comes using Excel 2013.

Tableau

Licensing:

• Per core or per user licensing (ref)
• Tableau Desktop licensed per user for development activities
• Tableau Server or Tableau Online (cloud offering) for publishing, and separately licensed.
• A breakdown of costs has put together by Brad Fair at Interworks.

Pros:

• Beautiful worksheets and dashboards out of the box
• Developing worksheets and dashboards is a drag & drop activity.
• Data can be "blended" from multiple sources
• Tableau "packaged dashboards" (.twbx files) are self-contained, with some or all data embedded.
• "twbx" files can be distributed to users who can view them using the free Tableau Viewer.
• Works best with single "flat" data sources or with  dimensional data such as SQL Server Analysis Services (SSAS)
• Supports geospatial analysis and charting (i.e. plotting points on a map)
• Software assurance (a.k.a. free upgrades) are included with maintenance costs 

Cons:

• Relatively expensive, if you already have an investment in SQL Server or some other BI suite with required yearly maintenance costs (see breakdown of costs)
• Does not directly integrate into SharePoint, dashboards are shown via Page Viewer IFRAME HTML elements.
• Not fully "touch" enabled, some features such as tool-tops require mouse-hover to show.
• Tuning queries is difficult, since Tableau was designed for the embedded data model first
• Tableau Online requires that reports be 100% "twbx" embedded data or have access to internet enabled data sources
• When using dimensional data, textual reports (non numeric) are difficult, and the dimensional model must be tuned to support them, through "existence" facts.

Learning Management Systems for SharePoint

A few of my clients have inquired about LMS systems for SharePoint. What I found is that most of them focus on on-premises deployments, mostly because they've been around since the WSS3.0/MOSS days. It also seems that they have their own databases and custom web parts to work with content. But over all they seem to be competent and fairly feature compatible with Moodle.

So this is list is by no means exhaustive, but I think it gives a pretty good jumping off comparison between Moodle, SharePoint LMS, and ShareKnowledge (the latter two being SharePoint integrated LMS systems).

Feature	Moodle	SharePoint LMS	ShareKnowledge LMS
Enrollment
Supervisor Assigned Course	Yes	Yes	Yes
Elecctive Courses	Yes	Yes	Yes
Cohort Enrollment (Group Enrollment)	Yes	Yes	Yes
Guest Access	Yes	Yes****	Yes****
Course Tracks	Yes	Yes	Yes
Category Enrollment (Enroll in courses by category)	Yes
External Database	Yes
Flat Files	Yes
IMS Enterprise	Yes
LDAP Enrollment	Yes
Mnet - Linked Moodle Sites	Yes
PayPal	Yes
User Interface
Responsive Design	Yes	Yes***	Yes***
Personalised Dashboards	Yes	Yes	Yes
Collaborative Tools	Yes	Yes	Yes
Calendar	Yes	Yes	Yes
Drag & Drop Files	Yes	Yes	Yes
Web based Text Editor	Yes	Yes	Yes
Notifications	Yes	Yes	Yes
Progress tracking	Yes	Yes	Yes
Customizable Site Design	Yes	Yes	Yes
Multilingual Capabilities	Yes	Yes	Yes
Bulk Corse Creation	Yes	Yes	Yes*
Social Tagging		Yes	Yes
Integrated Access Controls	Yes	Yes	Yes
Course Capabilities
Platform based Course & Quiz	Yes	Yes	Yes
Randomized Questions			Yes
Time Limits			Yes
SCORM Integration	Yes	Yes	Yes
AICC Integration	Yes	Yes
LTI External Web Site Integration	Yes
Instructor Lead Course	Yes	Yes	Yes
Self-paced Course	Yes	Yes	Yes
Blended Instructor/Self-paced	Yes	Yes	Yes
Multi Media Integration	Yes	Yes	Yes
Peer and Self Assessment	Yes
Gradebook	Yes	Yes	Yes
Scaled Grading	Yes	Yes	Yes
Course Privacy	Yes	Yes	Yes
Version Control			Yes
Course Certificates	Yes	Yes	Yes
Reporting
Built in Reports	Yes	Yes	Yes
Integrated Report Builder	Yes	Yes	Yes
External Integration			Yes**
Integration
AD Synchronization			Yes
HRIS Sintegration			Yes
Outlook Claendar & Notifications			Yes
WebEx, Lync, GoToWebinar, AdobyConnect			Yes
* ShareKnowledge has a bulk question load capabilitiy as well
** Via SQL Server Reporting Services or other BI Reporting tools
*** Responsive Design must be built into the SharePoint installation
**** Guest Users must be authenticated

Pitfalls of Site Collection Consolidation

So, you're considering consolidating Site Collections in SharePoint.  I suspect that some of your reasons are:

1. I feel like I have too many site collections.
2. It feels like it's difficult to manage the security of all of my site collections.
3. Publishing based navigation doesn't cross the site collection boundary.
4. We're really only hosting a single "site" in this web application anyway.

What do you loose when you consolidate Site Collections?

• Maximal, you can only have one Content Database per Site Collection.
• More content databases allow for different SQL Server based backup schedules based on the site collection's use.
• Content Databases will be at their smallest (individually) when there's only one Site Collection in each database.
• Content Databases can be distributed across multiple SQL Server instances, distributing the SQL workload across a SQL grid.
• Power Shell & Central Admin based backups all you to backup individual Site Collections.
• If you have one big site collection, your backup is going to be one big file.

What do you gain by consolidating Site Collections?

• Security will be just as difficult.
• If you users are installing no-code solutions (i.e. site templates), they'll need to be site collection admins.  This can get complicated quickly if users become an admin of the entire web app.
• You'll still need to audit the entire hierarchy's ACLs to ensure you're protecting information appropriately.
• Publishing based navigation can be used to navigate everything in your site.
• Granted that your web-sites don't get deeper than your maximum menu depth.  That's typically 2-3 levels based on what you've munged in stock Master Pages.
• With 3rd party solutions for SP2010 and Managed Navigation for SP2013, the problem becomes how important is top-menu security trimming.

What are some gotcha's if you decide to consolidate?

• Depending on the tool you use you need to match Site Collection Features in the Source and Target.
• Many Collection level features install content types and list templates, if these aren't in the target you're going to run into trouble.
• In SP2013 if your going from a Publishing Site to another site, Export-SPWeb and Import-SPWeb won't let you pass unless Publishing Infrastructure is turned on.
• SP2013 Workflows: They may not enable after merging site collections. Scenario:
• Source site collection has some SP2010 workflow remnants, and aren't necessarily in use.
• Destination site didn't have Workflows enabled before the merge.
• After the merge the Workflow feature won't enable because some of the content types are already present, and marked as non-replacable.
• It's really a bug in the installer that it doesn't recognize that these are the same, but it sill won't go.
• Solution: Enable Workflows prior to merging, or you're going to have to re-merge with a site collection where it is enabled, or workflows will be a no-go.

Short story, Site Collection consolidation is something that requires more than a few moments of thought.  Make sure you're doing it for the right reasons, take precautionary steps, and you'll meet with success.

Site Collection Consolidation

Site Collection consolidation is an interesting topic, and in my opinion one that bears some significant consideration before proceeding.  Why this article?  I recently worked with a client who wanted to consolidate from 50+ Site Collections to ~3.  The reason, the number of site collections seemed unmanageable at that point.  There was a only a single Content Database as well.  So since then I've been putting more and more thought in to the issue.  Here's some of the talking points:

Size:

• Microsoft recommends limiting content databases to 200 GB.
• Each site collection may reside in a single content database
• Thus recommend maximum size for a site collection is also 200 GB.

Security:

• Site Collections represent a monolithic security context, i.e. they do not inherit permissions from other objects.
• One object may be that a Site Collection inherits the ability to provide anonymous access from the Web Application that surrounds it.
• Site Collections inherit the authentication mechanism(s) defined by the Web App.
• Neither of these are permissions, but rather mechanical 'illities that are granted to the Site Collection by it's parent Web App.

Content and Navigation:

• With Publishing Infrastructure (SharePoint Enterprise), navigation can be auto-generated based on the Site-Subsite relationships
• Automatic security trimming based user access
• Publishing based navigation doesn't cross the Site Collection boundary
• I'm not sure if this is good or bad.  I'm usually building single tenant intranet sites, so this isn't a plus, but recently I've had a chance to work with a client who provides SharePoint sites to multiple tenants, and this feature is a plus.
• SharePoint 2013 brought about pinned Managed Metadata for navigation.
• Each site collection requires a copy of the pinned data, which can clutter the metadata tree.
• Security Trimming doesn't occur here

 

• Lists that refer to other lists only occur with in a single site collection.
• Again more of a problem for a single tenant with lots of site collections, a requirement for one collection per tenant.
• Controls that pull resources like XSLT from inside the SharePoint Web structure can't access content from other Site Collections.
• You'll need to duplicate those resources across collections.
• Master Pages must be installed in each site collection.
• Pages can't reference a master page from another collection.

So next post, more on Issues with Consolidating Site Collections.

Famous Omahans

OK, so not really a technical post, but being from Omaha, I just hit me that I should catalog some the famous among us.  (Listed by birth date)

Past:
Fred Astaire - b. 5/10/1899 d. 6/22/1987 -- Actor, Dancer, Choreographer, Singer, Musician
Henry Fonda - b. 5/19/1905 d. 8/12/1982 -- Actor
Gerald R. Ford - b. 7/14/1913 d. 12/26/2006 -- President of the United States
Marlon Brando - b. 4/3/1925 d. 7/1/2004 -- Actor
Malcolm X - b. 5/19/1925 d. 2/21/1965 -- Muslim Minister & Activist

Living:
Warren Buffet - b. 8/30/1930 -- 3^rd richest man in the world (Forbes Profile)
Nick Nolte - b. 2/8/1941 -- Actor
Joe Ricketts - b. 7/16/1941 -- Owner Chicago Cubs, CEO of TD Ameritrade (fmr.)
Gale Sayers - b. 5/30/1943 -- NFL Running back (raised in Omaha)
John Beasley - b. 6/26/1943 -- Actor
Johny Rogers - b. 7/5/1951 -- Heisman Trophy Winner
Swoosie Kurtz - b.9/6/1955 -- Actor
Paula Zahn - b. 2/24/1956 -- Journalist & Newscaster
Wade Boggs - b. 6/15/1958 -- Professional Baseball Third Baseman
James M. Connor - b. 6/16/1960 -- Actor
Alexander Payne - b. 2/10/1961 -- Director, Screenwriter & Producer
Nicholas Sparks - b. 12/31/1965 -- Novelist, Screenwriter, & Producer
Calvin Jones - b. 11/27/1970 -- Raiders, Packers & Nebraska Cornhusker Runningback
Huston Alexander - b. 3/22/1972 -- Mixed Martial Artist
Gabriell Union - b. 10/29/1972 -- Actor
Ahman Green - b. 2/16/1977 -- Seahawks, Packers & Nebraska Cornhusker Runningback
Brian Greenberg -b. 5/24/1978 -- Actor
Eric Crouch - b. 9/16/1978 -- Heisman Trophy Winner & Sports Analyst
Chris Klein - b. 3/14/1979 -- Actor (Graduated form Millard West High School)
Andy Roddick - b. 8/30/1982 -- Former World #1 Professional Tennis Player
311 - b. 1988 -- OK, technically a band and not a person

SQL Server Access Control & Synonyms

OK. So, say you have a SQL Server database and you want to provide varying levels access to different users groups.   This database was created for one application, and now you are being asked to provide access to a report development team.   How do you go about doing that in some rational way, say the way an admin assigns access to files on a file server. 

Well, that would be great, only there aren't any folders in SQL Server.  BUT, since SQL Server 2005 we've been given real Schema objects and Synonyms to boot.

SO, how does that help us?  Lets take a look, say you built your database and like any rational developer you built everything in DBO.  Here's a list of your tables:

dbo.Customers
dbo.PurchaseOrders
dbo.Invoices
dbo.UserLogins
dbo.ApplicationSettings

Obviously you don't want the report developers having access to the UserLogins and ApplicationSettings.  One, they just don't need it, and two, there's sensitive stuff in there.

Our approach:

• Use an active directory group (My_Domain\Report Writers) to control access.
• Create a Schema for the report writers to access
• Assign access to the new schema and not the old one.

 Step 1) Create the new schema:
CREATE SCHEMA reports

Step 2) Add some table synonyms to the schema:
CREATE SYNONYM reports.PurchaseOrders FOR dbo.PurchaseOrders
CREATE SYNONYM reports.Invoices FOR dbo.Invoices
CREATE SYNONYM reports.Customers FOR dbo.Customers

 Step 3) Map the AD group to your database
CREATE LOGIN [MY_DOMAIN\Report Writers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE USER [MY_DOMAIN\Report Writers] FOR LOGIN [MY_DOMAIN\Report Writers]

(This is the same as adding a Login at the server level, and mapping to the public role on a database catalog).

Step 4) Give the report writers access to the reports schema.
GRANT SELECT ON SCHEMA :: reports TO [MY_DOMAIN\Report Writers]

What have we accomplished?

• Your report writer team can log into your database
• Your report writer team can view all of the table synonyms in Management Studio
• Your report writer team doesn't have any write permissions (INSERT/DELETE/UDPATE) to anything.
• Your report writer team cannot query the objects in DBO directly, so they don't have access to sensitive tables like UserLogins and ApplicationSettings.

Why did this work?

Turns out that in SQL Server Synonyms are like file system hard links.  So if you had a file in one directory, and took away permissions on that directory.  Then created a hard link in another directory and give permissions, the user would have access.  The same idea works here.   Since the report writers didn't have access to the DBO schema, they can't view the tables there.  But since they have access to REPORTS they may read the synonyms and query them as well.

Turns out that you can customize access to the synonyms once they are created.  All of the GANT/DENY/REVOKE commands work the same.  You'll even be able to apply column level security!

Anonymous Performance Point Dashboards (SP2013)

Performance Point (PPS) became part of the Enterprise offering of SharePoint starting with Microsoft Office SharePoint Server 2007.  As a tool it was branded as "Bringing BI to the Masses."  In SharePoint 2010, it was possible to deploy PPS dashboards to BI sites with anonymous access.  SharePoint 15 (2013) broke this, either on purpose or by mistake, and here's how it happened:

Assembly: Microsoft.PerformancePoint.ScoreCard.WebControls.dll
Version: 14.0.0.0
Class: Microsoft.PerformancePoint.ScoreCard.OlapViewCache
Derived Class: System.Web.UI.Page

Assembly: Microsoft.PerformancePoint.ScoreCard.WebControls.dll
Version: 15.0.0.0
Class: Microsoft.PerformancePoint.ScoreCard.OlapViewCache
Derived Class: Microsoft.SharePoint.WebControls.LayoutsPageBase

Differences between version 14 & 15: Other than derived class, none.

Result of the change: _layouts/PPSWebParts/OlapViewCache.aspx requires user authentication with SharePoint 2013 (v15), where as SharePoint 2010 (v14) did not.  This means that while the ASPX application page generated by SharePoint designer can be placed in an anonymous access document library, elements referenced on the page via Image (<img src=""/>) tags require authentication.  Failure to provide credentials causes the chart elements to not render, causing a critical failure of the dashboard in anonymous access sites.

Here's the work around we implemented.

1. Create an ASPX page which duplicates the operations of Microsoft.PerformancePoint.ScoreCard.OlapViewCache.
2. Copy the ASPX page from (1) to:
   • 15\TEMPLATE\LAYOUTS\PPSWebParts
   • 14\TEMPLATE\LAYOUTS\PPSWebParts

Note: an IISRESET may be required after placing the files in the 14 & 15 hives.

The following content implements the replacement OlapViewCache.aspx which derives from Page instead of LayoutsPageBase.

<%@ Page Language="C#" %>
<%@ Assembly Name="Microsoft.PerformancePoint.ScoreCards.ServerCommon, 
        Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.PerformancePoint.Scorecards"  %>
<%@ Import Namespace="Microsoft.SharePoint.WebControls"  %>
<%@ Import Namespace="System"  %>
<%@ Import Namespace="System.Globalization"  %>
<%@ Import Namespace="System.Web"  %>
<%--
    Name:                   OlapViewCache.aspx
    Deployment Location:    15\TEMPLATE\LAYOUTS\PPSWebParts
    Description:
        Replaces the SharePoint 2013 OlapViewCache.aspx utility page.  The script
        code in this file was produced to replicate
        Microsoft.PerforamcePoint.Scorecards.WebControls which changed inheritance
        to LayoutsPageBase in SharePoint v15 (2013).  In v14, System.Web.UI.Page
        was the derived class.  The change in v15 caused the page to require 
        authentication meanwhile, other dashboard components could be used 
        anonymously.  This ASPX class derives from Page once more.
--%>
<script runat="server" type="text/C#">
    private void Page_Load(object sender, EventArgs e) {
        string externalkey = Request.QueryString["cacheID"];
        string s1 = Request.QueryString["height"];
        string s2 = Request.QueryString["width"];
        string tempFcoLocation = Request.QueryString["tempfco"];
        string str1 = Request.QueryString["cs"];
        string str2 = Request.QueryString["cc"];
        int height;
        int width;
        
        try {
            height = int.Parse(s1, (IFormatProvider)CultureInfo.InvariantCulture);
            width = int.Parse(s2, (IFormatProvider)CultureInfo.InvariantCulture);
        } catch {
            height = 480;
            width = 640;
        }
        
        int colStart = 1;
        int colCount = 100;
        try {
            if (str1.Length > 0)
                colStart = Convert.ToInt32(str1, 
                        (IFormatProvider)CultureInfo.CurrentCulture);
            if (str2.Length > 0)
                colCount = Convert.ToInt32(str2, 
                        (IFormatProvider)CultureInfo.CurrentCulture);
        } catch {
            colStart = 1;
            colCount = 100;
        }
        
        string mimeType;
        string viewHtml;
        byte[] bytesImageData;
        if (!BIMonitoringServiceApplicationProxy.Default
                .GetReportViewImageData(tempFcoLocation, externalkey, 
                    height, width, colStart, colCount, out mimeType, 
                    out viewHtml, out bytesImageData))
            return;
        
        if (mimeType.IndexOf("TEXT", StringComparison.OrdinalIgnoreCase) >= 0) {
            Response.ContentType = mimeType;
            Response.Write(viewHtml);
        } else {
            if (bytesImageData.Length <= 0)
                return;
            Response.Clear();
            Response.ContentType = mimeType;
            Response.BinaryWrite(bytesImageData);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
</script>