إنشاء نموذج بيانات بكفاءة في الذاكرة باستخدام Excel والوظيفة الاضافيه Power Pivot

في Excel 2013 أو الإصدارات الأحدث ، يمكنك إنشاء نماذج بيانات تحتوي علي ملايين الصفوف ، ثم اجراء تحليل فعال للبيانات مقابل هذه النماذج. يمكن إنشاء نماذج البيانات باستخدام الوظيفة الاضافيه Power Pivot أو بدونها لدعم اي عدد من PivotTables والمخططات ومرئيات Power View في المصنف نفسه.

ملاحظة: تصف هذه المقالة نماذج البيانات في Excel 2013. على الرغم من ذلك، تطبق نفس نماذج البيانات وميزات Power Pivot في Excel 2013 على Excel 2016. يوجد اختلافاً صغيراً فعالاً بين هذه الإصدارات من Excel.

علي الرغم من انه يمكنك بسهوله إنشاء نماذج بيانات ضخمه في Excel ، الا انه هناك عده أسباب لا يمكنها القيام بذلك. يتم إنهاء النماذج الكبيرة التي تحتوي علي مولتيتوديس من الجداول والاعمده لمعظم التحليلات ، والعمل علي قائمه حقول بطيئا. في الثانية ، تستخدم النماذج الكبيرة الذاكرة القيمة ، والتي تؤثر سلبيا علي التطبيقات والتقارير الأخرى التي تشترك في نفس موارد النظام. وأخيرا ، في Office 365 ، يقوم كل من SharePoint Online و Excel Web App بتحديد حجم ملف Excel إلى 10 ميغابايت. بالنسبة إلى نماذج البيانات الخاصة بالمصنف التي تحتوي علي ملايين الصفوف ، سيتم تشغيلها بسرعة 10 ميجا بايت علي حده. راجع مواصفات نموذج البيانات وحدوده.

في هذه المقالة ، ستتعلم كيفيه إنشاء نموذج تم إنشاؤه بشكل محكم يسهل العمل عليه ويستخدم ذاكره اقل. سيؤدي تخصيص الوقت للتعرف علي أفضل الممارسات في تصميم النماذج الفعالة إلى الدفع بسرعة لأي نموذج تقوم بإنشاءه واستخدامه ، سواء كنت تقوم بعرضه في Excel 2013 أو Office 365 SharePoint Online أو علي Office Online خادم أو في SharePoint 2013.

يمكنك أيضاً تشغيل Workbook Size Optimizer. فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. تنزيل محسن حجم المصنف.

في هذه المقالة

نسب الضغط ومحرك التحليلات في الذاكرة

لا شيء يفوق إمكانيات عمود غير موجود لاستخدام الذاكرة المنخفضة

مثالان للاعمده التي يجب استبعادها دائما

كيفيه استبعاد أعمده غير ضرورية

ماذا عن تصفيه الصفوف الضرورية فقط ؟

ماذا افعل إذا احتجت إلى العمود ؛ هل لا يزال بإمكانك تقليل تكلفه المساحة الخاصة بها ؟

تعديل أعمده التاريخ/الوقت

تعديل استعلام SQL

استخدام القياسات المحسوبة ل DAX بدلا من الاعمده

اي عمودين يجب الاحتفاظ بهما ؟

الخاتمة

ارتباطات ذات صلة

نسب الضغط ومحرك التحليلات في الذاكرة

تستخدم نماذج البيانات في Excel مشغل التحليلات في الذاكرة لتخزين البيانات في الذاكرة. ينفذ المشغل تقنيات ضغط فعاله لتقليل متطلبات التخزين ، مع تقليص مجموعه النتائج حتى تصبح بحجمها الأصلي.

بالنسبة إلى المتوسط ، يمكنك ان تتوقع ان يكون نموذج البيانات هو 7 إلى 10 مرات أصغر من البيانات نفسها عند نقطه الأصل الخاصة به. علي سبيل المثال ، إذا كنت تقوم باستيراد 7 ميغابايت من البيانات من قاعده بيانات SQL Server ، فبامكان نموذج البيانات في Excel بسهوله الوصول إلى 1 ميغابايت أو اقل. تتوقف درجه الضغط التي تم إنجازها فعليا علي عدد القيم الفريدة في كل عمود. القيم الفريدة ، والمزيد من الذاكرة مطلوبه لتخزينها.

لماذا تحدث عن الضغط والقيم الفريدة ؟ نظرا لان إنشاء نموذج فعال يؤدي إلى تصغير استخدام الذاكرة هو كل شيء حول ماكسيميزيشن الضغط ، والطريقة الأسهل لاجراء ذلك هو التخلص من اي أعمده لم تكن بحاجه اليها ، خاصه إذا كانت هذه الاعمده تتضمن عددا كبيرا من القيم الفريدة.

ملاحظة:  يمكن ان تكون الاختلافات في متطلبات التخزين للاعمده الفردية ضخمه. في بعض الحالات ، من الأفضل ان يكون لديك أعمده متعددة ذات عدد قليل من القيم الفريدة بدلا من عمود واحد يحتوي علي عدد كبير من القيم الفريدة. يغطي القسم الذي يتم تحسينه لهذه التقنية بالتفصيل.

لا شيء يفوق إمكانيات عمود غير موجود لاستخدام الذاكرة المنخفضة

العمود الأكثر فعاليه للذاكرة هو نفسه الذي لم تقم باستيراده في المكان الأول. إذا كنت تريد إنشاء نموذج فعال ، فالق نظره علي كل عمود واطرح عليك ما إذا كنت تساهم في التحليل الذي تريد تنفيذه. إذا لم يكن الأمر كذلك أو لم تكن متاكدا ، فاتركه. يمكنك دائما أضافه أعمده جديده لاحقا إذا كنت بحاجه اليها.

مثالان للاعمده التي يجب استبعادها دائما

يتعلق المثال الأول بالبيانات التي تنشا من مستودع البيانات. في مستودع البيانات ، من الشائع العثور علي نتائج عمليات اتل التي تقوم بتحميل البيانات وتحديثها في المستودع. يتم إنشاء الاعمده مثل "تاريخ الإنشاء" و "تاريخ التحديث" و "اتل run" عند تحميل البيانات. لا حاجه اي من هذه الاعمده في النموذج ويجب إلغاء تحديدها عند استيراد البيانات.

يتضمن المثال الثاني حذف عمود المفتاح الأساسي عند استيراد جدول حقائق.

العديد من الجداول ، بما في ذلك جداول الحقائق ، لديها مفاتيح أساسيه. بالنسبة إلى معظم الجداول ، مثل تلك التي تحتوي علي بيانات العميل أو الموظف أو المبيعات ، ستحتاج إلى المفتاح الأساسي الخاص بالجدول بحيث يمكنك استخدامه لإنشاء علاقات في النموذج.

جداول الحقائق مختلفه. في جدول حقائق ، يتم استخدام المفتاح الأساسي لتعريف كل صف بشكل فريد. عند الضرورة لأغراض التسوية ، يكون من المفيد في نموذج البيانات المكان الذي تريده فقط للاعمده المستخدمة للتحليل أو لإنشاء علاقات الجداول. لهذا السبب ، لا تتضمن المفتاح الأساسي الخاص به عند الاستيراد من جدول حقائق. تستهلك المفاتيح الاساسيه في جدول الحقائق كميات هائل من المساحة في النموذج ، ولا توفر اي فائده ، لأنه لا يمكن استخدامها لإنشاء علاقات.

ملاحظة:  في مستودعات البيانات وقواعد بيانات متعددة الابعاد ، يشار غالبا إلى الجداول الكبيرة التي تتكون من البيانات الرقمية باسم "جداول الحقائق". تتضمن جداول الحقائق عاده بيانات أداء الاعمال أو المعاملات ، مثل نقاط بيانات المبيعات والتكلفة التي يتم تجميعها ومحاذاتها إلى الوحدات التنظيمية والمنتجات ومقاطع السوق والمناطق الجغرافية وما إلى ذلك. يجب تضمين كافة الاعمده الموجودة في جدول الحقائق التي تحتوي علي بيانات الاعمال أو التي يمكن استخدامها للبيانات المرجعية المتقاطعة المخزنة في الجداول الأخرى في النموذج لدعم تحليل البيانات. العمود الذي تريد استبعاده هو عمود المفتاح الأساسي لجدول الحقائق ، والذي يتكون من قيم فريدة موجودة فقط في جدول الحقائق والمهجورات الأخرى. نظرا لان جداول الحقائق تعتبر ضخمه ، فان بعض الأرباح الكبرى في الكفاءة الانتاجيه مشتقه من استثناء الصفوف أو الاعمده من جداول الحقائق.

كيفيه استبعاد أعمده غير ضرورية

تحتوي النماذج الفعالة فقط علي الاعمده التي تحتاج اليها في المصنف. إذا أردت التحكم في الاعمده المضمنة في النموذج ، سيتعين عليك استخدام "معالج استيراد الجدول" في الوظيفة الاضافيه Power Pivot لاستيراد البيانات بدلا من مربع الحوار "استيراد البيانات" في Excel.

عند بدء تشغيل "معالج استيراد الجدول" ، يمكنك تحديد الجداول المراد استيرادها.

معالج استيراد الجدول في الوظيفة الإضافية PowerPivot

بالنسبة إلى كل جدول ، يمكنك النقر فوق الزر معاينه & عامل التصفية وتحديد أجزاء الجدول التي تريدها فعلا. من المستحسن أولا إلغاء تحديد كل الاعمده ، ثم المتابعة للتحقق من الاعمده التي تريدها ، بعد التفكير في ما إذا كانت مطلوبه للتحليل.

جزء المعاينة في معالج استيراد الجدول

ماذا عن تصفيه الصفوف الضرورية فقط ؟

تحتوي العديد من الجداول في قواعد بيانات الشركات ومستودعاتها علي بيانات تاريخيه متراكمة في فترات طويلة من الوقت. بالاضافه إلى ذلك ، قد تجد ان الجداول التي تهمك تحتوي علي معلومات لمناطق العمل غير المطلوبة للتحليل الخاص بك.

باستخدام "معالج استيراد الجدول" ، يمكنك تصفيه البيانات القديمة أو المرتبطة بها ، التالي حفظ مساحة كبيره في النموذج. في الصورة التالية ، يتم استخدام عامل تصفيه التاريخ لاسترداد الصفوف التي تحتوي علي البيانات الخاصة بالسنه الحالية فقط ، باستثناء البيانات التاريخية التي لن تتم الحاجة اليها.

جزء التصفية في معالج استيراد الجدول

ماذا افعل إذا احتجت إلى العمود ؛ هل لا يزال بإمكانك تقليل تكلفه المساحة الخاصة بها ؟

هناك بعض التقنيات الاضافيه التي يمكنك تطبيقها علي جعل العمود مرشحا أفضل للضغط. تذكر ان الصف الوحيد للعمود الذي يؤثر علي الضغط هو عدد القيم الفريدة. في هذا القسم ، ستتعرف علي كيفيه تعديل بعض الاعمده لتقليل عدد القيم الفريدة.

تعديل أعمده التاريخ/الوقت

في بعض الحالات ، تاخذ أعمده التاريخ والوقت مساحة كبيره. الحظ ، هناك عدد من الطرق لتقليل متطلبات التخزين لنوع البيانات هذا. تتباين التقنيات تعتمد علي كيفيه استخدام العمود ، ومستوي الراحة الخاصة بك في إنشاء استعلامات SQL.

تتضمن أعمده Datetime جزء تاريخ ووقت. عندما تسال نفسك عما إذا كنت بحاجه إلى عمود ، اطرح السؤال نفسه عده مرات لعمود التاريخ والوقت:

  • هل احتاج إلى جزء الوقت ؟

  • هل احتاج إلى جزء الوقت في مستوي الساعات ؟ تستغرق? /? جزء?

  • هل لدي أعمده التاريخ والوقت المتعددة لأني أريد حساب الفرق بينها ، أو فقط لتجميع البيانات حسب السنه والشهر وربع السنه وما إلى ذلك.

كيفيه الرد علي كل من هذه الاسئله يحدد الخيارات المتعلقة بالتعامل مع عمود التاريخ والوقت.

تتطلب كل هذه الحلول تعديلا في استعلام SQL. لتسهيل عمليه تعديل الاستعلام ، يجب عليك تصفيه عمود واحد علي الأقل في كل جدول. بتصفية عمود ، يمكنك تغيير إنشاء الاستعلام من تنسيق مختصر (حدد *) إلى جمله SELECT التي تتضمن أسماء أعمده مؤهله بالبالكامل ، والتي من السهل تعديلها.

دعنا نتعرف علي الاستعلامات التي تم إنشاؤها لك. من مربع الحوار خصائص الجدول ، يمكنك التبديل إلى محرر الاستعلام ورؤية استعلام SQL الحالي لكل جدول.

الشريط في نافذة Power Pivot يعرض الأمر "خصائص الجدول"

من خصائص الجدول ، حدد محرر الاستعلام.

فتح "محرر الاستعلام" من مربع الحوار "خصائص الجدول"

يعرض "محرر الاستعلام" استعلام SQL المستخدم لملء الجدول. إذا قمت بتصفية اي عمود اثناء الاستيراد ، سيتضمن الاستعلام أسماء أعمده مؤهله بالبالكامل:

استعلام SQL المستخدم لاسترداد البيانات

وفي المقابل ، إذا قمت باستيراد جدول في مجمله ، بدون إلغاء تحديد اي عمود أو تطبيق اي عامل تصفيه ، ستشاهد الاستعلام ك "Select * from" ، مما سيكون أكثر صعوبة لتعديله:

استعلام SQL يستخدم بناء الجملة الافتراضية الأقصر

تعديل استعلام SQL

الآن وقد تعرفت علي كيفيه العثور علي الاستعلام ، يمكنك تعديله لتقليل حجم النموذج بشكل أكبر.

  1. بالنسبة إلى الاعمده التي تحتوي علي العملة أو البيانات العشرية ، إذا لم تكن بحاجه إلى المنازل العشرية ، فاستخدم بناء الجملة هذا للتخلص من الأرقام العشرية:

    "تحديد ROUND ([Decimal_column_name], 0)... .”

    إذا كنت بحاجه إلى سنت وليست كسور السنت ، فاستبدل القيمة 0 في 2. إذا كنت تستخدم أرقاما سالبه ، فيمكنك التقريب إلى الوحدات ، والعشرات ، والمئات ، وما إلى ذلك.

  2. إذا كان لديك عمود Datetime يسمي dbo.bigtable. Dbo.bigtable. [Date Time] ولم تكن بحاجه إلى جزء الوقت ، استخدم بناء الجملة للتخلص من الوقت:

    "حدد CAST (dbo.bigtable. Dbo.bigtable. [Date time] كتاريخ) ك [Date time]) "

  3. إذا كان لديك عمود Datetime يسمي dbo.bigtable. Dbo.bigtable. [Date Time] وتحتاج إلى جزئي التاريخ والوقت ، استخدم أعمده متعددة في استعلام SQL بدلا من عمود Datetime الواحد:

    "حدد CAST (dbo.bigtable. Dbo.bigtable. [تاريخ الوقت] كتاريخ) ك [تاريخ الوقت] ،

    datepart (hh, dbo.bigtable. Dbo.bigtable. [Date Time]) ك [تاريخ الوقت والساعات] ،

    datepart (dbo.bigtable. Dbo.bigtable. [Date Time]) ك [تاريخ وقت الدقائق] ،

    datepart (ss, dbo.bigtable. Dbo.bigtable. [Date Time]) ك [التاريخ والوقت] ،

    datepart (ms, dbo.bigtable. Dbo.bigtable. [Date Time]) ك [تاريخ الوقت بالمللي ثانيه] "

    استخدم أيا من الاعمده التي تحتاجها لتخزين كل جزء في أعمده منفصلة.

  4. إذا كنت بحاجه إلى ساعات ودقائق ، وكانت تفضلها معا كعمود زمني واحد ، فيمكنك استخدام بناء الجملة:

    تيميفرومبارتس (datepart (hh, dbo.bigtable. Dbo.bigtable. [Date Time]), datepart (mm, dbo.bigtable. Dbo.bigtable. [Date Time]))) ك [Date Time هورمينوتي]

  5. إذا كان لديك عمودان للتاريخ والوقت ، مثل [وقت البدء] و [وقت الانتهاء] ، وما تحتاج اليه هو اختلاف الوقت بينهما بالثواني كعمود يسمي [Duration] ، فقم بازاله كلا العمودين من القائمة وأضافه:

    "datediff (ss, [تاريخ البدء], [تاريخ الانتهاء]) ك [المدة]"

    إذا كنت تستخدم الكلمة الاساسيه ms ، ستحصل علي المدة بالمللي ثانيه

استخدام القياسات المحسوبة ل DAX بدلا من الاعمده

إذا قمت بالعمل باستخدام لغة تعبير DAX من قبل ، فقد تكون قد تعرفت بالفعل انه قد تم استخدام الاعمده المحسوبة لاشتقاق الاعمده الجديدة استنادا إلى عمود آخر في النموذج ، بينما يتم تعريف المقاييس المحسوبة مره واحده في النموذج ، ولكن يتم تقييمها فقط عند استخدامها في PivotTable أو تقرير آخر.

أسلوب توفير الذاكرة الواحدة هو استبدال الاعمده العادية أو المحسوبة بالمقاييس المحسوبة. المثال الكلاسيكي هو سعر الوحدة والكمية والإجمالي. إذا كان لديك ثلاثه ، فيمكنك توفير مساحة بالاحتفاظ فقط باثنين وحساب الشخص الثالث باستخدام DAX.

اي عمودين يجب الاحتفاظ بهما ؟

في المثال أعلاه ، احتفظ بالكمية وسعر الوحدة. يتضمن هذين القيمتين من القيم الأقل من الإجمالي. لحساب الإجمالي ، أضف مقياسا محسوبا مثل:

"TotalSales: = sumx (' جدول المبيعات ', ' جدول المبيعات ' [سعر الوحدة] * ' جدول المبيعات ' [Quantity])"

الاعمده المحسوبة تشبه الاعمده العادية في ان كلا الأمرين يستهلك مساحة في النموذج. بالتباين ، يتم حساب المقاييس المحسوبة علي الحركة ولا تاخذ مساحة.

الخاتمة

في هذه المقالة ، تحدثنا حول العديد من الطرق التي يمكن ان تساعدك علي إنشاء نموذج الذاكرة بكفاءة أكبر. تتمثل الطريقة التي يمكن من خلالها تقليل حجم الملف ومتطلبات الذاكرة لنموذج بيانات في تقليل عدد الاعمده والصفوف الاجماليه ، وعدد القيم الفريدة التي تظهر في كل عمود. اليك بعض التقنيات التي تناولناها:

  • أزاله الاعمده هو بدوره تدريبيه أفضل طريقه لتوفير المساحة. حدد الاعمده التي تحتاجها فعلا.

  • في بعض الأحيان ، يمكنك أزاله عمود واستبداله بمقياس محسوب في الجدول.

  • قد لا تحتاج إلى كل الصفوف في جدول. يمكنك تصفيه الصفوف في معالج استيراد الجدول.

  • بشكل عام ، ان تقسيم عمود واحد إلى عده أجزاء مختلفه هو طريقه جيده لتقليل عدد القيم الفريدة في عمود. سيكون لكل من الجزءين عدد قليل من القيم الفريدة ، سيكون الإجمالي الموحد أصغر من العمود الموحد الأصلي.

  • في حالات كثيره ، تحتاج أيضا إلى الأجزاء المميزة التي يجب استخدامها كمقسمات طرق عرض في تقاريرك. عند الضرورة ، يمكنك إنشاء تسلسلات هيكليه من أجزاء مثل الساعات والدقائق والثواني.

  • لعده مرات ، تحتوي الاعمده علي المزيد من المعلومات عن تلك التي تحتاجها أيضا. علي سبيل المثال ، افترض ان العمود يخزن الأرقام العشرية ، ولكنك طبقت تنسيقا لإخفاء كل المنازل العشرية. يمكن ان تكون التقريب فعاله جدا في تقليل حجم عمود رقمي.

الآن وقد قمت باجراء ما يمكنك تقليل حجم المصنف ، فضع في اعتبارك أيضا تشغيل محسن حجم المصنف. فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. تنزيل محسن حجم المصنف.

ارتباطات ذات صلة

مواصفات نموذج البيانات وحدوده

تنزيل محسن لحجم المصنف

PowerPivot: التحليل الفعّال للبيانات وإنشاء نماذج بيانات في Excel

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×