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

Programmā Excel 2013 vai jaunākā versijā varat izveidot datu modeļus, kuros ir miljoniem rindu, un pēc tam veikt jaudīgu datu analīzi, izmantojot šos modeļus. Datu modeļus var izveidot ar vai bez Power Pivot pievienojumprogrammu, lai tajā pašā darbgrāmatā atbalstītu jebkuru PivotTable, diagrammu un Power View vizualizāciju skaitu.

Piezīme.: Šajā rakstā ir aprakstīti datu modeļi programmā Excel 2013. Taču tie paši datu modelēšanas un Power Pivot līdzekļi, kas ieviesti programmā Excel 2013, attiecas arī uz Excel 2016. Starp šīm Excel versijām ir efektīva neliela atšķirība.

Lai gan varat viegli izveidot lielus datu modeļus programmā Excel, pastāv vairāki iemesli. Pirmkārt, lielie modeļi, kuros ir daudz tabulu un kolonnu, ir overkill lielākajai daļai analīžu un ir apgrūtinošs lauku saraksts. Otrais, lielie modeļi izmanto vērtīgo atmiņu, negatīvi ietekmē citas lietojumprogrammas un atskaites, kurās ir kopīgi tie paši sistēmas resursi. Visbeidzot Office 365 gan SharePoint Online, gan Excel Web App ierobežo Excel faila lielumu līdz 10 MB. Darbgrāmatas datu modeļiem, kas satur miljoniem rindu, ir diezgan ātri jāizpilda 10 MB. Skatiet rakstu datu modeļa specifikācija un ierobežojumi.

Šajā rakstā uzzināsit, kā izveidot cieši izveidotu modeli, ar kuru ir vieglāk strādāt un kas patērē mazāk atmiņas. Ņemot vērā laiku, lai apgūtu paraugprakses efektīvu modeļu izveidē, jums tiks atmaksāts jebkurš modelis, ko veidojat un izmantojat, neatkarīgi no tā, vai skatāt to programmā Excel 2013, Office 365 SharePoint Online, Office Web Apps serverī vai SharePoint 2013.

Apsveriet iespēju izmantot arī darbgrāmatas lieluma optimizētāju. Tas analizē jūsu Excel darbgrāmatu un, ja iespējams, vēl vairāk to saspiež. Lejupielādēt darbgrāmatas lieluma optimizētāju

Šajā rakstā

Saspiešanas koeficienti un atmiņas analīzes programma

Neesošas kolonnas nav pārspējas nepietiekamai atmiņas lietošanai

Divu tādu kolonnu piemēri, kas vienmēr ir 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šamā kolonna; vai joprojām var samazināt savas vietas izmaksas?

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ājums

Saistītās saites

Saspiešanas koeficienti un atmiņas analīzes programma

Datu modeļi programmā Excel izmanto atmiņu analīzes programmu, lai glabātu datus atmiņā. Dzinējs realizē jaudīgas saspiešanas paņēmienus, lai samazinātu krātuves prasības, samazinot rezultātu kopu, līdz tā ir daļa no tā sākotnējā izmēra.

Vidēji varat gaidīt, lai datu modelis būtu 7 līdz 10 reizes mazāks par tiem pašiem datiem tā izcelsmes vietā. Piemēram, ja importējat 7 MB datu no SQL Server datu bāzes, datu modelis programmā Excel var viegli būt 1 MB vai mazāks. Faktiski saspiešanas līmenis ir atkarīgs no katras kolonnas unikālās vērtības. Vairāk unikālo vērtību, jo vairāk atmiņas ir nepieciešamas, lai tās saglabātu.

Kāpēc mēs runājam par saspiešanu un unikālām vērtībām? Tā kā izveidot efektīvu modeli, kas samazina atmiņas lietojumu, ir par saspiešanas palielināšanu un vienkāršākais veids, kā to izdarīt, ir atbrīvoties no visām kolonnām, kas jums nav vajadzīgas, it īpaši tad, ja šajās kolonnās ir liels skaits unikālu vērtību.

Piezīme.:  Atsevišķu kolonnu krātuves prasību atšķirības var būt milzīgas. Dažos gadījumos labāk ir izveidot vairākas kolonnas ar nelielu skaitu unikālo vērtību, nevis vienu kolonnu ar lielu unikālo vērtību skaitu. Sadaļa par datetime optimizācijām attiecas uz šo tehniku detalizēti.

Neesošas kolonnas nav pārspējas nepietiekamai atmiņas lietošanai

Visefektīvākā kolonna atmiņa ir tā, kuru nekad neesat importējis pirmajā vietā. Ja vēlaties izveidot efektīvu modeli, aplūkojiet katru kolonnu un uzdodiet sev, vai tas sekmēs analīzes veikšanu. Ja tā nav vai neesat pārliecināts, atstājiet to. Jūs vienmēr varat pievienot jaunas kolonnas vēlāk, ja tās ir vajadzīgas.

Divu tādu kolonnu piemēri, kas vienmēr ir jāiekļauj

Pirmais piemērs ir saistīts ar datiem, kas iegūti no datu noliktavas. Datu noliktavā ir kopējs, kas atrod artefaktus par ETL procesiem, kuros dati tiek ielādēti un atsvaidzināti noliktavā. Kad dati tiek ielādēti, tiek izveidotas kolonnas, piemēram, "izveidot datumu", "atjaunināšanas datums" un "ETL Run". Šajā modelī nav nepieciešamas neviena no šīm kolonnām, un, importējot datus, ir jābūt atlasītai.

Otrs piemērs ir saistīts ar primārās atslēgas kolonnas importēšanu faktu tabulā.

Daudzas tabulas, ieskaitot faktu tabulas, ir primārās atslēgas. Lielākajai daļai tabulu, piemēram, to, kas satur klientu, darbinieku vai pārdošanas datus, ir jābūt tabulas primārajai atslēgai, lai jūs varētu to izmantot, lai izveidotu relācijas modelī.

Faktu tabulas ir atšķirīgas. Faktu tabulā primārā atslēga tiek izmantota, lai unikāli identificētu katru rindu. Lai gan normalizācijas nolūkos nepieciešams, tas ir mazāk noderīgs datu modelī, kur vēlaties izmantot tikai tās kolonnas, kuras izmanto analīzē vai izveidot tabulu relācijas. Šī iemesla dēļ, importējot no faktu tabulas, neiekļaujiet tās primāro atslēgu. Primārās atslēgas faktu tabulā patērē milzīgu vietu modelī, tomēr nesniedz nekādu labumu, jo tās nevar izmantot relāciju izveidei.

Piezīme.:  Datu noliktavās un daudzdimensiju datu bāzēs lielas tabulas, kurās ir galvenokārt skaitliski dati, bieži dēvē par "FACT Tables". Faktu tabulās parasti ir darījumu veiktspējas vai transakciju dati, piemēram, pārdošanas un izmaksu datu punkti, kas apkopoti un saskaņoti ar organizācijas vienībām, produktiem, tirgus segmentiem, ģeogrāfiskajiem reģioniem utt. Šajā modelī ir jāiekļauj visas faktu tabulas kolonnas, kurās ir biznesa dati, vai kuras var izmantot, lai pievienotu atsauces datus, kas tiek glabāti citās tabulās. 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 nekur citur. Tā kā faktu tabulas ir tik milzīgas, daži no lielākajiem ieguvumiem modeļa efektivitātē ir iegūti, neiekļaujot rindas vai kolonnas no faktu tabulām.

Kā izslēgt nevajadzīgās kolonnas

Efektīvākajos modeļos ir tikai tās kolonnas, kas faktiski ir nepieciešamas darbgrāmatā. Ja vēlaties kontrolēt, kuras kolonnas ir iekļautas modelī, varat izmantot tabulu importēšanas vedni pievienojumprogrammā Power Pivot, lai importētu datus , nevis "dialoglodziņš datu importēšana" programmā Excel.

Startējot tabulu importēšanas vedni, tiek atlasītas importējamās tabulas.

Pievienojumprogrammas PowerPivot tabulu importēšanas vednis

Katrai tabulai varat noklikšķināt uz pogas priekšskatīt & filtru un atlasīt tās tabulas daļas, kas jums ir nepieciešamas. Ieteicams vispirms noņemiet atzīmi no visām kolonnām un pēc tam pārejiet pie nepieciešamās kolonnas, lai noskaidrotu, vai tās ir nepieciešamas analīzei.

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

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

Daudzas korporatīvās datu bāzēs un datu noliktavās ir iekļauti vēsturiskie dati, kas uzkrājušies ilgu laiku. Turklāt jūs, iespējams, atklāsiet, ka interesējošās tabulas satur informāciju par uzņēmuma jomām, kas nav nepieciešamas specifiskai analīzei.

Izmantojot tabulu importēšanas vedni, varat filtrēt vēsturiskos vai nesaistītos datus un tādējādi ietaupīt daudz vietas modelī. Nākamajā attēlā datuma filtrs tiek izmantots, lai izgūtu tikai tās rindas, kurās ir pašreizējā gada dati, izņemot vēsturiskos datus, kas nav nepieciešami.

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

Kā rīkoties, ja ir nepieciešamā kolonna; vai joprojām var samazināt savas vietas izmaksas?

Ir daži papildu paņēmieni, kurus var lietot, lai padarītu kolonnu labāku saspiešanas kandidātu. Atcerieties, ka tikai tās kolonnas raksturlielums, kas ietekmē saspiešanu, ir unikālo vērtību skaits. Šajā sadaļā uzzināsit, kā dažas kolonnas var modificēt, lai samazinātu unikālo vērtību skaitu.

Datuma/laika kolonnu modificēšana

Daudzos gadījumos datetime kolonnas aizņem daudz vietas. Par laimi, ir vairāki veidi, kā samazināt datu tipa krātuves prasības. Metodes var atšķirties atkarībā no tā, kā izmantojat kolonnu, un jūsu komforta līmenis SQL vaicājumu izveidē.

Datetime kolonnās ir iekļauta datuma daļa un laiks. Kad jums jāuzdod, vai jums ir jābūt kolonnai, uzdodiet vienu un to pašu jautājumu vairākas reizes uz datuma/laika kolonnu.

  • Vai ir nepieciešamas laika daļa?

  • Vai ir jāveido laika daļa stundu līmenī? minūtes? Sekundes? milisekundēs?

  • Vai man ir vairākas datetime kolonnas, jo vēlos aprēķināt to starpību vai vienkārši apvienot datus pēc gada, mēneša, ceturkšņa utt.

Atbilde uz katru no šiem jautājumiem nosaka opcijas, kas attiecas uz datuma/laika kolonnu.

Visiem šiem risinājumiem nepieciešama SQL vaicājuma modificēšana. Lai padarītu vaicājuma modifikāciju vienkāršāku, katrā tabulā ir jāizfiltrē vismaz viena kolonna. Filtrējot kolonnu, jūs maināt vaicājumu būvniecību no saīsinātā formāta (atlasiet *) uz priekšrakstu SELECT, kas ietver pilnībā kvalificētus kolonnu nosaukumus, ko ir vieglāk modificēt.

Aplūkosim jums izveidotos vaicājumus. Dialoglodziņā Tabulas rekvizīti varat pārslēgties uz vaicājumu redaktoru un skatīt pašreizējo SQL vaicājumu katrai tabulai.

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ājumu redaktorā tiek parādīts SQL vaicājums, kas tiek izmantots, lai aizpildītu tabulu. Ja esat filtrējis jebkuru kolonnu importēšanas laikā, vaicājumā ir pilnībā kvalificēti kolonnu nosaukumi:

Datu izgūšanai izmantotais SQL vaicājums

Savukārt, ja esat importējis tabulu pilnībā, neatzīmējot nevienu kolonnu vai nelietojot nevienu filtru, vaicājums tiek parādīts kā "select * from", ko būs grū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 modeļa lielumu.

  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 ([Decimal_column_name],0)... .”

    Ja jums ir nepieciešami santīmi, bet ne santīmi, aizstājiet vērtību 0 ar 2. Ja izmantojat negatīvos skaitļus, varat noapaļot uz vienībām, desmitiem, simtiem utt.

  2. Ja jums ir datuma/laika kolonna ar nosaukumu dbo. BigTable. [Datums laiks] un nav nepieciešams laika daļa, izmantojiet sintaksi, lai atbrīvotos no laika:

    "SELECT CAST (DBO. BigTable. [Datums laiks] as date) AS [datums laiks]) "

  3. Ja jums ir datuma/laika kolonna ar nosaukumu dbo. BigTable. [Datums laiks] un ir nepieciešamas gan datuma, gan laika daļas, izmantojot vairākas kolonnas SQL vaicājumā, nevis vienu datetime kolonnu:

    "SELECT CAST (DBO. BigTable. [Datums laiks] as date) AS [datums laiks],

    DatePart (HH, dbo. BigTable. [Datums laiks]) as [datums laiks stundas],

    DatePart (MI, dbo. BigTable. [Datums laiks]) as [datums laiks minūtes]

    DatePart (ss, dbo. BigTable. [Datums laiks]) as [datums laiks sekundēs]

    DatePart (MS, dbo. BigTable. [Datums laiks]) as [datums laiks milisekundes] "

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

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

    Timefromparts (DatePart (HH, dbo. BigTable. [Datums laiks]), DatePart (mm, dbo. BigTable. [Datums laiks]) as [datums laiks HourMinute]

  5. Ja jums ir divas datetime kolonnas, piemēram, [sākuma laiks] un [beigšanas laiks], un kas jums ir ļoti nepieciešams, ir laika starpība starp tām sekundēs kā kolonna ar nosaukumu [ilgums], noņemt abas kolonnas no saraksta un pievienot:

    "DateDiff (ss, [sākuma datums], [beigšanas datums]) as [ilgums]"

    Ja izmantosit atslēgvārdu MS, nevis ss, tiek parādīts ilgums milisekundēs

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

Ja iepriekš esat strādājis ar DAX izteiksmes valodu, iespējams, jau zināt, ka aprēķinātās kolonnas tiek izmantotas, lai iegūtu jaunas kolonnas, pamatojoties uz kādu citu modeļa kolonnu, bet aprēķinātie mēri tiek definēti vienreiz modelī, bet tiek novērtēti tikai tad, ja tiek izmantoti Rakurstabulas vai citas atskaites.

Viens atmiņas taupīšanas paņēmiens ir aizstāt parastās vai aprēķinātās kolonnas ar aprēķinātajiem mēriem. Klasiskajā piemērā ir vienības cena, daudzums un kopsumma. Ja jums ir trīs, varat ietaupīt vietu, saglabājot tikai divus un aprēķinot trešo, izmantojot DAX.

Kuras 2 kolonnas ir jāpatur?

Iepriekšējā piemērā saglabājiet daudzumu un vienības cenu. Šiem diviem ir mazāk vērtību nekā kopsummu. Lai aprēķinātu kopsummu, pievienojiet aprēķināto mērvienību, piemēram:

"TotalSales: = sumx (' pārdošanas tabula ', ' pārdošanas tabula ' [vienības cena] * ' pārdošanas tabula ' [daudzums])"

Aprēķinātās kolonnas ir kā parastas kolonnas, jo abas aizņem vietu modelī. Turpretim aprēķinātie mēri tiek aprēķināti, lidojot un neaizņemot vietu.

Secinājums

Šajā rakstā mēs runājām par vairākām pieejām, kas var palīdzēt izveidot daudz efektīvāku modeli. Veids, kā samazināt datu modeļa faila lielumu un atmiņas prasības, ir samazināt kopējo kolonnu un rindu skaitu un unikālo vērtību skaitu, kas tiek rādītas katrā kolonnā. Šeit ir aprakstītas dažas metodes, kas attiecas uz:

  • Noņemot kolonnas, ir vislabākais veids, kā taupīt vietu. Izlemiet, kuras kolonnas tiešām ir vajadzīgas.

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

  • Jums, iespējams, nav nepieciešamas visas tabulas rindas. Tabulu importēšanas vednī varat filtrēt rindas.

  • Vispārīgi atdaliet vienu kolonnu vairākās noteiktās daļās, ir labs veids, kā samazināt unikālo vērtību skaitu kolonnā. Katrai daļai būs mazs unikālo vērtību skaits, un kopējais kopsummas lielums būs mazāks par oriģinālo vienoto kolonnu.

  • Daudzos gadījumos ir nepieciešamas arī atsevišķas daļas, kas atskaitēs ir jāizmanto kā datu griezumi. Ja nepieciešams, varat izveidot hierarhijas no tādām daļām kā stundas, minūtes un sekundes.

  • Vairākkārt kolonnās ir vairāk informācijas, nekā jums ir nepieciešams. Piemēram, pieņemsim, ka kolonnā ir saglabātas decimāldaļas, bet ir lietots formatējums, lai paslēptu visus decimāldaļas. Noapaļošana var būt ļoti efektīva, lai samazinātu skaitliskās kolonnas lielumu.

Tagad, kad esat pabeidzis, kā samazināt darbgrāmatas lielumu, ņemiet vērā, ka darbojas arī darbgrāmatas lieluma optimizētājs. Tas analizē jūsu Excel darbgrāmatu un, ja iespējams, vēl vairāk to saspiež. Lejupielādēt darbgrāmatas lieluma optimizētāju

Saistītās saites

Datu modeļa specifikācija un ierobežojumi

Darbgrāmatas lieluma optimizētājs 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 jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×