في Excel، يمكنك إنشاء نماذج بيانات تحتوي على الملايين من الصفوف، ثم إجراء تحليل فعال للبيانات مقابل هذه النماذج. يمكن إنشاء نماذج البيانات مع الوظيفة الإضافية Power Pivot أو بدونها لدعم أي عدد من جداول PivotTable والمخططات ومرئيات Power View في المصنف نفسه.
على الرغم من أنه يمكنك بسهولة إنشاء نماذج بيانات ضخمة في Excel، إلا أن هناك عدة أسباب لعدم القيام بذلك. أولا ، النماذج الكبيرة التي تحتوي على العديد من الجداول والأعمدة مبالغ فيها لمعظم التحليلات ، وتجعل قائمة الحقول مرهقة. ثانيا، تستخدم النماذج الكبيرة ذاكرة قيمة، مما يؤثر سلبا على التطبيقات والتقارير الأخرى التي تتشارك في نفس موارد النظام. أخيرا، في Microsoft 365، يحدد كل من SharePoint Online وExcel Web App حجم ملف Excel ل 10 ميغابايت. بالنسبة إلى نماذج بيانات المصنف التي تحتوي على الملايين من الصفوف، ستصل إلى حد 10 ميغابايت بسرعة كبيرة. راجع مواصفات نموذج البيانات وحدوده.
في هذه المقالة، ستتعرف على كيفية إنشاء نموذج محكم البناء يسهل استخدامه ويستخدم ذاكرة أقل. إن قضاء بعض الوقت في تعلم أفضل الممارسات في تصميم النموذج الفعال سيؤتي ثماره في المستقبل لأي نموذج تقوم بإنشائه واستخدامه، سواء كنت تعرضه في Excel أو Microsoft 365 SharePoint Online أو على خادم Office Web Apps أو في SharePoint.
يمكنك أيضاً تشغيل Workbook Size Optimizer. فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. قم بتنزيل Workbook Size Optimizer.
في هذه المقالة
نسب الضغط ومحرك التحليلات داخل الذاكرة
تستخدم نماذج البيانات في Excel محرك التحليلات في الذاكرة لتخزين البيانات في الذاكرة. ينفذ المحرك تقنيات ضغط قوية لتقليل متطلبات التخزين، مما يؤدي إلى تقليص مجموعة النتائج حتى تصبح جزءا صغيرا من حجمها الأصلي.
في المتوسط، يمكنك توقع أن يكون نموذج البيانات أصغر من البيانات نفسها في نقطة أصله ب 7 إلى 10 أضعاف. على سبيل المثال، إذا كنت تستورد 7 ميغابايت من البيانات من قاعدة بيانات SQL Server، فقد يصل حجم نموذج البيانات في Excel بسهولة إلى 1 ميغابايت أو أقل. تعتمد درجة الضغط التي تم تحقيقها فعليا بشكل أساسي على عدد القيم الفريدة في كل عمود. كلما زادت القيم الفريدة، تطلب تخزين المزيد من الذاكرة.
لماذا نتحدث عن الضغط والقيم الفريدة؟ لأن بناء نموذج فعال يقلل من استخدام الذاكرة يتعلق بتكبير الضغط ، وأسهل طريقة للقيام بذلك هي التخلص من أي أعمدة لا تحتاجها بالفعل ، خاصة إذا كانت هذه الأعمدة تحتوي على عدد كبير من القيم الفريدة.
ملاحظة
يمكن أن تكون الاختلافات في متطلبات التخزين للأعمدة الفردية كبيرة. في بعض الحالات، من الأفضل أن تتضمن أعمدة متعددة ذات عدد قليل من القيم الفريدة بدلا من عمود واحد يحتوي على عدد كبير من القيم الفريدة. يغطي القسم الخاص بتحسينات Datetime هذه التقنية بالتفصيل.
لا شيء يضاهي عمود غير موجود نظرا لانخفاض استخدام الذاكرة
العمود الأكثر كفاءة في استخدام الذاكرة هو العمود الذي لم تقم باستيراده من قبل. إذا كنت تريد إنشاء نموذج فعال، فانظر إلى كل عمود واسأل نفسك ما إذا كان يساهم في التحليل الذي تريد تنفيذه. إذا لم يحدث أو كنت غير متأكد، فاتركه. يمكنك دائما إضافة أعمدة جديدة لاحقا إذا احتجت إليها.
مثالان للأعمدة التي يجب استبعادها دائما
يتعلق المثال الأول بالبيانات التي تنشأ من مستودع بيانات. في مستودع البيانات، من الشائع العثور على سلوكيات عمليات ETL التي تقوم بتحميل البيانات وتحديثها في المستودع. يتم إنشاء أعمدة مثل "تاريخ الإنشاء" و"تاريخ التحديث" و"تشغيل ETL" عند تحميل البيانات. لا حاجة إلى أي من هذه الأعمدة في النموذج ويجب إلغاء تحديده عند استيراد البيانات.
يتضمن المثال الثاني حذف عمود المفتاح الأساسي عند استيراد جدول حقائق.
تحتوي العديد من الجداول، بما فيها جداول الحقائق، على مفاتيح أساسية. بالنسبة إلى معظم الجداول، كتلك التي تحتوي على بيانات العملاء أو الموظفين أو المبيعات، ستحتاج إلى المفتاح الأساسي للجدول بحيث يمكنك استخدامه لإنشاء علاقات في النموذج.
جداول الحقائق مختلفة. في جدول الحقائق، يستخدم المفتاح الأساسي لتعريف كل صف بشكل فريد. على الرغم من ضرورة ذلك لأغراض التسوية، إلا أنه أقل فائدة في نموذج البيانات حيث تريد استخدام هذه الأعمدة فقط للتحليل أو لإنشاء علاقات بين الجداول. لهذا السبب، عند الاستيراد من جدول حقائق، لا تقم بتضمين المفتاح الأساسي الخاص به. تستهلك المفاتيح الأساسية في جدول الحقائق مساحات هائلة في النموذج، ولكنها لا توفر أي فائدة، حيث لا يمكن استخدامها لإنشاء علاقات.
ملاحظة
في مستودعات البيانات وقواعد البيانات المتعددة الأبعاد، غالبا ما يشار إلى الجداول الكبيرة التي تتكون في الغالب من بيانات رقمية باسم "جداول الحقائق". تتضمن جداول الحقائق عادة أداء الأعمال أو بيانات المعاملات، مثل نقاط بيانات المبيعات والتكلفة التي يتم تجميعها ومحاذاتها مع وحدات المؤسسة والمنتجات وقطاعات السوق والمناطق الجغرافية، وما إلى ذلك. يجب تضمين كل الأعمدة الموجودة في جدول الحقائق والتي تحتوي على بيانات العمل أو التي يمكن استخدامها لإسناد ترافقي للبيانات المخزنة في جداول أخرى في النموذج لدعم تحليل البيانات. العمود الذي تريد استبعاده هو عمود المفتاح الأساسي لجدول الحقائق، الذي يتكون من قيم فريدة موجودة في جدول الحقائق فقط وليس في أي مكان آخر. نظرا لأن جداول الحقائق ضخمة جدا، فإن بعضا من أكبر المكاسب في فعالية النموذج يتم اشتقاقها من استبعاد الصفوف أو الأعمدة من جداول الحقائق.
كيفية استبعاد الأعمدة غير الضرورية
لا تحتوي النماذج الفعالة إلا على الأعمدة التي ستحتاج إليها فعليا في المصنف. إذا كنت تريد التحكم في الأعمدة المضمنة في النموذج، فيجب استخدام معالج استيراد الجدول في الوظيفة الإضافية Power Pivot لاستيراد البيانات بدلا من استخدام مربع الحوار "استيراد البيانات" في Excel.
عند بدء تشغيل معالج استيراد الجدول، ستحدد الجداول التي تريد استيرادها.
لكل جدول، يمكنك النقر فوق الزر معاينة & عامل تصفية وتحديد أجزاء الجدول التي تحتاجها فعلا. نوصي بأن تقوم أولا بإلغاء تحديد كافة الأعمدة، ثم المتابعة لفحص الأعمدة التي تريدها، بعد التفكير فيما إذا كانت مطلوبة للتحليل أم لا.
ماذا عن تصفية الصفوف الضرورية فقط؟
يحتوي عدد كبير من الجداول في قواعد بيانات الشركة ومستودعات البيانات على بيانات تاريخية متراكمة على مدى فترات طويلة من الزمن. بالإضافة إلى ذلك، قد يتبين لك أن الجداول التي تهتم بها تحتوي على معلومات لمجالات العمل غير المطلوبة لتحليلك المحدد.
باستخدام معالج استيراد الجدول، يمكنك تصفية البيانات التاريخية أو غير المرتبطة، وبالتالي توفير مساحة كبيرة في النموذج. في الصورة التالية، يتم استخدام عامل تصفية التاريخ لاسترداد الصفوف التي تحتوي على بيانات عن السنة الحالية فقط، باستثناء البيانات القديمة التي لن تكون مطلوبة.
ماذا لو احتجنا إلى العمود. هل لا يزال بإمكاننا تقليل تكلفة المساحة؟
هناك بعض التقنيات الإضافية التي يمكنك تطبيقها لجعل العمود مرشحا أفضل للضغط. تذكر أن السمة الوحيدة للعمود التي تؤثر في الضغط هي عدد القيم الفريدة. في هذا القسم، ستتعلم كيف يمكن تعديل بعض الأعمدة لتقليل عدد القيم الفريدة.
تعديل أعمدة Datetime
في العديد من الحالات، تأخذ أعمدة Datetime مساحة كبيرة. لحسن الحظ، هناك عدد من الطرق لتقليل متطلبات التخزين لهذا النوع من البيانات. ستختلف التقنيات اعتمادا على كيفية استخدامك للعمود ومستوى راحتك في إنشاء استعلامات SQL.
تتضمن أعمدة التاريخ والوقت جزءا ووقتا وتاريخا. عندما تسأل نفسك ما إذا كنت بحاجة إلى عمود، اطرح السؤال نفسه عدة مرات لعمود وقت التاريخ:
- هل أحتاج إلى جزء الوقت؟
- هل أحتاج إلى جزء الوقت على مستوى الساعات؟ ، بالدقائق؟ ، الثواني؟ ، مللي ثانية؟
- هل لدي أعمدة تاريخ ووقت متعددة لأنني أريد حساب الفرق بينها أو تجميع البيانات حسب السنة والشهر وربع السنة وهكذا فقط.
تحدد الطريقة التي تجيب بها عن كل سؤال من هذه الأسئلة الخيارات المتاحة لك للتعامل مع عمود وقت التاريخ.
تتطلب كل هذه الحلول تعديل استعلام SQL. لتسهيل تعديل الاستعلام، يجب تصفية عمود واحد على الأقل في كل جدول. من خلال تصفية عمود، يمكنك تغيير بناء الاستعلام من تنسيق مختصر (SELECT *) إلى جملة SELECT التي تتضمن أسماء أعمدة مؤهلة بالكامل، والتي تعد تعديلها أسهل بكثير.
دعنا نلق نظرة على الاستعلامات التي تم إنشاؤها لك. من مربع الحوار "خصائص الجدول"، يمكنك التبديل إلى محرر الاستعلام ورؤية استعلام SQL الحالي لكل جدول.
من "خصائص الجدول"، حدد محرر Power Query.
يعرض محرر Power Query استعلام SQL المستخدم لملء الجدول. إذا قمت بتصفية أي عمود أثناء الاستيراد، فسيتضمن الاستعلام أسماء أعمدة مؤهلة بالكامل:
في المقابل، إذا قمت باستيراد جدول بأكمله، دون إلغاء تحديد أي عمود أو تطبيق أي عامل تصفية، فسيظهر الاستعلام ك "تحديد * من"، والذي سيكون من الصعب تعديله:
|
|---|
تعديل استعلام SQL
الآن بعد أن عرفت كيفية العثور على الاستعلام، يمكنك تعديله لتصغير حجم النموذج بشكل إضافي.
- بالنسبة للأعمدة التي تحتوي على بيانات عملات أو عشريات، إذا لم تكن بحاجة إلى الأرقام العشرية، فاستخدم بناء الجملة التالي للتخلص من الأرقام العشرية:
"SELECT ROUND([Decimal_column_name],0)... .”
إذا كنت بحاجة إلى السنتات وليس كسور السنتات ، فاستبدل 0 ب 2. إذا كنت تستخدم أرقاما سالبة ، فيمكنك التقريب إلى وحدات وعشرات ومئات وما إلى ذلك. - إذا كان لديك عمود Datetime باسم dbo. طاولة كبيرة. [التاريخ والوقت] ولست بحاجة إلى جزء الوقت، استخدم بناء الجملة للتخلص من الوقت:
"حدد إرسال (dbo. طاولة كبيرة. [التاريخ والوقت] كتاريخ) AS [التاريخ والوقت]) " - إذا كان لديك عمود Datetime باسم dbo. طاولة كبيرة. [التاريخ والوقت] وكنت بحاجة إلى جزئي التاريخ والوقت، استخدم أعمدة متعددة في استعلام SQL بدلا من عمود Datetime الفردي:
"حدد إرسال (dbo. طاولة كبيرة. [التاريخ والوقت] كتاريخ ) AS [التاريخ والوقت]،
DatePart(HH, dbo. طاولة كبيرة. [التاريخ والوقت]) ك [التاريخ والوقت والساعات]،
Datepart(mi, dbo. طاولة كبيرة. [التاريخ والوقت]) ك [دقائق التاريخ والوقت]،
Datepart(ss, dbo. طاولة كبيرة. [التاريخ والوقت]) ك [التاريخ والوقت والثواني]،
DatePart(MS, dbo. طاولة كبيرة. [التاريخ والوقت]) ك [التاريخ والوقت والملي ثانية]"
استخدم عدد الأعمدة الذي تحتاجه لتخزين كل جزء في أعمدة منفصلة. - إذا كنت تحتاج إلى ساعات ودقائق، وكنت تفضلها معا كعمود وقت واحد، يمكنك استخدام بناء الجملة:
Timefromparts(datepart(hh, dbo. طاولة كبيرة. [التاريخ والوقت]), datepart(mm, dbo. طاولة كبيرة. [التاريخ والوقت])) AS [التاريخ والوقت والساعة والدقيقة] - إذا كان لديك عمودان "تاريخ ووقت"، مثل [وقت البدء] و[وقت الانتهاء]، وما تحتاجه بالفعل هو فارق الوقت بينهما بالثواني كعمود يسمى [المدة]، فقم بإزالة كلا العمودين من القائمة وإضافة:
"datediff(ss,[start date],[end date]) as [Duration]"
إذا كنت تستخدم الكلمة الأساسية ms بدلا من ss، فستحصل على المدة بالمللي ثانية
استخدام DAX للقياسات المحسوبة بدلا من الأعمدة
إذا سبق لك استخدام لغة تعبيرات DAX، فقد تعلم بالفعل أنه يتم استخدام الأعمدة المحسوبة لاشتقاق أعمدة جديدة استنادا إلى بعض الأعمدة الأخرى في النموذج، في حين يتم تعريف المقاييس المحسوبة مرة واحدة في النموذج، ولكن يتم تقييمها فقط عند استخدامها في PivotTable أو تقرير آخر.
من أساليب حفظ الذاكرة استبدال الأعمدة العادية أو المحسوبة بمقاييس محسوبة. المثال الكلاسيكي هو سعر الوحدة والكمية والإجمالي. إذا كان لديك الثلاثة، يمكنك توفير مساحة عن طريق الاحتفاظ باثنين فقط وحساب الثالث باستخدام DAX.
أي عمودين يجب الاحتفاظ بهما؟
في المثال أعلاه، احتفظ بالكمية وسعر الوحدة. يحتوي هاتان القيمتان على قيم أقل من الإجمالي. لحساب الإجمالي، أضف مقياسا محسوبا مثل:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
تشبه الأعمدة المحسوبة الأعمدة العادية حيث يشغل كلا من الأعمدة مساحة في النموذج. في المقابل ، يتم حساب المقاييس المحسوبة بسرعة ولا تأخذ مساحة.
الخاتمة
تحدثنا في هذه المقالة عن العديد من الأساليب التي يمكن أن تساعدك في بناء نموذج أكثر كفاءة في استخدام الذاكرة. تتمثل طريقة تقليل حجم الملف ومتطلبات الذاكرة لنموذج البيانات في تقليل العدد الإجمالي للصفوف والأعمدة، وعدد القيم الفريدة التي تظهر في كل عمود. فيما يلي بعض التقنيات التي قمنا بتغطيتها:
- إزالة الأعمدة هي بالطبع أفضل طريقة لتوفير المساحة. حدد الأعمدة التي تحتاجها حقا.
- في بعض الأحيان يمكنك إزالة عمود واستبداله بمقياس محسوب في الجدول.
- قد لا تحتاج إلى جميع الصفوف في جدول. يمكنك تصفية الصفوف في "معالج استيراد الجدول".
- وبشكل عام، يعد تقسيم عمود واحد إلى أجزاء متعددة مميزة طريقة جيدة لتقليل عدد القيم الفريدة في العمود. سيكون لكل جزء من الأجزاء عدد صغير من القيم الفريدة، وسيكون الإجمالي المجمع أصغر من العمود الموحد الأصلي.
- وفي كثير من الحالات، تحتاج أيضا إلى الأجزاء المميزة لاستخدامها كمقسمات طرق عرض في التقارير. عند الضرورة، يمكنك إنشاء تسلسلات هيكلية من أجزاء مثل الساعات والدقائق والثواني.
- في العديد من الأحيان، تحتوي الأعمدة على معلومات أكثر مما تحتاج إليه أيضا. على سبيل المثال، لنفترض أن العمود يخزن الأرقام العشرية، ولكنك قمت بتطبيق التنسيق لإخفاء كافة الأرقام العشرية. قد يكون التقريب فعالا جدا في تصغير حجم العمود الرقمي.
الآن وقد انتهيت من بذل كل ما في وسعك لتصغير حجم المصنف، يمكنك أيضا تشغيل Workbook Size Optimizer. فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. قم بتنزيل Workbook Size Optimizer.
ارتباطات ذات صلة
PowerPivot: التحليل الفعّال للبيانات وإنشاء نماذج بيانات في Excel