Skip to main content

PowerBI dashboard

Feature RequestData Tab

Also update CEDARS site navigation in integrate with other pages / resources.

Background

From Motive Power:
"When we spoke about the Power Bi dashboard recently, the first dashboard to be addressed was for the Annual Report. At that time, Amy threw out the idea to the PA's that the Annual Report Power Bi would live on the homepage. This dashboard would be visible to all users.

Additionally, Frontier Energy, Sonoma Clean Power and Bay Area Metro communicated that they submit quarterly claims and then an Annual Report as a "true-up" but there are redundancies here. They request that we evaluate using data already submitted quarterly to automate the Annual Report so the PAs are more so reviewing the report Vs spending time re-submitting data just in a different format during the Annual Report.

... I would also believe that this would probably be a lovely opportunity to do a bit of redesign on the homepage to not only accommodate the dashboard, but perhaps also incorporate further changes in design/copy that would provide users and visitors with a greater understanding of what they're viewing in the dashboard and perhaps ways to use the tool."

Question: Is the "we" in the first sentence is MP, Amy, and the IOUs?

Answer: It is a combination of Amy, the IOU’s and specifically Frontier Energy, Sonoma Clean Power and Bay Area Metro as stated above.

Stories

As a PA, I want the data summaries I have been providing in annual report Excel spreadsheets to be dynamically shown on the CEDARS home page so that the information is available to the CPUC and the public without the need to submit data summaries alongside the record-level data submitted to CEDARS.


As a CPUC staff member, I want to be able to quickly understand PA performance based on key metrics currently summarized in PA annual reports.

Goals

To make the data summaries required by CPUC staff available in CEDARS, generated from the PA claim and budget filing data submissions.


To eliminate the need for PAs to submit annual report Excel spreadsheets alongside their data submissions to CEDARS.

Requirements

Data Mapping & Preparation

  • Jenn verifies and provides data mapping to ensure data flows accurately from CEDARS to Power BI.

  • The Dashboard pulls pre-summarized data from CEDARS, minimizing processing time and ensuring quick load speeds.

  • Additional summaries are created only where necessary, prioritizing a streamlined dataset with minimal redundancy.

  • Any existing data accuracy bugs are resolved before dashboard implementation, ensuring data reliability and consistency.

Dashboard Creation

  • Dashboards created in Power BI by Motive Power
    - Cost Effectiveness
    - Energy Savings
    - Environmental Impacts
    - Financial Performance

  • Sound Data to create a bar chart for Portfolio Summary in Confirmed Claims**

Dashboard Placement & Design

  • Update CEDARS navigation to include a new tab for the Annual Report PowerBI dashboard with summaries of PA data.

  • Sonja is provided with an example of the Dashboard to mock-up, showing how users will navigate to and interact with it.

  • Any guidance on accessing, navigating, or interpreting the Dashboard is displayed and accessible.

  • The Dashboard includes guidance notes that explain each view, including definitions, data sources, and the date of the last update.

  • An accessible colour palette is applied to ensure clarity for all users, including those with colour vision deficiencies.

Integration & Functionality

  • The Dashboard is fully integrated with CEDARS and updates nightly to reflect any new or corrected data.

  • To maintain data consistency across views and over time, all unit conversions are correctly applied, with clear indicators of any time-based adjustments.

  • Ensure all users can download the data summaries and visualizations

User testing

Sonja is to run user testing with the help of Motive Power
Reach out to PA, CPUC and some community users

Posted by Jennifer Scheuerell ·
Status: Completed12 comments

Log in to comment and vote

Comments12

  • akulka

    •

    Jan 17, 2025

    @Sonja @cedars team Amy has requested a hyperlink to the goal attainment page to be placed on the Reports tab with the Portfolio Metrics dashboard. This is in lieu of Amy’s desire to create a dedicated goal attainment page within the dashboard itself.

    • Jennifer Scheuerell

      Team•

      Jan 17, 2025

      @akulka What is the URL of the page in CEDARS that you are calling the the goal attainment page? I’m not sure what page in CEDARS you mean for us to link to. Perhaps you mean the four IOU confirmed claim dashboards that include the goal attainment summary data (soon to be updated to include the recently tested visualizations)? e.g.

      https://cedars.cpuc.ca.gov/claims/confirmed-dashboard/PGE/

      Any other details you can provide, like where Amy wants the link(s) to be placed in the Portfolio Metrics page (i.e. above the PowerBI dashboard) or any other specific requirements would be helpful.

    • cedars team

      Team•

      Jan 21, 2025

      I’ve made a mockup of two simple options, which can be reviewed in the Dev Demo tomorrow. Link to the mock-up:

      https://www.figma.com/proto/ysmQxqgqrZxm2QuVsr359a/Annual-Report?page-id=442%3A1254&node-id=442-1257&viewport=981%2C865%2C0.26&t=6hmzTDR1jvN0tHQK-1&scaling=scale-down&content-scaling=fixed&starting-point-node-id=442%3A1257

      • Amyreardon

        •

        Jan 28, 2025

        Hi Sonja, I am checking in on the topic of goal attainment links. Would it be possible to find another location for these links? If so, then this approach is an adequate solution for the time being (we will probably scope enhancements to deploy in a later phase of the dashboards so that goal attainment is visualized rather than linked. TBD)

        Perhaps I only saw the one mock-up, where the links were situated below the iframe, but one suggestion I have is to make the goal attainment links more prominent. I noticed that there appears to be marginal space to either side of the iframe—I am not sure if that is dependent on one’s monitor settings, but perhaps goal attainment links could be put into the lefthand margin, similar to the layout used on our other pages?

        Thanks,

        Amy

  • Jennifer Scheuerell

    Team•

    Jul 6, 2024

    Assigned task priority 12 out of 14 in Motive Power ADP ranking.

  • Jennifer Scheuerell

    Team•

    Oct 3, 2024

    This needs to be done before the annual report due 3/12/25.

  • akulka

    •

    Oct 3, 2024

    Yes, complete before Annual report. Before 3/12 (end of Feb). Get the ground work done in October.

  • akulka

    •

    Oct 3, 2024

    Incorporate in the dashboard work (possibly). Fixing the CEDARS program module (see other ticket)

  • akulka

    •

    Oct 3, 2024

    Dashboard covers this

  • sshkolnikov

    •

    Oct 28, 2024

    Per MVP Meeting 10/25/24:
    User Understanding

    1. Who are the primary users of the dashboard?

      • External audiences/all CEDARS users

        1. For people who are as familiar with the information

        2. Annual Report is the most important report of the year

    2. What specific problems or pain points are users trying to solve with the dashboard?

      • PA’s submit 2 files for the report currently

        1. Looking to offset the excel file submission because the majority of the data is already in CEDARS. The excel file is duplicative for this reason. Opportunity is to eliminate this file submission and reduce the amount of time and effort expended by the PA’s.

        2. Submission of the excel file is extremely laborious and takes a large time investment and resources.

    a)      There are hundreds of data points within each Annual Report excel tab. Every time a change is made to a PA’s claims, as they’re building their annual report, they have to download all the output data and recreate. Additionally, they QC all the data points again for regulatory filing.

    b)      Dashboard will be especially effective for statewide programs as the data tends to fluctuate towards the end of the annual report filings.

    1. Benefit: By adding an Annual Report dashboard there is reporting consistency for PA’s and consistency in display of information for users.

    2. What decisions will users be making based on the data presented?

      • Dashboard will allow external users to compare the data in one location vs having to download each report separately and run the information independently outside of CEDARS.

    Data Requirements

    1. What key metrics and KPIs do users need to see? Are there any specific calculations or aggregations required?

      • See excel spreadsheet that Gary provided, with Jake comments. Spreadsheet indicates the following data to include in the dashboard:

        1. Portfolio Summary – PA’s goals, achievements, etc.

        2. Cost Effectiveness

        3. Energy Savings

        4. Environmental Impacts

        5. Financial Performance

      • An additional requirement is to also display a map of the territory with different colors for different territories. Include Ren’s and CCA’s. Display goals within map.

      • All data documented in Gary’s excel spreadsheet would be found within the CET output.

    a)      We can eliminate tables 1, 3-5 and 7. For table 2, we believe we can eliminate the fuel sub. Tables 6 and 8-13, the data/information is not in CEDARS.

    b)      For goal attainment, we should rely on the dashboard we already have on the claims page where we built all the logic.

    1. Claims dashboard displays PA status currently on CEDARS and this would be what stakeholders would like to see duplicated.

    2. Possible second step/phase of the dashboard development may be to pull some of the common metrics into Cedars as well, because a lot of them are calculated just off of Cedar's information.

      1. However, the guidance is to wait until the advice letter related to that is dispositioned first.

    3. What data sources will the dashboard need to connect to? Are there any data quality issues we need to address?

      • For performance reasons, we would want to use summaries where they already exist and add summaries where they do not currently exist.

        1. Generate on big summary to care for all is an opportunity

    4. How frequently does the data need to be updated?

      • Update nightly

    Functionality

    1. What are the must-have features for the initial dashboard?

      • Export option

        1. Whatever graphic or data configuration that the user creates and the specific data configured with the graphic

        2. Underline data yes, but want to investigate further if this impacts performance

        3. Summary level data available – yes

    a)      Downloadable in an excel file

    1. Are there any specific visualizations that are essential?

      • Display data by segment and by sector. Filter by PA statewide and program level.

      • No round charts. Favor histograms/bar charts.

      • Default highest to lowest. Discuss default settings and options

      • Colors, warm and greens but not reds, yellows – thematic choices for presentation

      • Hover overs where the actual values display

  • cedars team

    Team•

    Feb 3, 2025

    As per the Final Delivery Summary: Portfolio Metrics Dashboard (Power BI) from Motive Power, the prototype will remain in the CEDARS staging environment.

    CPUC and PAs can continue to interact with the dashboard and make notes about future enhancements and desired features.

  • sshkolnikov

    •

    Feb 4, 2025

    Final dashboard delivery summary emailed to IOU’s and Amy Reardon 2/3/25: