Lightspeed Retail Power BI dashboard

Through the API for your point-of-sale system. Lightspeed has two retail POS products, the R-Series and the X-Series, each with its own API and documentation. Both use OAuth for access. We load the data and build the Power BI model on top of it.

For why we start with the decisions you need to make rather than the charts, see Power BI dashboards.

Lightspeed has renamed its products. The former Lightspeed Retail is now Lightspeed Retail POS (R-Series), and Vend is now Lightspeed Retail POS (X-Series). According to Lightspeed, the L-Series is its restaurant POS system: Lightspeed Restaurant POS (L-Series).

This page covers the two retail POS systems. If you’re not sure which one you have, we’ll work it out together in our first conversation.

The R-Series API includes sales with line items and payments, products, stores, employees, customers, suppliers and purchase orders. Each sale includes the store, register, employee and customer, along with a timestamp for its most recent change.

The loading step uses that timestamp to load only what has changed since the previous run.

In the R-Series, stock is held in a separate table by product and store. It includes the quantity on hand, the reorder point and the target stock level. A row with store number 0 contains the total across all stores; we leave it out of the model to avoid double-counting.

This lets you see which products are at or below the reorder point in each store, and where else they are still in stock.

The X-Series has its own API covering sales, products, outlets, stock, customers, users, consignments and purchase orders. Each request requires a specific permission, such as read-only access to sales.

The model stays the same: sales by outlet and employee, and stock by outlet. Only the loading step differs between versions.

Lightspeed eCom is the online store; Retail is the in-store POS system. When they’re connected, Lightspeed identifies the POS system as the source for products. In-store sales and stock then come from Retail, while online orders come from eCom.

For how we bring online store data into Power BI, see the Lightspeed eCom Power BI dashboard.

We keep the credentials for loading data outside the report, with only the permissions needed. In Power BI, row-level security limits what each person can see. For example, a store manager can see only their own store. Sales by employee should preferably be shown only to the people who manage those employees.

For how we set up workspaces and refreshes, see Power BI consultant.

Typically, they report on revenue and margin by store, product category and employee; the number of receipts and average transaction value; stock by store compared with sales; and purchase orders that are still open.

For cash flow and budget versus actuals, see the financial dashboard in Power BI. If you have one store and the back office meets your needs, we’ll tell you.