Overview
Reports often forms one of the most critical and visible parts of any PPM (Project Portfolio Management) solution. Excellent reports communicate key insights quickly and effectively, and support informed decision making at all levels of an organisation. They also help with governance and the overall process of your solution. Microsoft Project Online allows for the creation of custom Excel Services and OData based reports. This is powerful as it allows end users to use Excel tools to build reports. However many customers have reporting needs that go beyond the abilities of OData and Excel... in this situation, the Sensei Reporting Hub comes to the rescue!
Our Project Online Reporting Hub enables you to report on data maintained in Project Online and SharePoint using a fast, reliable SQL Database connection. You can utilise the Reporting Hub to create reports in Microsoft SQL Server Reporting Services (SSRS) or any other reporting tool. If you have existing SSRS reports from an on-premises installation, they will work with minor modification in the Cloud.
This document will provide an overview of Reporting Hub, but your circumstances may differ hence this cannot be used for operational purposes but rather general information. This is a document to facilitate discussion, it is not a formal design or document to be used for anything but getting together and talking about.
What is it?
The Reporting Hub is software as a service that clients subscribe to from Sensei. This is not a custom built solution for a specific client, but rather a standardised service that everyone uses that is Cloud based. This specifically gives you, without needing to built or maintain it yourself:
a reporting service (database and report renderer) to consolidate, serve and visualise Project Online and other data
real-time reporting on your Project Online content
create reports and export reports in PDF, Word, Excel and PowerPoint
SharePoint Project Site data can be easily reported against
paid per month, per customer (not per user)
runs in Microsoft Azure
Benefits
the provided database is modelled after the on-premises Project Server reporting database. Therefore most existing "on premises" reports can be easily moved to Project Online with little or no changes
your Project Online data is available in real-time with no maintenance requirements
your data is secured and stored in your dedicated Database in your nearest Azure data centre
you don't need to develop reports using slow OData queries. Our Reporting Hub is fast to retrieve your reports
you can extend your Reporting Hub database to include data from other Line of Business applications for reporting purposes
Sensei Reporting Hub is an enabler of reporting in Project Online. It has two main pieces:
Real-time SQL Azure Data Source. Sensei Reporting Hub retrieves all the Project Server and SharePoint list data from your installation and creates a set of tables and views inside SQL Azure that make most reporting tasks very easy.
SQL Server Reporting Services Renderer. Reporting Services is a technology used to store and render reports for users. These reports are available via an App Part in the main Project Web Application (PWA) website and can be exported by the user in various formats such as PDF, Microsoft Word, Excel etc.
Both of these pieces are provided as a subscription service known as Reporting Hub. The SQL Azure data source also has other use-cases such as integration with on premise data.
Design Advantages
There are several advantages in using the Reporting Hub for report confection. The top 4 main points are:
High Availability solution with Geo-Replication
Data security including credentials, location and your own database
Elastic performance with scalable DTUs
Utility with 250GB data storage included and ability to bring your own on-premise data
Using the Reporting Hub
The Reporting Hub is a service that you subscribe to, along with many other clients around the world. As a result you have your own SQL database that is populated by the Reporting Hub with your Project Online data, based on the Project Server 2013 reporting database structure.
You can write reports that use Reporting Hub using any reporting tool such as Power BI, SSRS etc. How you use the Reporting Hub database depends on the reports you want to write, and the results you get from your reports in terms of speed and usefulness will depend on how you write the queries, as well as if you have the Standard or Enterprise version of Reporting Hub.
Solution Overview
Job Queue and Timing Process
The changes done in Project Online are instantaneously reflected in the Reporting Hub. These changes are executed in the background on a separate space without affecting the overall Project Online domain space. They are not related to foreground activities and are executed with an appropriate execution priority. To ensure that users working with the Reporting Hub and Project Online are not affected by the activity.
Only when the data has been changed a job is generated. If no changes between the target data and the current data is detected nothing is queued and therefore it does not count against the number of changes. It means the Reporting Hub uses a very efficient method of capturing and tracking changes only when needed, and where they are needed.
Solution Architecture
Cloud Authentication Architecture
Reporting Hub Architecture
There are three main parts to the Reporting Hub product
Data Collector and SQL Azure DB (Green lines)
Integration Hub Service Add-on (Yellow lines)
SSRS Renderer (Orange lines)
The following describes the flow through Reporting Hub for those interested in the technical architecture.
| Item | Details |
|---|---|
| 1 | Reporting Hub registers Project Online and SharePoint Online remote event handlers |
| 2 | Changes are queued in the closest region |
| 3 | The data pump de-queue the jobs |
| 4 | The data pump read the changed data form Project Online and SharePoint Online |
| 5 | The data pump updated the SQL Azure database |
| 6 | CSV files are generated by SAP and transferred to a machine within the DMZ. CSV files are downloaded from the Integration Hub are imported into SAP |
| 7 | CSV files are uploaded to and downloaded from an Azure Blob Storage using HTTPS |
| 8 | The Integration Hub will monitor the Blob Storage for changes and push new export files to the Blob Storage |
| 9 | The import logic detects changes in integration data |
| 10 | The import logic pushes the updates to Project Online and SharePoint Online |
| 11 | An user request to view a report from Project Online |
| 12 | The user request is validated against the Azure Active Directory |
| 13 | The user request for rendering a report is send to Reporting Services |
| 14 | Reporting Services gathers the report data from the Customer SQL Azure database |
| 15 | Each customer has an individual SQL Azure database. Each database is secured with individual users and firewall rules. |
Frequently Asked Questions
The following are frequently asked questions that may help you with your further understanding of Reporting Hub.
Is this customised for me? Similar to O365 itself, the Reporting Hub is a Software As A Service (SaaS) product that clients subscribe to from Sensei. This is not a custom built solution for each a specific client, but rather a multi-tenant service.
Where is my data? In the Microsoft data centre most closely aligning to your O365 tenant in Melbourne, West US or West Europe.
How is my data stored? Sensei's Reporting Hub is a Cloud based solution that is hosted in a Microsoft Azure private Cloud instance. Each customer has an individually secured and encrypted Contained-Mode Azure SQL Database backing their application and these services can be replicated and configured to interact with multiple pre-production environments for testing and change management purposes.
What knowledge do I need to use the Reporting Hub? Reporting Hub is a SQL database, therefore you would need to understand the data structure to at least a basic level if you are using Power BI. If you are writing SQL queries you will need the understanding of a report developer who has used Project Server or Project Online and built up knowledge.
Is all Project Online data available in the Reporting Hub? No. If Microsoft has not exposed the data through ODATA then it is not available to the Reporting Hub or any other method, and hence it is not available for reporting. An example is some workflow data. In addition if data is not commonly used, Sensei has not exposed it. An example is the portfolio selection data. In this case it is mostly available and can be added to the Reporting Hub if requested.
How do I use it? Reporting Hub is a SQL database. It consists of SQL objects such as tables and views. You include these objects in SQL queries, or you use Power BI to bring through the default tables. Like any database you can create your own objects as well that specifically suit your needs.
Is the Reporting Hub a reporting tool? Yes it is a database and a SSRS renderer. A report authoring tool such as Reporting Services Report Builder or Power BI Desktop is used to author the reports used with the service.
What does the Reporting Hub give me that Project Online does not? Project Online is Cloud based, and therefore does not expose a SQL database that can be used for writing queries against, such as those used in reports. Without Reporting Hub, Project Online has the following limitations:
Without Reporting Hub, SharePoint data is only provided by Project Online for out of the box fields. No new lists or fields are exposed through OData. Therefore you use Reporting Hub to gain access to all of your lists including customised new lists;
Without Reporting Hub you need to use OData, which results in relationships between data and more advanced queries not being as easy. Using Reporting Hub means you use traditional SQL.
Does Reporting Hub provide anything specific that any other SQL database doesn't? Yes and no. It is a SQL database. It provides some additional relationships between tables, but conceptually it acts as any SQL database and should be used as such. The value of the Reporting Hub is in its ability to move data from Project Online into the SQL database so that you can report on it while also providing legacy compatibility for on-premises reporting investments.
Do I have to use the objects in the Reporting Hub? No. A SQL database consists of tables, and a set of views and procedures that exist to save you time if you keep needing to bring the same tables together. However since these views are created to provide all fields in Project Online so that anyone can use them, they are by default going to include more fields than you want in most cases. You may need to create your own views as they will be targeted to your specific needs. The smaller the view the faster it will perform as well.
Can I use another database? Using the hosted SQL Azure contained mode database or a BYOD SQL Azure database are the only two supported options.
Can I use any reporting tool? Yes. The Reporting Hub is exposed to you as a SQL database in Azure. Any reporting tool that can connect to it can be used, such as Reporting Services, Power BI, Excel, Qlik etc.
What do I do if my reports are slow? If reports are running slow there can be several reasons. First, is the query as optimal as it can be? Second, is the query using a default object such as a view that itself cannot handle the volume of fields? Third, is the query complex and needing to process a lot of data in a complex manner, and needs more processing power than the Reporting Hub Standard edition provides? If more processing resources are required, there are higher performance tiers of the Reporting Hub service available. Only pay for what you use.
Are there different levels of power available? Yes. By default the Standard level is provided. This serves the needs of most clients. However if you fall into one of the following you may need the Enterprise level:
Large volume of data being processed by complex reports that result in some reports being slow and while they process other reports are slowed down;
Stored procedures being used that are complex;
Significant number of custom fields, which are more than the default objects can handle. Note that changing the queries themselves is usually the best approach to this not more processing power;
High number of reports being run simultaneously.
Is the data structure in the Reporting Hub the same as the Project Server 2013 database? The main tables and views are the same. We have tried to maintain compatibility between 2010, 2013 and Online where possible. There are tables that are not present as this data is not exposed by Microsoft, and therefore cannot be obtained to add into the Reporting Hub. This is a technical limitation of Microsoft and no solution can access this data. An example is some workflow data.
There are a few tables that Sensei have not added into the Reporting Hub as the demand for them is low. These can be added if needed. An example is the portfolio analyser data.
The Reporting Hub does provide additional keys linking project and site data to allow for simpler queries to be written, and threby improves the database.
Are the views in the Reporting Hub the same as those in Project Server 2013? Where possible we have tried to maintain compatibility by reproducing the tables and views. But some of these objects are dynamically built when you add new custom fields. Each time a custom field is added the view is torn down and rebuilt automatically. This places a performance load onto the Reporting Hub.
The built in views are generic and are dynamically built to add in all of your custom fields. If there are a lot of custom fields this can be slow, which is why most report developers learn the data structure and then create their own views.
If needed Sensei can assist you with creating reports, views or queries. We are happy to discuss a project focussed on reporting with you.
Does Reporting Hub solve the 2,000 Project Site limit? Project Online can have 30,000 projects in it. Each project can have a project site. But only 2,000 of those projects can have project sites. To get around that different site collections are created. Enterprise Reporting Hub however allows all the data from multiple site collections to be brought into one database and therefore you can report across it all. This is specifically an Enterprise Reporting Hub feature.
Can I migrate existing reports from older Project Server to Project Online using Reporting Hub? Yes, with some updating of the data sources. The Reporting Hub structure is based on that of the Project Server 2013 reporting database. This means the tables mostly align. However the underlying technology is Azure in the cloud, not your local SQL Server. As a result you may find that performance is different, and you may need to look at the performance of the Azure under Reporting Hub. In addition you may find that queries and views that performed one way on premises perform differently in Azure.
I have a lot of fields and data. Does that matter? Yes. While the Reporting Hub which is based on SQL in Azure can handle a significantly large database, and is used globally for large databases, you need to size that database correctly. Just like your on premises system had the server sized to fit you and possibly increased over time, so does Reporting Hub need to be sized.
In addition, if you have a lot of custom fields the queries within Reporting Hub that implement the views may not be able to dynamically rebuild themselves based on the number of fields. As a result you may need to create your own tailored views. There is a 450 custom field limit in Project Online but that does not directly impact Reporting Hub. Reporting Hub is indirectly slowed when you try to use generic views that are out of the box with any large number of fields, which co-incidently may be close to 450.