7 ms·

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 ab

by tqi 3y ago

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.

doppenhe 3y ago
this makes me happy , I was the PM for that feature :)
davio 3y ago
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.
hermitcrab 3y ago
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.
- tqi 3y ago
  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.
SOLAR_FIELDS 3y ago
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.
- Jgrubb 3y ago
  Can you give an example? I can make metabase pivot tables DANCE (imho).
- deleted 3y ago
  [deleted]
- namtab00 3y ago
  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.
kyllo 3y ago
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.
- tqi 3y ago
  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.
  - kyllo 3y ago
    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.
- breadwinner 3y ago
  Where does Power Pivot fit into all this? The word "Pivot" in the name seems to indicate that it has Pivot Table-like functionality.
  - kyllo 3y ago
    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.
archiewood 3y ago
> 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?
- tqi 3y ago
  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.
  - archiewood 3y ago
    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!
    - tqi 3y ago
      > 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.
  - deleted 3y ago
    [deleted]
  - seektable 3y ago
Vaslo 3y ago
Amen to the MDX pain. I was always trying to get other tools to write it for me when possible.

by tqi 3y ago

doppenhe 3y ago

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

davio 3y ago

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.

hermitcrab 3y ago

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.

tqi 3y ago
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.

SOLAR_FIELDS 3y ago

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.

Jgrubb 3y ago
Can you give an example? I can make metabase pivot tables DANCE (imho).
deleted 3y ago
[deleted]
namtab00 3y ago
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.

kyllo 3y ago

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.

tqi 3y ago
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.
- kyllo 3y ago
  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.
breadwinner 3y ago
Where does Power Pivot fit into all this? The word "Pivot" in the name seems to indicate that it has Pivot Table-like functionality.
- kyllo 3y ago
  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.

archiewood 3y ago

> 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?

tqi 3y ago
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.
- archiewood 3y ago
  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!
  - tqi 3y ago
    > 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.
- deleted 3y ago
  [deleted]
- seektable 3y ago

Vaslo 3y ago

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