في Excel 2013 أو أي وقت لاحق، يمكنك إنشاء نماذج بيانات تحتوي على ملايين الصفوف، ثم إجراء تحليل قوي للبيانات مقابل هذه النماذج. يمكن إنشاء نماذج البيانات باستخدام أو بدون Power Pivot الإضافية لدعم أي عدد من مخططات PivotTables والمخططات Power View المرئيات في المصنف نفسه.
ملاحظة: تصف هذه المقالة نماذج البيانات في Excel 2013. على الرغم من ذلك، تطبق نفس نماذج البيانات وميزات Power Pivot في Excel 2013 على Excel 2016. هناك اختلاف صغير فعليا بين هذه الإصدارات من Excel.
على الرغم من أنه يمكنك بسهولة إنشاء نماذج بيانات ضخمة في Excel، هناك عدة أسباب وراء عدم القيام بذلك. أولا، إن النماذج الكبيرة التي تحتوي على عدد كبير من الجداول والأعمدة تكثر من كثرتها في إجراء معظم التحليلات، كما أنها تقوم بصنع قائمة الحقول المرهقة. ثانيا، تستخدم النماذج الكبيرة ذاكرة قيمة، مما يؤثر سلبا على التطبيقات والتقارير الأخرى التي تشارك موارد النظام نفسها. وأخيرا، في Microsoft 365 ، SharePoint Online Excel Web App حجم ملف Excel إلى 10 مبايت. بالنسبة لنماذج بيانات المصنف التي تحتوي على ملايين الصفوف، سوف تركض إلى حد 10 مبايت بسرعة كبيرة. راجع مواصفات نموذج البيانات وحدوده.
في هذه المقالة، ستتعرف على كيفية إنشاء نموذج تم إنشاءه بشكل مشدود يسهل العمل عليه ويستخدم ذاكرة أقل. ستدفعك الاستفادة من الوقت للتعرف على أفضل الممارسات في تصميم النماذج الفعالة إلى أي نموذج تقوم بإنشاءه واستخدامه، سواء كنت تعرضه في Excel 2013 أو Microsoft 365 SharePoint عبر الإنترنت أو على خادم Office Online أو في SharePoint 2013.
يمكنك أيضاً تشغيل Workbook Size Optimizer. فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. قم بتنزيل "محسن أحجام المصنفات".
في هذه المقالة
نسب الضغط ومحرك التحليلات في الذاكرة
تستخدم نماذج البيانات Excel محرك التحليلات في الذاكرة لتخزين البيانات في الذاكرة. ينفذ المحرك أساليب ضغط فعالة لتقليل متطلبات التخزين، مما يؤدي إلى تقليص مجموعة النتائج حتى تصبح جزءا من حجمها الأصلي.
في المتوسط، يمكنك توقع أن يكون نموذج البيانات أصغر من البيانات نفسها في نقطة الأصل من 7 إلى 10 مرات. على سبيل المثال، إذا كنت تستورد 7 مبايت من البيانات من قاعدة بيانات SQL Server، فمن السهل أن يكون نموذج البيانات في Excel 1 مبايت أو أقل. تعتمد درجة الضغط التي تم تحقيقها فعليا بشكل أساسي على عدد القيم الفريدة في كل عمود. كلما كانت القيم الفريدة أكثر، كلما تطلب الأمر المزيد من الذاكرة لتخزينها.
لماذا نتحدث عن الضغط والقيم الفريدة؟ لأن إنشاء نموذج فعال يقلل من استخدام الذاكرة هو كل شيء حول تكبير الضغط، وأسهل طريقة للقيام بذلك هي التخلص من أي أعمدة لا تحتاجها في الواقع، خاصة إذا كانت هذه الأعمدة تتضمن عددا كبيرا من القيم الفريدة.
ملاحظة: قد تكون الاختلافات في متطلبات التخزين للأعمدة الفردية ضخمة. في بعض الحالات، من الأفضل أن يكون لديك أعمدة متعددة ذات عدد منخفض من القيم الفريدة بدلا من عمود واحد مع عدد كبير من القيم الفريدة. يتناول القسم الخاص بالتحسينات في Datetime هذه التقنية بالتفصيل.
لا شيء يتفوق على عمود غير موجود لاستخدام الذاكرة المنخفضة
العمود الأكثر فعالية للذاكرة هو العمود الذي لم تستورده في المقام الأول. إذا كنت تريد إنشاء نموذج فعال، فانظر إلى كل عمود واسأل نفسك ما إذا كان يساهم في التحليل الذي تريد إجراءه. إذا لم يكن الأمر كذلك أو إذا لم تكن متأكدا، فاتركه. يمكنك دائما إضافة أعمدة جديدة في وقت لاحق إذا كنت بحاجة إليها.
مثالان للأعمدة التي يجب استبعادها دائما
يتعلق المثال الأول بالبيانات التي تنشأ من مستودع بيانات. في مستودع البيانات، من الشائع العثور على عمليات ETL التي يتم تحميلها وتحديثها في المستودع. يتم إنشاء أعمدة مثل "تاريخ إنشاء" و"تاريخ التحديث" و"تشغيل ETL" عند تحميل البيانات. لا حاجة إلى أي من هذه الأعمدة في النموذج ويجب إلغاء تحديدها عند استيراد البيانات.
يتضمن المثال الثاني حذف عمود المفتاح الأساسي عند استيراد جدول الحقائق.
تتضمن العديد من الجداول، بما في ذلك جداول الحقائق، مفاتيح أساسية. بالنسبة لمعظم الجداول، مثل تلك التي تحتوي على بيانات العملاء أو الموظفين أو المبيعات، يجب أن يكون المفتاح الأساسي في الجدول بحيث يمكنك استخدامه لإنشاء علاقات في النموذج.
تختلف جداول الحقائق. في جدول الحقائق، يتم استخدام المفتاح الأساسي لتعريف كل صف بشكل فريد. على الرغم من أنه ضروري لأغراض التطبيع، إلا أنه أقل فائدة في نموذج البيانات حيث تريد استخدام تلك الأعمدة فقط للتحليل أو لإنشاء علاقات الأعمدة. لهذا السبب، عند الاستيراد من جدول الحقائق، لا تتضمن المفتاح الأساسي الخاص به. تستهلك المفاتيح الأساسية في جدول الحقائق كميات هائلة من المساحة في النموذج، ومع ذلك لا تقدم أي فائدة، حيث لا يمكن استخدامها لإنشاء علاقات.
ملاحظة: في مستودعات البيانات وقواعد البيانات متعددة الأبعاد، يشار في أغلب الأحيان إلى الجداول الكبيرة التي تتألف من بيانات رقمية في معظمها ب "جداول الحقائق". تتضمن جداول الحقائق عادة أداء الأعمال أو بيانات المعاملات، مثل نقاط بيانات المبيعات والتكلفة التي يتم تجميعها ومحاذاتها مع الوحدات التنظيمية والمنتجات وشرائح السوق والمناطق الجغرافية وما إلى ذلك. يجب تضمين كل الأعمدة الموجودة في جدول الحقائق التي تحتوي على بيانات عمل أو التي يمكن استخدامها للتضمين المرجعي للبيانات المخزنة في جداول أخرى في النموذج لدعم تحليل البيانات. العمود الذي تريد استبعاده هو عمود المفتاح الأساسي في جدول الحقائق، الذي يتكون من قيم فريدة موجودة فقط في جدول الحقائق وليس في أي مكان آخر. نظرا لضخمة جداول الحقائق، فإن بعض أكبر المكاسب في فعالية النموذج يتم اشتقاقها من استبعاد الصفوف أو الأعمدة من جداول الحقائق.
كيفية استبعاد الأعمدة غير الضرورية
تحتوي النماذج الفعالة على الأعمدة التي ستحتاج إليها في المصنف فقط. إذا كنت تريد التحكم في الأعمدة المضمنة في النموذج، يجب استخدام معالج استيراد الجدول في الوظائف الإضافية Power Pivot لاستيراد البيانات بدلا من مربع الحوار "استيراد البيانات" في Excel.
عند بدء تشغيل معالج استيراد الجدول، يمكنك تحديد الجداول التي تريد استيرادها.
لكل جدول، يمكنك النقر فوق الزر معاينة & عامل التصفية وتحديد أجزاء الجدول التي تحتاجها بالفعل. نوصي أولا ب إلغاء تحديد كل الأعمدة، ثم المتابعة للتحقق من الأعمدة التي تريدها، بعد التفكير في ما إذا كانت مطلوبة للتحليل أم لا.
ماذا عن تصفية الصفوف الضرورية فقط؟
تحتوي العديد من الجداول في قواعد بيانات الشركة ومخازن البيانات على بيانات تاريخية تراكمت على مدى فترات زمنية طويلة. بالإضافة إلى ذلك، قد تجد أن الجداول التي تريدها تحتوي على معلومات حول مناطق العمل غير مطلوبة لتحليلك المحدد.
باستخدام معالج استيراد الجدول، يمكنك تصفية البيانات التاريخية أو غير المرتبطة، وبالتالي توفير مساحة كبيرة في النموذج. في الصورة التالية، يتم استخدام عامل تصفية التاريخ لاسترداد الصفوف التي تحتوي على بيانات السنة الحالية فقط، باستثناء البيانات التاريخية التي لن تكون مطلوبة.
ماذا لو كنا بحاجة إلى العمود؛ هل ما زال يمكننا تقليل تكلفة المساحة الخاصة به؟
هناك بعض التقنيات الإضافية التي يمكنك تطبيقها لجعل العمود مرشحا أفضل للضغط. تذكر أن السمة الوحيدة التي تميز العمود الذي يؤثر على الضغط هي عدد القيم الفريدة. في هذا القسم، ستتعرف على كيفية تعديل بعض الأعمدة لتقليل عدد القيم الفريدة.
تعديل أعمدة Datetime
في العديد من الحالات، تأخذ أعمدة Datetime مساحة كبيرة. لحسن الحظ، هناك عدد من الطرق لتقليل متطلبات التخزين لنوع البيانات هذا. ستختلف التقنيات استنادا إلى كيفية استخدام العمود ومستوى راحتك في إنشاء SQL الاستعلامات.
تتضمن أعمدة Datetime جزءا من التاريخ والوقت. عندما تسأل نفسك ما إذا كنت بحاجة إلى عمود، اطرح السؤال نفسه عدة مرات على عمود Datetime:
-
هل أحتاج إلى جزء الوقت؟
-
هل أحتاج إلى جزء الوقت على مستوى الساعات؟ ، دقائق؟ ، الثواني؟ ، مللي ثانية؟
-
هل لدي أعمدة تاريخ متعددة لأنني أريد حساب الفرق بينها، أو فقط لتجميع البيانات حسب السنة والشهر والربع وما إلى ذلك.
تحدد كيفية الإجابة على كل سؤال من هذه الأسئلة خياراتك للتعامل مع عمود Datetime.
تتطلب كل هذه الحلول تعديل استعلام SQL. لتسهيل تعديل الاستعلام، يجب تصفية عمود واحد على الأقل في كل جدول. من خلال تصفية عمود، يمكنك تغيير إنشاء الاستعلام من تنسيق مختصر (SELECT *) إلى العبارة SELECT التي تتضمن أسماء أعمدة مؤهلة بالكامل، والتي يمكن تعديلها بسهولة.
دعنا نلقي نظرة على الاستعلامات التي تم إنشاؤها لك. من مربع الحوار خصائص الجدول، يمكنك التبديل إلى محرر الاستعلام لمشاهدة الاستعلام SQL الحالي لكل جدول.
من خصائص الجدول، حدد محرر الاستعلام.
يعرض محرر الاستعلام SQL المستخدم لملء الجدول. إذا قمت بتصفية أي عمود أثناء الاستيراد، يتضمن الاستعلام أسماء أعمدة مؤهلة بالكامل:
|
في المقابل، إذا قمت باستيراد جدول بالكامل، دون إلغاء تحديد أي عمود أو تطبيق أي عامل تصفية، سترى الاستعلام على أنه "تحديد * من "، والذي سيكون من الصعب تعديله:
|
تعديل استعلام SQL
الآن وقد تعرفت على كيفية العثور على الاستعلام، يمكنك تعديله لتقليل حجم النموذج بشكل أكبر.
-
بالنسبة إلى الأعمدة التي تحتوي على بيانات عملة أو رقم عشري، إذا لم تكن بحاجة إلى المنازل العشرية، فاستخدم بناء الجملة هذا للتخلص من المنازل العشرية:
"SELECT ROUND([Decimal_column_name],0)... .”
إذا كنت بحاجة إلى السنت وليس الكسور من السنت، فاستبدل 0 ب 2. إذا كنت تستخدم أرقاما سالبة، يمكنك تقريبها إلى وحدات أو العشرات أو المئات وغير ذلك.
-
إذا كان لديك عمود Datetime يسمى dbo. Bigtable. [وقت التاريخ] ولا تحتاج إلى جزء الوقت، استخدم بناء الجملة للتخلص من الوقت:
"SELECT CAST (dbo. Bigtable. [وقت التاريخ] كالتاريخ) AS [تاريخ الوقت]) "
-
إذا كان لديك عمود Datetime يسمى dbo. Bigtable. [وقت التاريخ] وأنت بحاجة إلى كل من جزءي التاريخ والوقت، استخدم أعمدة متعددة في استعلام SQL بدلا من عمود التاريخ المفرد:
"SELECT CAST (dbo. Bigtable. [تاريخ الوقت] كالتاريخ ) AS [تاريخ الوقت]،
datepart(hh, dbo. Bigtable. [تاريخ الوقت]) ك [ساعات وقت التاريخ]،
datepart(mi, dbo. Bigtable. [تاريخ الوقت]) ك [دقائق وقت التاريخ]،
datepart(ss, dbo. Bigtable. [تاريخ الوقت]) ك [ثواني وقت التاريخ]،
datepart(ms, dbo. Bigtable. [تاريخ الوقت]) ك [Date Time Milliseconds]"
استخدم العدد الذي تحتاجه من الأعمدة لتخزين كل جزء في أعمدة منفصلة.
-
إذا كنت بحاجة إلى ساعات ودقائق، وتفضلها معا كعمود مرة واحدة، يمكنك استخدام بناء الجملة:
Timefromparts(datepart(hh, dbo. Bigtable. [وقت التاريخ])، datepart(mm, dbo. Bigtable. [تاريخ الوقت])) As [Date Time HourMinute]
-
إذا كان لديك عمودان من أعمدة وقت التاريخ، مثل [وقت البدء] و[وقت الانتهاء]، وما تحتاج إليه حقا هو فرق الوقت بينهما بالثواني كعمود يسمى [المدة]، فإزالة العمودين من القائمة وإضافة:
"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])"
تشبه الأعمدة المحسوبة الأعمدة العادية حيث تأخذ كل منهما مساحة في النموذج. في المقابل، يتم حساب القياسات المحسوبة بشكل تلقائي ولا تأخذ مساحة.
الخاتمة
في هذه المقالة، تحدثنا عن العديد من النهج التي يمكن أن تساعدك على إنشاء نموذج أكثر فعالية في استخدام الذاكرة. إن الطريقة التي يمكن من خلالها تقليل حجم الملف ومتطلبات الذاكرة لنموذج بيانات هي تقليل العدد الإجمالي للأعمدة والصفوف، وعدد القيم الفريدة التي تظهر في كل عمود. فيما يلي بعض التقنيات التي تناولناها:
-
إن إزالة الأعمدة هي بالطبع أفضل طريقة لتوفير المساحة. تحديد الأعمدة التي تحتاج إليها بالفعل.
-
في بعض الأحيان، يمكنك إزالة عمود واستبداله بمقياس محسوب في الجدول.
-
قد لا تحتاج إلى كل الصفوف في جدول. يمكنك تصفية الصفوف في معالج استيراد الجدول.
-
وبشكل عام، فإن تقسيم عمود واحد إلى أجزاء متعددة مميزة هو طريقة جيدة لتقليل عدد القيم الفريدة في عمود. سيكون لكل جزء من الأجزاء عدد صغير من القيم الفريدة، وسيصغر الإجمالي المجمع عن العمود الموحد الأصلي.
-
في العديد من الحالات، تحتاج أيضا إلى الأجزاء المميزة لاستخدامها كمبرمجات طرق العرض في التقارير. يمكنك إنشاء تسلسلات هرمية من أجزاء مثل الساعات والدقائق والثواني، عندما يكون ذلك مناسبا.
-
في عدة مرات، تحتوي الأعمدة على معلومات أكثر مما تحتاج إليها أيضا. على سبيل المثال، افترض أن عمودا يخزن المنازل العشرية، ولكنك قمت بتطبيق التنسيق لإخفاء كل المنازل العشرية. يمكن أن يكون ال تقريب فعالا جدا في تقليل حجم عمود رقمي.
الآن وقد انتهيت من القيام بما يمكنك لتقليل حجم المصنف، يمكنك أيضا تشغيل "محسن أحجام المصنف". فهي تعمل على تحليل مصنف Excel وتضغطه أكثر إذا أمكن. قم بتنزيل "محسن أحجام المصنفات".
ارتباطات ذات صلة
PowerPivot: التحليل الفعّال للبيانات وإنشاء نماذج بيانات في Excel
