Een geheugenefficiënt gegevensmodel maken met Excel en de PowerPivot-invoegtoepassing

Belangrijk: Dit artikel is automatisch vertaald, bekijk de disclaimer. De Engelse versie van dit artikel vindt u hier voor referentiedoeleinden.

In Excel 2013 of hoger gebruikt, kunt u miljoenen rijen met gegevensmodellen maken en vervolgens krachtige gegevensanalyses tegen deze modellen. Gegevensmodellen kunnen worden gemaakt met of zonder de Power Pivot -invoegtoepassing voor de ondersteuning van een willekeurig aantal draaitabellen, grafieken en Power View-visualisaties in dezelfde werkmap.

Opmerking: In dit artikel worden de gegevensmodellen in Excel 2013. Echter de dezelfde gegevens modeling en de Power Pivot-functies in Excel 2013 geïntroduceerd ook van toepassing op Excel-2016. Er is effectief weinig verschil tussen deze versies van Excel.

Hoewel u eenvoudig enorme gegevensmodellen kunt bouwen in Excel, zijn er enkele reden waarom u dit niet moet doen. Ten eerste zijn grote modellen met veel tabellen en kolommen een overkill voor de meeste analysen, waardoor een omslachtige lijst met velden wordt verkregen. Ten tweede verbruiken grote modellen kostbaar geheugen, wat een negatief effect heeft op andere toepassingen en rapporten waarin dezelfde systeembronnen worden gedeeld. Ten slotte mag binnen Office 365 in zowel SharePoint Online als Excel Web App een Excel-bestand niet groter zijn dan 10 MB. Bij werkmapgegevensmodellen met miljoenen rijen zit u vrij snel aan de limiet van 10 MB. Zie Specificatie en limieten van gegevensmodellen.

In dit artikel leert u hoe u een krachtig model bouwt dat eenvoudiger in gebruik is en minder geheugen verbruikt. Als u de tijd neemt om aanbevolen procedures voor efficiënt modelontwerp te leren, werpt dit later zijn vruchten af voor elk model dat u maakt en gebruikt, ongeacht of u dit weergeeft in Excel 2013, in Office 365 SharePoint Online, op een Office Web Apps-server of in SharePoint 2013.

Houd rekening met de werkmap Size Optimizer ook worden uitgevoerd. Er worden geanalyseerd uw Excel-werkmap en worden deze indien mogelijk verder gecomprimeerd. Download de werkmap Size Optimizer.

In dit artikel

Compressieratio’s en de analyse-engine in het geheugen

Niets verbruikt minder geheugen dan een niet-bestaande kolom

Twee voorbeelden van kolommen die u beter altijd kunt uitsluiten

Onnodige kolommen uitsluiten

En wat als alleen de benodigde rijen worden gefilterd?

Wat als we de kolom nodig hebben? Kunnen we dan toch nog de benodigde ruimte beperken?

Datum-/tijdkolommen wijzigen

De SQL-query wijzigen

Met DAX berekende maateenheden in plaats van kolommen

Welke twee kolommen kunt u het beste behouden?

Conclusie

Verwante koppelingen

Compressieratio’s en de analyse-engine in het geheugen

Voor gegevensmodellen in Excel wordt de analyse-engine in het geheugen gebruikt om gegevens in het geheugen op te slaan. Met de engine worden krachtige compressietechnieken geïmplementeerd om opslagvereisten te verminderen, waardoor een resultatenset wordt verkleind tot een fractie van de oorspronkelijke grootte.

Gemiddeld kunt u verwachten dat een gegevensmodel zeven tot tien keer zo klein is als dezelfde gegevens op het punt van herkomst. Als u bijvoorbeeld 7 MB aan gegevens importeert uit een SQL Server-database, kan het gegevensmodel in Excel gemakkelijk 1 MB of kleiner zijn. De werkelijk gerealiseerde mate van compressie hangt hoofdzakelijk af van het aantal unieke waarden in elke kolom. Hoe meer unieke waarden er zijn, des te meer geheugen nodig is om deze op te slaan.

Waarom hebben we het over compressie en unieke waarden? Omdat het allemaal draait rond maximale compressie bij het bouwen van een efficiënt model dat zo weinig mogelijk geheugen verbruikt. Dit kunt u het eenvoudigst realiseren door kolommen te verwijderen die u niet echt nodig hebt, met name als deze kolommen veel unieke waarden bevatten.

Opmerking: De verschillen in opslagvereisten voor individuele kolommen kunnen enorm zijn. In sommige gevallen is het beter om meerdere kolommen met weinig unieke waarden te hebben dan één kolom met veel unieke waarden. In de sectie over optimalisaties voor datum/tijd wordt gedetailleerd ingegaan op deze techniek.

Niets verbruikt minder geheugen dan een niet-bestaande kolom

De meest geheugenefficiënte kolom is de kolom die u in de eerste plaats nooit hebt geïmporteerd. Wilt u een efficiënt model bouwen? Bekijk dan elke kolom en vraag u af of deze een bijdrage levert aan de analyse die u wilt uitvoeren. Laat de kolom weg als dit niet het geval is of als u het niet zeker weet. U kunt altijd naderhand nieuwe kolommen toevoegen als u deze nodig hebt.

Twee voorbeelden van kolommen die u beter altijd kunt uitsluiten

Het eerste voorbeeld houdt verband met gegevens die afkomstig zijn uit een datawarehouse. In een datawarehouse komen vaak artefacten van ETL-processen voor waarmee gegevens in de warehouse worden geladen en vernieuwd. Kolommen zoals 'datum maken', 'datum bijwerken' en 'ETL-uitvoering' worden gemaakt wanneer de gegevens worden geladen. Geen van deze kolommen is nodig in het model en kan het beste worden uitgeschakeld wanneer u gegevens importeert.

Het tweede voorbeeld heeft betrekking op het weglaten van de primaire-sleutelkolom wanneer u een feitentabel importeert.

Veel tabellen, waaronder feitentabellen, bevatten primaire sleutels. Voor de meeste tabellen, zoals tabellen met klant-, personeels- of verkoopgegevens, zult u relaties in het model willen maken met behulp van de primaire sleutel van de tabel.

Feitentabellen zijn anders. In een feitentabel wordt de primaire sleutel gebruikt om elke rij uniek aan te duiden. Hoewel een dergelijke tabel nodig is voor normalisatiedoeleinden, is deze minder nuttig in een gegevensmodel waarin u alleen kolommen wilt die worden gebruikt om analysen uit te voeren of tabelrelaties tot stand te brengen. Neem daarom niet de primaire sleutel op wanneer u gegevens importeert uit een feitentabel. Primaire sleutels in een feitentabel verbruiken enorm veel ruimte in het model terwijl ze geen voordeel bieden, want ze kunnen niet worden gebruikt om relaties te maken.

Opmerking: In datawarehouses en multidimensionele databases worden grote tabellen met vooral numerieke gegevens vaak 'feitentabellen' genoemd. Feitentabellen bevatten meestal bedrijfsprestaties of transactiegegevens, zoals verkoop- en kostengegevenspunten, die worden samengevoegd en afgestemd op organisatie-eenheden, producten, marktsegmenten, geografische regio's en dergelijke. Alle kolommen in een feitentabel die bedrijfsgegevens bevatten of die kunnen worden gebruikt om kruislings te verwijzen naar gegevens in andere tabellen, kunnen het beste worden opgenomen in het model om gegevensanalysen te ondersteunen. De kolom die u wilt uitsluiten, is de primaire-sleutelkolom van de feitentabel. Deze bestaat uit unieke waarden die alleen voorkomen in de feitentabel en nergens anders. Omdat feitentabellen zo groot zijn, wordt de modelefficiëntie met name verbeterd door rijen en kolomen van feitentabellen uit te sluiten.

Onnodige kolommen uitsluiten

Efficiënte modellen bevatten alleen kolommen die u werkelijk nodig hebt in uw werkmap. Als u wilt regelen welke kolommen worden opgenomen in het model, moet u de gegevens importeren met de wizard Tabel importeren in de Power Pivot-invoegtoepassing in plaats van het dialoogvenster Gegevens importeren in Excel.

Wanneer u de wizard Tabel importeren start, selecteert u welke tabellen u wilt importeren.

Wizard Tabel importeren in PowerPivot-invoegtoepassing

Voor elke tabel kunt u op de knop Voorbeeld en filteren klikken en de delen van de tabel selecteren die u echt nodig hebt. U kunt het beste eerst alle kolommen uitschakelen en daarna de gewenste kolommen controleren nadat u hebt overwogen of ze nodig zijn voor de analyse.

Voorbeeldvenster van de wizard Tabel importeren

En wat als alleen de benodigde rijen worden gefilterd?

Veel tabellen in bedrijfsdatabases en datawarehouses bevatten historische gegevens die gedurende lange tijd zijn opgebouwd. Bovendien kunnen de tabellen waarin u geïnteresseerd bent, gegevens bevatten voor gebieden van het bedrijf die niet nodig zijn voor uw specifieke analyse.

Met de tabel Wizard importeren kunt u historische of ongerelateerde gegevens eruitfilteren en zo veel ruimte in het model besparen. In de volgende afbeelding wordt een datumfilter gebruikt om alleen rijen met gegevens voor het huidige jaar op te halen, behalve historische gegevens die u niet nodig hebt.

Filterdeelvenster van de wizard Tabel importeren

Wat als we de kolom nodig hebben? Kunnen we dan toch nog de benodigde ruimte beperken?

U kunt enkele aanvullende technieken toepassen zodat een kolom beter kan worden gecomprimeerd. Onthoud dat het aantal unieke waarden de enige eigenschap is die van invloed is op compressie. In deze sectie leert u hoe sommige kolommen kunnen worden gewijzigd om het aantal unieke waarden te verminderen.

Datum-/tijdkolommen wijzigen

In veel gevallen kunnen datum-/tijdkolommen veel ruimte innemen. U kunt de opslagvereisten voor dit gegevenstype op een aantal manieren verminderen. De technieken variëren afhankelijk van hoe u de kolom gebruikt en hoe ervaren u bent met het bouwen van SQL-query's.

Datum-/tijdkolommen bevatten een datumgedeelte en een tijd. Stel uzelf meerdere keren dezelfde vraag voor een datum-/tijdkolom als u zich afvraagt of u deze nodig hebt:

  • Heb ik het tijdgedeelte nodig?

  • Heb ik het tijdgedeelte nodig op het niveau van uren? Minuten? Seconden? Milliseconden?

  • Heb ik meerdere datum-/tijdkolommen omdat ik het verschil hiertussen wil berekenen of omdat ik de gegevens per jaar, maand, kwartaal en dergelijke wil optellen? Enzovoort.

Uw antwoord op elk van deze vragen is bepalend voor de opties waarmee u de datum-/tijdkolom kunt beheren.

Voor al deze oplossingen moet een SQL-query worden gewijzigd. Als u query's eenvoudiger wilt kunnen wijzigen, moet u ministens één kolom in elke tabel eruitfilteren. Door een kolom eruit te filteren wijzigt u elke constructie van een afgekorte indeling (SELECT *) in een instructie SELECT die de volledig gekwalificeerde kolomnaam omvat. Deze kunnen veel eenvoudiger worden gewijzigd.

Laten we eens kijken naar de query's die voor u worden gemaakt. In het dialoogvenster Tabeleigenschappen kunt u overschakelen naar de queryeditor en de huidige SQL-query voor elke tabel bekijken.

lint in het powerpivot-venster met de opdracht tabeleigenschappen

Selecteer in Tabeleigenschappen de optie Queryeditor.

Queryeditor openen vanuit het dialoogvenster Tabeleigenschappen

In de queryeditor wordt de SQL-query weergegeven waarmee de tabel wordt ingevoerd. Als u een kolom tijdens de import eruit hebt gefilterd, omvat uw query volledig gekwalificeerde kolomnamen:

sql-query waarmee de gegevens worden opgehaald

Als u daarentegen een tabel volledig hebt geïmporteerd zonder een kolom uit te schakelen of een filter toe te passen, ziet u de query als '* selecteren vanaf'. Deze is lastiger te wijzigen:

sql-query waarin de kortere standaardsyntaxis wordt gebruikt

De SQL-query wijzigen

Nu u weet hoe u de query kunt zoeken, kunt u deze verder wijzigen om de grootte van uw model verder te beperken.

  1. Gebruik de volgende syntaxis om de decimalen te verwijderen als u deze niet nodig hebt in kolommen met valuta- of decimaalgegevens:

    "SELECT ROUND([kolomnaam_met_decimalen],0)… ."

    Vervang de 0 door 2 als u de centen nodig hebt, maar geen fracties van centen. Als u negatieve getallen gebruikt, kunt u afronden op eenheden, tientallen, honderdtallen, enzovoort.

  2. Gebruik de volgende syntaxis om de tijd te verwijderen als u een datum-/tijdkolom met de naam dbo.grotetabel.[Datum Tijd] hebt en het tijdgedeelte niet nodig hebt:

    “SELECT CAST (dbo.grotetabel.[Datum Tijd] as date) AS [Datum Tijd]) “

  3. Gebruik meerdere kolommen in de SQL-query in plaats van één datum-/tijdkolom als u een datum-/tijdkolom met de naam dbo.grotetabel.[Datum tijd] hebt en zowel het datum- als het tijdgedeelte nodig hebt:

    “SELECT CAST (dbo.grotetabel.[Datum Tijd] as date) AS [Datum Tijd],

    datepart(hh, dbo.grotetabel.[Datum Tijd]) as [Datum Tijd Uren],

    datepart(mi, dbo.grotetabel.[Datum Tijd]) as [Datum Tijd Minuten],

    datepart(ss, dbo.grotetabel.[Datum Tijd]) as [Datum Tijd Seconden],

    datepart(ms, dbo.grotetabel.[Datum Tijd]) as [Datum Tijd Milliseconden]"

    Gebruik zoveel kolommen als nodig om elk deel in een aparte kolom op te slaan.

  4. Als u uren en minuten nodig hebt en deze liever samenvoegt in één tijdkolom, kunt u de volgende syntaxis gebruiken:

    Timefromparts(datepart(hh, dbo.grotetabel.[Datum Tijd]), datepart(mm, dbo.grotetabel.[Datum Tijd])) as [Datum Tijd Uren Minuut]

  5. Als u twee datum-/tijdkolommen hebt, zoals [Begintijd] en [Eindtijd], en u alleen het tijdverschil hiertussen in seconden nodig hebt als kolom met de naam [Duur], verwijdert u beide uit de lijst en voegt u het volgende toe:

    "datediff(ss,[Begindatum],[Einddatum]) as [Duur]"

    Als u het trefwoord ms in plaats van ss gebruikt, verkrijgt u de duur in milliseconden.

Met DAX berekende maateenheden in plaats van kolommen

Als u eerder hebt gewerkt met een taal voor DAX-expressies, weet u mogelijk al dat berekende kolommen worden gebruikt om nieuwe kolommen af te leiden van een andere kolom in het model, maar alleen worden geëvalueerd wanneer ze in een draaitabel of ander rapport worden gebruikt.

U kunt onder andere geheugen besparen door normale of berekende kolommen te vervangen door berekende maateenheden. Het klassieke voorbeeld is Eenheidsprijs, Hoeveelheid en Totaal. Als u alle drie hebt, kunt u ruimte besparen door er slechts twee te handhaven en de derde te berekenen met DAX.

Welke twee kolommen kunt u het beste behouden?

Behoud in het bovenstaande voorbeeld Hoeveelheid en Eenheidsprijs. Deze twee bevatten minder waarden dan Totaal. Als u Totaal wilt berekenen, voegt u een berekende maateenheid toe zoals:

“TotaleVerkoop:=sumx('Verkooptabel','Verkooptabel'[Eenheidsprijs]*'Verkooptabel'[Hoeveelheid])”

Berekende kolommen nemen net zoals normale kolommen ruimte in het model in beslag. Berekende maateenheden worden echter berekend zodra ze zich voordoen en nemen geen ruimte in beslag.

Conclusie

In dit artikel hebben we verschillende benaderingen besproken waarmee u een geheugenefficiënter model kunt opbouwen. Als u de bestandsgrootte en geheugenvereisten van een gegevensmodel wilt beperken, vermindert u het totale aantal kolommen en rijen, en het aantal unieke waarden dat in elke kolom wordt weergegeven. Onder andere de volgende technieken zijn aan de orde gekomen:

  • Natuurlijk kunt u het beste ruimte besparen door kolommen te verwijderen. Bepaal welke kolommen u echt nodig hebt.

  • Soms kunt u een kolom verwijderen en vervangen door een berekende maateenheid in de tabel.

  • Mogelijk hebt u niet alle rijen in een tabel nodig. U kunt rijen eruitfilteren in de wizard Tabel importeren.

  • In het algemeen is het een goed idee om één kolom te verdelen in meerdere aparte delen als u het aantal unieke waarden in een kolom wilt verminderen. Elk deel bevat een klein aantal unieke waarden, en het gecombineerde totaal is kleiner dan de originele samengevoegde kolom.

  • In veel gevallen hebt u ook de verschillende delen nodig als slicer in uw rapporten. Zo nodig kunt u hiërarchieën maken van delen als Uren, Minuten en Seconden.

  • Vaak bevatten kolommen meer gegevens dan nodig. Stel dat in een kolom decimalen worden opgeslagen, terwijl u opmaak hebt toegepast om alle decimalen te verbergen. Afronding kan dan erg effectief zijn om een numerieke kolom te verkleinen.

Nu u hebt gedaan wat kunt u om de grootte van uw werkmap te beperken, kunt u de werkmap Size Optimizer ook worden uitgevoerd. Er worden geanalyseerd uw Excel-werkmap en worden deze indien mogelijk verder gecomprimeerd. Download de werkmap Size Optimizer.

Verwante koppelingen

Specificaties en beperkingen van gegevensmodellen

Werkmap Size Optimizer downloaden

PowerPivot: Krachtige gegevensanalyse en gegevensmodellering in Excel

Opmerking: Disclaimer voor automatische vertaling: Dit artikel is vertaald door een computersysteem zonder menselijke tussenkomst. Microsoft biedt deze automatische vertalingen aan om niet-Engels sprekende gebruikers te helpen de inhoud over producten, services en technologieën van Microsoft te raadplegen. Omdat het artikel automatisch is vertaald, bevat het mogelijk fouten in grammatica, woordenschat en syntaxis.

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×