Mencipta Model Data cekap memori menggunakan Excel dan Power Pivot tambahan

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Dalam Excel 2013 atau lebih baru, anda boleh mencipta model data yang mengandungi jutaan baris dan kemudian menjalankan analisis data yang berkesan terhadap model ini. Model data yang boleh dicipta dengan atau tanpa Power Pivot tambahan untuk menyokong sebarang nombor Jadual pangsi, carta dan penggambaran Power View dalam buku kerja yang sama.

Nota: Artikel ini memerihalkan model data dalam Excel 2013. Walau bagaimanapun, pemodelan data yang sama dan ciri Power Pivot yang diperkenalkan dalam Excel 2013 juga digunakan untuk Excel 2016. Terdapat berkesan kecil perbezaan antara versi Excel ini.

Walaupun anda dengan mudah boleh membina model data yang besar dalam Excel, Terdapat beberapa sebab tidak ke. Model pertama, besar yang mengandungi kebanyakan jadual dan lajur berlebihan untuk kebanyakan analisis dan membuat untuk senarai medan yang rumit. Model kedua, besar menggunakan memori bernilai, negatif menjejaskan aplikasi lain dan laporan yang berkongsi sumber sistem yang sama. Akhirnya, dalam Office 365, SharePoint Online dan Excel Web App had saiz fail Excel untuk 10 MB. Untuk model data buku kerja yang mengandungi jutaan baris, anda akan menghadapi had 10 MB cukup dengan cepat. Lihat spesifikasi dan had Data Model.

Dalam artikel ini, anda akan mengetahui cara membina model padat yang mudah digunakan dan tidak memerlukan banyak memori. Mengambil masa untuk mengetahui mengenai amalan terbaik dalam reka bentuk model yang cekap mendatangkan manfaat daripada sebarang model yang anda cipta dan gunakan, sama ada anda melihatnya dalam Excel 2013, Office 365 SharePoint Online, pada Office Online Server atau dalam SharePoint 2013.

Pertimbangkan juga menjalankan Pengoptimum saiz buku kerja. Menganalisis buku kerja Excel anda dan jika boleh, memampatkannya selanjutnya. Muat turun Pengoptimum saiz buku kerja.

Dalam artikel ini

Nisbah pemampatan dan enjin analisis dalam memori

Tiada apa-apa yang lebih baik daripada lajur yang tidak wujud untuk penggunaan memori yang rendah

Dua contoh lajur yang selalunya patut dikecualikan

Cara mengecualikan lajur yang tidak diperlukan

Bagaimana jika menapis hanya baris yang diperlukan?

Bagaimana jika kami memerlukan lajur; Bolehkah kami mengurangkan kos Ruang?

Mengubah suai lajur Datetime

Mengubah suai pertanyaan SQL

Menggunakan ukuran selain daripada lajur terhitung DAX

2 lajur manakah yang patut anda simpan?

Kesimpulan

Pautan berkaitan

Nisbah pemampatan dan enjin analisis dalam memori

Model data dalam Excel menggunakan enjin analisis dalam memori untuk menyimpan data dalam memori. Enjin tersebut melaksanakan teknik pemampatan yang berkesan untuk mengurangkan keperluan storan, mengecilkan set hasil sehingga menjadi pecahan daripada saiz asalnya.

Secara purata, anda boleh menjangka model data menjadi 7 hingga 10 kali lebih kecil daripada saiz asalnya. Contohnya, jika anda mengimport 7 MB data dari pangkalan data SQL Server, model data dalam Excel boleh menjadi 1 MB atau kurang dengan mudah. Darjah pemampatan yang dicapai sebenarnya bergantung terutamanya pada bilangan nilai unik dalam setiap lajur. Lebih banyak nilai unik, lebih banyak memori diperlukan untuk menyimpannya.

Mengapakah kita berbincang mengenai pemampatan dan nilai unik? Kerana membina model berkesan yang meminimumkan penggunaan memori adalah berkisar tentang pemaksimuman pemampatan dan cara termudah untuk melakukannya adalah dengan membuang mana-mana lajur yang tidak diperlukan terutamanya lajur yang mengandungi bilangan nilai unik yang banyak.

Nota: Perbezaan dalam keperluan storan untuk lajur individu boleh menjadi sangat besar. Dalam sesetengah kes, adalah lebih baik untuk mempunyai berbilang lajur dengan bilangan nilai unik yang sedikit daripada mempunyai satu lajur dengan nilai unik yang banyak. Seksyen tentang pengoptimuman Datetime merangkumi teknik ini secara terperinci.

Tiada yang lebih baik daripada lajur yang tidak wujud untuk penggunaan memori yang rendah

Lajur memori paling cekap ialah lajur yang tidak pernah anda import sejak awal. Jika anda ingin membina model yang cekap, lihat pada setiap lajur dan tanya diri anda sama ada ia menyumbang kepada analisis yang anda ingin jalankan. Jika ia tidak menyumbang atau anda tidak pasti, biarkannya. Anda sentiasa boleh menambah lajur baru kemudian jika anda memerlukannya.

Dua contoh lajur yang selalunya patut dikecualikan

Contoh pertama berkaitan dengan data yang berasal dari gudang data. Dalam gudang data, ia perkara biasa untuk menemui artifak proses ETL yang memuatkan dan menyegar semula data dalam gudang. Lajur seperti “tarikh cipta”, “tarikh kemas kini” dan “ETL dijalankan” akan dicipta apabila data dimuatkan. Semua lajur ini tidak diperlukan dalam model dan hendaklah dikosongkan pilihannya apabila anda mengimport data.

Contoh kedua melibatkan pengabaian lajur kunci primer apabila mengimport jadual fakta.

Banyak jadual termasuk jadual fakta mempunyai kunci primer. Bagi kebanyakan jadual seperti jadual yang mengandungi data pelanggan, pekerja atau jualan, anda akan memerlukan kunci primer jadual agar anda boleh menggunakannya untuk mencipta perhubungan dalam model.

Ini berbeza dengan jadual fakta. Dalam jadual fakta, kunci primer digunakan untuk mengenal pasti setiap baris secara unik. Walaupun diperlukan untuk tujuan penormalan, ia kurang berguna dalam model data di mana anda hanya memerlukan lajur yang digunakan untuk analisis atau untuk membentuk perhubungan jadual. Atas sebab ini, apabila mengimport dari jadual fakta, jangan masukkan kunci primernya. Kunci primer dalam jadual fakta memakan ruang yang sangat besar dalam model, namun tidak memberi sebarang manfaat kerana ia tidak boleh digunakan untuk mencipta perhubungan.

Nota: Dalam gudang data dan pangkalan data berbilang dimensi, jadual besar yang kebanyakannya mengandungi data berangka biasanya dirujuk sebagai “jadual fakta”. Jadual fakta biasanya termasuk prestasi perniagaan atau data urus niaga seperti titik data jualan dan kos yang diagregatkan dan dijajarkan kepada unit organisasi, produk, segmen pasaran, wilayah geografi dan lain-lain. Semua lajur dalam jadual fakta yang mengandungi data perniagaan atau yang boleh digunakan untuk merujuk silang data yang disimpan dalam jadual lain hendaklah dimasukkan dalam model untuk menyokong analisis data. Lajur yang anda ingin kecualikan ialah lajur kunci primer jadual fakta yang mengandungi nilai unik yang hanya wujud dalam jadual fakta dan tiada di tempat lain. Oleh kerana jadual fakta sangat besar, sesetengah perolehan paling besar dalam kecekapan model diperoleh dengan mengecualikan baris atau lajur daripada jadual fakta.

Cara mengecualikan lajur yang tidak diperlukan

Model cekap mengandungi hanya lajur yang anda sebenarnya memerlukan dalam buku kerja anda. Jika anda ingin mengawal lajur yang termasuk dalam model, anda perlu menggunakan Bestari Import Jadual dalam Power Pivot tambahan untuk mengimport data daripada kotak dialog "Mengimport Data" dalam Excel.

Apabila anda memulakan Bestari Import Jadual, anda memilih jadual mana untuk diimport.

Bestari Import Jadual dalam tambahan PowerPivot

Bagi setiap jadual, anda boleh mengklik butang Pratonton & Penapis dan memilih bahagian jadual yang anda benar-benar perlukan. Kami syorkan agar anda mengosongkan tanda semak semua lajur terlebih dahulu, kemudian teruskan dengan menandakan lajur yang anda inginkan selepas mempertimbangkan sama ada ia diperlukan untuk analisis.

Anak tetingkap Pratonton dalam bestari Import Jadual

Bagaimana jika hanya menapis baris yang diperlukan?

Banyak jadual dalam pangkalan data korporat dan gudang data mengandungi data sejarah yang terkumpul untuk tempoh yang lama. Selain itu, anda mungkin mendapati jadual yang anda minati mengandungi maklumat bidang perniagaan yang tidak diperlukan untuk analisis khusus anda.

Menggunakan bestari Import Jadual, anda boleh menapis data sejarah atau data tidak berkaitan dan dengan ini menjimatkan banyak ruang dalam model. Dalam imej berikut, penapis tarikh digunakan untuk mendapatkan baris yang hanya mengandungi data untuk tahun semasa, tidak termasuk data sejarah yang tidak diperlukan.

Anak tetingkap Penapis dalam bestari Import Jadual

Bagaimana jika kami memerlukan lajur, bolehkah kami mengurangkan kos ruang?

Terdapat beberapa teknik tambahan yang anda boleh gunakan untuk menjadikan lajur lebih sesuai bagi pemampatan. Sila ingat bahawa satu-satunya ciri lajur yang mempengaruhi pemampatan ialah bilangan nilai uniknya. Dalam seksyen ini, anda akan mengetahui cara sesetengah lajur boleh diubah suai untuk mengurangkan bilangan nilai uniknya.

Mengubah suai lajur Datetime

Dalam kebanyakan kes, lajur Datetime memakan ruang yang banyak. Mujurnya, terdapat beberapa cara bagi mengurangkan keperluan storan untuk jenis data ini. Teknik ini berbeza mengikut cara anda menggunakan lajur dan keselesaan anda dalam membina pertanyaan SQL.

Lajur Datetime termasuk bahagian tarikh dan masa. Apabila anda bertanya diri anda sama ada anda memerlukan lajur, pertimbangkan soalan yang sama sebanyak beberapa kali untuk lajur Datetime:

  • Adakah saya perlukan bahagian masa?

  • Adakah saya perlukan bahagian masa pada peringkat jam? , minit? , Saat? , milisaat?

  • Adakah saya perlukan berbilang lajur Datetime kerana saya ingin mengira perbezaan antara lajur tersebut atau cuma untuk mengagregat data mengikut tahun, bulan, suku tahun dan sebagainya.

Cara anda menjawab setiap soalan ini akan menentukan opsyen anda untuk berurusan dengan lajur Datetime.

Semua penyelesaian ini memerlukan pengubahsuaian pertanyaan SQL. Untuk memudahkan lagi pengubahsuaian pertanyaan, anda harus menapis keluar sekurang-kurangnya satu lajur dalam setiap jadual. Dengan menapis keluar lajur, anda mengubah binaan pertanyaan daripada format singkatan (SELECT *) kepada kenyataan SELECT yang termasuk nama lajur yang layak sepenuhnya, yang jauh lebih mudah untuk diubah suai.

Mari kita lihat pertanyaan yang telah dicipta untuk anda. Dari kotak dialog Sifat Jadual, anda boleh bertukar kepada editor Pertanyaan dan melihat pertanyaan SQL semasa untuk setiap jadual.

Reben dalam tetingkap PowerPivot menunjukkan perintah Sifat Jadual

Dari Sifat Jadual, pilih Editor Pertanyaan.

Buka Editor Pertanyaan dari dialog Sifat Jadual

Editor Pertanyaan menunjukkan pertanyaan SQL yang digunakan untuk mengisi jadual. Jika anda menapis keluar sebarang lajur semasa mengimport, pertanyaan anda termasuk nama lajur yang layak sepenuhnya:

Pertanyaan SQL digunakan untuk mendapatkan data

Sebaliknya, jika anda mengimport seluruh jadual, tanpa mengosongkan tanda semak sebarang lajur atau menggunakan sebarang penapis, anda akan melihat pertanyaan sebagai “Pilih * dari ”, yang akan menjadi lebih sukar untuk diubah suai:

Pertanyaan SQL menggunakan sintaks lalai dan lebih pendek

Mengubah suai pertanyaan SQL

Kini setelah anda tahu cara mencari pertanyaan, anda boleh mengubah suainya untuk mengurangkan lagi saiz model anda.

  1. Untuk lajur yang mengandungi mata wang atau data perpuluhan, jika anda tidak memerlukan perpuluhan, gunakan sintaks ini untuk membuang perpuluhan:

    “SELECT ROUND([Decimal_column_name],0)… .”

    Jika anda perlukan sen tetapi bukan pecahan sen, gantikan 0 dengan 2. Jika anda menggunakan nombor negatif, anda boleh membundarkan kepada unit, puluh, ratus dll.

  2. Jika lajur Datetime anda bernama dbo.Bigtable.[Date Time] dan anda tidak perlukan bahagian Masa, gunakan sintaks untuk membuang masa tersebut:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Jika lajur Datetime anda bernama dbo.Bigtable.[Date Time] dan anda perlukan kedua-dua bahagian Tarikh dan Masa, gunakan berbilang lajur dalam pertanyaan SQL bukannya lajur Datetime tunggal:

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Gunakan sebanyak mana lajur yang anda perlukan untuk menyimpan setiap bahagian dalam lajur berasingan.

  4. Jika anda memerlukan jam dan minit dan anda lebih sukakannya bersama sebagai lajur satu masa, anda boleh gunakan sintaks :

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Jika anda mempunyai dua lajur datetime, seperti [Masa Mula] dan [Masa Tamat], dan apa yang anda perlukan sebenarnya ialah perbezaan masa antaranya dalam saat sebagai satu lajur dipanggil [Tempoh], alih keluar kedua-dua lajur dari senarai dan tambahkan:

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    Jika anda menggunakan kata kunci ms bukannya ss, tempohnya menjadi milisaat

Menggunakan ukuran terhitung DAX selain daripada lajur

Jika anda pernah bekerja dengan bahasa ungkapan DAX sebelum ini, anda mungkin sudah tahu bahawa lajur terhitung digunakan untuk memperoleh lajur baru berdasarkan beberapa lajur lain dalam model, manakala ukuran terhitung ditakrifkan sekali dalam model tetapi hanya dinilai apabila digunakan dalam Jadual Pangsi atau laporan lain.

Salah satu teknik menjimatkan memori adalah menggantikan lajur biasa atau terhitung dengan ukuran terhitung. Contoh klasik ialah Harga Unit, Kuantiti dan Jumlah. Jika anda mempunyai ketiga-tiganya, anda boleh menjimatkan ruang dengan mengekalkan dua daripadanya dan menghitung yang ketiga menggunakan DAX.

2 lajur manakah yang patut anda simpan?

Dalam contoh di atas, simpan Kuantiti dan Harga Unit. Dua lajur ini mempunyai kurang nilai berbanding Jumlah. Untuk menghitung Jumlah, tambahkan ukuran terhitung seperti:

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”

Lajur terhitung sama seperti lajur biasa iaitu kedua-duanya memakan ruang dalam model. Sebaliknya, ukuran terhitung dikira dengan cepat dan tidak memakan ruang.

Kesimpulan

Dalam artikel ini, kita berbincang mengenai beberapa pendekatan yang dapat membantu anda membina model yang lebih cekap memori. Cara mengurangkan saiz fail dan keperluan memori model data ialah dengan mengurangkan bilangan keseluruhan lajur dan baris serta bilangan nilai unik yang muncul dalam setiap lajur. Berikut ialah beberapa teknik yang diliputi:

  • Mengalih keluar lajur sememangnya cara terbaik untuk menjimatkan ruang. Tentukan lajur mana yang anda benar-benar perlukan.

  • Kadang kala anda boleh mengalih keluar lajur dan menggantikannya dengan ukuran terhitung dalam jadual.

  • Anda mungkin tidak memerlukan semua baris dalam jadual. Anda boleh menapis keluar baris dalam Bestari Import Jadual.

  • Secara umum, memisahkan lajur tunggal ke dalam berbilang bahagian berbeza ialah cara yang baik untuk mengurangkan bilangan nilai unik dalam sesuatu lajur. Setiap satu bahagian akan mempunyai bilangan nilai unik yang kecil dan jumlah tergabung akan menjadi lebih kecil daripada lajur disatukan yang asal.

  • Dalam kebanyakan kes, anda juga memerlukan bahagian berbeza ini untuk digunakan sebagai penghiris dalam laporan anda. Apabila sesuai, anda boleh mencipta hierarki daripada bahagian seperti Jam, Minit dan Saat.

  • Sering kali, lajur mengandungi lebih banyak maklumat daripada apa yang anda perlukan. Contohnya, katakan lajur menyimpan perpuluhan tetapi anda telah menggunakan pemformatan untuk menyembunyikan semua perpuluhan. Pembundaran sangat berkesan untuk mengurangkan saiz lajur berangka.

Sekarang bahawa anda telah selesai apa yang anda boleh mengurangkan saiz buku kerja anda, pertimbangkan juga menjalankan Pengoptimum saiz buku kerja. Menganalisis buku kerja Excel anda dan jika boleh, memampatkannya selanjutnya. Muat turun Pengoptimum saiz buku kerja.

Pautan berkaitan

Spesifikasi dan had Model Data

Muat turun Pengoptimum saiz buku kerja

Power Pivot: Analisis data yang berkesan dan pemodelan data dalam Excel

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×