Productioneer has an extensive paginated report library for our customers to utilize. We have everything from simple data exports to paginated reports that highlight variances and trends.
One of the reports we have had in our library for quite a while, is the Tank Stock report. The user selects a day and the report returns the tank stock and tank production for each tank in the organization for the selected day. It aggregates the stock and production at the tank, battery, field, and organization level and provides the tank strappings (feet and inches) for each tank. It is a standard and widely used report and I know it has not had any significant changes requested to it for at least 3 years.
Suddenly, without any warning, drastic changes in the market caused everyone to take a closer look at their storage capacity. We were receiving requests from multiple clients to add more analytics to the Tank Stock report.
The data set this report was built on only pulls 2 days of data. The requests we were getting involved spotting trends over a longer period to more accurately forecast how long storage capacity would last based on current production trends.
Sometimes report column changes are easy, and sometimes they are surgery. This would be surgery.
Instead of scrubbing in, I was fairly certain the desired output could be achieved faster with a Power BI report than modifying the existing paginated report. The built in aggregations, seamless combining of datasets, and the ability to easily create sophisticated DAX calculation expressions at row and table levels made it an attractive option for the problem at hand.
While I was fairly confident I could get the job done with Power BI, I wasn’t 100% sure. I had one of our developers keep working on implementing the changes in the paginated report while I took on the Power BI version.
I realize two people working on the same thing is generally not the best use of team resources. That being the case, I told myself that if I could not get 90% of the way there in 2 hours, then we would continue adapting our current report and implementing additional features to it when they became available.
Long story short, we were able to achieve the result in Power BI and our customers are now able to use the Tank BI Report in our Report Portal. The Tank BI Report empowers them to plan operations and make decisions based on forecast storage capacity from a sophisticated production calculation. No more exporting raw data and doing their own calculations in Excel.
If you want to sign up for Productioneer and use this BI report, or if you would like us to adapt it to your current production reporting software, please contact us.
The details of how we did it are below.
The Power BI report has two datasets. The first data set is the Tank Stock data set (TankData). This data set contains the most recent day’s tank data. The most recent day could vary by tank battery. This data set has one row per tank and the tank data can be aggregated to a Battery and Field level – this report aggregates at the battery level. Key columns in this table are ProdDate, BatteryID, TankID, TankName, GaugeInches, TankStock.
The second data set is the Battery Oil Production data set (BattOilPrd). Since we are interested in tracking oil production at the battery level (not the tank level), I wrote a simple query that returns a ProdDate, BatteryId, and BatteryOilProduction columns. One line per battery per day and the query returns 14 days. The BattOilPrd data set is related to the TankData data set by ProdDate and BatteryID.
One of our customers gave us the formula they were using in Excel to calculate the days remaining based on tank capacity, tank stock and production:
Days Remaining = (((Total Storage Capacity x 0.95) – Current Tank Stock) / Three Day Average of Oil Production ) – 2
One of the wrinkles in the formula is that the Three Day Average of Oil should include the three most recent days, which could differ from battery to battery, and exclude days with zero production.
This requirement is necessary because not all gauges are entered for all of the tanks in the organization at the same time. Ordinarily, most operators would just use the previous days number, but storage is being monitored so closely that the most up-to-date data for each tank is required. It is something the customer was not able to calculate efficiently in Excel, but this feature would make the calculation more accurate.
With our datasets in place, we were able to adapt some DAX shared by Simon Lamb on the Power BI User Group to calculate a special three day moving average. The resulting measure is called “mi4-3DayAverage” and relies on three other intermediary measures:
If you would like to talk with us about how Power BI can be used in your organization please leave us some contact information. We look forward to talking with you.