your opinion on Business Intelligence software?

[orbs]

hi everyone,

looking for a BI (Business Intelligence) software, for use by non-technical end-user.

requirements are trivial: connect to MySQL, retrieve data thru some filtering, and present the data mainly as a pivot table, which allows further filtering, sort options, and custom real-time computed fields.

tried Microsoft Power BI Desktop, didn’t like it. The UI is horrific at best; query set-up is cumbersome to the level of impracticality; custom fields are near-impossible to define; and when it comes to ~10M rows, it becomes sluggish beyond usefulness.

what are you using, which you can recommend?

(open-source is preferable, of course.)

[Earthshine]

I think you can use vbscript inside Excel to do all of that. I made one one time that connected to an Oracle database and pull the data out and made reports for the manager to see what level of testing the software product had been Successful and how much failed so all they had to do was open the spreadsheet to see where their project was with a glance. It was a giant living document that was constantly being updated by developers and testers alike. Management could easily track where they were in our meeting stayed short and focused every day. That was the best rapid application development environment I have ever worked in

Edited October 26, 2020 by Earthshine

[orbs]

Excel is the current solution, and it works great - until you try to work with more than a million rows. The dataset is growing to nearly 20 times that.

[Earthshine]

How about you have stored procedure you kick off that makes a temp table indexed for you with all your data and you pull off that?

[orbs]

yeah, but then the question becomes - what software to use, to allow the end user to work effectively with the resulting dataset of several million records.

so far i've looked into Metabase, Knowage, Seal Report (search then in google if you wish) - none are satidfactory.

what looks promising at the moment is the Excel "Power Pivot" add-in. not happy about this being mainly a client-based solution (ideally, i hoped for a server-based solution, with a browser-based client with no special confiurartion). but it seems to get the job done. still testing...

[Confuzzled]

Just a cheeky question: Are you working on the NHS #spreadshit covid-19 reporting update project? Does it exist yet?

You are looking to extract and report on a humongous database?

Tried some of the 'Big Data' stuff from IBM, Oracle, Amazon, SAP/Crystal Reports, and Google? Somehow lowly Excel and Access doesn't quite cut it when things get THAT big! You sure the end user needs to have several million datapoints on their desktop to manipulate, or they just want a snapshot at a point in time?

Issues such a security and access, contention, record locking, backup, performance, tuning, rollback, data consistency, granularity, end-user futzing/customisation etc raise their ugly heads when it starts to get that big.

Then you put it all on the cloud, unprotected...!

Get the big iron to do the heavy duty number crunching, and get the data as small as possible before it hits the desktop and Excel where the end users fiddle with the data. Make sure they cannot write to the database if that is not their function. Go through a standard API for end users - don't permit them to get to the data directly. If they cannot figure how to address an API, then certainly you don't want them locking the entire database while they go to a long lunch.

Often batch jobs are the solution, rather than real time reporting. Do you really need to know to the nanosecond what is happening, or will something from an hour, or day, or week ago suffice? Can you be sure you are comparing similar results if reports that are generated at different times of the day are used for comparison? Often the data is only needed for regularly scheduled meetings, so you can plan ahead for generating your reports.

The time you spend on scoping the project, doing detailed specifications and design work, making standard templates as a guide, consulting and pinning down what exactly the end users NEED (not want) - in writing, and putting dollar figures on specific functionality will certainly go a long way to making it a successful project.

You cannot cut corners. The systems analysis portion of your project, before you even cut one line of code, is by far the most important.

[KazzioRibald]

If you are in need of a pressure washer, Sun Joe has an excellent product to offer.  Read this review https://gardeningfacts.org/best-pressure-washer/sun-joe-pressure-washers/sun-joe-spx3500-review/

Edited February 24, 2021 by KazzioRibald

[orbs]

On 10/31/2020 at 12:05 AM, Confuzzled said:

get the data as small as possible before it hits the desktop

 

On 10/31/2020 at 12:05 AM, Confuzzled said:

Often batch jobs are the solution, rather than real time reporting

 

these points seem to be the key to effective workflow. now in discussion with end-users and managers to see exactly how i can narrow the dataset down, perhaps split into several datasets each requiring its own analysis routines, and producing those reports overnight so they are ready the next morning. hoping this can reduce specification requirements.

[Confuzzled]

Um, you don't want different datasets which you have to keep concurrent if they have the same content. Conversely you don't want unnecessary fragmentation either.

This problem of management reporting is found globally and has been solved decades ago. Batch jobs are the way to go. If they want real time reports, they pay for it with added software and hardware, and you better add a few zeros to the first figure you come up with for future expansion of requirements. If one manager has A4 two color reports, the next will want A3 in sixteen colors and multiple graphs and pie charts - it never ends!

Careful system specification and analysis is the key concept here. Get that right and your job becomes very easy. Make sure your users spell out exactly what they want, and promise and deliver no more. Make sure they put it in writing. Scope creep is the bane of every developer, and the downfall of every salesman.

Once you have scoped out what the users really need, the solution usually is fairly obvious. If your data extraction can produce a report of only a few lines as a management summary, then Excel may fit the bill nicely. If they want multi-paged reports with comparison charts, then they might have to go for something more sophisticated.

Nobody in the forums is going to hand hold you with systems analysis and specification, which should be your first priority. Once you have that sorted, then come back with more specific requirements and somebody will offer some suggestions.

[Confuzzled]

On 10/26/2020 at 9:43 AM, orbs said:

requirements are trivial: connect to MySQL, retrieve data thru some filtering, and present the data mainly as a pivot table, which allows further filtering, sort options, and custom real-time computed fields.

'Trivial' sounds like you could do this with a few short scripts within Excel VBA and some query language commands. Somehow I don't think it is that trivial what you need.