Hacker News Reader

Comment by taylodl | Hacker News Reader

> This alone could eliminate the need for websites that just want this sort of data

I catch flak for this all the time! Our users just want to be able to use Excel to free-form analyze their data. IT, of which I'm a part of, insists on building web apps to do that for them. They're never happy because all they wanted was Excel.

I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze. Don't let them anywhere near the source data. They keep looking at me like I have ten heads. Instead they want to launch multi-million dollar projects to bring Tableau to our users - and they don't want it! It's asinine!

View on HN

Our company uses Power BI for this very reason. Most of our users are content with what's available in the reports we've built in Power BI. However, a few power users directly connect to the datasets we've created (that also feed the Power BI reports) and can mix and match that data in Excel.

Excel will import datasets from the Power BI client, and you can refresh the data at any time. The nice part of this arrangement is that the whole model is imported. All the relationships between tables, all the added calculation columns, all the measures are there for use by the end user in Excel.

Power BI has been a great solution for our company (~1000 employees).

There’s also the Power Query add-in for Excel that’s super steezy.

It’s like a light Power BI.

Honestly even thought I can make interactive report webapps, that stuff needs to be built and maintained so I rather have people use Excel or a BI tool. Even I rather use Excel or a BI tool than write code.

To be fair, Tableau Desktop, Tableau Prep, and Tableau Cloud are great if expensive products. With Tableau Prep, you get a visual ETL tool. Being technical, I prefer SQL, but for business folks Tableau Prep is more of a REPL so they see what their actions are going to do. They can schedule these Prep flows in Tableau Cloud which will ETL and produce daily datasets which users can then interact via the dashboards on Tableau Cloud.

IMO, Excel is on the right path with out of the box connectors to Salesforce, Azure databases, etc. What it really needs is full blown SQL with REPL output so the users can see the effects of their SQL. (The way Tableau Prep does it is that it defaults to a sampling of rows to output immediate results). There used to be a Microsoft service called Excel online but it was another thing you had to buy and was super confusing. Excel needs to let users schedule their ETL/datasets in SharePoint online and then let other users subscribe to it. They can call it Excel Super+ and charge a small fortune. I am positive there are people with their wallets open.

> I am positive there are people with their wallets open.

Take our money! The entirety of the Fortune 1000 would license it on day one!

That last part is almost exactly what we do with Power BI. We import tables from a SQL data warehouse into Power BI. We then build a data model (creating relationships between tables, adding calculated columns to those tables where needed, and creating measures to summarize the data). We then create reports based on those datasets. Most of our users are content with what they see in the Power BI web client.

This is the killer feature, however: power users can access those same datasets through Excel and import the entire model. All the relationships, all the calculated columns, and all the measures are there for the power users to mix and match and filter however they want in Excel. It's been a phenomenal success for our business.

>This is the killer feature, however: power users can access those same datasets through Excel and import the entire model.

I had this once. Then someone decided it was 'dangerous' and locked us out. I went back to exporting the data from the ERP and loading it into Excel...because I was allowed to do that

DataIku

> What it really needs is full blown SQL with REPL output so the users can see the effects of their SQL.

FWIW, I built a version of this at $DAYJOB. You can get REPL-like access and export to Excel using a tool like this: https://azure.microsoft.com/en-us/products/data-studio

Non-software engineers looked me like I had ten heads for suggesting that people would need to learn a modicum T-SQL to make use of it. I even volunteered to write all the views for them! The lesson learned for me is that horrible expensive ERPs/ETLs tools are never going to be replaced with Excel + a database because even the (non-software) engineers at a large company are not interested to learn how to write basic SQL statements.

FWIW I thought Azure Data Studio was very cool.

Google’s ecosystem makes this incredibly simple.. all you need is to populate BigQuery with the datasets of interest, and Sheets can pull it in via a connector that’s configureable in a GUI. I sincerely hope my org never migrates to MS for this reason alone.

Came here to say the same thing.

Another good reason to not migrate to MS is that they are a security disaster.

Google's Data Studio is pretty good as well—shame about the recent rename to "Looker Studio."

> I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze.

I can relate and have experienced this. The result was that they would end up exporting from a series of Tableau/Looker workspaces, and importing into excel and doing their analysis there.

Then continually making enhancement requests on the Tableau/Looker workspaces to get any additional data they need for their excel work.

Then of course, the excel document and its multitude of versions would get emailed around until it hit the attachment size limit, or if there were too many version of it to figure out which one was "actionable".

We finally figured out what they were doing when the enhancement requests for the reporting workspaces got too bizarre.

Insanity.

How do you lock down Excel - that is the issue we have where I work and the main reason excel is strongly discouraged.

There are two main issues my org has battled with:

1.People basically turn excel into their own mini databases which causes a support nightmare.

2. This is strongly related to 1) Data provenance is a nightmare because so many people were passing around excel spreadsheets it became very difficult to track down the source of the figures people were using. User A passed their spreadsheet to user B who modified it passed it to User C who further modified it, passed it to User d from there some figures ended up in front of a manager and now we have a problem...

It's not so much a problem with Power users those are the types of people using python (or R) it's the tier of users below that level that cause the damage with excel.

3rd_quarter_draft_final_krf_v3_edits.xslx

We have all been there.

Are you using Power BI? If it is easy to find and use ground truth data I find that people will start using that. Encourage sharing reports, but make it "official" by sharing them through Power BI.

You're probably asking for something impossible. It's common for the raw data to not fit in excel. You have to do some sort of aggregation or filtering, which means having that app where you aggregate and filter.

Almost all these tools support exporting to Excel or CSV, so they can do whatever they want in Excel after that.

In multiple orgs now I've had raw access to data warehouses (DB2 and SQL Server based) that had aggregates of petabytes of data from ETL that I was free to access via Excel PowerQuery, direct SQL tools, Python or R, or even PowerBI.

If you're getting aggregates, it's not the raw data by definition. Someone set up aggregation for you to use, much like I described.

I didn't mean aggregate as in summarized data, but aggregate as in a common repository of multiple different data sources as a single massive data warehouse. If I needed to group or summarize the data, I had to do it in my own queries.

My point wasn't that you should never give people access to raw data, but that the raw data won't fit in excel rows. You have to do aggregation or filtering somewhere to get the data within the row limit.

> I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze.

I mean, that's like >90% of building a CRUD web app. If you truly only need the R in CRUD, your approach sounds reasonable. Otherwise, just go the extra distance and build a CRUD app with csv export / direct SQL access.

I’m physically uncomfortable with the idea of a CRUD app for a data mart.

Why?

Because then you can’t trust it. The only changes to a data mart should be replicated from authoritative upstream systems.

That is quite a generalization, not every data warehouse / mart / workflow is architected in the exact same way.

I honestly can’t imagine the value of a user-editable data mart. Maybe you can educate me?

Reply on news.ycombinator.com