Connecting Lightspeed to Power BI

jtenneyjtenney Member Posts: 2
in Tutorials

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.

Tags:
· Share on Google+

Answers

  • gregaricangregarican Member Posts: 901 

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


    Hope this helps!

    · Share on Google+
  • jtenneyjtenney Member Posts: 2

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

    · Share on Google+
  • Adrian SamuelAdrian Samuel Moderator, Lightspeed Staff Posts: 654 moderator
    edited August 2019

    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

    Adrian Samuel

    Software Developer

    Lightspeed HQ

    · Share on Google+
  • blackballblackball Member Posts: 2
    edited December 2019

    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]

    · Share on Google+
  • MichieldoMichieldo Member Posts: 1

    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.

    · Share on Google+
  • VintageWineGuyVintageWineGuy Member Posts: 131 

    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.

    Annotation 2020-04-28 174031.png

    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.

    · Share on Google+
  • VintageWineGuyVintageWineGuy Member Posts: 131 

    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.

    · Share on Google+
Sign In or Register to comment.