Hacker News Reader

Comment by tqi | Hacker News Reader

Pivot tables powered by SSAS cubes may be the best self serve analytics tool I've ever seen (where "best" is measured by how much users actually use it). The ability to meet business users where they are is huge for actual adoption, especially compared to something like Looker (which is hot garbage for other reasons as well). Plus, 9 times out of 10 people want to apply additional lightweight transformations/calculations to the data, so nothing beats being in Excel already.

Too bad OSX support is non existent and writing MDX is a pain in the fucking ass.

View on HN

I agree and I do this via Power BI. If you import data into a Power BI report, create a data model with calculated measures (in DAX, not MDX), and publish it to the online service, then users can click on "Analyze in Excel" and it downloads an Excel workbook with a pivot table connected to that data model. I provide this to the PMs for the product I work on and they're able to answer a lot of their questions just by pivoting instead of having to write bespoke SQL.

Oh that's cool - does it work for business users on OSX? I feel like the fall of Thinkpads was the final nail in the coffin. We tried running a dedicated windows machine that people could remote into, but it was just too much friction.

Edit: One tool that looks promising is Equals (equals.com), but I haven't had a chance to play with it directly to see how it compares.

Assuming the company has a Microsoft 365 subscription, Mac users can just use the Excel web client in their browser to access the data via pivot tables.

Where does Power Pivot fit into all this? The word "Pivot" in the name seems to indicate that it has Pivot Table-like functionality.

Power Pivot is the name for the tabular data modeling feature in Excel. It works similarly to Power BI or SQL Server Analysis Services, sharing the underlying database technology. You can load data from queries and files, transform it, add relationships, hierarchies, and calculated measures, and connect pivot tables to it.

I worked at a company that had this (OLAP Cube + Pivot Tables) as one of my first internships and it was mind blowing. I bring this up in terms of amazing analytics tools in the data space somewhat often. Even highly developed BI tools like metabase can't handle dimensions as well as the pivot table.

Can you give an example? I can make metabase pivot tables DANCE (imho).

I used to work on a BI and reporting platform on the Microsoft stack (SSIS + SSAS + SSRS).. Even got decent at writing MDX.

Thing is, you needed beasty servers to get good performance (event with loads of cube modeling optimizations), but that was rarely the case... This was in the physical servers era, mind you, even started on 32 bit Win Server, which choked up pretty fast...

I switched company around when tabular OLAP models started replacing multi-dimensional models, so I never got to understand those.

MS SSAS got replaced with Qlik / Tableau AFAIK.

Back in the mid 2000s, our killer app was an example app from MSDN magazine. It basically embedded Excel in a simple web page and we could use the pivot tables against SSAS cubes. We did a little web work for permissions and to save views, but probably had less than a total week of dev work.

Have you tried Easy Data Transform? It is a lightweight ETL tool for doing data transformations (such as pivot) on Excel, CSV and various other file formats. Runs natively on Windows and Mac.

The ETL part is not really the problem business users seem to have, but rather needing the ability to use pre-built measures / dimensions natively in Excel.

> Looker (which is hot garbage for other reasons as well)

From what I remember, Looker does allow you to create pivot tables from the Explore interface?

You can then also download to csv / excel from a Looker explore.

Something missing for you there?

It does, but everything is translated to raw sql then pushed to the database layer, which means anything with a meaningful amount of data runs like dogshit. I haven't touched MDX in a long time, but my recollection is that OLAP cubes make a lot of these pivot-table type queries a lot more performant.

Also, while it may seem like a minor thing, not being connected to live source introduces a significant amount of friction and room for human error. Adding a new filter or measure = new copy of a file that you need to keep track of, refreshing with a new month of data = a new copy of a file, etc.

Oh yeah. Any manual data update has potential to go wrong.

Especially since in my exp the most common way to do this is to paste the new data over previous sheet in an excel, and hope all the formulas still work.

Kind of fine, but let’s hope there aren’t any new categories that weren’t there last month!

> Kind of fine, but let’s hope there aren’t any new categories that weren’t there last month!

if it does mess up your formulas hopefully it does it in a way that you actually notice!

hyperbole aside, I don't think it's entirely Looker's fault that business users can't seem to get the hang of it, but I think the delta between what users "should use" and "actually use" is large enough that the tool just isn't worth it.

> everything is translated to raw sql then pushed to the database layer

All ROLAP-kind of BI tools do that (including PowerBI when it uses direct-query connection mode), it is expected that underlying data sources are fast enough to handle these aggregate queries very quickly. In fact this approach may be used even with non-OLAP databases (like PostgreSql or SQLServer) and specialized analytical datastore is needed only for really big datasets (BigQuery, Snowflake, ClickHouse etc). In many cases correct usage of report parameters that can filter DB records by indexed columns OR usage of pre-aggregated materialized views, or tuning of SQL query generation (say, avoid JOINs and SQL-calculations when they are not needed for the concrete report) can solve performance issues.

This doesn't mean that Excel's PivotTable (and SSAS cubes) is good and ROLAP-kind pivot tables are bad because their applications are different. In cases when pivot tables should show actual (near real-time) data and this is main purpose of this kind of reports in BI tools; when users need to explore some dataset in a disconnected mode they always may export concrete report's data to Excel - in fact, some BI tools can export their internal pivot table into Excel file with pre-configured PivotTable.

Amen to the MDX pain. I was always trying to get other tools to write it for me when possible.

this makes me happy , I was the PM for that feature :)

Reply on news.ycombinator.com