This question comes up on such a regular basis, that I decided to blog it up this weekend. What’s the difference between the business intelligence story in Project Online vs. Project Server 2013 On-Premises? To be honest, answering this question goes a long way to answering the question of what the difference is between Project Online and Project Server, period. The main difference is business intelligence.
First off, a bit of term definition:
- Project Online – the offering provided by Microsoft that’s really Project Server 2013 + whatever upgrades have happened in the Online product between when 2013 was released to market and the date of this blog post. (Watch the comments for updates).
- Project Server 2013 – well, Project Server 2013, as deployed on premises – again, assuming all of the updates as of this blog posting.
Note that the reason I have to be careful about term definitions is that much of the v.Next functionality for Project Server on premises is being released right now in the online environment. I would expect many of these features to first appear online, then migrate over to the on-premises world – which will make this post outdated pretty soon.
Before reading any further, feel free to check out some of my other blog posts on reporting options available both online and on-premises.
The following components come into play when discussing the difference between online and on-premises reporting:
- Data Source – the underlying raw data related to projects, resources, etc. This data source may be real time, or for various reasons, replicated to a secondary data source using a timed process.
- Data Model – the relationships and hierarchy between the various data points. For example, I may have three resources assigned to a project that is supporting my drilling operations. That would necessitate pulling data from the resource table, assignment table, project table, and potentially additional tables related to workflow.
- Reporting Layer – the tools used to actually generate the reports.
- Presentation Layer – the platform used to surface the reports to the general users.
The Basics: Data Access
Project Server stores everything in a set of tables within a SQL database. Hence, any reporting tool that can access SQL (which is pretty much all of them) can access and report on Project Server data.
Project Online doesn’t allow direct access to SQL data, as the database instance is a shared environment, and thus, users could theoretically impact other tenants if they had direct access. For Online, the preferred reporting is OData, which is an RSS-like data feed. Open an OData compliant reporting tool (like Excel 2013), point it at the feed URL, and you have the data (albeit not the data model – which is a different story).
As many clients still prefer to report against SQL, Microsoft has recently released (via Cumulative Update) an SSIS upgrade that will allow organizations to set up timed reporting jobs to import OData feeds into an on-premises SQL database – thus allowing the use/migration of existing reports that look for SQL data.
The issue, of course, is that the SSIS integration reporting isn’t quite real time – or it’s a mix of real time and not-so-real time. Reports requiring a limited data set can pull from the live online OData. Reports requiring a lot of data, i.e. timesheet reporting, etc., can pull from the SQL database. The main caveat here is that if you’re the kind of organization that likes to generate reports, spot mistakes, republish projects, and regenerate reports, latent data may not work for you.
If on the other hand, you feel the savings from going online offsets the issues with not having real time data, the the Online offering may be appropriate for you.
Hence, we actually have three scenarios to compare. Note that these may not be mutually exclusive – as you can mix and match some of the functionality depending on your needs. You can also have hybrid online/on-prem environments if there’s something available in one that you absolutely need to support your organization.
- Project Server (On-Premises)
- Project Online (Native OData)
- Project Online (SSIS Integration)
|Scenario||On Premises||Online (OData)||Online (SSIS)|
|Data Source||SQL (Real Time)||OData (Real Time)||OData (Real Time – Limited Data Set)SSIS (Sync’d via timer)|
|Data Model||SQL||PowerPivot (Excel)||PowerPivot (Excel)SQL|
|Reporting Tools||Excel, SSRS, Standard Reporting Tools||Excel 2013Other tools that support OData (limited availability)||Excel, SSRS, Standard Reporting Tools|
|Presentation Layer||SharePoint||SharePoint (add’l options to support iPad compatibility with HTML5)||SharePoint (add’l options to support iPad compatibility with HTML5)|
|Natural Language Queries (Power BI)||Not Available On-Prem||Power BI Portal||Power BI Portal|
And Another Thing….Power BI Sites
The term Power BI is confusing as it is often used for both the Microsoft Power BI offerings (Power Pivot, Power View, Power Maps) and the Power BI sites. As of this writing, most of the Power BI suite is availabe online or on-premises, but the Power BI sites are only available online. Power BI sites are SharePoint portals available under an ala carte licensing scheme that provide the following functionality:
- Data Feeds – configurable to integrate both on-premises and online data.
- Power View reporting – similar to what’s available in SharePoint.
- Natural Language Querying – which is one of the more exciting things to be happening in the BI world, i.e. the ability to create data models and allow users to enter questions into the browser window – which the system answers. For example, you can type something like “Show me all projects starting in Botswana in 2015,” and the system will automatically spit out a map of Botswana showing the location of all projects starting in 2015.
That’s the main round up of differences. Did I miss anything? Feel free to drop me a line in the comments section.