Hacker News Reader

Comment by kfk | Hacker News Reader

"Knowing what matters is the real key to being an effective data scientist."

Absolutely. What is absurd, though, is that even for the simplest of metrics, we don't have really good tools in place to:

1. Do matrix calculations (think R dataframes)

2. Build quick dynamic dashboard with strong data segregation by user (you don't want to show all data to all employees)

3. Clearly separate values from dimensions so hierarchies and mappings can be managed properly

4. Allow for manual inputs

5. Decently interface with Excel in some way

View on HN

4. Holy shit 4. I actually disagree with some of your other points; if I need DF calculation I'll do pandas in python, and in a pinch can make some raw SQL/terrible LINQ work; dynamic dashboards exist but data segregation is indeed a problem I don't see solved outside of the slightly aformentioned "make views and give very fine grained permissions" runaround; 3 I'm not sure I am understanding you right because I feel like I can do this in a combination of sql views and powerpivot, and 5, see the above, powerpivot.

But 4, someday if it weren't the property of some company now I would LOVE to show off some of the hacks I've done to get manual value input in Excel to work to do live re-processing on data originally sourced elsewhere. Probably some of the least maintainable and robust code I have _ever_ written, and that's saying something at this point, unfortunately...

I should have mentioned that all my points should be easily done in an Excel-like interface, so no coding allowed. Point 3 is how you look stuff, maybe team A defines China as China, but team B defines it as China + Taiwan + Hong Kong, at scale this kind of use case is very hard to manage on a centralized BI system.

I'd be interested in hearing more about your hack, my email is in my profile, manual inputs are one of the biggest pain in BI and one of the reason people default to Excel files by email. In general, I am very passionate around those topics, but I am having a hard time building an audience around this.

Re: your profile, I saw a contact us for what looked to be a startup, and your medium page, but no email (unless I'm particularly blind/you meant the "contact us).

That being said, it's a simple enough explanation so I'll just divest it here. The process I use certainly is constrained to certain requirements on the dataset, but it's "worked for me" as a pattern a few times now. 1. Get your data into powerBI, data source of your choosing. Build any custom columns you want primarily in the data model.

2. Bring the powerBI as a pivotTable (not directly as a chart.)

3. this is where things get messy/constrained, and will have needed to plan ahead for this bit. Treat a cell in the chart as your "input cell", and create new cells/new rows with formulas that utilize the input cell as WELL as the cells underneath the pivot chart you pulled in. NOTE: excel REALLY DOESN'T like you doing this. If you try to select the raw cells in the pivot table, it will get confused and not behave as you expect. If you type in the cell identifiers as you would with raw excel (B3:B24, etc) and they "just happen" to be under a pivot chart, however, it works fine.

This step is largely where the "unmaintainability/fragility" comes into play since as you can probably tell, the whole workaround relies on the _exact_ structure of the data as it relates to cell locations. You may need to ensure this constraint yourself; e.g. if I had a timeseries pivotchart that I append to, I'd need to order descending of the most recent 100 rows, to ensure my static excel formulas were being populated as expected. I feel like my explanation of this is a bit lacking, so please don't hesitate to ask questions if I'm unclear, it's a rather mundane solution in and of itself.

4. Once you're confident your data isn't going to change "structure" during a refresh, you can build up a typical formulation chain -> chart as you would with pure Excel, except that the formulation utilizes your input row for custom on the fly user inputs, and is sourced from refresh-able external data.

I have been working in Business Intelligence for almost a decade now and you are spot on with you comment.

Every BI tool sells an entire ecosystem with it that ties the purchasing organisation into the platform. It is shocking to see that none of the things you mention exist as FOSS.

Unfortunately a lot of the open source BI tools out there are really still very immature compared to the commercial products.

To be honest, the top stuff available for top $$ is also pretty bad. I am working now on a BI project and I am dealing with SAP BOBJ/HANA/Tableau/SFDC - it's an absolute nightmare. Nobody seems to realize that if you don't get to a point where on the same data team A can build view X with access Y and team B can build view X' with access Y', everybody will just keep using Excel and BI remains a word for a glorified database extraction tool. Maybe I should do a write up on this, I'd be interested to see if there is enough interest in the industry for consulting/better solutions.

You are spot on! For what you pay for top $$ products they fall WAY short of what is required. If you do end up doing a write up please send me the link. I sometimes feel like the only person in the room that can see the end result just being a glorified ETL tool with some rudimentary dashboard porn that is of no use to anyone, all of this exactly due to the problem you described.

Cool, you can follow me on medium (https://medium.com/@kfk) or send me an email (in my HN profile), I'll work on some write up soon.

Don't really have an answer for any of your other points, but at least for 1), have you considered Fortran (90)? The language is built for linear algebra and mathematical computing, and matrix multiplication that makes sense comes out of the box with it.

Reply on news.ycombinator.com