Connecting Lightspeed to Power BI

jtenney

I'm trying to connect our instance of Lightspeed Retail to Power BI so we can run customized reports ad-hoc inside of Power BI off of order data. However I'm not sure where to start or what is required to do so.

gregarican

Here's where you can get starting with the Lightspeed Retail API --> https://developers.lightspeedhq.com/retail/introduction/introduction/.

Hope this helps!

jtenney

How am I supposed to get authorization code if Power BI is run locally on my machine?

Adrian Samuel

Hey @jtenney,

You might consider using a custom data connector built by Microsoft like so:

https://github.com/Microsoft/DataConnectors

More details can be found here:

https://docs.microsoft.com/en-us/power-query/samples/trippin/readme

It enables you to write code to interface between PowerBi and any data source such as the Lightspeed Retail API

blackball

Are you still looking for a solution to connect your Lightspeed data to Power BI?

We've released a Lightspeed connector for SyncHub to incrementally pull all of your Lightspeed data into Power BI. Find out more at https://www.synchub.io or send me a message at [email protected]

Michieldo

Hi @Jtenny,

I was wondering if you already found a way to connect Lightspeed to Power BI? We would like to report in Power BI as well, but are facing the same challenge with limited developing capabilities within the company.

VintageWineGuy

I have been beating my head against this for a while as well. I took today and spent pretty much all day trying to come up with something I could do that wasn't super labor or tool intensive. I just had a little epiphany and breakthrough, so thought I would share.

PowerBI (including the free desktop version) reads JSON, and can parse through the JSON that the lightspeed API produces. So, I am currently having some success with extracting a full dump of data from the API as JSON, saving it off, and feeding it to Power BI.

The trick is mastering the right clicks in PowerBI to expand the data. Some binging turned up this video that was helpful: https://www.youtube.com/watch?v=ipI6mrWLQKA

Just open the JSON file directly (the example in the video is a csv with JSON, but it helps with the concept and what to click), right click the Column, Convert to Table, then Expand the Column (uncheck the use column name as prefix option), and keep doing that for any sub-levels. Things like Price on Item you can choose to Expand to New Rows and it will give you a record for Default, Online, MSRP, etc.

I am still experimenting, but I had been going down a path of building a data warehouse, but I may just add saving the json to my nightly batch and let PowerBI do the lifting.

VintageWineGuy

Adding on to my other post, I was working with the JSON raw in PowerBI Desktop and was getting frustrated with the speed, so I tried another trick that has worked pretty well. I used Talend Open Studio (free version) which has a pretty good JSON importer and a MySQL exporter. I took the raw JSON from the API, fed it to Talend, and pushed it to MySQL. Now that it is set up (took about 2-3 hours including clueless bumbling) I have a reporting database I can refresh periodically. A couple manual steps that will take 5-10 min to repeat, but not bad for a bunch of free tools.