Muistia säästävän tieto mallin luominen Excelin ja Power Pivot-apuohjelman avulla

Excel 2013 tai sitä uudemmissa versioissa voit luoda tieto malleja, jotka sisältävät miljoonia rivejä, ja suorittaa tehokkaita tietojen analyyseja näitä malleja vasten. Tieto malleja voi luoda Power Pivot-apuohjelman kanssa tai ilman sitä, jotta voidaan tukea mitä tahansa saman työkirjan pivot-taulu koiden, kaavioiden ja Power View-visualisointien määrää.

Huomautus: Tässä artikkelissa kuvataan Excel 2013-tieto mallit. Samat tieto mallinnuksen ja Power Pivotin ominaisuudet, jotka on otettu käyttöön Excel 2013-sovelluksessa, koskevat myös Excel 2016-tietoja. Näiden Excel-versioiden välillä on käytännössä vain vähän eroja.

Vaikka voit helposti luoda valtavia tieto malleja Excelissä, syitä on useita. Ensinnäkin suuret mallit, jotka sisältävät useita taulu koita ja sarakkeita, ovat ylimitoittaisia useimmille analyyseille ja tekevät siitä vaivalloisen kenttä luettelon. Toiseksi suuret mallit käyttävät arvokasta muistia, joka vaikuttaa kielteisesti muihin sovelluksiin ja raportteihin, jotka jakavat samat järjestelmä resurssit. Microsoft 365 lisäksi sekä SharePoint Online että Excel Web App rajoittavat Excel-tiedoston kokoa 10 Mega tavuun. Jos työkirja tieto malleissa on miljoonia rivejä, voit suorittaa 10 Mt:N rajan melko nopeasti. Katso tieto mallin määritykset ja rajoitukset.

Tässä artikkelissa kerrotaan, miten voit luoda tiiviisti rakennetun mallin, joka on helpompi käsitellä ja kuluttaa enemmän muistia. Kun olet käyttänyt aikaa parhaiden käytäntöjen opettelu-mallin tehokkaaseen malliin, voit luoda ja käyttää mitä tahansa mallia, jota käytät luodessasi ja katselitpa sitä Excel 2013: ssä, Microsoft 365 SharePoint Onlinessa, Office Online-palvelimessa tai SharePoint 2013: ssä.

Voit myös käyttää työkirjan koon optimointityökalua. Se analysoi Excel-työkirjan ja pakkaa sen entistä pienempään kokoon, jos se on mahdollista. Lataa työkirjan koon optimointi.

Tämän artikkelin sisältö

Puristus suhteet ja muisti analytiikka-ohjelma

Mikään ei voita ei-olemassa olevaa saraketta, jos muisti on vähissä

Kaksi esimerkkiä sarakkeista, jotka jätetään aina pois

Tarpeettomien sarakkeiden jättäminen pois

Entä suodatus vain tarvittavat rivit?

Entä jos tarvitsemme saraketta; Voimmeko edelleen pienentää sen tilaa?

Datetime-sarakkeiden muokkaaminen

SQL-kyselyn muokkaaminen

DAX-laskettujen mittojen käyttäminen sarakkeiden sijaan

Mitkä 2 saraketta kannattaa säilyttää?

Yhteenveto

Aiheeseen liittyvät linkit

Puristus suhteet ja muisti analytiikka-ohjelma

Excelin tieto mallit käyttävät in-Memory Analytics-moduulia tietojen tallentamiseen muistiin. Moottorissa on tehokkaat pakkaus tekniikat, jotka vähentävät tallennus tilaa, jolloin tulos joukkoa pienennetään, kunnes se on murto-osa alkuperäisestä koosta.

Voit olettaa, että tieto malli on Keski määrin 7 – 10, jos se on pienempi kuin samat tiedot alku perä hetkellä. Jos tuot esimerkiksi 7 Mt tietoja SQL Server-tieto kannasta, Excelin tieto malli voi olla helposti enintään 1 Mt. Tosiasiallisesti saavutettu pakkaus aste riippuu ensisijaisesti kunkin sarakkeen yksilöllisten arvojen määrästä. Mitä enemmän yksilöllisiä arvoja on, sitä enemmän muistia tarvitaan niiden tallentamiseen.

Miksi puhumme pakkaus-ja Unique-arvoista? Koska tehokas malli, joka minimoi muistin käytön, on kyse pakkaus tietojen maksimointi tavasta, ja helpoin tapa tehdä se on päästä eroon kaikista sarakkeista, joita et oikeasti tarvitse, etenkin jos nämä sarakkeet sisältävät suuren määrän yksilöllisiä arvoja.

Huomautus:  Yksittäisten sarakkeiden tallennus tila vaatimusten erot voivat olla valtavia. Joissain tapa uksissa on parempi olla useita sarakkeita, joissa on pieni määrä yksilöllisiä arvoja yhden sarakkeen sijaan, jossa on paljon yksilöllisiä arvoja. Datetime-optimoinnit-osa kattaa tämän tekniikan yksityiskohtaisesti.

Mikään ei voita ei-olemassa olevaa saraketta, jos muisti on vähissä

Enimmät muisti-iskuvoimainen jono on ainoa että te ei koskaan maahantuoja kotona edellä kohta. Jos haluat luoda tehokkaan mallin, Katso jokaista saraketta ja kysy itseltäsi, sisältyykö se analyysiin, jonka haluat suorittaa. Jos se ei ole tai et ole varma, jätä se pois. Voit tarvittaessa lisätä uusia sarakkeita myöhemmin.

Kaksi esimerkkiä sarakkeista, jotka jätetään aina pois

Ensimmäinen esimerkki koskee tieto varastosta perä isin olevia tietoja. Tieto varastossa on tavallista löytää ETL-prosessien esineitä, jotka lataavat ja päivittävät tietoja fyysisessä varastoinnissa. Sarakkeet, kuten luonti päivämäärä, päivitys päivämäärä ja ETL Run, luodaan, kun tiedot ladataan. Mitään näistä sarakkeista ei tarvita mallissa, eikä niitä pitäisi olla valittuna, kun tuot tietoja.

Toinen esimerkki koskee perusavaimen sarakkeen poisviemistä fakta taulukkoa tuotaessa.

Monissa taulukoissa, mukaan lukien fakta taulukoissa, on perusavaimet. Useimmat taulukot, kuten asiakas-, työn tekijä-tai myynti tiedot, kannattaa taulukon perusavainta, jotta voit käyttää sitä mallin välisten suhteiden luomiseen.

Fakta taulukoita on eri. Fakta taulukossa perusavainta käytetään kunkin rivin yksilöimiseen. Vaikka se on tarpeen normalisointi tarkoituksessa, se ei ole yhtä hyödyllinen tieto mallissa, jossa haluat vain analysoinnissa käytettävät sarakkeet tai taulukon yhteyksien luominen. Tästä syystä, kun tuot fakta taulukosta, Älä sisällytä sen perusavainta. Fakta taulukon perusavaimet kuluttavat malliin valtavia määriä tilaa, mutta eivät anna mitään hyötyä, koska niitä ei voi käyttää yhteyksien luomiseen.

Huomautus:  Tieto varastoissa ja moniulotteisessa tieto kannassa käytetään usein numeerisia tietoja, kuten fakta taulukoita. Fakta taulukoihin sisältyvät yleensä yrityksen suoritus kyky-tai tapahtuma tiedot, kuten myynti-ja kustannus arvo Pisteet, jotka yhdistetään ja kohdistetaan organisaatio yksiköihin, tuotteisiin, markkina segmentteihin, maantieteellisiin alue isiin ja niin edelleen. Tiedot-analyysin tueksi olisi sisällytettävä kaikki fakta taulukon sarakkeet, jotka sisältävät yritys tietoja tai joita voidaan käyttää muihin taulu koihin tallennettuihin risti viite tietoihin. Sarake, jonka haluat jättää pois, on fakta taulukon perusavain-sarake, joka koostuu ainutkertaisesta arvosta, joka on olemassa vain fakta taulukossa eikä missään muualla. Koska fakta pöydät ovat niin valtavia, jotkin mallin tehokkuuden suurimmat voitot johdetaan siitä, että ne eivät sisällä fakta taulukoiden rivejä tai sarakkeita.

Tarpeettomien sarakkeiden jättäminen pois

Tehokkaat mallit sisältävät vain ne sarakkeet, joita tarvitset itse työkirjassa. Jos haluat määrittää, mitkä sarakkeet sisältyvät malliin, sinun on käytettävä PowerPivot-apuohjelman ohjattua taulukon tuomista, jotta voit tuoda tiedot Excelin tuo tiedot-valinta ikkunan sijaan.

Kun käynnistät ohjatun taulukon tuomisen, valitset tuotavat taulukot.

PowerPivot-apuohjelman ohjattu taulukon tuonti

Voit napsauttaa kunkin taulukon esikatselu & Suodata-painiketta ja valita tarvitsemasi taulukon osat. Suosittelemme, että poistat ensin kaikkien sarakkeiden valinnan ja siirrymme sitten tarkistamaan haluamasi sarakkeet sen jälkeen, kun olet harkinnut, tarvitaanko analyysia.

Ohjatun taulukon tuonnin esikatseluruutu

Entä suodatus vain tarvittavat rivit?

Monet yritys tieto kantojen ja tieto varastojen taulut sisältävät historiallisia tietoja, jotka on kertynyt pitkiä aikoja. Lisäksi saatat huomata, että kiinnostavat pöydät sisältävät tietoja niiden yritysten alueista, joita ei tarvita tietyn analyysin yhteydessä.

Ohjatulla taulukon tuonti toiminnolla voit suodattaa historiallisia tai toisiinsa liittymättömiä tietoja ja säästää siten paljon tilaa mallissa. Seuraavassa kuvassa päivämäärä suodatinta käytetään palauttamaan vain rivit, jotka sisältävät kuluvan vuoden tietoja, lukuun ottamatta historiallisia tietoja, joita ei tarvita.

Ohjatun taulukon tuonnin suodatinruutu

Entä jos tarvitsemme saraketta; Voimmeko edelleen pienentää sen tilaa?

On olemassa muutamia muita tekniikoita, joita voit käyttää, kun haluat tehdä sarakkeesta paremman pakkaus menetelmän. Muista, että vain tiivistykseen vaikuttavan sarakkeen ominaisuus on yksilöllisten arvojen määrä. Tässä osiossa opit, miten joitakin sarakkeita voi muokata yksilöllisten arvojen määrän pienentämiseksi.

Datetime-sarakkeiden muokkaaminen

Monissa tapa uksissa DateTime-sarakkeet kestävät paljon tilaa. Tällä tieto tyypillä on useita tapoja vähentää tallennus tilan vaatimuksia. Tekniikat vaihtelevat sen mukaan, miten käytät saraketta, ja mukavuus tasoistasi SQL-kyselyjen luomisessa.

Datetime-sarakkeet sisältävät päivämäärä osan ja kellon ajan. Kun kysyt itseltäsi, Tarvitsetko sarakkeen, esitä sama kysymys DateTime-sarakkeelle useaan kertaan:

  • Tarvitsenko aika-osan?

  • Tarvitsenko aika-osan tuntien tasolla? minuuttia? Sekuntia? Milli sekunteina?

  • Onko minulla useita DateTime-sarakkeita, koska haluan laskea niiden välisen eron tai vain koota tiedot vuoden, kuukauden tai vuosi neljänneksen mukaan.

Se, miten vastaat kaikkiin näihin kysymyksiin, määrittää DateTime-sarakkeen käsittely vaihtoehdot.

Kaikki nämä ratkaisut vaativat SQL-kyselyn muokkaamisen. Jotta kyselyjen muokkaaminen olisi helpompaa, Suodata vähintään yksi sarake jokaiseen taulukkoon. Suodattamalla sarakkeen, muutat kyselyn rakennetta lyhennetyssä muodossa (SELECT *) SELECT-lauseeksi, joka sisältää täysin pätevät sarake nimet, joita on paljon helpompi muokata.

Katsotaan, mitä kyselyitä olet luonut sinulle. Taulukon ominaisuudet-valinta ikkunassa voit vaihtaa kysely Editoriin ja tarkastella kunkin taulukon nykyistä SQL-kyselyä.

PowerPivot-ikkunan valintanauha, jossa näkyy Taulukon ominaisuudet -komento

Valitse taulukon ominaisuuksista kysely editori.

Avaa Kyselyeditori Taulukon ominaisuudet -valintaikkunassa.

Kysely editori esittää taulukon täyttämiseen käytetyn SQL-kyselyn. Jos olet suodattanut minkä tahansa sarakkeen tuonnin aikana, kysely sisältää täysin hyväksytyt sarakkeiden nimet:

Tietojen hakemiseen käytetty SQL-kysely

Jos olet tuonut taulukon kokonaan ilman, että et ole tarkistanut mitään saraketta tai ottanut käyttöön mitään suodatinta, kysely tulee näkyviin muodossa "Select * from", jota on vaikeampi muokata:

SQL-kysely, jossa käytetään lyhyempää oletussyntaksia

SQL-kyselyn muokkaaminen

Nyt kun osaat löytää kyselyn, voit muokata sitä, jos haluat pienentää mallin kokoa entisestään.

  1. Jos sarakkeet sisältävät valuutta-tai desimaali tietoja, jos et tarvitse Desi maaleja, voit poistaa Desi maalit tämän syntaksin avulla:

    "Valitse ROUND ([Decimal_column_name], 0)... .”

    Jos tarvitset senttejä, jotka eivät ole sentti lukuja, Vaihda 0: a luvulla 2. Jos käytät negatiivisia lukuja, voit pyöristää yksiköt, kymmenet, sadat jne.

  2. Jos sinulla on DateTime-sarake nimeltä DBO. Bigtable. [Päivä määrä kellon aika] etkä tarvitse aika-osaa, Käytä syntaksia, kun haluat poistaa kellon ajan:

    "SELECT CAST (DBO. Bigtable. [Päivä määrä kellon aika] päivä määränä) [päivä määrä kellon aika]) "

  3. Jos sinulla on DateTime-sarake nimeltä DBO. Bigtable. [Päivä määrä kellon aika] ja tarvitset sekä päivä määrä-että aika-osat, käyttää useita sarakkeita SQL-kyselyssä yksittäisen DateTime-sarakkeen sijaan:

    "SELECT CAST (DBO. Bigtable. [Päivä määrä aika] päivä määränä) AS [päivä määrä kellon aika],

    DatePart (hh, DBO. Bigtable. [Päivä määrä kellon aika]) AS [päivä määrä kellon aika],

    DatePart (mi, DBO. Bigtable. [Päivä määrä kellon aika]) AS [päivä määrä aika minuutteina]

    DatePart (SS, DBO. Bigtable. [Päivä määrä kellon aika]) AS [päivä määrä aika sekunteina]

    DatePart (MS, DBO. Bigtable. [Päivä määrä kellon aika]) AS [päivä määrä aika Milli sekunteina] "

    Käytä niin monta saraketta kuin haluat tallentaa osat erillisiin sarakkeisiin.

  4. Jos tarvitset tunnit ja minuutit ja haluat, että ne ovat yhtä aikaa samassa sarakkeessa, voit käyttää syntaksia:

    Timefromparts (DatePart (hh, DBO. Bigtable. [Päivä määrä aika]); DatePart (mm, DBO. Bigtable. [Päivä määrä kellon aika])) AS [päivä määrä kellon aika]

  5. Jos sinulla on kaksi DateTime-saraketta, kuten [alkamis aika] ja [päättymis aika], ja mitä todella tarvitset, on niiden välinen aikaero sekunteina sarakkeena [kesto], poistaa molemmat sarakkeet luettelosta ja lisätä:

    "DateDiff (SS, [alkamis päivä], [päättymis päivä]) as [kesto]"

    Jos käytät avain sanaa MS eikä SS, saat keston Milli sekunteina.

DAX-laskettujen mittojen käyttäminen sarakkeiden sijaan

Jos olet aiemmin käyttänyt DAX-lausekkeen kieltä, tiedät ehkä jo, että laskettuja sarakkeita käytetään uusien sarakkeiden lisäämiseen mallin jonkin muun sarakkeen perusteella, kun taas lasketut mitat määritetään kerran mallissa, mutta ne lasketaan vain silloin, kun niitä käytetään Pivot-taulukko tai muu raportti.

Yksi muistin säästö menetelmä on, jos haluat vaihtaa tavallisia tai laskettuja sarakkeita ja laskettuja mittoja. Klassinen esimerkki on yksikkö hinta, määrä ja summa. Jos sinulla on kaikki kolme, voit säästää tilaa säilyttämällä vain kaksi ja laskemalla kolmannen DAX-käyttö oikeudet.

Mitkä 2 saraketta kannattaa säilyttää?

Yllä olevassa esimerkissä säilytetään määrä ja yksikkö hinta. Nämä kaksi ovat vähemmän arvoja kuin summa. Jos haluat laskea summan, Lisää laskettu mitta, kuten:

"TotalSales: = SUMX (' Sales Table ', ' Sales Table ' [yksikkö hinta] * ' Sales Table ' [määrä])"

Lasketut sarakkeet ovat kuin tavallisia sarakkeita, jotka molemmat käyttävät malliin tilaa. Sen sijaan lasketut mitat lasketaan lennossa eikä niissä ole tilaa.

Yhteenveto

Tässä artikkelissa puhuttiin useista eri tavoista, joiden avulla voit luoda enemmän muistia kuluttavan mallin. Tapa pienentää tieto mallin tiedosto kokoa ja muisti vaatimuksia on vähentää sarakkeiden ja rivien kokonaismäärää sekä kussakin sarakkeessa näkyvien yksilöllisten arvojen määrää. Seuraavassa on joitakin tekniikoita, jotka olemme käsitelleet:

  • Sarakkeiden poistaminen on tietenkin paras tapa säästää tilaa. Päätä, mitkä sarakkeet todella tarvitset.

  • Joskus voit poistaa sarakkeen ja vaihtaa sen taulukon lasketulla mitta yksikkönä.

  • Et ehkä tarvitse kaikkia taulukon rivejä. Voit suodattaa rivejä ohjatussa taulukon tuonnissa.

  • Yleensä yhden sarakkeen jakaminen useaan eri osaan on hyvä tapa vähentää sarakkeen yksilöllisten arvojen määrää. Jokaisessa osassa on pieni määrä yksilöllisiä arvoja, ja yhteenlaskettu summa on pienempi kuin alkuperäinen yhdistetty sarake.

  • Monissa tapa uksissa tarvitset myös eri osat, joita käytetään osittajia raporteissasi. Voit tarvittaessa luoda hierarkioita osista, kuten tunnit, minuutit ja sekunnit.

  • Usein sarakkeet sisältävät enemmän tietoja kuin tarvitset niitä. Oletetaan esimerkiksi, että sarakkeeseen tallennetaan Desi maaleja, mutta olet ottanut muotoilun käyttöön, jotta kaikki Desi maalit piilotetaan. Pyöristys voi olla erittäin tehokasta numeerisen sarakkeen koon pienentämiseen.

Nyt kun olet tehnyt mitä voit pienentää työkirjan kokoa, Harkitse myös työkirjan koko-optimoijan suorittamista. Se analysoi Excel-työkirjan ja pakkaa sen entistä pienempään kokoon, jos se on mahdollista. Lataa työkirjan koon optimointi.

Aiheeseen liittyvät linkit

Tietomallin määritykset ja rajoitukset

Työkirjan koon optimointi työkalun lataaminen

PowerPivot: tehokas tietojen analysointi ja tietomallien luominen Excelissä

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×