Skip to main content

Power BI Connection Guide

This guide explains how to connect Microsoft Power BI Desktop and Power BI Service to ECOSIRE's Analytics API, enabling you to build custom dashboards on top of your live ERP data.

Introduction​

ECOSIRE exposes analytics endpoints that return structured JSON data suitable for Power BI's Web connector. You can import revenue time-series, sales pipeline, inventory KPIs, HR metrics, and customer data directly into Power BI models.

Connection methods covered:

  1. Power Query Web connector (recommended for live refresh)
  2. Excel + Power Query (for ad-hoc analysis)
  3. ECOSIRE Power BI Template File (fastest setup)

Prerequisites​

  • Microsoft Power BI Desktop (free, download here)
  • ECOSIRE account with Analytics module access
  • ECOSIRE API key (Dashboard → API Keys)
  • Power BI Pro license (for scheduled refresh and sharing)

Step 1 — Get Your API Key​

Generate a read-only API key for Power BI:

  1. Go to ecosire.com/dashboard/api-keys.
  2. Click Generate new key → set name to Power BI → set permission to read.
  3. Copy the key. You will paste it into Power Query as a parameter.

Step 2 — Connect via Power Query Web Connector​

  1. Open Power BI Desktop.
  2. Click Get Data → Web.
  3. Select Advanced and enter:
URL parts:
https://api.ecosire.com/api/analytics/revenue?from=2026-01-01&to=2026-03-31&groupBy=month

HTTP request header parameters:
Authorization  | Bearer eco_live_YOUR_KEY_HERE
  1. Click OK. Power BI fetches the JSON and shows the query editor.

Step 3 — Parse the Response​

In the Power Query editor:

  1. Click Convert → Into Table on the data column.
  2. Expand the timeSeries list: click the expand icon → select all fields.
  3. Rename columns as needed: period, revenue, orders, refunds.
  4. Set data types: period → Text, revenue / orders → Whole Number.
  5. Click Close & Apply.

Step 4 — Create Parameterized Queries​

To make date ranges dynamic, create Parameters:

  1. Home → Manage Parameters → New Parameter:

    • Name: DateFrom | Type: Text | Default: 2026-01-01
    • Name: DateTo | Type: Text | Default: 2026-03-31

  2. In the Advanced Editor, reference the parameter:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.ecosire.com/api/analytics/revenue",
            [
                Query = [from = DateFrom, to = DateTo, groupBy = "month"],
                Headers = [Authorization = "Bearer eco_live_YOUR_KEY"]
            ]
        )
    ),
    timeSeries = Source[timeSeries],
    toTable = Table.FromList(timeSeries, Splitter.SplitByNothing()),
    expanded = Table.ExpandRecordColumn(toTable, "Column1", {"period", "revenue", "orders"})
in
    expanded

Step 5 — Import Multiple Endpoints​

Create separate queries for each data domain:

Query NameECOSIRE Endpoint
Revenue/api/analytics/revenue
SalesPipeline/api/analytics/sales
Customers/api/analytics/customers
Inventory/api/inventory/products
SupportStats/api/helpdesk/stats

Repeat Steps 2–4 for each endpoint. Each becomes a table in your Power BI data model.

Step 6 — Build Relationships​

In Model view, connect the tables:

  • Revenue[period] → SalesPipeline[period] (many-to-one, shared date dimension)
  • Create a Date Table using DAX:
DateTable = CALENDAR(DATE(2026,1,1), DATE(2026,12,31))

Step 7 — Schedule Automatic Refresh​

  1. Publish your report to Power BI Service (File → Publish → My Workspace).
  2. In Power BI Service, go to your dataset → Settings → Scheduled Refresh.
  3. Add the ECOSIRE API key as a Data source credential:
    • Authentication method: Anonymous (the key is in the query header)
  4. Set refresh frequency: hourly, daily, or as needed.

For real-time dashboards, use the Streaming dataset API or Direct Query mode (see Analytics API).

Step 8 — Use the ECOSIRE Power BI Template​

Download the pre-built template from your dashboard:

  1. Go to Dashboard → Downloads → Power BI Template.
  2. Open ECOSIRE_Dashboard_Template.pbit in Power BI Desktop.
  3. When prompted, enter your API key and date range.
  4. All 8 dashboard pages load automatically.

Troubleshooting​

IssueSolution
403 ForbiddenAPI key does not have read access to the analytics endpoint
Empty data returnedVerify the from/to date range has data
Refresh fails in ServiceRe-enter credentials in Power BI Service dataset settings
Slow query performanceReduce date range; use groupBy=month instead of day
JSON parse errorConfirm the endpoint returns 200 — check for typos in URL

Next Steps​

Last updated on