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