5 ms·

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

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

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