Node-Red business case

[Glenn]

I was wondering if others used this program for business uses.

I was thinking of automating processes and for data transformation.

Eg if we get a file, node-red would validate the file and output the data into an CSV format.

I like Nodered for the simplicity of the flows and it makes the complex seem easier.

Could also be used for automating sending of reports and downloading data from external servers.

Is this something others do, if so how did they put a business case forward.

[Julian Knight]

Hi Glenn, I've always been interested in Node-RED as an ETL/Informatics tool and I think it can find a place though typically, the kind of people who might use it are not generally familiar with JavaScript - Python and R would be more familiar.

I think that you need to find a group of people who might benefit from this kind of tool over using other ETL/BI type tools or indeed the excellent ETL tool now built-in to Excel (PowerQuery). You would then need to persuade them to try it and to do some learning.

So it would fit into an organisation that doesn't have other ETL/automation tools available already and that cannot justify the cost but would still benefit from an ETL/automation tool with users prepared to roll up their sleeves and get stuck in.

[Glenn]

Thanks, I'm thinking of being that person so happy to get my hands dirty.

I just think it can provide great benifits over just using powershell for automation.

It can do great data transformation for big data.

I guess I just need to do an end-to-end automation project to show if it can be good or not.

One thing that I like is it is easier to find problems, as each node can be debuged

[Julian Knight]

Well that makes it rather easier to "sell" since it doesn't need any infrastructure and there is no license cost so a "business case" is a little redundant ;-)

Big data could also be problematic since NR started as an IoT tool and is mainly geared around keeping things in memory. Though I think someone wrote some nodes to process larger files. But if you have a 64bit machine/OS with plenty of RAM (16GB or more) and use 64bit Node.JS you should be able to process a fair bit in memory.

One of the great things about using PowerShell is its ability to stream data through a set of functions using its native pipes so handling large datasets is pretty seamless. I'm not sure how Excel/PowerQuery does its processing but I can say that it can handle complex transformations on at least hundreds of thousands of rows of CSV even just on a 64bit Windows 10 laptop as I've done that fairly regularly.

One of the strengths of NR though is that it is easily run as a service on any supported platform so you can keep flows live in the background which is good for automation. Link to PowerShell scripts via native Windows pipes if you like or maybe via MQTT since Mosquitto runs happily on Windows and is pretty lightweight on resources.

Hopefully as NR continues to mature, it will pick up some better streaming capabilities which would be very useful in this scenario.

Yes, you really do need a practical use in order to get started and this can be tricky.

In terms of debugging, this is known to be a limitation right now and I know that Dave/Nick continue to think about how that can be improved. The nice thing is that, once working, NR tends to be very stable.

[Julian Knight]

Oh, and it might be interesting to see a full MQ type node rather than just MQTT if you are using a message bus in your business, that would make for a very interesting use case.

On Monday, 1 May 2017 12:23:07 UTC+1, Julian Knight wrote:

...

[Glenn]

I waa just going to use inject nodes to perform tasks on a schedule.

Cost is only one factor of a business case. Usability is another. What benifits does it bring.

I was thinking routine tasks can be replaced with nodes and it can be more flexible for small datasets

[Julian Knight]

Yes, it is good for some routine tasks, especially if you can set up an event system like a mini enterprise service bus (again, you could use MQTT for that). So that you can trigger events from multiple locations/systems and send/receive data easily.

You could also do that via your existing email system, I've done that before but not with Node-RED. NR would have made it much easier and a lot quicker to implement. In the past, I built a registration system that monitored a mailbox for emails with certain attachments (Excel spreadsheets). It checked the spreadsheet data for validity, sent validation failures back to the senders (system vendors from all over the country) and sent validated files on to a team for processing. It also had some web pages for reference data that the spreadsheet template would use when opened so that the spreadsheet template always had the latest reference data in it.

[Glenn]

Interesting. MQTT won't work. But email might. Care to share some more info on that if you can.

[Julian Knight]

Well it was quite a while ago, back in the dark days of PHP!

The the principals are fairly straight forward. I used IMAP to monitor for new emails in a particular inbox. If the email had an attachment of the right kind, I would add a flag to indicate it was being processed. Then extract the excel file and used a library in PHP to read key data from it and check that it validated. If it did, I would send an email confirmation back to the sender with a reference ID and another email with the same ID and with the attachment through to a different mailbox in BT who were managing the registrations.

The reference data was fairly easy to do as all I needed was to create a tabular layout with some data from a database. Excel is easily able to read that when opened and use it to update an internal set of tables used as lookups in formulae and conditional formatting.

I've done plenty of other similar but simpler processes over the years where I've used email attachments to consolidate data from spreadsheets or CSV files. Notably for a large UK ISP that was having problems with its sales staff and with sales forecasts.

The combination of a templated spreadsheet, email for transport and some tool for consolidation is really powerful. It allows business users to use familiar tools (Excel and email) while consolidating into a central database or central automation process.

I remember that the biggest difficulty with the first example was that the PHP IMAP library was pretty rubbish and didn't play well with Exchange. So, with some help from a colleague at the time, we wrote a new one. The 2nd biggest difficulty was getting the spreadsheet template right. The overall registration process was a bear and it required a lot of reference data and cross-referencing.

[Glenn]

Thanks. My biggest hurdle is the human element as emails are typed by a person

Meaning flows can be missed. I will keep you updated.

[Julian Knight]

Yes, that is always an issue. But there are more ways around that too. That's why I used a structured excel template with validation. Nothing more or less than a form of course 😀

You might also be able to use something like red-hot which has some useful stuff for this kind of thing and doesn't have to be driven by a bot.

Speaking of bots. These days, that's another option for creating user interactions. And has the advantage of being fairly easy to secure.

[Glenn]

Bots is one step too far when dealing with people who aren't familar with tech.

How would I get Node-red to interact with Excel? Would that require something extra.

I can use the file node to output to text but don't now how to add it to excel.

[Julian Knight]

Well AlaSQL (http://flows.nodered.org/node/node-red-contrib-alasql) claims to be able to read Excel, I did some tests when it first came out and it seemed to work. There are also several ODBC nodes that you could use.

There are other Node.JS libraries out there that handle excel files so they could be required and used or wrapped in a simple node. https://www.npmjs.com/browse/keyword/excel.

[Julian Knight]

Oh, and users - even techno-fearing ones often find it much easier to interact using bots in a familiar setting. So if you already use Twitter, Skype, Facebook then using a bot is a simple move either with those services or something more specialised - Telegram is my current favourite since it is so easy to create bots for.

[Glenn]

Hi Julian,

You have been a great help. So I wanted to pick your brains some more.

How would you recommend getting a Excel table into node red.

Would you suggest MQTT or ODBC.

I basically want the dashboard to update when the Excel is updated.

It's one worksheet but with multiple sub-tables

[Julian Knight]

Well you could use MQTT to disaggregate flows but it won't help further unless you were to build an overriding save function into the Excel worksheet that triggered a push to MQTT on save - that seems rather overkill to me.

Of course the easiest way would be to not use Excel but rather a CSV file and you could override the save function in Excel to create a CSV copy on save as that isn't hard at all. But it would mean having VBA macros active in the SS which some people and organisations don't like.

So a pure Node-RED solution would need 2 things. Firstly something that registers a change in the file and secondly something that reads the file.

Reading would be via ODBC or AlaSQL or similar so that is pretty easy.

The trigger is a bit harder. If the file is on a "normal" filing system - which is to say not on a networked filing system - you should be able to use the "watch" node which I think works on Windows as well as Linux/Mac. However, if the file is on a shared drive, I don't think that works. In that case, you would need to have a periodic trigger that used one of the filing system nodes to check for the last update timestamp and if it has changed since the last run then process the file. Of course, you would then need to save the last run timestamp so that you can read it next time around. As an alternative to the file timestamps, you could put a datetime entry in the spreadsheet but then you would have to read it every time.

[Glenn]

Wow, this is getting over complicated. Maybe Sharepoint is the way to go? We already moved to Office 365.

[Julian Knight]

:-)

Really depends what you are wanting to achieve. Without a doubt, if you already know Excel well and are using a reasonably up-to-date version, you have some extremely powerful tools including visual ETL (PowerQuery). Moving Excel spreadsheets to a SharePoint list gives you some nice additional features, at least if you are using Office 365, with more data types, lookups, etc. It also then lets you use O365 Flow - though you might want to ask Microsoft some hard questions about where your data goes if you use it for sensitive information processing.

But on those odd times where you need to do something different with the data, especially if you can dump it to CSV, and particularly if you then want to dynamically integrate with other systems, that's where Node-RED might help. For example, if you had a need to periodically upload Excel/CSV data to a web API, NR would likely be a good candidate to help especially if you wanted to leave it running on a server somewhere.

As always, it's a matter of finding the right use case.