How to connect Power BI to Katana
Short answer
Katana has no direct database access, so Power BI connects through the REST API: pull orders, stock and purchase data into a warehouse or dataflow on a schedule and model it there. Build dashboards for stock health, order backlog and supplier delivery, then add natural-language Q&A on top of the model.
Katana's built-in reports cover a lot, but owners eventually want trends across months, margin by product family, and a view that combines Katana with accounting or ad spend. That is when Power BI comes up.
The first surprise is that you cannot point Power BI at a Katana database. The route is the API, and that shapes everything: you extract on a schedule, you keep history yourself, and you design for rate limits. This page covers that route and the dashboards that earn their keep for a small manufacturer.
What teams use it for
Stock health and reorder dashboard
Days of cover per variant and material, with items below reorder point, built from stock snapshots you accumulate daily.
Order backlog and ship performance
Open sales orders by age, promised versus actual dispatch date, and on-time rate by product family.
Margin by product and customer
Joins sales order rows with recipe cost at the time of sale to expose where margin is eroding.
Supplier delivery reliability
Compares purchase order expected and received dates by supplier, and the cost of late materials in delayed builds.
Production throughput
Manufacturing orders completed per week, time in production and planned versus actual quantities.
Combined view with accounting and ads
Joins Katana revenue and COGS with QuickBooks or Xero totals and marketing spend for an owner scorecard.
Katana integration surface
- Katana REST API as the only data path. Authenticate with a bearer key and page through list endpoints for sales orders, manufacturing orders, purchase orders, stock and catalogue data. Verify current paths and filters in the docs.
- No public SQL access and no on-prem database, so no on-premises data gateway is needed for Katana itself.
- Power BI can call web APIs with Power Query's Web connector, though pagination, auth and rate limits make it fragile for anything large. Prefer a separate extract job.
- A warehouse or lake landing zone (Azure SQL, Fabric, Postgres, BigQuery or even blob storage) is where history lives, because the API gives you current state and not daily snapshots.
- Integration platforms such as Zapier, Make or ETL tools can move data on a schedule if you do not want to write code. Check whether a maintained Power BI connector exists before building.
How it works
- 1
Define the questions first
Write down the six decisions the dashboard must support. This tells you which Katana objects and which history you need.
- 2
Build an extract job
A scheduled script or ETL job calls the API with pagination and backoff, writes raw responses to a landing table and then to a clean model.
- 3
Snapshot what the API cannot give you
Save stock levels and open order status daily so you can chart trends. Katana shows today, so history exists only if you store it.
- 4
Model for Power BI
Build a star schema: fact tables for order rows, manufacturing orders, purchase rows and stock snapshots, with dimensions for variant, customer, supplier, location and date.
- 5
Connect and publish
Point Power BI at the warehouse, build the measures in DAX, publish to the service and set a refresh that follows the extract.
- 6
Add natural-language Q&A
Use the Power BI Q&A or Copilot features if your licensing allows, and curate synonyms. Or add a separate assistant that queries the warehouse and shows the SQL it ran.
Example flows
Nightly extract to dashboard
Trigger: Scheduler fires at 02:00
- Job pulls orders and purchase orders changed since the last run.
- It snapshots stock levels per variant and location.
- It updates fact tables in the warehouse.
- Power BI refresh runs at 05:00.
Result: The owner opens fresh dashboards at the start of the day, with trends the API alone could not provide.
Ask a question over the model
Trigger: Owner types a question into the Q&A box or a chat assistant
- Question: which five variants had the biggest margin drop this quarter.
- The model maps terms to the margin measure and variant dimension.
- It returns a visual and the underlying numbers.
Result: A quick answer without building a new report, as long as the semantic model is curated.
Keeping data on-prem
Katana is cloud only, so the extract must run somewhere that can call the Katana API. That can be an on-prem server or a private cloud VM writing to your own SQL Server, with Power BI Desktop or Report Server reading from it through a gateway if required. This gives you control over history and where combined data lives. Be clear that the source remains in Katana's cloud, and that the benefit of on-prem here is the warehouse and any private AI layer, not Katana itself.
Security checklist
- Use a read-only API key dedicated to the extract job and keep it in a secrets store.
- Apply row-level security in Power BI if different users should see different customers or product lines.
- Hide cost and margin tables from roles that should not see them.
- Encrypt the warehouse and restrict network access to the extract host.
- Document refresh credentials and owners so they do not rely on one person's account.
Ways to get it done
Power Query Web connector directly to the API
Effort: Hours to days
Small data volumes and a quick prototype. Weak for pagination, history and rate limits.
ETL tool or script into a warehouse, then Power BI
Effort: One to three weeks
Most teams; gives history, performance and a clean model.
Netray builds the model and a Q&A layer
Effort: A scoped project
You want a curated model, snapshots and a natural-language assistant that shows its queries.
Pitfalls
- !Relying on live API calls in Power BI refresh and breaking on rate limits or timeouts.
- !Forgetting to snapshot stock, then discovering you cannot chart last quarter.
- !Mixing variants, products and materials in one table, which breaks measures.
- !Using current recipe cost for historical margin; store cost at the time of sale if you need accuracy.
- !Turning on Q&A over an uncurated model and getting confident but wrong answers.
Frequently asked questions
Is there a native Katana connector for Power BI?
Check Microsoft AppSource and Katana's integrations page for the current state, as we cannot assume one exists. Without one, use the REST API through an extract job into a warehouse, which is also the more robust design.
Can Power BI connect straight to Katana's database?
No, Katana is a hosted product and does not provide direct database access. All data access is through its API or exports. That is why a warehouse that stores your own history is so important for trend reporting.
Do I need an on-premises data gateway?
Not for Katana itself. You need one only if your warehouse sits on a private network that the Power BI service cannot reach, such as an on-prem SQL Server. Cloud warehouses can usually connect without a gateway.
How often can data refresh?
That depends on Katana's rate limits, your data volume and your Power BI licence. Many teams do a nightly full refresh with incremental pulls during the day. Webhooks, if available, can help keep a few operational tables fresher.
Can I ask questions in plain English?
Yes, through Power BI Q&A or Copilot where your licence includes them, or a separate assistant over the warehouse. Quality depends on a well-named semantic model. Show the generated query or measure so users can trust and verify answers.
Want Power BI working on your Katana?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment