Getting started with Power BI and NISC iVUE

July 13, 2024

As I described in What’s old is new again – Lake Data Insights, there is a significant opportunity for data analytics to make a difference in electric utility coops. This post discusses a core use case, the architecture approach, and technologies that enable data driven decision making for the coop.

Most electric utility coops use the iVUE Enterprise suite from National Information Solutions Cooperative (NISC) as their primary ERP system. iVUE has a business intelligence platform that supports core analytics needs. While this platform provides a core set of reporting capability, some coops want to take advantage of Microsoft’s Power BI platform for their business intelligence requirements. There are multiple reasons for this, here are a few:

  • Power BI can be a more flexible solution for advanced reporting needs when working with iVUE data.
  • Power BI enables data from multiple sources beyond iVUE. This includes meter data, data from power suppliers, and other sources.
  • Power BI can be the unifying analytics platform for the coop, simplifying access, governance, and training of team members.

I’ve built a wide variety of Power BI reports for electric coops that make use of iVUE service data, outage data, financial data, and service order data. In many cases, the iVUE data has either been transformed before reaching Power BI or acts as a supplementary source for other data within Power BI. In this getting started post, I’m going to provide an overview for the most basic scenario where iVUE data is directly queried and visualized by Power BI.

Data engineering lifecycle

As I described in Using the data engineering lifecycle for data architecture – Lake Data Insights, the data engineering lifecycle is a useful framework for describing data architectures. In this case, the phases of Ingestion-Transformation-Serving exist even though it’s primarily down within the boundaries of Power BI.

A couple of notes from the diagram:

  • I use T-SQL for both ingestion and much of the transformation. It’s usually a better proposition to acquire only the data you need and then transform the data as part of the “get data” process than over-invest in DAX (Data Analysis Expressions) based transformations in Power BI. That said, I will use DAX in some scenarios (adding a column, creating a measure) where it suits me.
  • The star schema approach to data modeling is the recommended approach for Power BI. There are two core ingredients to the star schema:
    • Fact tables are the high volume transactional tables in the star schema. For example, this could represent monthly usage and charges for every member in the coop.
    • Dimension tables provide additional information and have a one-to-many relationship to the fact table. For example, a dimension table could provide member information (name, service address, billing address, etc). Rather than repeating that information for each row in the fact table, it is located in a dimension table.

Development and deployment steps

Here are the steps required to get started using Power BI with NISC iVUE. I will break these into development steps and deployment steps.

Development

Developing reports is done on your local computer. Power BI Desktop is free, so you can get started without getting out your credit card.

  1. Set up ODBC access to the iVUE Oracle database.
    • While not required, the Oracle SQL Developer (SQL Developer | Oracle) is a useful tool for T-SQL query development. It is also a useful tool to verify your connection to the Oracle database.
    • The reporting login, rptXXXXX, is used. XXXXX should be replaced with the unique ID of your NISC installation.
  2. Install Power BI Desktop.
  3. Ingest data by connecting via ODBC and writing T-SQL queries.
    • In Power BI, search for the ODBC connector in Get Data.
    • After connecting to the driver, select the appropriate data source and expand Advanced options to get to the SQL statement box.
    • As a general rule, you’ll want to write T-SQL queries instead of loading entire tables. Using T-SQL not only limits the columns and data volume to what you need, you can also perform more complex operations such as joins, aggregates, and window functions.
    • Reference Service/Billing Information (nisc.coop) for information on the available views and how they are related.
    • There is a way to determine the table and column name for a specific field in the iVUE application. This can be very helpful in identifying what needs to be queried, although exploring the tables is also recommended. This is also where Oracle SQL Developer is very useful to discover what’s in different views.
    • Think about what your data model should be. The ease of data modeling is reason #1 in Why I love Power BI – Lake Data Insights.  General guidance is a star schema with fact and dimension tables.
  4. Build your visualizations in Power BI. There are tons of training options available, so I won’t get into guidance for building visualizations here.
  5. Save your report. Using a OneDrive / SharePoint location is a handy way to ensure you have a backup and some basic versioning of the report.

Deployment

Reason #3 in Why I love Power BI – Lake Data Insights is sharing and refreshing. Being a cloud hosted deployment platform, Power BI reports are easy to share with others. You’ll need proper licensing for this, something that is also out of scope for this blog post. Fortunately, the most basic licensing is a small fee per user per month, so there’s no need for a large up front outlay.

The most basic sharing approach is to Publish the report to your personal workspace from Power BI Desktop. Once published, you can go to the report and explicitly share it with targeted teammates. That’s a simple way to get started, but it doesn’t scale well. Here are some basic governance steps that will enable you to grow more successfully.

  1. After you log into powerbi.com, create a workspace that aligns with the functional area of the business your report is focused on. For example, an outage report might go in an Operations workspace.
  2. Publish your report to the new workspace.  When you publish the report, two artifacts show up in your workspace – the report and the semantic model. The latter simply refers to your data model.
  3. Set up refresh on the sematic model. Since the iVUE deployment is typically on premise and your report is now in the cloud, you need to set up a Power BI gateway (Power BI Gateway | Microsoft Power BI) on an on premise server. This server needs ODBC installed and access to iVUE.
  4. After publishing, create an App associated with the workspace. An App is a bundling mechanism that enables you to distribute multiple related reports with a single discoverable URL. It also allows you to specify who should have access to the reports as part of the App creation / update.
  5. Share the URL for the app with your audience.
  6. If you re-publish your report later, update the App to pick up the latest changes.

While this seems like quite a few steps, it’s straightforward and supported with good documentation. Once you have this in place, creating your next report is much easier.

Power BI is an excellent analytics tools and something that can be very useful for iVUE and beyond. Future posts will discuss how I’ve used it for other data sources alongside iVUE.