Datu modeļa izveide ar efektīvu atmiņas izmantošanu, izmantojot Excel un pievienojumprogrammu Power Pivot

Piezīme.:  Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā .

Programmā Excel 2013 vai jaunākā versijā, varat izveidot datu modeļus, kas satur miljoniem rindu un pēc tam veiciet jaudīga datu analīze pret šiem modeļiem. Datu modeļu var izveidot ar vai bez Power Pivot pievienojumprogrammu atbalstam rakurstabulām, diagrammām un Power View vizualizācijas skaits vienā un tajā pašā darbgrāmatā.

Piezīme.: Šajā rakstā paskaidrota datu modeļiem programmā Excel 2013. Tomēr vienā datu modelēšana un Power Pivot līdzekļus, kas ieviesti programmā Excel 2013 attiecas arī uz Excel 2016. Pastāv efektīvi maz atšķirība starp šīm Excel versijām.

Lai gan jūs varat viegli izveidot lielu datu modeļiem programmā Excel, ir vairāki iemesli, ne pat. Pirmā, lielu modeļi, kurās ir ļaužu tabulu un kolonnu overkill lielākā daļa analīzes, un padarīt apgrūtinoši lauku sarakstu. Otrkārt, lielas modeļiem izlietot vērtīgs atmiņas, negatīvi neietekmējot citas programmas un atskaites, kurās ir pašu sistēmas resursus. Visbeidzot, Office 365, gan SharePoint Online un Excel Web App ierobežot 10 MB Excel failu lielumu. Darbgrāmatas datu modeļiem, kas satur miljoniem rindu, rodas maksimālā 10 MB diezgan ātri. Skatiet rakstā datu modeļa specifikācija un ierobežojumi.

Šajā rakstā uzzināsit, kā izveidot cieši konstruētu modeli, ar kuru ir ērtāk strādāt un kurš izmanto mazāk atmiņas. Laika veltīšana labākās prakses apgūšanai, lai efektīvi noformētu modeli, vēlāk atmaksāsies jebkuram modelim, kuru veidosit un izmantosit, neatkarīgi no tā, vai skatīsit to programmā Excel 2013, pakalpojumā Office 365 SharePoint Online, produktu komplektā Office Web Apps Server vai SharePoint 2013 vidē.

Apsveriet arī darbojas darbgrāmatas lieluma optimizētājs. Excel darbgrāmatas analizē un saspiež, ja tas ir iespējams to tālāk. Lejupielādējiet darbgrāmatas lieluma optimizētājs.

Šajā rakstā

Saspiešanas attiecības un atmiņā veiktas analītikas programma

Atmiņas lietojuma ziņā nekas nepārspēj neesošu kolonnu zemas

Divu tādu kolonnu piemēri, kas nekad nav jāiekļauj

Kā izslēgt nevajadzīgās kolonnas

Kā sastāv ar tikai nepieciešamo rindu filtrēšanu?

Kā rīkoties, ja ir nepieciešama šī kolonna; vai joprojām var samazināt tai nepieciešamo vietu?

Datuma/laika kolonnu modificēšana

SQL vaicājuma modificēšana

DAX aprēķināto mēru izmantošana kolonnu vietā

Kuras 2 kolonnas ir jāpatur?

Secinājumu

Saistītās saites

Saspiešanas attiecības un atmiņā veiktas analītikas programma

Excel datu modeļi datu glabāšanai atmiņā izmanto atmiņā veiktas analītikas programmu. Šī programma nodrošina jaudīgus saspiešanas paņēmienus, lai samazinātu krātuves prasības, rezultātu kopu saspiežot līdz nelielai tās sākotnējā lieluma daļai.

Vidēji varat sagaidīt, ka datu modelis būs 7–10 reizes mazāks nekā šie dati sākotnējā stāvoklī. Piemēram, ja importējat 7 MB datu no SQL Server datu bāzes, datu modelis programmā Excel varētu būt 1 MB liels vai pat mazāks. Faktiski iegūtā saspiešanas pakāpe pamatā ir atkarīga no unikālo vērtību daudzuma katrā kolonnā. Jo vairāk unikālo vērtību, jo vairāk atmiņas nepieciešams to glabāšanai.

Kāpēc mēs runājam par saspiešanu un unikālajām vērtībām? Jo tāda efektīva modeļa izveidei, kas minimizē atmiņas lietojumu, ir nepieciešama saspiešanas maksimizēšana, un vieglākais veids, kā to panākt, ir atbrīvoties no visām kolonnām, kas nav nepieciešamas, īpaši, ja tajās ir daudz unikālu vērtību.

Piezīme.: Krātuves prasības atsevišķām kolonnām var ievērojami atšķirties. Dažos gadījumos labāk ir izveidot vairākas kolonnas ar nelielu unikālo vērtību daudzumu, nevis vienu kolonnu ar daudzām unikālām vērtībām. Datuma/laika optimizēšanas sadaļā šis paņēmiens ir apspriests detalizēti.

Maza atmiņas lietojuma ziņā nekas nepārspēj neesošu kolonnu

Atmiņas izmantošanas ziņā visefektīvākā kolonna ir tā, kas nemaz netika importēta. Ja vēlaties izveidot efektīvu modeli, aplūkojiet katru kolonnu un pavaicājiet sev, vai tā ir nepieciešama veicamajai analīzei. Ja nav vai ja neesat pārliecināts, neizmantojiet to. Ja būs nepieciešams, vēlāk vienmēr varēsit pievienot jaunas kolonnas.

Divu tādu kolonnu piemēri, kas nekad nav jāiekļauj

Pirmais piemērs ir saistīts ar datiem no datu noliktavas. Datu noliktavā bieži vien ir atrodami to ETL procesu artefakti, kas noliktavā ielādē un atsvaidzina datus. Datu ielādes laikā tiek izveidotas tādas kolonnas kā “izveides datums”, “atjaunināšanas datums” un “ETL izpilde”. Modelī neviena no šīm kolonnām nav nepieciešama, tāpēc, importējot datus, ir jānoņem to atlase.

Otrs piemērs ir saistīts ar primārās atslēgas kolonnas noņemšanu, importējot faktu tabulu.

Daudzās tabulās, ieskaitot faktu tabulas, ir primārās atslēgas. Vairākumā tabulu, piemēram, tajās, kurās ir dati par klientiem, darbiniekiem vai pārdošanu, jums būs nepieciešama tabulas primārā atslēga, lai modelī to varētu izmantot relāciju izveidei.

Faktu tabulas ir citādas. Faktu tabulā primārā atslēga tiem izmantota katras rindas unikālai identificēšanai. Lai gan tas ir nepieciešams normalizēšanai, tomēr tas ir mazāk noderīgi datu modelī, kur nepieciešamas tikai analīzei izmantojamās vai tabulas relāciju izveidei nepieciešamās kolonnas. Šī iemesla dēļ, importējot no faktu tabulas, neiekļaujiet tās primāro atslēgu. Faktu tabulas primārā atslēga modelī prasa ļoti daudz vietas, bet nesniedz nekādus ieguvumus, jo to nevar izmantot relāciju izveidei.

Piezīme.: Datu noliktavās un daudzdimensiju datu bāzēs lielas tabulas, kas pārsvarā sastāv no skaitliskiem datiem, bieži sauc par faktu tabulām. Parasti faktu tabulās ir biznesa veiktspējas vai transakciju dati, piemēram, pārdošanas un izmaksu datu punkti, kas tiek apkopoti un līdzināti ar organizācijas vienībām, produktiem, tirgus segmentiem, ģeogrāfiskajiem reģioniem utt. Datu analīzes atbalstam modelī ir jāiekļauj visas faktu tabulas kolonnas, kurās ir biznesa dati vai kuras var izmantot mijnorādei uz citās tabulās esošiem datiem. Kolonna, kuru vēlaties izslēgt, ir faktu tabulas primārās atslēgas kolonna, kas sastāv no unikālām vērtībām, kuras pastāv tikai faktu tabulā un kuru nav nekur citur. Tā kā faktu tabulas ir tik apjomīgas, daži no lielākajiem modeļa efektivitātes ieguvumiem rodas no faktu tabulu rindu vai kolonnu neiekļaušanas.

Kā izslēgt nevajadzīgās kolonnas

Efektīvu modeļi satur tikai kolonnas, kas faktiski ir savā darbgrāmatā. Ja vēlaties noteikt, kuras kolonnas tiek iekļauti modelis, jums ir izmantot tabulas importēšanas vedni pievienojumprogrammā Power Pivot datu importēšanai , nevis "Importēt datus" dialoglodziņš programmā Excel.

Startējot tabulu importēšanas vedni, jūs atlasāt, kuras tabulas importēt.

Pievienojumprogrammas PowerPivot tabulu importēšanas vednis

Katrai tabulai varat noklikšķināt uz priekšskatījuma un filtra pogas un atlasīt tās tabulas daļas, kas tiešām ir nepieciešamas. Ieteicams vispirms noņemt visu kolonnu atzīmes un pēc tam atzīmēt nepieciešamās kolonnas, apsverot, vai analīzei tās tiešām ir nepieciešamas.

Tabulu importēšanas vedņa priekšskatījuma rūts

Kā sastāv ar tikai nepieciešamo rindu filtrēšanu?

Daudzas uzņēmumu datu bāzu un datu noliktavu tabulas satur vēsturiskos datus, kas ir uzkrāti ļoti ilgā laika periodā. Turklāt varat konstatēt, ka jūs interesējošās tabulās ir informācija par biznesa jomām, kas konkrētajai analīzei nav nepieciešamas.

Izmantojot tabulu importēšanas vedni, varat atfiltrēt vēsturiskos un nesaistītos datus, tā modelī ietaupot ļoti daudz vietas. Nākamajā attēlā tiek izmantots datuma filtrs, lai izgūtu tikai tās rindas, kurās ir pašreizējā gada dati, izslēdzot vēsturiskos datus, kas nebūs vajadzīgi.

Tabulu importēšanas vedņa filtrēšanas rūts

Kas notiek, ja ir nepieciešama šī kolonna, vai joprojām var samazināt tai nepieciešamo vietu?

Pastāv daži papildu paņēmieni, kurus varat izmantot, lai kolonnu varētu labāk saspiest. Atcerieties, ka kolonnas vienīgais raksturlielums, kas ietekmē saspiešanu, ir unikālo vērtību skaits. Šajā sadaļā uzzināsit, kā var modificēt dažas kolonnas, lai samazinātu unikālo vērtību daudzumu.

Datuma/laika kolonnu modificēšana

Daudzos gadījumos datuma/laika kolonnas aizņem daudz vietas. Par laimi pastāv vairāki veidi, kā samazināt šī datu tipa krātuves prasības. Paņēmieni atšķiras atkarībā no kolonnas izmantošanas veida un jūsu zināšanām par SQL vaicājumu izveidi.

Datuma/laika kolonnās ir datuma daļa un laika daļa. Kad jautājat sev, vai ir nepieciešama kāda kolonna, datuma/laika kolonnas gadījumā uzdodiet šo jautājumu vairākkārt:

  • Vai ir nepieciešama laika daļa?

  • Vai laika daļa ir nepieciešama stundu, minūšu, sekunžu vai milisekunžu līmenī?

  • Vai vairākas datuma/laika kolonnas ir tāpēc, ka jāaprēķina to starpība, vai vienkārši tāpēc, lai apkopotu datus pēc gada, mēneša, ceturkšņa u.tml.

Jūsu atbildes uz šiem jautājumiem nosaka iespējas rīkoties ar datuma/laika kolonnu.

Visiem šiem risinājumiem ir nepieciešama SQL vaicājuma modificēšana. Lai atvieglotu vaicājuma modificēšanu, atfiltrējiet vismaz vienu katras tabulas kolonnu. Atfiltrējot kolonnu, vaicājuma konstrukcija tiek mainīta no saīsinātā formāta (SELECT *) uz priekšrakstu SELECT, kur iekļauti pilnībā kvalificēti kolonnu nosaukumi, kurus ir vieglāk modificēt.

Aplūkosim izveidotos vaicājumus. No tabulas rekvizītu dialoglodziņa varat pārslēgties uz vaicājumu redaktoru un skatīt katras tabulas pašreizējo SQL vaicājumu.

PowerPivot loga lente ar tabulas rekvizītu komandu

No tabulas rekvizītiem atlasiet Vaicājuma redaktors.

Atveriet vaicājuma redaktoru no tabulas rekvizītu dialoga

Vaicājuma redaktorā redzams tabulas aizpildīšanai izmantotais SQL vaicājums. Ja importējot atfiltrējāt kādu kolonnu, vaicājumā ir iekļauti pilnībā kvalificēti kolonnu nosaukumi:

Datu izgūšanai izmantotais SQL vaicājums

Pretstatā tam, ja tabulu importējāt pilnībā, nenoņemot nevienas kolonnas atzīmi un nelietojot nevienu filtru, redzēsit vaicājumu kā “Select * from”, kuru ir sarežģītāk modificēt:

SQL vaicājums ar noklusējuma, īsāku sintaksi

SQL vaicājuma modificēšana

Tagad, kad zināt, kā atrast vaicājumu, varat to modificēt, lai vēl vairāk samazinātu sava modeļa datu apjomu.

  1. Kolonnām, kurās ir norādīta valūta vai decimālskaitļu dati, ja decimāldaļas nav vajadzīgas, izmantojiet šo sintaksi, lai atbrīvotos no decimāldaļām:

    “SELECT ROUND([Decimālskaitļa_kolonnas_nosaukums],0)… .”

    Ja ir nepieciešami santīmi, bet ne to daļas, aizstājiet 0 ar 2. Izmantojot negatīvus skaitļus, varat noapaļot līdz vienībām, desmitiem, simtiem utt.

  2. Ja jums ir datuma/laika kolonna ar nosaukumu dbo.Liela_tabula.[Datums Laiks] un nav nepieciešama laika daļa, izmantojiet šo sintaksi, lai no tās atbrīvotos:

    “SELECT CAST (dbo.Liela_tabula.[Datums Laiks] as date) AS [Datums Laiks]) “

  3. Ja jums ir datuma/laika kolonna ar nosaukumu dbo.Liela_tabula.[Datums Laiks] un ir nepieciešamas abas daļas (Datums un Laiks), vienas datuma/laika kolonnas vietā SQL vaicājumā izmantojiet vairākas kolonnas:

    “SELECT CAST (dbo.Liela_tabula.[Datums Laiks] as date ) AS [Datums Laiks],

    datepart(hh, dbo.Liela_tabula.[Datums Laiks]) as [Datums Laiks Stundas],

    datepart(mi, dbo.Liela_tabula.[Datums Laiks]) as [Datums Laiks Minūtes],

    datepart(ss, dbo.Liela_tabula.[Datums Laiks]) as [Datums Laiks Sekundes],

    datepart(ms, dbo.Liela_tabula.[Datums Laiks]) as [Datums Laiks Milisekundes]”

    Izmantojiet tik daudz kolonnu, cik nepieciešams, lai katru daļu glabātu atsevišķā kolonnā.

  4. Ja ir nepieciešamas stundas un minūtes un vēlaties tās paturēt kopā kā vienu laika kolonnu, varat izmantot šādu sintaksi:

    Timefromparts(datepart(hh, dbo.Liela_tabula.[Datums Laiks]), datepart(mm, dbo.Liela_tabula.[Datums Laiks])) as [Datums Laiks Stunda_minūte]

  5. Ja jums ir divas datuma/laika kolonnas, piemēram [Sākuma laiks] un [Beigu laiks], un ir nepieciešama tikai laika starpība starp tām sekundēs kolonnā ar nosaukumu [Ilgums], izņemiet no saraksta abas kolonnas un pievienojiet:

    “datediff(ss,[Sākuma datums],[Beigu datums]) as [Ilgums]”

    ja ss vietā izmantosit atslēgvārdu ms, iegūsit ilgumu milisekundēs

DAX aprēķināto mēru izmantošana kolonnu vietā

Ja iepriekš esat strādājis ar izteiksmju valodu DAX, iespējams, jau zināt, ka aprēķinātās kolonnas tiek izmantotas, lai no kādas modeļa kolonnas atvasinātu jaunas kolonnas, bet aprēķinātie mēri modelī tiek definēti vienu reizi un novērtēti tikai tad, kad tiek izmantoti rakurstabulā vai citā atskaitē.

Viens no atmiņas taupīšanas paņēmieniem ir parastās vai aprēķinātās kolonnas aizstāt ar aprēķinātiem mēriem. Klasisks piemērs ir vienības cena, daudzums un kopsumma. Ja ir visas trīs vērtības, vietu var ietaupīt, uzturot tikai divas, bet trešo aprēķinot ar DAX palīdzību.

Kuras 2 kolonnas ir jāpatur?

Iepriekšējā piemērā paturiet daudzumu un vienības cenu. Tajās ir mazāk vērtību, nekā kopsummas kolonnā. Lai aprēķinātu kopsummu, pievienojiet šādu aprēķināto mēru:

“Kopējā_pārdošana:=sumx(‘Pārdošanas tabula’,’Pārdošanas tabula’[Vienības cena]*’Pārdošanas tabula’[Daudzums])”

Aprēķinātās kolonnas gluži kā parastās kolonnas modelī aizņem vietu. Bet aprēķinātie mēri tiek aprēķināti pēc vajadzības un neaizņem vietu.

Secinājums

Šajā rakstā apspriedām vairākus paņēmienus, kas var palīdzēt izveidot atmiņas izmantošanas ziņā efektīvāku modeli. Lai samazinātu datu modeļa faila lielumu un atmiņas prasības, ir jāsamazina kopējais kolonnu un rindu daudzums, kā arī katrā kolonnā esošo unikālo vērtību daudzums. Lūk, daži aplūkotie paņēmieni:

  • Kolonnu noņemšana, protams, ir vislabākais vietas taupīšanas veids. Izlemiet, kuras kolonnas tiešām ir nepieciešamas.

  • Dažkārt var noņemt kolonnu un tabulā to aizstāt ar aprēķinātu mēru.

  • Iespējams, nav nepieciešamas visas tabulas rindas. Rindas var atfiltrēt tabulu importēšanas vednī.

  • Pamatā vienas kolonnas sadalīšana vairākās atsevišķās daļās ir lielisks veids, kā samazināt kolonnas unikālo vērtību daudzumu. Katrā daļā būs neliels unikālo vērtību skaits, un to kopsumma mazāka nekā sākotnējā apvienotajā kolonnā.

  • Daudzos gadījumos būs nepieciešamas arī atšķirīgas daļas, kuras atskaitē izmantot kā datu griezumus. Kur tas ir piemēroti, no šīm daļām varat veidot hierarhijas, piemēram, stundas, minūtes un milisekundes.

  • Daudzos gadījumos kolonnās ir vairāk informācijas, nekā nepieciešams. Piemēram, ja kolonnā ir decimāldaļskaitļi, bet tiek lietots formatējums, kas paslēpj visas decimāldaļas. Noapaļošana var būt ļoti efektīvs veids, kā samazināt skaitliskas kolonnas lielumu.

Tagad, kad esat pabeidzis, varat savas darbgrāmatas lieluma samazināšana, apsveriet arī darbojas darbgrāmatas lieluma optimizētājs. Excel darbgrāmatas analizē un saspiež, ja tas ir iespējams to tālāk. Lejupielādējiet darbgrāmatas lieluma optimizētājs.

Saistītās saites

Datu modeļa specifikācija un ierobežojumi

Darbgrāmatas lieluma optimizētāja lejupielāde

PowerPivot: jaudīga datu analīze un datu modelēšana programmā Excel

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×