All posts by opspro

API and BI – Where acronyms meet

Data analytics and business intelligence used to only be available to large corporations. These large enterprises had massive amounts of data to consume that Business intelligence had to become ingrained in their reporting tools. The goal of these tools was always an easy interpretation of vast and sometimes unstructured data.

In recent years, these tools have evolved to where a small business is no longer left behind. Whether your company is big or small, or you are the CEO or an employee, the tools available have progressed so you can access and make use of the same interactive reports. You no longer need large databases and dedicated analysis to retrieve your information in a meaningful way. These tools let users consume data in tailor-fit solutions that provide them with up-to-date information with a modern look and immersive feel.

Right now, you may have processes in place where you pull and export data from Procas into excel. Each time the data is pulled you or an employee may have to waste time updating formulas, reselecting chart areas, filtering and removing columns, but now there is a new way.

Microsoft defines Power BI (Business Intelligence) as a “collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI lets you easily connect to your data sources, visualize and discover what's important, and share that with anyone or everyone you want.”

An API, Application Program Interface, is a set of programming instructions. These instructions enable computer programs to communicate with each other. These programs allow a software-to-software interface which allows the programs to communicate with each other without any user intervention. The API lets Microsoft’s Power BI Application communicate and exchange details with the Procas server. The API provides us with the data and the Power BI Application gives us the tools to export, transform, and refresh the data without repeating or reconfiguring steps or formulas.

What do you need to visualize and discover? What’s important to you?

Our clients look at a vast array of items from their project profitably, their resource management, to employee timesheets, and income statement details. These and more could be at your fingertips.

Follow along in the series to see detailed examples of how OPSPro and Procas used each powerful application to help create an easy-to-consume report.

Source:

PROCAS Reporting PowerBI API Report

The blog post been guest written by our great partners over at OPSPro, LLC. Cathy Goin, Tricia Santos, and their team have been instrumental in helping us develop the ability to connect PROCAS data to Business Intelligence systems via an API. To learn more about the relationship between API and BI, click here for Part 1.

Download - OPSPro Demo of PROCAS Data Using PowerBI.pdf (1.19 mb)

Connect and Transform PROCAS Data - Power Query in Excel

Once you have Procas API access setup, there are a few things you will need to do. You will need to connect using Power Query in Excel or connect to Power BI. Power Query in Excel is a lighter version of the Power BI desktop application.

With Power Query, you can:

Connect to the data
Merge with other data sources
Transform pivot or unpivot
Remove or add columns
Apply custom formula’s
And so much more

With the shaped data, it can be loaded into your excel file as a table. This method will provide you with an introduction to many of the data mining methods used in Power BI but in a format, you might be more familiar with.

Reasons to Explore the Power Query in Excel:

Provides a great way to validate data in a macro table view. You can apply custom cell formatting to make data that does not validate correctly appear in bright red.
Gets you out of the cell method and into the column method. Rather than focusing on the formula of a single cell and then replicating either across or down. Power Query is all Column-based logic.
Using Power Query's column-based logic it can be easier for you to check your math and reduce formula errors.
You can refresh the data when you open the file. It will keep all your steps and transformations.
You can apply Pivots and charts.
Combine and merge data from other sources such as; SharePoint, Excel, and hundreds more.

Some limitations:

Not all connectors are available.
The data must be refreshed manually.
Changes made to any cells inside a table loaded from Power Query will be reset to reflect what is in the data source on refresh.

Upgrading your Reports

Power BI Desktop application and Power BI Service are the true powerhouses behind this self-service reporting tool. Not only does it provide a way to format and clean your data, but it also delivers these engaging, interactive visualizations to tell a story or highlight critical decision-making information.

In addition to all the Power Query features in Excel, there are additional connectors and additional methods to handling formulas:

Using DAX formulas to create calculated columns, measures, and dynamic formatting.
Bookmarks and interactive buttons
Customizable visuals and a visual marketplace
Custom slicers and hierarchies
Custom report pages and dashboards
Change the interaction between visuals.
Schedule refresh with the Power BI service.
Restrict access using Row-level security or RLS.

Tips and Tricks:

You can copy your query steps between both systems naturally because they all use the M Query language.
Many Excel formulas have equivalent formulas in DAX.
Have a goal or question you want answered before beginning a report or data set. Start off narrow and simple and build the report up in pieces.

Leveraging PROCAS Data via the API

Data mining and cleaning your data can be daunting. OPSPro can help. OPSPro has experience connecting and combining many different types of data sources including SharePoint lists, SharePoint Files, OneDrive files, Office 365 Admin Centers, Trello, Jira, Excel, and many more.

OPSPro can also design custom export solutions using Microsoft Flows and SharePoint. OPSPro received some of the earliest access to the Procas API so we are ready to help you navigate. Our services range from consulting to full outsourcing services.