Formules herberekenen in Power Pivot

Wanneer u met gegevens werkt in Power Pivot, moet u mogelijk gegevens uit de bron vernieuwen, formules in berekende kolommen herberekenen of draaitabellen bijwerken die gebruikmaken van het gegevensmodel.

In dit artikel wordt het verschil uitgelegd tussen gegevens vernieuwen en herberekenen, wordt overzichtelijk beschreven hoe herberekening wordt geactiveerd en worden de opties beschreven voor het beheren van herberekening.

Gegevensvernieuwing en herberekening

Power Pivot gebruikt zowel gegevens vernieuwen als herberekenen:

Gegevens vernieuwen betekent actuele gegevens ophalen uit externe gegevensbronnen. In Power Pivot worden wijzigingen in externe gegevensbronnen niet automatisch gedetecteerd. Als u bijgewerkte gegevens wilt aanvoeren, vernieuwt u het gegevensmodel.

Herberekenen betekent het bijwerken van alle kolommen, tabellen, grafieken en draaitabellen in uw werkmap die formules bevatten. Aangezien het herberekenen van een formule van invloed is op de prestaties, is het belangrijk te weten hoe de afhankelijkheden binnen elke berekening in elkaar zitten.

Handmatige versus automatische herberekening

Standaard worden in Power Pivot automatisch herberekeningen uitgevoerd als dat nodig is en wordt tegelijkertijd de verwerkingstijd geoptimaliseerd. Hoewel herberekening lang kan duren, is het een belangrijke taak. Tijdens de herberekening controleert Power Pivot kolomafhankelijkheden en ontvangt u een melding als een kolom is gewijzigd, als de gegevens ongeldig zijn of als er een fout is opgetreden in een formule die tot dan toe werkte. U kunt er echter voor kiezen om geen validatie uit te voeren en de berekeningen alleen handmatig bij te werken, met name wanneer u met complexe formules of zeer grote gegevenssets werkt en de timing van de updates wilt bepalen.

Zowel handmatige als automatische modi hebben voordelen. Gebruik echter de modus voor automatische herberekening. In deze modus blijven de Power Pivot-metagegevens gesynchroniseerd en worden problemen voorkomen die worden veroorzaakt door het verwijderen van gegevens, wijzigingen in namen of gegevenstypen, of ontbrekende afhankelijkheden. 

Automatische herberekening gebruiken

Wanneer u de modus voor automatische herberekening gebruikt, leiden wijzigingen in gegevens die een wijziging in de uitkomst van een formule kunnen veroorzaken, tot herberekening van de gehele kolom die een formule bevat. Voor de volgende wijzigingen is altijd een herberekening van de formules vereist:

• Waarden van een externe gegevensbron worden vernieuwd.
• De definitie van de formule is gewijzigd.
• De namen van tabellen of kolommen waarnaar in een formule wordt verwezen, worden gewijzigd.
• Relaties tussen tabellen worden toegevoegd, gewijzigd of verwijderd.
• Er worden nieuwe metingen of berekende kolommen toegevoegd.
• Wijzigingen worden aangebracht in andere formules in de werkmap, zodat kolommen of berekeningen die van die berekening afhankelijk zijn, moeten worden vernieuwd.
• Rijen worden ingevoegd of verwijderd.
• U hebt een filter toegepast waarvoor een query moet worden uitgevoerd om de gegevensset bij te werken. Het filter kan in een formule of als onderdeel van een draaitabel of draaigrafiek worden toegepast.

Handmatige herberekening gebruiken

Gebruik handmatige herberekening om de kosten van de berekening van het formuleresultaat te voorkomen totdat u klaar bent. De handmatige modus komt in de volgende situaties van pas:

• U ontwerpt een formule op basis van een sjabloon en wilt de namen van de kolommen en tabellen die in de formule worden gebruikt, wijzigen voordat u deze valideert.
• U weet dat sommige gegevens in de werkmap zijn gewijzigd, maar u werkt met een andere kolom die niet is gewijzigd, dus wilt u de herberekening uitstellen.
• U werkt in een werkmap die veel afhankelijkheden heeft en wilt de herberekening uitstellen totdat u er zeker van bent dat alle benodigde wijzigingen zijn aangebracht.

Als u de werkmap instelt op de handmatige berekeningsmodus, worden formules niet door Power Pivot in Excel gevalideerd of gecontroleerd. De volgende resultaten treden op:

• Alle nieuwe formules die u aan de werkmap toevoegt, worden gemarkeerd met een aanduiding dat er een fout is.
• In nieuwe berekende kolommen worden geen resultaten weergegeven.

De werkmap instellen op handmatig herberekenen

1. Selecteer in Power Pivot de optie Ontwerpberekeningen>>Berekeningsopties>Handmatige berekeningsmodus.
2. Als u alle tabellen opnieuw wilt berekenen, selecteert u Berekeningsopties>Nu berekenen.
   De werkmap controleert formules op fouten en werkt tabellen bij met resultaten. De werkmap reageert mogelijk tijdelijk niet meer als gevolg van de hoeveelheid gegevens en het aantal berekeningen.

Problemen met herberekening oplossen

Afhankelijkheden

Wanneer een kolom afhankelijk is van een andere kolom en de inhoud van die andere kolom wordt gewijzigd, moet u mogelijk alle gerelateerde kolommen opnieuw berekenen. Wanneer u wijzigingen aanbrengt in de Power Pivot-werkmap, analyseert Power Pivot in Excel de bestaande Power Pivot-gegevens om te bepalen of herberekening is vereist en wordt de update op de meest efficiënte manier uitgevoerd.

Stel dat u een tabel genaamd Verkopen hebt en dat deze gerelateerd is aan de tabellen Product en Productcategorie, en formules in de tabel Verkopen zijn beide van de andere tabellen afhankelijk. Elke wijziging in de tabel Product of Productcategorie heeft tot gevolg dat alle berekende kolommen in de tabel Verkoop worden herberekend. Dit heeft zin als u bedenkt dat u mogelijk formules hebt waarmee de verkopen per categorie of per product worden verzameld. Om ervoor te zorgen dat de resultaten juist zijn, moet u de formules opnieuw berekenen op basis van de gegevens.

Met Power Pivot wordt altijd een volledige herberekening van een tabel uitgevoerd, omdat een volledige herberekening efficiënter is dan controleren op gewijzigde waarden. De wijzigingen die herberekening activeren, kunnen belangrijke wijzigingen zijn, zoals het verwijderen van een kolom, het wijzigen van het numerieke gegevenstype van een kolom of het toevoegen van een nieuwe kolom. Schijnbaar onbelangrijke wijzigingen, zoals de het wijzigen van de kolomnaam, kunnen echter ook herberekening activeren. Dit probleem treedt op omdat de namen van kolommen als id's in formules worden gebruikt.

In sommige gevallen wordt in Power Pivot bepaald dat kolommen kunnen worden uitgesloten van herberekening. Als u bijvoorbeeld een formule hebt waarmee een waarde zoals [Productkleur] in de tabel Producten wordt opgezocht en de kolom die u hebt gewijzigd [ Hoeveelheid] in de tabel Verkoop is, hoeft de formule niet te worden herberekend, ook al zijn de tabellen Verkopen en Producten gerelateerd. Als u echter formules hebt die afhankelijk zijn van Sales[Quantity], is herberekening wel vereist.

Volgorde van herberekening voor afhankelijke kolommen

In Power Pivot worden afhankelijkheden berekend voordat kolommen worden herberekend. Als meerdere kolommen van elkaar afhankelijk zijn, volgt Power Pivot de volgorde van de afhankelijkheden. Deze opeenvolging zorgt ervoor dat de kolommen in de juiste volgorde en met de maximale snelheid worden verwerkt.

Transacties

Bewerkingen waarmee gegevens worden herberekend of vernieuwd, worden uitgevoerd als een transactie. Als een deel van de vernieuwingsbewerking mislukt, worden de resterende bewerkingen ongedaan gemaakt. Dit gedrag zorgt ervoor dat gegevens niet in een gedeeltelijk verwerkte staat achterblijven. U kunt de transacties niet zoals in een relationele database beheren en u kunt geen controlepunten maken.

Herberekening van veranderlijke functies

Sommige functies, zoals NOW, RAND, of TODAY, hebben geen vaste waarden. Om prestatieproblemen te voorkomen, wordt door het uitvoeren van een query of filteren deze functies meestal niet opnieuw geëvalueerd door Power Pivot als u deze in een berekende kolom gebruikt. Power Pivot berekent de resultaten van deze functies alleen opnieuw wanneer de hele kolom wordt herberekend. Situaties als het vernieuwen van een externe gegevensbron of het handmatig bewerken van gegevens zorgen ervoor dat formules die deze functies bevatten, opnieuw worden geëvalueerd. Als u echter vluchtige functies, zoals NOW, RANDof TODAY in de definitie van een berekend veld gebruikt, worden deze altijd opnieuw berekend.