Live PivotTable in Excel naar MySQL

[ps88]

Ik wil graag vanuit Excel een pivottable kunnen maken op een MySQL-database. Deze koppeling moet 'live' zijn. Dat wil zeggen dat de query die nodig is, pas wordt uitgevoerd als je een selectie hebt gemaakt in de pivottable.

Tools als MySQL for Excel of een koppeling via ODBC heb ik al geprobeerd. Deze werken, maar vereisen dat eerst de volledige achterliggende dataset wordt gedownload naar Excel. Dat is vaak niet nodig en kost veel tijd.

Ik maak gebruik van Excel 2016 (lokaal), MySQL 5.7 (op server, verbinding via SSH),

Is er een goede plugin/methode om dit voor elkaar te krijgen?

[DJMaze]

ps88 schreef op dinsdag 7 november 2017 @ 09:19:
Ik wil graag vanuit Excel een pivottable kunnen maken op een MySQL-database.

Ik wil graag dat men stopt met het gebruik van Excel

[MAX3400]

Als je MySQL hebt draaien, is het vast niet onwaarschijnlijk dat er ook een PHP-framework bij draait (of simpel ernaast gezet) kan worden.

Met een (scheduled) stored procedure o.i.d. kan je dan een pivot "intern" querien en weergeven.

Excel is hier imho niet de beste oplossing voor. Zeker niet als je database "binnenkort" groeit naar, pak 'm beet, 80GB.

[ Voor 11% gewijzigd door MAX3400 op 07-11-2017 11:38 ]

[ps88]

Ha, helemaal eens dat Excel niet de meest optimale tool is . De situatie is echter gelukkig wel zo dat de MySQL-database altijd leidend is en wordt beheerd door mij en collega's die wel van query's schrijven houden.

Er zijn echter ook een flink aantal collega's die regelmatig een inzicht uit deze database willen halen (bijvoorbeeld: hoeveel van product X in jaar Y bij klant Z), maar geen verstand hebben van MySQL. De mogelijkheden aan doorsneden die je zou kunnen maken zijn eindeloos, dus alles in rapporten voorrekenen werkt niet. Excel en pivot's kennen al mijn collega dan weer wel...

Voor bijvoorbeeld SAP zijn er plugins die een 'live connectie' naar de database maken en alleen de relevantie informatie voor de pivottabel ophalen. Ik zoek daarvan een alternatief dat kan praten met MySQL.

[Mud]

Dit soort rapportages moet je eigenlijk via een BI tool (bijvoorbeeld Qlik Sense) beschikbaar stellen

https://www.qlik.com/nl-nl/products/qlik-sense

[Vaan Banaan]

Misschien dat Power Query for Excel add-on iets is?
https://support.office.co...79-4E1F-BAD5-89F6269CD605

Voor MySQL heb je nog wel een connector nodig: https://support.office.co...b9-409d-b312-6ea8f84a269b
En de SSH tunnel zul je helaas ook zelf moeten opzetten. (Ik gebruik Putty Link)

[ Voor 41% gewijzigd door Vaan Banaan op 08-11-2017 14:16 ]

[DHH]

Als alternatief zou je nog kunnen kijken naar Power BI van Microsoft. Kost je wellicht eenmalig tijd om een aantal veelgebruikte tabellen in te lezen, maar daarna kunnen gebruikers wel vrij eenvoudig zelf aan de slag om gegevens gevisualiseerd te krijgen. Dit kan op een visueel zeer aantrekkelijke manier worden gepresenteerd (net als Qlik), met goede app- en browser-ondersteuning.

Korte (gratis) cursus voor het gebruik van Power BI is hier te vinden.

(ik heb geen aandelen )

[Reinier]

Mijn stem gaat naar http://xlcubed.com

[DJMaze]

ps88 schreef op dinsdag 7 november 2017 @ 12:34:
Er zijn echter ook een flink aantal collega's die regelmatig een inzicht uit deze database willen halen (bijvoorbeeld: hoeveel van product X in jaar Y bij klant Z), maar geen verstand hebben van MySQL. De mogelijkheden aan doorsneden die je zou kunnen maken zijn eindeloos, dus alles in rapporten voorrekenen werkt niet.

Dat hoeft ook helemaal niet, je sleutelwoord is filters.

Ik ontwikkel software in C++ met een database.
Daarin kan de gebruiker bijvoorbeeld zeggen:

A. Toon alle "omzet":
+ datum tussen X en Y
+ "productgroepen" (INNER JOIN productgroepen USING grooup_id)
+ "klantnaam" (INNER JOIN customers USING customer_id)

Maar dit werkt natuurlijk alleen als je tabel AUTO_INCREMENT/SERIAL kolommen een prefix hebben ipv "id".
Zoiets als:

SQL:

CREATE TABLE turnover (
    turnover_id SERIAL,
    turnover_date DATETIME
    product_id INT,
);

CREATE TABLE products (
    product_id SERIAL,
    product_name VARCHAR
);

De INNER JOIN table USING *_id kan je dan zelf makkelijk dynamisch opzetten door de information_schema af te struinen voor de AUTO_INCREMENT kolom.
Daarnaast kan je ook dynamisch koppel tabellen raadplegen.

Zorg gewoon voor een juiste DB structuur

[ps88]

Dank voor alle tips en reacties, erg leerzaam.
Een collega wees mij onlangs ook op de mogelijkheid om een OLAP-tool te installeren op de server.

Alle data die we willen gebruiken staat al in een ster-model met bijbehorende foreign keys in MySQL. Wie heeft er goede evaringen met een OLAP-tool die kan praten met MySQL en Excel?

[MSteverink]

Op welk server-OS moet die OLAP-server gaan draaien?

Overigens ga je dit

Deze koppeling moet 'live' zijn. Dat wil zeggen dat de query die nodig is, pas wordt uitgevoerd als je een selectie hebt gemaakt in de pivottable.

niet voor elkaar krijgen in een OLAP-omgeving. Althans, niet in een OLAP-omgeving die ik ken.

[ Voor 73% gewijzigd door MSteverink op 14-11-2017 17:15 ]

[Reinier]

@ps88 ik heb wel eens met Mondrian geklungeld en dat was wel aardig. Tegenwoordig praat het ook met Excel dacht ik.
Verder zoals gezegd XLCubed.

[ps88]

MSteverink schreef op dinsdag 14 november 2017 @ 13:10:
Op welk server-OS moet die OLAP-server gaan draaien?

Overigens ga je dit

[...]

niet voor elkaar krijgen in een OLAP-omgeving. Althans, niet in een OLAP-omgeving die ik ken.

De server draait op CentOS i.c.m. MySQL Community Edition.

‘Live’ hoeft ook niet per se, ik bedoelde meer dat ik niet wil dat Excel eerst de hele dataset hoeft te downloaden maar enkel het resultaat.

[Hooglander1]

PowerQuery of PowerPivot moet dit wel kunnen. Al ben ik niet geheel goed op de hoogte van de non-enterprise kant van PowerBI.

Je zoekt eigenlijk gewoon een BI ad-hoc tool. Daar zijn er een heleboel van, maar met Semi-Native Excel tools moet je echt een eind kunnen komen.

[ps88]

Uiteindelijk hebben we verschillende tools geprobeerd (waaronder bijv. XLCubed, PowerQuery/Pivot); we gaan icCube (https://www.iccube.com/) voor wat langere tijd uitproberen, dank voor al jullie suggesties!

[superduper]

Je hebt al een mogelijke oplossing, maar ik wil toch ook iets anders aandragen. Wij hebben soort gelijke vragen kunnen adresseren met Rshiny. Die kan mysql vanuit R querien en weer geven via een semi-interactieve webapp. Mogelijkheden haalt rshiny zelf uit de data en dan is het heel gemakkelijk om zaken tegen elkaar uit te zetten.