العمل العملي على تقنية olap في برنامج Excel. مكعبات Olap في برنامج Excel كتابة صيغة من الصفر

مهمة واحدة (عامة) (نقطتان).

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

2.1 المهمة (نقطتان).

يسافر أربعة أصدقاء بثلاث وسائل نقل: القطار والطائرة والسفينة. أبحر نيكولاي بالقارب مسافة 150 كيلومترًا، وسافر بالقطار مسافة 140 كيلومترًا، وطار بالطائرة مسافة 1100 كيلومتر. أبحر فاسيلي 200 كيلومتر بالقارب، وسافر 220 كيلومترًا بالقطار، وطار 1160 كيلومترًا بالطائرة. طار أناتولي 1200 كم بالطائرة، وسافر 110 كم بالقطار، وأبحر 125 كم بالقارب. سافرت ماريا مسافة 130 كيلومترًا بالقطار، وطارت مسافة 1500 كيلومترًا بالطائرة، وأبحرت مسافة 160 كيلومترًا بالقارب.
قم بإنشاء جدول بيانات بناءً على البيانات المذكورة أعلاه.

أضف عمودًا إلى الجدول الذي سيعرض إجمالي عدد الكيلومترات التي قطعها كل من الرجال.

احسب إجمالي عدد الكيلومترات التي قطعها الأطفال بالقطار، وطاروا بالطائرة، وأبحروا بالقارب (في كل نوع من وسائل النقل على حدة).

احسب إجمالي عدد الكيلومترات لجميع الأصدقاء.

تحديد الحد الأقصى والحد الأدنى لعدد الكيلومترات التي يقطعها الأصدقاء باستخدام جميع أنواع وسائل النقل.

تحديد متوسط ​​عدد الكيلومترات لجميع أنواع النقل.

2.2 المهمة (نقطتان).

قم بإنشاء جدول "بحيرات أوروبا" باستخدام البيانات التالية حول المساحة (كم مربع) والعمق الأكبر (م): لادوجا 17700 و225؛ أونيجا 9510 و110؛ بحر قزوين 371.000 و995؛ فينيرن 5550 و100؛ Chudskoye مع Pskovsky 3560 و 14 ؛ بالاتون 591 و 11؛ جنيف 581 و310؛ ويترن 1900 و119؛ كونستانس 538 و 252؛ مالارين 1140 و64. حدد أكبر وأصغر بحيرة في المنطقة، وأعمق وأعمق بحيرة.

2.3 المهمة (نقطتان).

قم بإنشاء جدول "أنهار أوروبا" باستخدام الطول (كم) ومساحة الحوض (ألف كم مربع): فولغا 3688 و 1350؛ الدانوب 2850 و817؛ الراين 1330 و 224؛ إلبه 1150 و 148؛ فيستولا 1090 و 198؛ لوار 1020 و 120؛ الأورال 2530 و 220 ؛ دون 1870 و 422؛ سينا 780 و 79؛ التايمز 340 و 15. تحديد أطول وأقصر نهر، وحساب المساحة الإجمالية لأحواض الأنهار، ومتوسط ​​طول الأنهار في الجزء الأوروبي من روسيا.

المهمة 3 (نقطتان).

يسجل البنك توقيت سداد القروض الصادرة للعديد من المنظمات. مبلغ القرض والمبلغ الذي دفعته المنظمة بالفعل معروفان. تم تحديد العقوبات على المدينين: إذا قامت الشركة بسداد القرض بنسبة تزيد عن 70 بالمائة، فستكون الغرامة 10 بالمائة من مبلغ الدين، وإلا ستكون الغرامة 15 بالمائة. احسب الغرامة لكل منظمة، ومتوسط ​​الغرامة، والمبلغ الإجمالي الذي سيحصل عليه البنك بالإضافة إلى ذلك. تحديد متوسط ​​غرامة المنظمات الميزانية.

العثور على مادة لأي درس،

أدوات عميل OLAP هي تطبيقات تحسب البيانات المجمعة (المجاميع والمتوسطات والقيم القصوى أو الدنيا) وتعرضها، بينما يتم تضمين البيانات المجمعة نفسها في ذاكرة تخزين مؤقت داخل مساحة العنوان لأداة OLAP هذه.

إذا كانت البيانات المصدر موجودة في نظام إدارة قواعد البيانات (DBMS) لسطح المكتب، فسيتم إجراء حساب البيانات المجمعة بواسطة أداة OLAP نفسها. إذا كان مصدر البيانات الأولية هو نظام إدارة قواعد البيانات (DBMS) الخاص بالخادم، فإن العديد من أدوات OLAP الخاصة بالعميل ترسل استعلامات SQL تحتوي على عبارة GROUP BY إلى الخادم، ونتيجة لذلك تتلقى البيانات المجمعة المحسوبة على الخادم.

كقاعدة عامة، يتم تنفيذ وظيفة OLAP في أدوات معالجة البيانات الإحصائية (منتجات هذه الفئة تستخدم على نطاق واسع في السوق الروسية منتجات StatSoft وSPSS) وفي بعض جداول البيانات. على وجه الخصوص، يحتوي Microsoft Excel على أدوات تحليل متعددة الأبعاد. باستخدام هذا المنتج، يمكنك إنشاء وحفظ مكعب OLAP محلي صغير متعدد الأبعاد وحفظه كملف وعرض مقاطع عرضية ثنائية أو ثلاثية الأبعاد منه.

الوظائف الإضافية لحزمة التطبيق مايكروسوفت أوفيساستخراج البيانات عبارة عن مجموعة من الوظائف التي توفر الوصول إلى إمكانات استخراج البيانات ومعالجتها من تطبيقات Microsoft Office، وبالتالي تمكين التحليل التنبؤي الكمبيوتر المحلي. بفضل حقيقة أن الخدمات مدمجة في منصات مايكروسوفت خادم قاعدة البياناتمن خلال خوارزميات استخراج البيانات ومعالجتها المتاحة من داخل بيئة تطبيقات Microsoft Office، يمكن لمستخدمي الأعمال بسهولة استخراج المعلومات القيمة من مجموعات البيانات المعقدة ببضع نقرات فقط. تمكن الوظائف الإضافية لاستخراج بيانات Office ومعالجتها المستخدمين النهائيين من إجراء التحليل مباشرة داخل Microsoft Excel وMicrosoft Visio.

يتضمن Microsoft Office 2007 ثلاثة مكونات منفصلة لـ OLAP:

1. يتيح لك Data Mining Client for Excel إنشاء وإدارة مشاريع استخراج البيانات المستندة إلى SSAS من داخل Excel 2007؛
2. أدوات تحليل الجدول تطبيقات اكسليتيح لك استخدام إمكانات استخلاص المعلومات ومعالجتها المضمنة في SSAS لتحليل البيانات المخزنة في جداول بيانات Excel.
3. تتيح لك قوالب استخراج بيانات Visio تصور أشجار القرار وأشجار الانحدار والرسوم التخطيطية العنقودية وشبكات التبعية في رسوم Visio التخطيطية.

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

مصطلحات تحليل البيانات متعددة المتغيرات

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

البيانات والقياسات متعددة الأبعاد

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

• أنواع أو مجموعات البضائع؛
• العلامات التجارية أو فئات المنتجات؛
• الفترات (الشهر، الربع، السنة)؛
• المشترين أو مجموعات المشترين.
• مناطق المبيعات
• وما إلى ذلك وهلم جرا.

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

• حجم المبيعات؛
• سعر البيع؛
• خصم فردي
• وما إلى ذلك وهلم جرا.

من الناحية النظرية، يمكن أن تكون البيانات أيضًا بُعدًا قياسيًا للمعلومات متعددة الأبعاد (على سبيل المثال، يمكنك تجميع البيانات حسب سعر البيع)، ولكن البيانات عادةً ما تظل نوعًا خاصًا من القيمة.

وهكذا يمكن القول في العمل التطبيقييستخدم الاقتصاديون نوعين من المعلومات: بيانات متعددة الأبعاد (الأعداد الفعلية والمخططة التي لها العديد من الخصائص) والكتب المرجعية (الخصائص أو قياسات البيانات).

OLAP

اختصار OLAP (المعالجة التحليلية عبر الإنترنت) يعني حرفيًا "المعالجة التحليلية في الوقت الفعلي". التعريف ليس محددًا جدًا ويمكن تلخيص أي تقرير عن أي منتج برمجي تحته. في معناه، يتضمن OLAP تقنية للعمل مع التقارير الخاصة، بما في ذلك برمجةللحصول على البيانات المنظمة متعددة الأبعاد وتحليلها. أحد منتجات البرامج الشائعة التي تطبق تقنيات OLAP هو SQL Server Analysis Server. حتى أن البعض يعتبره خطأً الممثل الوحيد لتنفيذ البرنامج لهذا المفهوم.

مكعب البيانات الظاهري

"المكعب الافتراضي" ( مكعب متعدد الأبعاد، OLAP cube) هو مصطلح خاص يقترحه بعض موفري البرامج المتخصصين. تقوم أنظمة OLAP عادةً بإعداد البيانات وتخزينها في بنياتها الخاصة، كما تصل واجهات التحليل الخاصة (مثل تقارير ملخص Excel) إلى البيانات الموجودة في هذه المكعبات الافتراضية. علاوة على ذلك، فإن استخدام مثل هذا التخزين المخصص ليس ضروريًا على الإطلاق لمعالجة المعلومات متعددة الأبعاد. على العموم، المكعب الظاهري- هذه عبارة عن مجموعة من البيانات متعددة الأبعاد المحسنة خصيصًا والتي يتم استخدامها لإنشاء تقارير موجزة. ويمكن الحصول عليها إما من خلال برامج متخصصة أو من خلال الوصول البسيط إلى جداول قاعدة البيانات أو أي مصدر آخر، مثل جدول بيانات Excel.

جدول محوري

Pivot Table عبارة عن واجهة مستخدم لعرض البيانات متعددة الأبعاد. باستخدام هذه الواجهة، يمكنك تجميع البيانات وفرزها وتصفيتها وإعادة ترتيبها للحصول على عينات تحليلية مختلفة. يتم تحديث التقرير باستخدام وسائل بسيطة واجهة المستخدم، يتم تجميع البيانات تلقائيًا وفقًا لقواعد محددة، دون الحاجة إلى إدخال إضافي أو متكرر لأية معلومات. ربما تكون واجهة الجدول المحوري في Excel هي الأكثر شيوعًا منتج برمجيللعمل مع البيانات متعددة الأبعاد. وهو يدعم كلاً من مصادر البيانات الخارجية (مكعبات OLAP وقواعد البيانات العلائقية) ونطاقات جداول البيانات الداخلية كمصدر بيانات. بدءًا من الإصدار 2000 (9.0)، يدعم Excel أيضًا نموذجًا رسوميًا لعرض البيانات متعددة الأبعاد - مخطط محوري.

تسمح لك واجهة PivotTable في Excel بترتيب أبعاد البيانات متعددة الأبعاد في منطقة ورقة العمل. للتبسيط، يمكنك اعتبار الجدول المحوري بمثابة تقرير يقع أعلى نطاق من الخلايا (في الواقع، هناك ارتباط معين لتنسيقات الخلايا بحقول الجدول المحوري). يحتوي Excel PivotTable على أربع مناطق لعرض المعلومات: عامل التصفية والأعمدة والصفوف والبيانات. يتم استدعاء أبعاد البيانات حقول الجدول المحوري. هذه الحقول لها خصائصها الخاصة وتنسيق العرض.

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

تحرير الجداول المحورية

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

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

إعداد البيانات متعددة الأبعاد

دعونا نأتي إلى تطبيق عمليالجداول المحورية. دعونا نحاول تحليل بيانات المبيعات في اتجاهات مختلفة. ملف Pivottableexample.xlsيتكون من عدة أوراق. ملزمة مثاليحتوي على معلومات أساسية حول المبيعات لفترة معينة. لتبسيط المثال، سنقوم بتحليل مؤشر رقمي واحد – حجم المبيعات بالكيلو جرام. أبعاد البيانات الرئيسية هي: المنتج والمشتري والناقل (شركة الشحن). وبالإضافة إلى ذلك، هناك عدة أبعاد إضافيةالبيانات التي هي خصائص المنتج: النوع، العلامة التجارية، الفئة، المورد، وكذلك المشتري: النوع. يتم جمع هذه البيانات في ورقة الدلائل. في الممارسة العملية، يمكن أن يكون هناك المزيد من هذه القياسات.

ملزمة مثاليتضمن علاج قياسيتحليل البيانات - التصفية التلقائية. وبالنظر إلى مثال ملء الجدول، فمن الواضح أن بيانات المبيعات حسب التاريخ (وهي مرتبة في أعمدة) قابلة للتحليل العادي. بالإضافة إلى ذلك، باستخدام عامل التصفية التلقائي، يمكنك محاولة تلخيص البيانات بناءً على مجموعات من معيار رئيسي واحد أو أكثر. لا توجد معلومات على الإطلاق حول العلامات التجارية والفئات والأنواع. لا توجد طريقة لتجميع البيانات من خلال الجمع التلقائي بواسطة مفتاح محدد (على سبيل المثال، حسب العملاء). بالإضافة إلى ذلك، فإن مجموعة التواريخ ثابتة، ولن يكون من الممكن عرض معلومات ملخصة لفترة معينة، على سبيل المثال، 3 أيام، باستخدام الوسائل التلقائية.

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

أولا، علينا أن نتخلص من هذا النقص - أي. إزالة الموقع المحدد مسبقًا لأحد أبعاد البيانات المصدر. مثال على الجدول الصحيح - ورقة مبيعات.

يحتوي الجدول على شكل سجل لإدخال المعلومات. هنا، التاريخ هو بُعد متساوٍ للبيانات. تجدر الإشارة أيضًا إلى أنه بالنسبة للتحليل اللاحق في الجداول المحورية، يكون الموضع النسبي للصفوف بالنسبة لبعضها البعض (بمعنى آخر، الفرز) غير مبالٍ تمامًا. السجلات في قواعد البيانات العلائقية لها هذه الخصائص. تهدف واجهة الجداول المحورية في المقام الأول إلى تحليل كميات كبيرة من قواعد البيانات. ولذلك، يجب عليك الالتزام بهذه القواعد عند العمل مع مصدر بيانات في شكل نطاقات خلايا. في الوقت نفسه، لا أحد يحظر استخدام أدوات واجهة Excel في عملهم - تقوم الجداول المحورية بتحليل البيانات فقط، ويمكن أن يكون التنسيق والمرشحات والتجمعات وفرز الخلايا المصدر تعسفيا.

من التصفية التلقائية إلى التقرير الموجز

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

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

الآن دعونا نرى ما هي الإمكانيات التي توفرها واجهة الجدول المحوري. على ورقة تحليل ملخصأنشأنا عدة تقارير بناءً على نطاق من الخلايا التي تحتوي على بيانات الورقة تحليل المبيعات.

تم إنشاء جدول التحليل الأول من خلال واجهة Excel 2007 الشريط\إدراج\PivotTable(في قائمة Excel 2000-2003 البيانات\PivotTable).

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

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

الخصائص والتنسيق

بالإضافة إلى عرض البيانات مباشرة، هناك مجموعة واسعة من الخيارات لعرض مظهر الجداول المحورية. يمكنك إخفاء البيانات غير الضرورية باستخدام المرشحات. بالنسبة لعنصر أو حقل واحد، يكون من الأسهل استخدام عنصر قائمة السياق يمسح(في الإصدار 2000-2003 يخفي).

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

بالإضافة إلى ذلك، قدم Excel 2007 العديد من أنماط عرض PivotTable المحددة مسبقًا:

لاحظ أن عوامل تصفية التحكم ومناطق السحب نشطة في المخطط.

الوصول إلى البيانات الخارجية

كما ذكرنا سابقًا، ربما يمكن الحصول على التأثير الأكبر من استخدام الجداول المحورية عند الوصول إلى البيانات مصادر خارجية– مكعبات OLAP واستعلامات قاعدة البيانات. عادةً ما تقوم هذه المصادر بتخزين كميات كبيرة من المعلومات ولها أيضًا بنية علائقية محددة مسبقًا، مما يجعل من السهل تحديد أبعاد البيانات متعددة الأبعاد (حقول الجدول المحوري).

يدعم Excel العديد من أنواع مصادر البيانات الخارجية:

يمكن تحقيق التأثير الأكبر من استخدام مصادر المعلومات الخارجية باستخدام أدوات التشغيل الآلي (برامج VBA) للحصول على البيانات ومعالجتها مسبقًا في الجداول المحورية.

المعالجة التحليلية عبر الإنترنت (OLAP) هي تقنية تُستخدم لتنظيم قواعد بيانات الأعمال الكبيرة ودعم ذكاء الأعمال. يتم تقسيم قواعد بيانات OLAP إلى مكعب واحد أو أكثر، ويتم تنظيم كل مكعب بواسطة مسؤول المكعب ليناسب طريقة استرداد البيانات وتحليلها لتسهيل إنشاء واستخدام تقارير PivotTable وتقارير PivotChart التي ستحتاج إليها.

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

ما هي تحليلات الأعمال؟

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

كيف يمكن مقارنة إجمالي مبيعات جميع المنتجات في عام 2007 بالمبيعات منذ عام 2006؟

كيف يمكن مقارنة هذا بالتاريخ والوقت حسب فترة الاستفادة من السنوات الخمس الماضية؟

ما مقدار الأموال التي أنفقها العملاء على 35 العام الماضي وكيف تغير هذا السلوك بمرور الوقت؟

ما عدد المنتجات التي تم بيعها في بلدين/منطقتين محددتين هذا الشهر مقارنةً بنفس الشهر من العام الماضي؟

بالنسبة لكل فئة عمرية من العملاء، ما هو تقسيم الربحية (نسبة الهامش والإجمالي) حسب فئة المنتج؟

ابحث عن أعلى وأدنى البائعين والموزعين والموردين والعملاء والشركاء والعملاء.

ما هي المعالجة التحليلية عبر الإنترنت (OLAP)؟

تعمل قواعد بيانات OLAP (المعالجة التحليلية عبر الإنترنت) على تبسيط استعلامات ذكاء الأعمال. OLAP هي تقنية قاعدة بيانات مُحسّنة للاستعلامات والتقارير بدلاً من معالجة المعاملات. مصدر البيانات لـ OLAP هو قواعد بيانات معالجة المعاملات عبر الإنترنت (OLTP)، والتي يتم تخزينها عادةً في مستودعات البيانات. يتم استخراج بيانات OLAP من هذه البيانات التاريخية ودمجها في هياكل تسمح بالتحليل المعقد. يتم أيضًا تنظيم بيانات OLAP بشكل هرمي ويتم تخزينها في مكعبات بدلاً من الجداول. إنها تقنية معقدة تستخدم هياكل متعددة الأبعاد لتوفيرها الوصول السريعإلى البيانات للتحليل. في هذه المؤسسة، يمكن لتقرير PivotTable أو تقرير PivotChart عرض بيانات ملخصة عالية المستوى بسهولة، مثل إجماليات المبيعات لبلد أو منطقة بأكملها، وكذلك عرض معلومات حول المواقع التي تكون فيها المبيعات قوية أو ضعيفة بشكل خاص.

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

تحتوي قواعد بيانات OLAP على نوعين رئيسيين من البيانات: المقاييس، وهي عبارة عن بيانات رقمية، وكميات، ومتوسطات تُستخدم لاتخاذ قرارات عمل مستنيرة، والأبعاد، وهي فئات تستخدم لتنظيم تلك المقاييس. تساعدك قواعد بيانات OLAP على تنظيم البيانات من خلال مستويات متعددة من التفاصيل، باستخدام نفس الفئات التي تعرفها لتحليل البيانات.

تصف الأقسام التالية كل مكون بالتفصيل أدناه.

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

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

عضوعنصر في التسلسل الهرمي يمثل تكرارًا واحدًا أو أكثر للبيانات. يمكن أن يكون العنصر فريدًا أو غير فريد. على سبيل المثال، يمثل 2007 و2008 أعضاء فريدين على مستوى العام لبُعد زمني، بينما يمثل يناير أعضاء غير فريدين على مستوى الشهر نظرًا لوجود أكثر من يناير واحد في بُعد زمني لأنه يحتوي على بيانات لأكثر من عام واحد.

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

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

تَسَلسُلبنية شجرة منطقية تقوم بترتيب أعضاء البعد بحيث يكون لكل عضو عضو أصل واحد وصفر أو أكثر من العناصر التابعة. الطفل هو عضو في مجموعة سابقة في التسلسل الهرمي المرتبطة مباشرة بالعضو الحالي. على سبيل المثال، في التسلسل الهرمي الزمني الذي يحتوي على مستويات الربع والشهر واليوم، يكون شهر يناير تابعًا لـ Qtr1. العنصر الأصلي هو عضو ذو مستوى أدنى في تسلسل هرمي يرتبط مباشرة بالعضو الحالي. عادةً ما تكون القيمة الأصلية هي دمج قيم جميع العناصر الفرعية. على سبيل المثال، في التسلسل الهرمي الزمني الذي يحتوي على مستويات ربع السنة والشهر واليوم، يكون Qtr1 هو الأصل لشهر يناير.

مستوىفي التسلسل الهرمي، يمكن تنظيم البيانات في مستويات أقل وأعلى من التفصيل، مثل السنوات وأرباع السنة والشهور ومستويات اليوم في التسلسل الهرمي الزمني.

وظائف OLAP في Excel

استرداد بيانات OLAPيمكنك الاتصال بمصادر بيانات OLAP تمامًا كما تتصل بمصادر البيانات الخارجية الأخرى. يمكنك العمل مع قواعد البيانات التي تم إنشاؤها باستخدام الخدمات مايكروسوفت SQL Server OLAP الإصدار 7.0، Microsoft SQL Server Analysis Services الإصدار 2000 وMicrosoft SQL Server Analysis Services الإصدار 2005، منتجات خادم Microsoft OLAP. بإمكان Excel أيضًا العمل مع منتجات OLAP التابعة لجهات أخرى والمتوافقة مع OLE-DB لـ OLAP.

يمكن عرض بيانات OLAP فقط كتقرير PivotTable أو تقرير PivotChart، أو في دالة ورقة عمل تم تحويلها من تقرير PivotTable، ولكن ليس كنطاق بيانات خارجي. يمكنك حفظ تقارير OLAP PivotTable وPivotChart في قوالب التقارير، وإنشاء ملفات اتصال بيانات Office (ODC) للاتصال بقواعد بيانات OLAP لاستعلامات OLAP. عند فتح ملف ODC في Excel، ستشاهد تقرير PivotTable فارغًا جاهزًا للوضع.

إنشاء ملفات مكعبة للاستخدام دون اتصال بالإنترنتيمكنك إنشاء ملف مكعب مستقل (.cub) مع مجموعة فرعية من البيانات من قاعدة بيانات خادم OLAP. تُستخدم ملفات المكعب غير المتصلة للعمل مع بيانات OLAP عندما لا تكون متصلاً بالشبكة. باستخدام المكعب، يمكنك العمل باستخدام المزيد من البيانات في تقرير PivotTable أو تقرير PivotChart أكثر مما تفعل بخلاف ذلك، كما يمكنك الحصول على البيانات بشكل أسرع. يمكنك إنشاء ملفات مكعب فقط إذا كنت تستخدم موفر OLAP، مثل الإصدار 2005 من Microsoft SQL Analysis Services، الذي يدعم هذه الميزة.

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

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

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

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

مكونات البرامج المطلوبة للوصول إلى مصادر بيانات OLAP

موفر OLAPلتكوين مصادر بيانات OLAP لبرنامج Excel، تحتاج إلى أحد موفري OLAP التاليين.

موفر مايكروسوفت OLAPيتضمن Excel برنامج تشغيل مصدر البيانات وبرنامج العميل للوصول إلى قواعد البيانات التي تم إنشاؤها باستخدام خدمات Microsoft SQL Server olap الإصدار 7.0، وMicrosoft SQL Server olap الإصدار 2000 (8.0)، وخدمات تحليل Microsoft SQL Server الإصدار 2005 (9،0).

موفري OLAP من الطرف الثالثتتطلب منتجات OLAP الأخرى تثبيت برامج تشغيل إضافية وبرامج عميل. لاستخدام إمكانيات Excel للعمل مع بيانات OLAP، يجب أن يتوافق منتج الجهة الخارجية مع معيار OLE-DB لـ OLAP وأن يكون متوافقًا مع Microsoft Office. للحصول على معلومات حول تثبيت واستخدام موفر OLAP لجهة خارجية، راجع مدير النظامأو بائع منتج OLAP الخاص بك.

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

مصادر البياناتيوفر مصدر البيانات إمكانية الوصول إلى كافة البيانات الموجودة في قاعدة بيانات OLAP أو ملف مكعب غير متصل. بمجرد إنشاء مصدر بيانات OLAP، يمكنك إنشاء تقارير عليه وإرجاع بيانات OLAP إلى Excel كتقرير PivotTable أو تقرير PivotChart، أو في دالة ورقة عمل تم تحويلها من تقرير PivotTable.

استعلام مايكروسوفتباستخدام الاستعلام، يمكنك استرداد البيانات من قاعدة بيانات خارجية، مثل Microsoft SQL أو مدخل البرمجيات المرنة. لا تحتاج إلى استخدام استعلام لاسترداد البيانات من جدول OLAP المحوري المرتبط بملف مكعب. معلومات إضافية .

الاختلافات في ميزات البيانات المصدر OLAP وغير OLAP

إذا كنت تستخدم تقارير PivotTable وPivotCharts من بيانات مصدر OLAP وأنواع بيانات المصدر الأخرى، فسترى بعض الاختلافات في الميزات.

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

في التقارير المستندة إلى بيانات مصدر OLAP، لا تتوفر خيارات حقل تصفية التقرير، ولا يتوفر استعلام الخلفية، ولا يتوفر خيار تحسين الذاكرة.

ملحوظة:لا يتوفر أيضًا خيار تحسين الذاكرة لمصادر بيانات OLEDB وتقارير PivotTable المستندة إلى نطاق من الخلايا.

أنواع الحقولبيانات مصدر OLAP. لا يمكن استخدام حقول الأبعاد إلا كصفوف (صفوف)، أو أعمدة (فئة)، أو حقول صفحة. لا يمكن استخدام حقول القياس إلا كحقول قيمة. بالنسبة لأنواع بيانات المصدر الأخرى، يمكن استخدام كافة الحقول في أي جزء من التقرير.

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

قد لا تحتوي حقول عامل تصفية تقرير OLAP الجميعالعناصر والفريق عرض صفحات تصفية التقريرغير متاح.

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

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

المجاميع الفرعيةفي التقارير التي تحتوي على بيانات مصدر OLAP، لا يمكنك تغيير وظيفة التلخيص للإجماليات الفرعية. باستخدام أنواع بيانات المصدر الأخرى، يمكنك تغيير وظائف الإجمالي للإجماليات الفرعية وإظهار الإجماليات الفرعية أو إخفائها لجميع حقول الصفوف والأعمدة.

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