Frequency of Datamart Refresh

I suspect that your question relates to the Automart, not the Datamart. The DataMart is legacy functionality that we used when Business Objects was our embedded reporting tool. The Automart, which is actually deployed in tables in the Datamart database schema, is a mechanism by which the Global Reporting Services team in Professional Services can make your data available for reporting via PowerBI. 

Our recommendation is to load the Automart once per day, at the end of your overnight batch job stream that calls the progressing engine, Financial Management loads, etc. In this way all data in the system is “up to date”. 

Running the Automart load process does have an impact of overall system performance and the duration of the load process depends on the quantity of data in your database - number of resources, projects, strategies, lines in project plans, time range, etc….. When it is being loaded then it is unavailable for use in reports - reports will error.

When the Automart load time is in the <30 minute time range then doing a load in the middle of the day, or say every 6 hours, is doable.

When it comes to PowerBI reporting, there are a number of components in the end-to-end reporting data flow that determine the currency (Up-to-date’ness) of the data in the report, This diagram shows them:

https://success.planview.com/Planview_Portfolios/Analytics_and_Reporting/Best_Practices_for_Reporting_Services_Datasets_for_OData

PowerBI can never provide “real-time” reporting. To get the speed and interactivity of PowerBI demands that data is loaded and in memory.

What is the specific problem you are trying to solve with this ask? 

The Global Reporting Services (GRS) team can assist you in this area. Please raise a Global Services case on support.planview.com and we have one of the team provide you with guidance. 

Refreshing the Automart takes a long time in our instance (not sure how long; I’m not on the admin team), so we cannot load more than once a day. We refresh Power BI twice daily and explain to our users that will be the case, to manage expectations. 

We were considering running every 6 hours but I have been told that this is impossible really because the time between jobs is calculated from the time the previous job ends.  So for example, if we ran the job at 12pm ET and the job runs for 15 minutes. Then the next job tries to run at 6pm, this job will fail because it hasn’t been 6 hrs from the end time of the last refresh job.       There is not an immediate problem trying to be solved but trying to understand the possibilities for us.

You are confusing PowerBI report refresh interval with Automart load internal. They are two different process with different rules/constraints.

PowerBI reports are loaded into memory on the report server and need a periodic refresh to get the latest data. Flow number 1 in the diagram below. This, for oData sourced PowerBI reports, is controlled by a PowerBI Report Refresh job in a jobstream and the default minimum permitted interval to run the same report refresh is 12 hours. This can be lowered to 4 hours through a CloudOps case.

Automart is one of the potential reporting data sources for PowerBI. It is loaded via an Automart refresh job and processes the data from the Portfolios transactional database into staged tables that are optimised/more efficient for reporting, and calculates lots of additional reporting data during the load. Flow number 2 in the diagram below Once refreshed the automart can be the datasource for multiple reports, each of which needs it’s own refresh.

Obviously the overall refresh (period of time since updated from base database) is a function of the data source refresh (automart load) and the report refresh. In addition there is a 60 minute cache on the oData service. Number 3 on the diagram.

Transactional database can be used as a data source for PowerBI. There are pros and cons to this.

These controls are in place to protect the overall service performance.

Recommend getting help from GRS team.

https://success.planview.com/Planview_Portfolios/Analytics_and_Reporting/Best_Practices_for_Reporting_Services_Datasets_for_OData

I think there’s an interesting tension when you’re promoting the use of a centralised system as a single source of truth with access real time data, but need to support this with a slick reporting solution which requires staged data. As our Power BI development matured, we’ve found some reporting demands more frequent refreshes and overnight updates just doesn’t cut it with our users.  It took ages experimenting with schedules - as has been mentioned, every 6hrs is theoretically possible, but throw in the other jobs and the fact that refresh times are based on the completion time of jobs, not start times, it can get quite messy.  We finally came up with a schedule which allows us to refresh the Automart 3 times a day without error, but the timings are not great and honestly I’d say that overnight and once during the online day would be just as effective 

Thank you so much for that response.  The reason for the external reporting is due to bringing the PV Portfolios together with two other sources - one being our support ticketing system data and the other being a portion of the overall portfolio that is not currently in PV Portfolios.    This reporting isn’t for reports that are only Portfolios data.  I agree that this reporting should be straight from Portfolios.  Thanks again.