4 ms·
What I think is missing in term of data analysis in Excel, is a pivot table that can work on a relational dataset in a SQL server, without downloading the whole
by cm2187 10y ago
What I think is missing in term of data analysis in Excel, is a pivot table that can work on a relational dataset in a SQL server, without downloading the whole table as a join as a first step.
Use case: the data is too large to be downloaded and held in memory on a client computer. What you really want to happen is for the aggregation to happen on the SQL server. So that the pivot table would generate SQL code dynamically for the SQL server to do the equivalent of what the pivot table would do with in-memory data, and would only download and display the result.
SQL is useful to developpers, but you can't expect business users to write complex queries.
- grzm 10y agoBasic SQL is pretty easy to pick up, especially if you're motivated to get something done. You can get a lot done knowing how to work simple JOINs, WHERE clauses, and some aggregate functions.
- anakic 10y agoYeah, if you can understand vlookup, you can certainly pick up sql joins fairly easily. You can do quite a lot even with basic SQL skills. Reaching business people from a marketing perspective and talking to them about SQL is a whole different thing, though.
- cm2187 10y agoYeah but I found myself in that exact position. I provided data to a bunch of business users who don't understand SQL but understand pivot tables extensively. I have the choice between either spending a few days educating them on how to do SQL, or to give them data in a way that they can use the tools they are already familiar with. I was too busy to do the former. The other thing is that SQL sucks at creating pivot tables. It is good when you only want to aggregate in one direction. When you want to aggregat in multiple directions (column A by row, column B by row) the syntax becomes tedious to do by hand.
- anakic 10y ago>> a pivot table that can work on a relational dataset in a SQL server, without downloading the whole table as a join as a first step. You can do that with PowerPivot + SQL Server Analysis Services, no? Requires SQL Server and SSAS licenses, though. >> SQL is useful to developers, but you can't expect business users to write complex queries. True. I remember reading/hearing somewhere that it was originally designed to be used by business people as well, but it didn't really turn out that way. I wanted to add that QueryStorm isn't just a pretty IDE:) It's a runtime as well. IT people can use it to prepare interactive reports that they can then share with business people. E.g. you change the value of a cell in Excel, a query is triggered in the background that uses that cell's value as a parameter, and updates an Excel table with the results. The query can use Excel tables and cells as well as outside databases. The user is just interacting with the workbook, without typing any SQL. I also added a QuickQueries dialog that can generate/run some useful queries. You can use it to do stuff without knowing SQL e.g. find outliers (people whose salary is > +2 stdevs for their jobtitle). The idea is to make it useful to non-techy people and to give techy people ideas of what it can do and a starting point for more complex queries.
- njx 10y agoExcel has pivot table feature but not sure if it works with SQL database. For analysts or business users you need just drag and drop feature but it is always good to know what SQL is generated by the actions like you can see that with http://infocaptor.com/ http://infocaptor.com/ http://infocaptor.com/img_assets/finance_data_viz1.gif http://infocaptor.com/img_assets/finance_data_viz1.gif
- cm2187 10y agoIt works with an excel database but it requires you to select a single table through a join, downloads all data to memory, and then does the pivotting in-memory. Impractical for a large dataset.