We want to be able to generate things like pivot tables (charts, graphs, etc) using location and/or time data from the records compared with many other fields in the record. We're feeding a little of the data into Microsoft SSRS, but setting it up is time-consuming and the results are slow and ugly.
I've done this in the past with OLAP cubes (SSAS) with much larger numbers of small records. The difference here is there are so many more fields and we have 20-30 new or modified fields each year. It doesn't seem like this should be a unique problem, but I'm having a hard time finding tools that might make it easier and less time-consuming. Maybe I'm just not asking the right questions.
So are pivot tables in excel. If you show me a tool that can handle csvs (and excel is crap at huge csvs, timelineexlporer is great) and nice graph UI, I am all for it. The number one priority is not missing stuff so there is no way around having to collect all the right data, eliminate known noise and sift through the data to piece together a timeline.
Have you evaluated PivotData Microservice (https://www.nrecosite.com/pivotdata_service.aspx) too? It supports dynamic schemas and beyond just API it comes with sample report builders (for end-users) and built-in pivot tables.
Theres also Tableau (http://www.tableausoftware.com/) for people interested in just pivoting data and charting. Its kind of expensive and PC only but serves that function well.
As a Statistician, I used to use SAS, Stata, R, Excel and of course SQL to extract data but for the purposes of pretty, pretty charts, Tableau is king.
Makes me kind of sad that OLAP cubes are going out of style. Pivot tables are incredibly powerful tools that a layman can use without much difficulty. One of my previous jobs made it pretty easy to get a cube into Excel pivot tables and the amount of statistical analysis that could be done over a few minutes of dragging things around in the pivot table was just staggering.
You mentioned pivot tables, so reporting tools may be more appropriate in your case. Say, you can keep using Google Sheets, and sync the worksheet with BI tool that for various reports. A lot of totally free options here (including SeekTable, why not?..).
Well, another nice thing with pivot tables, in excel at least, is I can just follow the data connection to its home and do whatever I want with that. RBQL in rainbowcsv is my usual.
Thank you for the feedback. I'll do some research along these lines. We have moved a report or two to Power BI which has been must faster than similar reports built using dynamic queries. Keeping the data in sync and the Power BI learning curve have not been easy.
Set up a “SQL-in-charts-out” tool. There’s a number of open source ones now. Apache Superset is one example, thought I don’t necessarily recommend it specifically. This is very low cost to do and it solves this problem exactly.
The next layer of the cake is more complex and costly - setting up a SaaS BI tool like Periscope or Chartio or Looker. This only really becomes useful when you need dynamic pivoting and grouping.
Free online tools to do pivot tables on spreadsheet data. Sounds like small business to me. People who already have their data in spreadsheets and don't want to hire a BI engineer to install SQL, Analysis Services, construct cubes, reports etc etc
I have created a fully functional pivot table designer to demonstrate the power of SQL Frames. Just bring any csv file (or xlsx) and start using drag and drop to create a pivot table. It has support for all the various "Show Values As" calculations.
There is a lot of buzz around NLP based data analysis these days. But I think the good old pivot table UI still feels much faster to analyze data.
How about working with poorly designed schemas? I work with SQL-Server as well, dealing with legacy data designed around imperative t-sql programming. Our 'BI-Solution', SSRS, crawls on pretty simple queries, where 'hacks' need to be done, joining on same table, all kinds of dirty tricks...
I don't know... I honestly feel like 'BI-Solutions' are a poor-persons Python if you are doing anything more than simple dashboards. Something that can be done in 2 lines of code in a Notebook requires endless fiddling in an IDE, to produce something not easily reproducible.
Aside, I've no experience with Tableau or Power-BI, just know that Crystal Reports and SSRS which are pretty painful.
Hi, I'm looking for a pivot table tool that is as easy to use as Excel Pivot tables and generates SQL queries by itself, hiding them from the user.
I have already checked:
1) Tableau has a weird UX for Pivot tables
2) Metabase is better, but also not as good as Excel
3) Power BI is too complicated and requires Microsoft Workspace (?). We use Google Workspace.
4) Apache SuperSet - haven't tested it yet.
There is a need for something that makes it easy to build and display pivot tables on the web. Tableau attempts to fill this, but it is not as easy and does not have as much functionality as Excel.
Try our free BI tool https://www.seektable.com
It has PostgreSql connector; with 'flat table' report type you can easily filter/sort records without need to write SQL, and pivot tables/charts are good for summary reports (under the hood GROUP BY queries are generated)
BTW, SeekTable can connect to ElasticSearch so it may be used with this data source too.
SharePoint 2010 and SQL Server 2008R2 introduced support for hosted Power Pivot workbooks.[0]
Power Pivot is the Excel add-in (available for Excel 2010+) which enables dimensional modelling and >1M row analysis in Excel, and which can be transitioned to a hosted environment via SharePoint and SQL Server.
Starting with SQL Server 2012, SSAS includes Tabular mode, which allows a seamless upgrade path from an Excel Power Pivot model to an SSAS instance.
This is seamless because the same DB engine is used in both products. Power Pivot runs a private, anonymous instance of SSAS Tabular behind the Excel process, with a few features disabled (e.g. row-level security).
Power View is a high level visualization and dashboarding tool introduced for SharePoint 2010 and 2013 as a part of the SQL Server 2012 SSRS module.[1] This is the visualization tool designed to pair with Power Pivot and the Tabular model. It is also available as an add-in for Excel 2013+[2], and as part of the standalone Power BI Desktop[3] which has been available since Power BI portal's general availability in July of 2015.
I do not know what you are referring to by saying that there is a 1M row limit in practice with SP 2013 and SQL Server 2012, because this is simply not the case. If your organization is not using the features and these are pain points for you, I'd love to have further discussion with you offline - my email is gregory.baldini at gmail dot com.
I've done this in the past with OLAP cubes (SSAS) with much larger numbers of small records. The difference here is there are so many more fields and we have 20-30 new or modified fields each year. It doesn't seem like this should be a unique problem, but I'm having a hard time finding tools that might make it easier and less time-consuming. Maybe I'm just not asking the right questions.
reply