کار عملی روی فناوری olap در اکسل. کار عملی در علوم کامپیوتر خانم

مشکلات تجزیه و تحلیل، OLAP، انبارهای داده در میان متخصصان فناوری اطلاعات روسی مورد توجه فزاینده ای قرار گرفته است. در حال حاضر، در مطبوعات رایانه ای ما و در اینترنت، مطالب آکادمیک و خوب زیادی در مورد این موضوع، از جمله مطالب مقدماتی، منتشر شده است. ما مقاله ای را مورد توجه شما قرار می دهیم که در آن عمداً سعی می کنیم OLAP را "روی انگشتان" با یک مثال خاص توضیح دهیم. تمرین نشان می دهد که چنین توضیحی برای برخی از متخصصان فناوری اطلاعات و به ویژه برای کاربران نهایی ضروری است.

بنابراین، OLAP * 1 در اولین تقریب، "روی انگشتان"، می تواند به عنوان یک روش خاص برای تجزیه و تحلیل داده ها و به دست آوردن گزارش ها تعریف شود. ماهیت آن ارائه یک جدول چند بعدی به کاربر است که به طور خودکار داده ها را در بخش های مختلف خلاصه می کند و به شما امکان می دهد محاسبات و شکل گزارش را به صورت تعاملی کنترل کنید. این مقاله در مورد فناوری و عملیات اساسی OLAP با استفاده از مثال تجزیه و تحلیل فاکتورهای یک عمده فروش مواد غذایی صحبت خواهد کرد.

*یک. OLAP - پردازش تحلیلی آنلاین، تجزیه و تحلیل داده های آنلاین.

به عنوان یک ابزار، ما یک سیستم OLAP از ساده ترین و ارزان ترین کلاس - مشتری OLAP * 1 را در نظر خواهیم گرفت. به عنوان مثال، ساده ترین محصول از تعداد مشتریان OLAP انتخاب شد - "Kontur Standard" توسط آزمایشگاه Intersoft. (برای وضوح، بعداً در مقاله، اصطلاحات پذیرفته شده OLAP با حروف پررنگ نشان داده می شوند و با همتایان انگلیسی زبان خود همراه می شوند.)

*یک. جزئیات بیشتر در مورد طبقه بندی سیستم های OLAP در مقاله "OLAP ساخت روسیه" در PC Week / RE، №3 / 2001 توضیح داده شده است.

بنابراین، بیایید به کار با سیستم بپردازیم. ابتدا باید منبع داده را شرح دهید - مسیر جدول و فیلدهای آن. این به کاربر بستگی دارد که پیاده سازی فیزیکی پایگاه داده را می داند. برای کاربران نهایی، نام جدول و فیلدهای آن را به اصطلاحات دامنه ترجمه می کند. پشت "منبع داده" یک جدول محلی، جدول یا نمای SQL Server یا رویه ذخیره شده وجود دارد.

به احتمال زیاد، در یک پایگاه داده خاص، فاکتورها نه در یک، بلکه در چندین جدول ذخیره می شوند. علاوه بر این، برخی از فیلدها یا رکوردها ممکن است برای تجزیه و تحلیل استفاده نشوند. بنابراین، سپس یک Selection (مجموعه نتیجه یا پرس و جو) ایجاد می شود که در آن موارد زیر پیکربندی می شوند: الگوریتم اتصال جداول توسط فیلدهای کلیدی، شرایط فیلتر و مجموعه ای از فیلدهای برگشتی. بیایید انتخاب خود را "فاکتورها" بنامیم و تمام فیلدهای منبع داده "فاکتورها" را در آن قرار دهیم. بنابراین، متخصص فناوری اطلاعات، با ایجاد لایه معنایی، اجرای فیزیکی پایگاه داده را از کاربر نهایی پنهان می کند.

سپس گزارش OLAP تنظیم می شود. این کار می تواند توسط یک متخصص موضوع انجام شود. ابتدا، فیلدهای نمونه گیری داده های مسطح به دو گروه تقسیم می شوند - حقایق (واقعیت ها یا معیارها) و ابعاد (ابعاد). حقایق اعداد هستند و ابعاد «برش‌هایی» هستند که حقایق را خلاصه می‌کنند. در مثال ما، ابعاد عبارتند از: "منطقه"، "شهر"، "مشتری"، "محصول"، "تاریخ" و یک واقعیت وجود خواهد داشت - قسمت "مقدار" فاکتور. در واقع، شما باید یک یا چند الگوریتم تجمیع را انتخاب کنید. OLAP نه تنها قادر به خلاصه کردن، بلکه انجام محاسبات پیچیده تر، تا تجزیه و تحلیل آماری است. انتخاب چندین الگوریتم تجمیع، حقایق مجازی و محاسبه شده را ایجاد می کند. در مثال، یک الگوریتم تجمیع انتخاب شده است - "Sum".

ویژگی خاص سیستم های OLAP تولید ابعاد و داده ها برای دوره های زمانی قدیمی تر از تاریخ و محاسبه خودکار مجموع برای این دوره ها است. بیایید دوره های "سال"، "ربع" و "ماه" را انتخاب کنیم، در حالی که هیچ داده ای برای هر روز در گزارش وجود نخواهد داشت، اما ابعاد تولید شده "سال"، "ربع" و "ماه" ظاهر می شود. بیایید گزارش را «تحلیل فروش» بنامیم و آن را ذخیره کنیم. کار روی ایجاد رابط برنامه تحلیلی به پایان رسیده است.

حال با راه اندازی این رابط به صورت روزانه یا ماهانه کاربر جدول و نموداری را مشاهده می کند که در آن فاکتورها بر اساس کالا، مشتریان و دوره ها خلاصه می شود.

به منظور شهودی ساختن دستکاری با داده ها، ابزارهای مدیریت یک جدول پویا عناصر خود جدول هستند - ستون ها و ردیف های آن. کاربر می تواند آنها را جابجا کند، حذف کند، فیلتر کند و سایر عملیات OLAP را انجام دهد. در این حالت، جدول به طور خودکار جمع های فرعی جدید و مجموع نهایی را محاسبه می کند.

به عنوان مثال، با کشیدن (عملیات "حرکت") ستون "محصول" به مکان اول، گزارش مقایسه ای را دریافت خواهیم کرد - "مقایسه فروش محصولات برای سال". برای جمع آوری داده ها برای یک سال، کافی است ستون های "ربع" و "ماه" را به بالای جدول - "منطقه ابعاد غیر فعال" بکشید. ابعاد "ربع" و "ماه" که به این منطقه منتقل شده اند، بسته خواهند شد (عملیات "بعد نزدیک")، یعنی از گزارش حذف می شوند. حقایق برای سال خلاصه می شود. با وجود بسته بودن ابعاد، می توان از آنها برای تعیین سال، فصل و ماه خاص برای فیلتر کردن داده ها استفاده کرد (عملیات "فیلتر").

برای وضوح بیشتر، اجازه دهید نوع نمودار نشان دهنده جدول OLAP و مکان آن روی صفحه را تغییر دهیم.

کاوش در داده ها (عملیات "دریل کردن") به ما این امکان را می دهد که اطلاعات دقیق تری در مورد فروش محصول مورد نظر خود به دست آوریم. با کلیک بر روی علامت "+" روبروی محصول "قهوه" شاهد حجم فروش آن در بافت مناطق خواهیم بود. پس از باز کردن منطقه اورال، حجم فروش را در بافت شهرهای منطقه اورال دریافت می کنیم، با بررسی داده های یکاترینبورگ، می توانیم داده های خریداران عمده فروشی این شهر را مشاهده کنیم.

همچنین می توانید از اندازه گیری های باز برای تنظیم فیلترها استفاده کنید. برای مقایسه پویایی فروش شیرینی در مسکو و یکاترینبورگ، اجازه دهید فیلترهایی را در ابعاد "محصول" و "شهر" تنظیم کنیم.

اندازه گیری های غیر ضروری را ببندید و نوع نمودار "خط" را انتخاب کنید. در نمودار به دست آمده، می توانید پویایی فروش را ردیابی کنید، نوسانات فصلی و رابطه بین سقوط و رشد فروش کالاها را در شهرهای مختلف ارزیابی کنید.

بنابراین، ما مطمئن شدیم که فناوری OLAP به کاربر اجازه می‌دهد تا ده‌ها نوع گزارش مختلف را از یک رابط واحد منتشر کند و یک جدول OLAP پویا را با ماوس دستکاری کند. وظیفه برنامه نویسی که صاحب چنین ابزاری است، کدگذاری معمول فرم های گزارش نیست، بلکه پیکربندی مشتری OLAP برای پایگاه های داده است. در عین حال، روش های مدیریت گزارش برای کاربر نهایی بصری است.

در واقع، OLAP ادامه و توسعه طبیعی ایده صفحات گسترده است. اساسا، رابط بصری OLAP نیز یک صفحه گسترده است، اما مجهز به یک موتور محاسباتی قدرتمند و یک استاندارد ویژه برای ارائه و دستکاری داده ها است. علاوه بر این، برخی از کلاینت‌های OLAP به عنوان افزونه‌های MS Excel پیاده‌سازی می‌شوند. بنابراین، یک ارتش یقه سفید میلیون نفری، که با اطمینان بر صفحات گسترده تسلط دارند، خیلی سریع بر ابزارهای OLAP تسلط پیدا می کنند. برای آنها، این یک "انقلاب مخملی" است که فرصت های جدیدی را فراهم می کند، اما با نیاز به آموزش مجدد مرتبط نیست.

اگر خواننده پس از مطالعه این مقاله علاقه خود را به OLAP از دست نداده است، می تواند به مطالب ذکر شده در ابتدا مراجعه کند. مجموعه هایی از چنین مطالبی در تعدادی از سایت ها در اینترنت از جمله سایت آزمایشگاه Intersoft - www.iso.ru ارسال شده است. همچنین می توانید نسخه آزمایشی سیستم Kontur Standard را با مثال توضیح داده شده در مقاله از آن دانلود کنید.

پردازش تحلیلی تحلیلی (OLAP) یک فناوری است که برای سازماندهی پایگاه های داده کسب و کار بزرگ و پشتیبانی از هوش تجاری استفاده می شود. پایگاه های داده OLAP به یک یا چند مکعب تقسیم می شوند و هر مکعب توسط مدیر مکعب سازماندهی می شود تا با نحوه بازیابی و تجزیه و تحلیل داده ها مطابقت داشته باشد تا ایجاد و استفاده از PivotTable و PivotChart گزارش های مورد نیاز شما را آسان تر کند.

در این مقاله

هوش تجاری چیست؟

یک تحلیلگر کسب و کار اغلب می خواهد تصویر بزرگی از یک کسب و کار به دست آورد تا روندهای گسترده تری را بر اساس داده های انباشته مشاهده کند و همچنین روندها را به هر تعداد متغیر تقسیم شده مشاهده کند. هوش تجاری فرآیند استخراج داده ها از پایگاه داده OLAP و تجزیه و تحلیل آن داده ها برای ارائه اطلاعاتی است که می توانید از آنها برای اتخاذ تصمیمات تجاری آگاهانه و اقدام استفاده کنید. به عنوان مثال، با OLAP و Business Intelligence می توانید به سوالات زیر در مورد داده های تجاری پاسخ دهید.

مجموع فروش تمام محصولات در سال 2007 در مقایسه با فروش سال 2006 چگونه است؟

این در مقایسه با تاریخ و زمان برای یک دوره مطلوب در پنج سال گذشته چگونه است؟

مشتریان در سال گذشته چه مقدار پول برای 35 خرج کردند و این رفتار در طول زمان چگونه تغییر کرده است؟

چه تعداد محصول در دو کشور/منطقه خاص در این ماه در مقایسه با ماه مشابه سال گذشته فروخته شد؟

برای هر گروه سنی مشتری تفکیک ROI (هم درصد حاشیه و هم کل) بر اساس دسته محصول چیست؟

فروشندگان بالا و پایین، توزیع کنندگان، تامین کنندگان، مشتریان، شرکا و مشتریان را جستجو کنید.

پردازش تحلیلی آنلاین (OLAP) چیست؟

پایگاه داده های پردازش تحلیلی آنلاین (OLAP) پرس و جوهای هوش تجاری را ساده می کند. OLAP یک فناوری پایگاه داده است که برای پرس و جوها و گزارش ها بهینه شده است، نه برای پردازش تراکنش. منبع داده OLAP پایگاه داده پردازش تراکنش آنلاین (OLTP) است که معمولاً در انبارهای داده ذخیره می شود. داده های OLAP از این داده های تاریخی استخراج شده و در ساختارهایی ترکیب می شوند که امکان تجزیه و تحلیل پیچیده را فراهم می کند. داده های OLAP نیز به صورت سلسله مراتبی سازماندهی می شوند و به جای جداول در مکعب ها ذخیره می شوند. این یک فناوری پیچیده است که از ساختارهای چند بعدی برای دسترسی سریع به داده ها برای تجزیه و تحلیل استفاده می کند. در این سازمان، برای گزارش PivotTable یا PivotChart، می‌توانید به راحتی داده‌های خلاصه سطح بالا، مانند کل فروش کل کشور یا منطقه را نمایش دهید و اطلاعاتی درباره سایت‌هایی که فروش در آن‌ها بالا یا ضعیف است را نمایش دهید.

پایگاه داده های OLAP برای سرعت بخشیدن به بارگذاری داده ها طراحی شده اند. از آنجایی که سرور OLAP، و نه مایکروسافت آفیس اکسل، مقادیر خلاصه شده را محاسبه می کند، هنگام ایجاد یا تغییر گزارش، داده های کمتری باید به اکسل ارسال شود. این رویکرد به شما اجازه می دهد تا با حجم بیشتری از داده های خام کار کنید تا زمانی که داده ها در یک پایگاه داده سنتی سازماندهی شده بودند، جایی که اکسل تمام رکوردهای فردی را بازیابی می کند و مقادیر خلاصه شده را محاسبه می کند.

پایگاه‌های داده OLAP شامل دو نوع داده اصلی است: اندازه‌گیری‌ها، که داده‌های عددی، کمیت‌ها و میانگین‌ها هستند که برای تصمیم‌گیری آگاهانه تجاری استفاده می‌شوند، و ابعاد، که دسته‌هایی هستند که برای سازماندهی آن معیارها استفاده می‌شوند. پایگاه های داده OLAP به شما کمک می کند تا داده های خود را با سطوح مختلف جزئیات سازماندهی کنید و از همان دسته هایی که برای تجزیه و تحلیل داده ها می شناسید استفاده کنید.

بخش های زیر هر جزء را با جزئیات در زیر توضیح می دهند.

مکعبیساختار داده ای که اندازه گیری ها را بر اساس سطح و سلسله مراتب برای هر بعد که می خواهید تجزیه و تحلیل کنید جمع آوری می کند. مکعب ها ابعاد متعددی مانند زمان، جغرافیا و خطوط تولید را با مجموعاتی مانند فروش و موجودی ترکیب می کنند. مکعب ها به معنای دقیق ریاضی "مکعب" نیستند، زیرا لزوماً اضلاع مشابهی ندارند. با این حال، آنها استعاره Apt را برای یک مفهوم پیچیده نشان می دهند.

اندازه گیری هامجموعه ای از مقادیر در یک مکعب بر اساس یک ستون در جدول واقعی مکعب، که معمولا یک مقدار عددی است. اندازه‌گیری‌ها مقادیر مرکزی در یک مکعب هستند که از قبل پردازش، پردازش و تجزیه و تحلیل می‌شوند. رایج ترین نمونه ها عبارتند از فروش، درآمد، درآمد و هزینه.

عضویک مورد در یک سلسله مراتب که یک یا چند مورد از داده ها را نشان می دهد. یک مورد می تواند منحصر به فرد یا غیر منحصر به فرد باشد. به عنوان مثال، 2007 و 2008 اعضای منحصربه‌فرد را در سطح سال بعد زمانی نشان می‌دهند، در حالی که ژانویه اعضای غیر منحصر به فرد را در سطح ماه نشان می‌دهد زیرا بیش از یک ژانویه در بعد زمانی وجود دارد زیرا حاوی داده‌هایی برای بیش از یک سال است.

عضو محاسبه شدهیک عضو بعد که مقدار آن در زمان اجرا با استفاده از یک عبارت محاسبه می شود. مقادیر محاسبه شده اعضا را می توان از مقادیر دیگر اعضا استخراج کرد. به عنوان مثال، یک آیتم محاسبه شده "سود" را می توان با کم کردن ارزش اقلام و همچنین بهای تمام شده از ارزش اقلام، فروش تعیین کرد.

بعد، ابعاد، اندازهمجموعه‌ای از یک یا چند سلسله مراتب سطح مکعبی مرتب شده که کاربر می‌فهمد و به عنوان مبنایی برای تجزیه و تحلیل داده‌ها استفاده می‌کند. به عنوان مثال، یک بعد جغرافیایی ممکن است شامل سطوح کشور / منطقه، ایالت / ناحیه و شهر باشد. علاوه بر این، یک بعد زمانی می تواند شامل یک سلسله مراتب با سطوح سال، سه ماهه، ماه و روز باشد. در گزارش PivotTable یا گزارش PivotChart، هر سلسله مراتب به مجموعه‌ای از فیلدها تبدیل می‌شود که می‌توانید آن‌ها را گسترش داده و جمع کنید تا سطوح پایین‌تر یا بالاتر را نشان دهید.

سلسله مراتبساختار درختی منطقی که اعضای بُعدی را مرتب می کند که هر عضو یک والد و صفر یا چند فرزند داشته باشد. کودک عضوی از گروه قبلی در سلسله مراتب است که مستقیماً با عضو فعلی مرتبط است. به عنوان مثال، در سلسله مراتب زمانی شامل سطوح چهارم، ماه و روز، ژانویه فرزند Qtr1 است. والد عضوی است در سطح پایین‌تری از سلسله مراتب که مستقیماً با عضو فعلی مرتبط است. ارزش والدین معمولاً تجمیع ارزش های همه فرزندان است. به عنوان مثال، در یک سلسله مراتب زمانی شامل سطوح "ربع"، "ماه" و "روز"، Qtr1 والد ژانویه است.

مرحلهدر یک سلسله مراتب، داده ها را می توان در سطوح پایین تر و بالاتر از جزئیات، مانند سال، چهارم، ماه، و سطوح روزانه در سلسله مراتب زمانی سازماندهی کرد.

توابع OLAP در اکسل

بازیابی داده های OLAPهمانطور که می توانید به سایر منابع داده خارجی متصل شوید، می توانید به منابع داده OLAP متصل شوید. می توانید با پایگاه های داده ایجاد شده با Microsoft SQL Server OLAP Services 7.0، Microsoft SQL Server Analysis Services 2000 و Microsoft SQL Server Analysis Services 2005 و Microsoft OLAP Server Products کار کنید. اکسل همچنین می تواند با محصولات OLAP شخص ثالث که با OLE-DB برای OLAP سازگار هستند کار کند.

داده‌های OLAP را فقط می‌توان به‌عنوان گزارش PivotTable یا گزارش PivotChart یا به‌عنوان تابع کاربرگ تبدیل شده از گزارش PivotTable نشان داد، اما نه به‌عنوان یک محدوده داده خارجی. می‌توانید گزارش‌های OLAP PivotTable و PivotChart را در قالب‌های گزارش ذخیره کنید، و می‌توانید فایل‌های Office Data Connection (ODC) را برای اتصال به پایگاه‌های داده OLAP برای جستجوهای OLAP ایجاد کنید. هنگامی که فایل .odc را در اکسل باز می کنید، یک گزارش PivotTable خالی نمایش داده می شود که آماده میزبانی است.

فایل های مکعبی را برای استفاده آفلاین ایجاد کنیدمی توانید یک فایل مکعب مستقل (. Cub) با زیر مجموعه ای از داده ها از پایگاه داده سرور OLAP ایجاد کنید. فایل‌های مکعب آفلاین برای کار با داده‌های OLAP زمانی که به شبکه متصل نیستید استفاده می‌شوند. با استفاده از یک مکعب، می‌توانید با داده‌های بیشتری در گزارش PivotTable یا PivotChart نسبت به موارد دیگر کار کنید و سرعت بازیابی داده‌ها را افزایش دهید. فقط در صورتی می توانید فایل های Cube ایجاد کنید که از یک ارائه دهنده OLAP مانند Microsoft SQL Analysis Services 2005 استفاده می کنید که از این ویژگی پشتیبانی می کند.

اقدامات سروریک عملکرد سرور یک تابع اختیاری است که یک مدیر مکعب OLAP می‌تواند روی سروری تعریف کند که از یک آیتم مکعب یا اندازه‌گیری به عنوان پارامتر در یک جستجو برای بازیابی اطلاعات در مکعب یا برای راه‌اندازی برنامه دیگری مانند مرورگر استفاده می‌کند. اکسل از URLها، گزارش‌ها، مجموعه‌های ردیف، تمرین‌های دقیق و دقیق عملکرد سرور پشتیبانی می‌کند، اما از اپراتور و مجموعه داده‌های بومی خود پشتیبانی نمی‌کند.

KPIشاخص عملکرد کلیدی (KPI) یک معیار محاسبه‌شده ویژه است که بر روی سرور تعریف شده است و «شاخص‌های کلیدی عملکرد»، از جمله وضعیت را ردیابی می‌کند (مقدار فعلی یک عدد خاص است). و روند (مقادیر در طول زمان). هنگامی که آنها نمایش داده می شوند، سرور می تواند نمادهای مناسب، مشابه نماد جدید اکسل، را برای ردیف کردن در بالا یا پایین سطوح وضعیت (مثلاً برای نماد توقف)، ارسال کند و مقدار را به سمت بالا یا پایین حرکت دهد (به عنوان مثال، یک جهت. نماد فلش).

قالب بندی سرورمدیران Cube می‌توانند با استفاده از قالب‌بندی رنگ، قالب‌بندی فونت، و قوانین قالب‌بندی مشروط، معیارها و اعضای محاسبه‌شده را ایجاد کنند، که می‌تواند به عنوان یک قانون استاندارد تجاری سازمانی اختصاص داده شود. به عنوان مثال، فرمت سمت سرور برای درآمد ممکن است یک قالب ارز عددی باشد، رنگ سلول اگر مقدار آن بزرگتر یا مساوی 30000 باشد، سبز و اگر مقدار کمتر از 30000 باشد قرمز است و سبک قلم اگر مقدار کمتر از 30000 باشد و اگر مقدار مثبت باشد پررنگ - معمولی. بزرگتر یا مساوی 30000 است. برای اطلاعات بیشتر، رجوع کنید به.

زبان ادارییک مدیر مکعب می‌تواند ترجمه‌هایی را برای داده‌ها و خطاها در سرور برای کاربران تعریف کند تا اطلاعات PivotTable را به زبانی دیگر مشاهده کنند. این تابع به عنوان یک ویژگی اتصال فایل تعریف شده است و منطقه کامپیوتر کاربر و کشور باید با زبان رابط مطابقت داشته باشد.

اجزای نرم افزار مورد نیاز برای دسترسی به منابع داده OLAP

ارائه دهنده OLAPبرای تنظیم منابع داده OLAP برای اکسل، به یکی از ارائه دهندگان OLAP زیر نیاز دارید.

ارائه دهنده OLAP مایکروسافتاکسل شامل یک درایور منبع داده و نرم افزار مشتری برای دسترسی به پایگاه های داده ایجاد شده با Microsoft SQL Server olap نسخه 7.0، Microsoft SQL Server olap نسخه 2000 (8.0) و Microsoft SQL Server Analysis نسخه 2005 (9، 0) است.

ارائه دهندگان OLAP شخص ثالثسایر محصولات OLAP نیاز به درایورهای اضافی و نرم افزار مشتری برای نصب دارند. برای استفاده از قابلیت‌های داده OLAP Excel، محصول شخص ثالث باید با استاندارد OLE-DB برای OLAP مطابقت داشته باشد و با Microsoft Office سازگار باشد. برای اطلاعات در مورد نصب و استفاده از یک ارائه دهنده OLAP شخص ثالث، با سرپرست سیستم یا ارائه دهنده محصول OLAP تماس بگیرید.

پایگاه های داده سرور و فایل های مکعبنرم افزار سرویس گیرنده Excel OLAP از اتصال به دو نوع پایگاه داده OLAP پشتیبانی می کند. اگر پایگاه داده روی سرور OLAP آنلاین است، می توانید داده های خام را مستقیماً از آن دریافت کنید. اگر یک فایل مکعب آفلاین دارید که حاوی داده های OLAP یا یک فایل تعریف مکعب است، می توانید به آن فایل متصل شوید و داده های خام را از آن دریافت کنید.

منابع دادهیک منبع داده دسترسی به تمام داده های موجود در پایگاه داده OLAP یا فایل مکعب آفلاین را فراهم می کند. هنگامی که یک منبع داده OLAP ایجاد کردید، می توانید گزارش ها را بر اساس آن قرار دهید و داده های OLAP را به عنوان گزارش PivotTable یا PivotChart و به عنوان یک تابع کاربرگ تبدیل شده از یک گزارش PivotTable به Excel برگردانید.

مایکروسافت پرس و جوبا Query می توانید داده ها را از یک پایگاه داده خارجی مانند Microsoft SQL یا Microsoft Access بازیابی کنید. برای بازیابی داده ها از یک OLAP PivotTable مرتبط با یک فایل مکعب، نیازی به استفاده از پرس و جو ندارید. اطلاعات تکمیلی.

تفاوت در ویژگی های OLAP و غیر OLAP داده های منبع

اگر با گزارش‌های PivotTable و PivotCharts از داده‌های منبع OLAP و انواع دیگر داده‌های منبع کار می‌کنید، تفاوت‌هایی را در عملکرد مشاهده خواهید کرد.

بازیابی داده هاهر زمان که طرح گزارش تغییر کند، سرور OLAP داده های جدید را به اکسل برمی گرداند. با انواع دیگر منابع داده خارجی، همه داده های منبع را یکباره پرس و جو می کنید، یا می توانید پارامترهایی را برای پرس و جو فقط هنگام نمایش عناصر مختلف فیلدهای فیلتر گزارش تنظیم کنید. علاوه بر این، شما چند گزینه دیگر برای به روز رسانی گزارش دارید.

در گزارش‌های مبتنی بر داده‌های خام OLAP، گزینه‌های فیلد فیلتر گزارش موجود نیست، پرسش پس‌زمینه در دسترس نیست، و گزینه بهینه‌سازی حافظه در دسترس نیست.

توجه داشته باشید:گزینه بهینه سازی حافظه نیز برای منابع داده OLEDB و گزارش های PivotTable بر اساس طیف وسیعی از سلول ها در دسترس نیست.

انواع میدانداده های منبع OLAP. فیلدهای بعد فقط می توانند به عنوان ردیف (ردیف)، ستون (رده) یا فیلد صفحه استفاده شوند. از فیلدهای اندازه گیری فقط می توان به عنوان فیلدهای مقدار استفاده کرد. برای انواع دیگر داده های منبع، همه فیلدها را می توان در هر نقطه از گزارش استفاده کرد.

دسترسی به داده های دقیقبرای داده های منبع OLAP، سرور سطوح موجود جزئیات را تعیین می کند و مقادیر خلاصه را محاسبه می کند، بنابراین رکوردهای جزئیات که مقادیر خلاصه را تشکیل می دهند ممکن است در دسترس نباشند. با این حال، سرور می تواند فیلدهای ویژگی را ارائه دهد که می توانید آنها را نمایش دهید. انواع دیگر داده‌های منبع دارای فیلدهای ویژگی نیستند، اما می‌توانید اطلاعات پایه را برای فیلدهای داده و مقادیر اعضا نمایش دهید و اعضای غیر داده را نمایش دهید.

ممکن است فیلدهای فیلتر گزارش OLAP نداشته باشند همهعناصر و فرمان نمایش صفحات فیلتر گزارشغیر قابل دسترسی.

ترتیب مرتب سازی اولیهبرای داده های منبع OLAP، موارد ابتدا به ترتیبی که توسط سرور OLAP برگردانده می شوند، نمایش داده می شوند. می توانید ترتیب اقلام را مرتب کنید یا به صورت دستی تغییر دهید. برای انواع دیگر داده های منبع، موارد گزارش جدید ابتدا بر اساس نام مورد به ترتیب صعودی مرتب می شوند.

نیمیسرورهای OLAP مقادیر خلاصه را مستقیماً برای گزارش ارائه می دهند، بنابراین نمی توانید توابع خلاصه را برای فیلدهای مقدار تغییر دهید. برای انواع دیگر داده های منبع، می توانید تابع مجموع را برای فیلد مقدار تغییر دهید و از چند توابع خلاصه برای همان فیلد مقدار استفاده کنید. شما نمی توانید فیلدهای محاسبه شده و اعضای محاسبه شده را در گزارش ها با داده های منبع OLAP ایجاد کنید.

جمع های فرعیدر گزارش‌هایی با داده‌های خام OLAP، نمی‌توانید تابع خلاصه را برای جمع‌های فرعی تغییر دهید. با انواع دیگر داده‌های منبع، می‌توانید توابع فرعی را برای جمع‌های فرعی تغییر دهید و جمع‌های فرعی را برای تمام فیلدهای ردیف و ستون نشان دهید یا پنهان کنید.

برای داده‌های منبع OLAP، می‌توانید موارد پنهان را هنگام محاسبه جمع‌های فرعی و کل کل اضافه یا حذف کنید. برای انواع دیگر داده‌های منبع، می‌توانید موارد پنهان در فیلدهای فیلتر گزارش را در جمع‌های فرعی بگنجانید، اما موارد پنهان در سایر فیلدها به‌طور پیش‌فرض حذف می‌شوند.

شگفت انگیز - نزدیک ...

در طول کار خود اغلب نیاز به تهیه گزارش های پیچیده داشتم، دائماً در تلاش بودم تا چیزی مشترک در آنها بیابم تا آنها را ساده تر و جهانی تر بسازم، حتی مقاله ای در مورد این موضوع "درخت اوسیپوف" نوشتم و منتشر کردم. با این حال، مقاله من مورد انتقاد قرار گرفت و گفت که تمام مشکلاتی که من مطرح کردم مدتهاست در MOLAP.RU v.2.4 (www.molap.rgtu.ru) حل شده است و توصیه می شود به جداول خلاصه در EXCEL نگاه کنید.
به قدری ساده بود که با استفاده از دستان کوچک مبتکر خود، یک طرح بسیار ساده برای تخلیه داده ها از 1C7 یا هر پایگاه داده دیگری (از این پس 1C به معنای هر پایگاه داده) و تجزیه و تحلیل در OLAP دریافت کردم.
من فکر می کنم بسیاری از طرح های آپلود OLAP بسیار پیچیده هستند، من سادگی را انتخاب می کنم.

مشخصات فنی :

1. فقط EXCEL 2000 برای عملیات مورد نیاز است.
2. خود کاربر می تواند بدون برنامه نویسی گزارش طراحی کند.
3. تخلیه از 1C7 در قالب فایل متنی ساده.
4. برای تراکنش های دفتر کل، از قبل یک پردازش بارگذاری عمومی وجود دارد که در هر پیکربندی کار می کند. پردازش نمونه برای بارگذاری داده های دیگر در دسترس است.
5. می توانید فرم های گزارش را از قبل طراحی کنید و سپس آنها را بدون طراحی مجدد روی داده های مختلف اعمال کنید.
6. عملکرد بسیار خوب. در اولین مرحله طولانی، ابتدا داده ها از یک فایل متنی به EXCEL وارد می شوند و یک مکعب OLAP ساخته می شود و سپس به سرعت می توان هر گزارشی را بر اساس این مکعب ساخت. به عنوان مثال، داده های مربوط به فروش محصول توسط یک فروشگاه به مدت 3 ماه با مجموعه ای از 6000 محصول در 8 دقیقه در Cel600-128M در EXCEL بارگذاری می شود، رتبه بندی محصولات و گروه ها (گزارش OLAP) در 1 دقیقه مجدداً محاسبه می شود.
7. داده ها از 1C7 به طور کامل برای مدت زمان مشخص (همه جابجایی ها، در همه انبارها، شرکت ها، حساب ها) دانلود می شوند. هنگام وارد کردن به EXCEL، می توان از فیلترهایی استفاده کرد که فقط داده های لازم را برای تجزیه و تحلیل بارگذاری می کنند (به عنوان مثال، از تمام حرکات، فقط فروش).
8. در حال حاضر روش هایی برای تجزیه و تحلیل حرکات یا باقیمانده ها ایجاد شده است، اما نه حرکت ها و باقیمانده ها با هم، هرچند در اصل این امکان وجود دارد.

OLAP چیست؟ : (www.molap.rgtu.ru)

فرض کنید شما یک شبکه فروش دارید. اجازه دهید داده های مربوط به عملیات تجاری در یک فایل متنی یا جدولی به شکل بارگذاری شود:

تاریخ - تاریخ بهره برداری
ماه - ماه عملیات
هفته - هفته جراحی
نوع - خرید، فروش، بازگشت، حذف
طرف مقابل - یک سازمان خارجی درگیر در یک عملیات
نویسنده - شخصی که فاکتور را صادر کرده است

به عنوان مثال، در 1C، یک خط از این جدول با یک خط فاکتور مطابقت دارد، برخی از فیلدها (Counterparty، Date) از هدر فاکتور گرفته شده است.

معمولاً داده ها برای تجزیه و تحلیل برای مدت زمان معینی در سیستم OLAP بارگذاری می شوند که اصولاً با اعمال فیلترهای بار می توان دوره دیگری را انتخاب کرد.

این جدول منبع تحلیل OLAP است.

گزارش	اندازه گیری ها	داده ها	فیلتر کنید
چند محصول و به چه میزان در روز فروخته می شود؟	تاریخ، آیتم	مقدار، مقدار	نوع = "فروش"
کدام طرف مقابل کدام محصول را با چه مبلغی در ماه تحویل داده است؟	ماه، طرف مقابل، مورد	مجموع	مشاهده = "خرید"
اپراتورهای فاکتور چه نوع فاکتوری را برای کل دوره گزارش نوشته اند؟		مجموع

خود کاربر تعیین می‌کند که کدام یک از فیلدهای جدول ابعاد، کدام داده و کدام فیلترها باشد. خود سیستم گزارش را به صورت جدولی بصری می سازد. ابعاد را می توان در عناوین ردیف یا ستون جدول گزارش قرار داد.
همانطور که می بینید، از یک جدول ساده، می توانید داده های زیادی را در قالب گزارش های مختلف دریافت کنید.

نحوه استفاده در منزل :

بسته بندی داده ها از کیت توزیع در فهرست c: \ fixin (برای یک سیستم معاملاتی، در گزارش های c: \ امکان پذیر است). readme.txt را بخوانید و تمام دستورالعمل‌های موجود در آن را دنبال کنید.

ابتدا باید پردازشی بنویسید که داده ها را از 1C در یک فایل متنی (جدول) تخلیه می کند. باید ترکیب فیلدهایی را که بارگیری می شوند تعیین کنید.
به عنوان مثال، یک پردازش جهانی آماده که در هر پیکربندی کار می کند و تراکنش ها را برای یک دوره برای تجزیه و تحلیل OLAP تخلیه می کند، فیلدهای زیر را برای تجزیه و تحلیل تخلیه می کند:

تاریخ | روز هفته | هفته | سال | سه ماهه | ماه | سند | شرکت | بدهی | Dt
DtGroupNomenclature | DtSectionNomenclature | اعتبار | مقدار | ارزش مجموع | مقدار
واحد پول
CTDifferent Objects

جایی که زیر پیشوندهای Дт (Кт) زیرمجموعه بدهی (اعتباری) است، Group گروه این زیرمجموعه (در صورت وجود)، بخش گروه گروه، کلاس گروه بخش است.

برای یک سیستم معاملاتی، فیلدها می توانند به شرح زیر باشند:

جهت | نوع حرکت | فروش | محصول | مقدار | قیمت | مقدار | تاریخ | شرکت
| انبار | ارز | سند | روز هفته | هفته | سال | سه ماهه | ماه | نویسنده
| دسته بندی محصولات | دسته حرکت | دسته بندی پیمانکار | گروه محصولات
| ValSum | قیمت تمام شده | طرف مقابل

برای تجزیه و تحلیل داده ها، از جداول "Analysis of moves.xls" ("Analysis of accounting.xls") استفاده کنید. هنگام باز کردن ماکروها، آنها را غیرفعال نکنید، در غیر این صورت نمی توانید گزارش ها را به روز کنید (آنها توسط ماکروها به زبان VBA راه اندازی می شوند). این فایل ها از فایل های C: \ fixin \ motions.txt (C: \ fixin \ buh.txt) گرفته شده اند، در غیر این صورت یکسان هستند. بنابراین، ممکن است لازم باشد داده های خود را در یکی از این فایل ها کپی کنید.
برای آپلود داده های خود در EXCEL، فیلتر خود را انتخاب کرده یا بنویسید و روی دکمه "Generate" در برگه "شرایط" کلیک کنید.
برگه های گزارش با پیشوند "گزارش" شروع می شود. به برگه گزارش بروید، روی "Refresh" کلیک کنید و داده های گزارش مطابق با آخرین داده های بارگذاری شده تغییر می کند.
اگر از گزارش های استاندارد راضی نیستید، یک الگوی ورق Rep وجود دارد. آن را در یک برگه جدید کپی کنید و ظاهر گزارش را با کار با جدول محوری در این برگه سفارشی کنید (درباره کار با جداول محوری - در هر کتابی بر اساس EXCEL 2000). توصیه می‌کنم گزارش‌ها را روی یک مجموعه داده کوچک تنظیم کنید و سپس آنها را روی یک مجموعه داده بزرگ اجرا کنید. هیچ راهی برای غیرفعال کردن ترسیم مجدد جدول در هر بار تغییر طرح گزارش وجود ندارد.

نکات فنی :

هنگام آپلود داده ها از 1C، کاربر پوشه ای را انتخاب می کند که فایل را در آن آپلود کند. من این کار را انجام دادم زیرا به احتمال زیاد در آینده نزدیک چندین فایل (باقی مانده و حرکت) آپلود خواهد شد. سپس، با کلیک بر روی دکمه "ارسال" در Explorer -> "برای تجزیه و تحلیل OLAP در EXCEL 2000"، داده ها از پوشه انتخاب شده در پوشه C: \ fixin کپی می شوند. (برای اینکه این دستور در لیست دستور "ارسال" ظاهر شود و باید فایل "For OLAP analysis in EXCEL 2000.bat" را در پوشه C: \ Windows \ SendTo کپی کنید) بنابراین، داده ها را فوراً با دادن نام بارگیری کنید. به فایل های motions.txt یا buh.txt.

فرمت فایل متنی:
خط اول فایل متنی سرصفحه ستون هایی است که با "|" از هم جدا شده اند، بقیه خطوط حاوی مقادیر این ستون ها هستند که با "|" از هم جدا شده اند.

برای وارد کردن فایل‌های متنی به اکسل، از Microsoft Query (بخشی از EXCEL) برای عملکرد آن استفاده می‌شود، لازم است یک فایل shema.ini در پوشه واردات (C: \ fixin) حاوی اطلاعات زیر باشد:

ColNameHeader = درست است
قالب = محدود (|)
MaxScanRows = 3
CharacterSet = ANSI
ColNameHeader = درست است
قالب = محدود (|)
MaxScanRows = 3
CharacterSet = ANSI

توضیح: motions.txt و buh.txt نام بخش است، مربوط به نام فایل وارد شده است، نحوه وارد کردن یک فایل متنی به اکسل را توضیح می دهد. بقیه پارامترها به این معنی است که خط اول شامل نام ستون ها است، جداکننده ستون "|" است، مجموعه کاراکتر Windows ANSI (برای DOS - OEM) است.
نوع فیلدها به طور خودکار بر اساس داده های موجود در ستون (تاریخ، شماره، خط) تعیین می شود.
لیست فیلدها نیازی به توضیح ندارد - EXCEL و OLAP خود تعیین می کنند که کدام فیلدها در فایل توسط سرصفحه های خط اول وجود دارد.

توجه، تنظیمات منطقه ای خود را "کنترل پنل" -> "تنظیمات منطقه ای" بررسی کنید. در پردازش من، اعداد با جداکننده کاما بارگیری می شوند و تاریخ ها در قالب "DD.MM.YYYY" هستند.

هنگامی که دکمه "Generate" فشار داده می شود، داده ها در جدول محوری در برگه "Base" بارگذاری می شوند و همه گزارش های مربوط به برگه های "گزارش" از این جدول محوری گرفته می شوند.

من درک می کنم که طرفداران MS SQL Server و پایگاه های داده قدرتمند شروع به غر زدن می کنند که من همه چیز را خیلی ساده کرده ام و پردازش من در یک نمونه یک ساله به هم می ریزد، اما اول از همه می خواهم مزایای تجزیه و تحلیل OLAP را برای سازمان های متوسط من این محصول را به عنوان یک ابزار تجزیه و تحلیل یک ساله برای عمده فروشان، تجزیه و تحلیل فصلی برای خرده فروشی و تجزیه و تحلیل عملیاتی برای هر سازمانی قرار می دهم.

مجبور شدم با VBA سرهم کنم تا داده ها از یک فایل با هر لیستی از فیلدها گرفته شود و امکان تهیه فرم های گزارش از قبل وجود داشته باشد.

شرح کار در EXCEL (برای کاربران):

دستورالعمل استفاده از گزارش ها:
1. داده های دانلود شده را برای تجزیه و تحلیل ارسال کنید (با مدیر بررسی کنید). برای انجام این کار، روی پوشه ای که داده ها را از 1C در آن دانلود کرده اید کلیک راست کرده و دستور "Send" و سپس "To OLAP analysis in EXCEL 2000" را انتخاب کنید.
2. فایل "Motion Analysis.xls" را باز کنید.
3. مقدار Filter را انتخاب کنید، فیلترهای مورد نیاز را می توانید در برگه "Values" اضافه کنید.
4. دکمه "Generate" را فشار دهید، داده های آپلود شده در EXCEL بارگذاری می شوند.
5. پس از بارگذاری داده ها در EXCEL، می توانید گزارش های مختلف را مشاهده کنید. برای انجام این کار، کافی است روی دکمه «Refresh» در گزارش انتخاب شده کلیک کنید. برگه های گزارش با Rep شروع می شوند.
توجه! بعد از اینکه مقدار فیلتر را تغییر دادید، باید دوباره روی دکمه "Generate" کلیک کنید تا داده های موجود در EXCEL از فایل آپلود مطابق با فیلترها دوباره بارگذاری شوند.

پردازش نسخه ی نمایشی:

Processing motionsbuh2011.ert آخرین نسخه بارگذاری تراکنش ها از حسابداری 7.7 برای تجزیه و تحلیل در اکسل است. دارای یک کادر انتخاب «پیوست به فایل» است که به شما امکان می‌دهد داده‌ها را به صورت قسمت‌هایی بارگذاری کنید، آنها را به همان فایل پیوست کنید، نه اینکه دوباره آنها را در همان فایل آپلود کنید:

پردازش motionswork.ert داده های فروش را برای تجزیه و تحلیل در اکسل آپلود می کند.

نمونه گزارش ها:

تخته شطرنجی بر اساس نوشته ها:

حجم کاری اپراتور بر اساس نوع فاکتورها:

P.S. :

واضح است که با یک طرح مشابه، می توانید بارگیری داده ها را از 1C8 سازماندهی کنید.
در سال 2011، کاربری با من تماس گرفت که باید این پردازش را در 1C7 نهایی کند تا حجم زیادی از داده را بارگذاری کند، من یک برون سپاری پیدا کردم و این کار را انجام دادم. بنابراین توسعه کاملاً مرتبط است.

پردازش motionsbuh2011.ert برای مقابله با تخلیه حجم زیادی از داده ها بهبود یافته است.

اولین رابط جداول محوری، همچنین به نام گزارش های محوری، در اکسل در سال 1993 (نسخه اکسل 5.0) گنجانده شد. علیرغم بسیاری از ویژگی های مفید آن، اکثر کاربران اکسل به سختی از آن استفاده می کنند. حتی کاربران باتجربه اغلب از اصطلاح "گزارش خلاصه" چیزی را می‌گویند که با فرمول‌های پیچیده ساخته شده است. بیایید سعی کنیم استفاده از جدول محوری را در کار روزانه اقتصاددانان رایج کنیم. این مقاله مبانی نظری ایجاد گزارش های خلاصه را مورد بحث قرار می دهد، توصیه های عملی برای استفاده از آنها ارائه می دهد و همچنین نمونه ای از دسترسی به داده ها را بر اساس جداول متعدد ارائه می دهد.

اصطلاحات تجزیه و تحلیل داده های چند متغیره

اکثر اقتصاددانان اصطلاحات "داده های چند بعدی"، "مکعب مجازی"، "فناوری های OLAP" و غیره را شنیده اند. اما با یک مکالمه دقیق، معمولاً معلوم می شود که تقریباً همه واقعاً نمی دانند سخنرانی درباره چیست. یعنی منظور از مردم چیز پیچیده ای است و معمولاً به فعالیت های روزانه آنها مربوط نمی شود. در حقیقت، موضوع این نیست.

داده های چند بعدی، اندازه گیری ها

به جرات می‌توان گفت که اقتصاددانان تقریباً دائماً با داده‌های چند بعدی مواجه هستند، اما سعی می‌کنند آن‌ها را به شکلی از پیش تعریف‌شده با استفاده از صفحات گسترده نمایش دهند. چند بعدی بودن در اینجا به معنای توانایی وارد کردن، مشاهده یا تجزیه و تحلیل همان اطلاعات با تغییر در ظاهر، استفاده از گروه بندی های مختلف و انواع داده ها است. به عنوان مثال، یک طرح فروش را می توان با توجه به معیارهای زیر تجزیه و تحلیل کرد:

• انواع یا گروه کالاها؛
• برندها یا دسته بندی های محصول؛
• دوره (ماه، سه ماهه، سال)؛
• خریداران یا گروهی از خریداران؛
• مناطق فروش
• و غیره.

هر یک از معیارهای فوق از نظر تحلیل داده های چند متغیره «بعد» نامیده می شود. می توان گفت که یک اندازه گیری اطلاعات را برای مجموعه ای از مقادیر مشخص مشخص می کند. نوع خاصی از اندازه گیری اطلاعات چند بعدی «داده» است. در مثال ما، داده های طرح فروش می تواند به صورت زیر باشد:

• حجم فروش؛
• قیمت فروش؛
• تخفیف فردی
• و غیره.

در تئوری، داده ها همچنین می توانند بعد استاندارد اطلاعات چند بعدی باشند (به عنوان مثال، می توانید داده ها را بر اساس قیمت فروش گروه بندی کنید)، اما معمولاً داده ها هنوز هم نوع خاصی از ارزش هستند.

بنابراین، می توان گفت که اقتصاددانان در کار عملی از دو نوع اطلاعات استفاده می کنند: داده های چند بعدی (اعداد واقعی و برنامه ریزی شده با ویژگی های فراوان) و کتاب های مرجع (ویژگی ها یا اندازه گیری داده ها).

OLAP

مخفف OLAP (پردازش تحلیلی آنلاین) به معنای واقعی کلمه به عنوان "پردازش تحلیلی در زمان واقعی" ترجمه شده است. این تعریف خیلی مشخص نیست، تقریباً هر گزارشی از هر محصول نرم افزاری را می توان در زیر آن خلاصه کرد. در مفهوم OLAP، به معنای فناوری کار با گزارش های خاص، از جمله نرم افزار، برای به دست آوردن و تجزیه و تحلیل دقیق داده های ساختاری چند بعدی است. یکی از نرم افزارهای محبوبی که فناوری های OLAP را پیاده سازی می کند SQL Server Analysis Server است. حتی برخی به اشتباه آن را تنها نماینده پیاده سازی نرم افزاری این مفهوم می دانند.

مکعب داده های مجازی

"مکعب مجازی" (مکعب چند بعدی، مکعب OLAP) یک اصطلاح فنی است که توسط برخی از فروشندگان نرم افزار تخصصی ابداع شده است. سیستم‌های OLAP معمولاً داده‌ها را در ساختارهای خود آماده و ذخیره می‌کنند و رابط‌های تجزیه و تحلیل ویژه (مثلاً گزارش‌های محوری Excel) به داده‌های این مکعب‌های مجازی دسترسی دارند. در عین حال، استفاده از چنین فضای ذخیره سازی اختصاصی برای پردازش اطلاعات چند بعدی اصلا ضروری نیست. به طور کلی، مکعب مجازی- این آرایه ای از داده های چند بعدی بهینه سازی شده است که برای ایجاد گزارش های خلاصه استفاده می شود. می توان آن را هم از طریق ابزارهای نرم افزاری تخصصی و هم از طریق دسترسی ساده به جداول پایگاه داده یا هر منبع دیگری مانند جدول اکسل به دست آورد.

جدول محوری

Pivot Table یک رابط کاربری برای نمایش داده های چند بعدی است. با استفاده از این رابط می توانید داده ها را گروه بندی، مرتب سازی، فیلتر و تغییر مکان دهید تا نمونه های تحلیلی مختلف را به دست آورید. به روز رسانی گزارش با استفاده از رابط کاربری ساده انجام می شود، داده ها به طور خودکار طبق قوانین مشخص شده جمع می شوند و نیازی به ورودی اضافی یا مکرر هیچ اطلاعاتی نیست. رابط Excel PivotTable شاید محبوب ترین محصول نرم افزاری برای کار با داده های چند بعدی باشد. هم از منابع داده خارجی (مکعب های OLAP و پایگاه های داده رابطه ای) و هم از محدوده های صفحه گسترده داخلی به عنوان منبع داده پشتیبانی می کند. با شروع نسخه 2000 (9.0)، اکسل همچنین از فرم گرافیکی نمایش داده های چند بعدی پشتیبانی می کند - نمودار محوری (Pivot Chart).

رابط PivotTable اکسل به شما امکان می دهد ابعاد داده های چند بعدی را در یک ناحیه از یک کاربرگ قرار دهید. برای سادگی، می‌توانید جدول محوری را به عنوان گزارشی در بالای طیفی از سلول‌ها در نظر بگیرید (در واقع، اتصال خاصی از قالب‌های سلول به فیلدهای جدول محوری وجود دارد). جدول محوری اکسل دارای چهار ناحیه برای نمایش اطلاعات است: فیلتر، ستون ها، ردیف ها و داده ها. ابعاد داده نامگذاری شده است فیلدهای جدول محوری... این فیلدها ویژگی ها و فرمت نمایش خود را دارند.

یک بار دیگر توجه شما را به این نکته جلب می کنم که جدول محوری اکسل صرفاً برای تجزیه و تحلیل داده ها بدون امکان ویرایش اطلاعات در نظر گرفته شده است. از نظر معنایی نزدیک تر، استفاده همه جا از اصطلاح "Pivot Report" است و این رابط تا سال 2000 به این ترتیب نامیده می شد. اما به دلایلی، در نسخه های بعدی، توسعه دهندگان آن را رها کردند.

ویرایش جداول محوری

طبق تعریف آن، فن آوری OLAP، در اصل، توانایی تغییر داده های منبع را در هنگام کار با گزارش ها ندارد. با این وجود، یک کلاس کامل از سیستم‌های نرم‌افزاری در بازار شکل گرفته‌اند که قابلیت‌های تحلیل و ویرایش مستقیم داده‌ها را در جداول چند بعدی پیاده‌سازی می‌کنند. اساساً چنین سیستم هایی بر حل مشکلات بودجه متمرکز هستند.

با استفاده از ابزارهای اتوماسیون داخلی اکسل، می توانید بسیاری از کارهای غیر استاندارد را حل کنید. نمونه ای از اجرای ویرایش برای جداول محوری اکسل بر اساس داده های کاربرگ را می توانید در وب سایت ما بیابید.

آماده سازی داده های چند بعدی

بیایید به استفاده عملی از جداول محوری برسیم. بیایید سعی کنیم داده های فروش را در جهات مختلف تجزیه و تحلیل کنیم. فایل pivottableexample.xlsاز چندین ورق تشکیل شده است. ورق مثالحاوی اطلاعات اولیه در مورد فروش برای یک دوره خاص است. برای سادگی مثال، ما یک شاخص عددی واحد - حجم فروش در کیلوگرم را تجزیه و تحلیل خواهیم کرد. ابعاد داده های کلیدی زیر وجود دارد: محصول، مشتری و حامل (شرکت حمل و نقل). علاوه بر این، چندین بعد داده اضافی وجود دارد که مشخصه یک محصول است: نوع، نام تجاری، دسته بندی، تامین کننده و مشتری: نوع. این داده ها در برگه مراجع جمع آوری می شوند. در عمل، چنین اندازه گیری های بیشتری می تواند وجود داشته باشد.

ورق مثالحاوی یک ابزار استاندارد تجزیه و تحلیل داده ها - AutoFilter. با نگاهی به مثال پر کردن جدول، بدیهی است که داده های مربوط به فروش بر اساس تاریخ (آنها در ستون ها مرتب شده اند) خود را به تجزیه و تحلیل عادی می رساند. علاوه بر این، با استفاده از یک فیلتر خودکار، می توانید سعی کنید داده ها را با ترکیبی از یک یا چند معیار کلیدی خلاصه کنید. مطلقاً هیچ اطلاعاتی در مورد مارک ها، دسته ها و انواع وجود ندارد. هیچ راهی برای گروه بندی داده ها با خلاصه سازی خودکار توسط یک کلید خاص (مثلاً توسط مشتریان) وجود ندارد. ضمناً مجموعه تاریخ ها ثابت است و امکان مشاهده خلاصه اطلاعات برای مدت معینی مثلاً 3 روز به صورت خودکار وجود نخواهد داشت.

به طور کلی وجود یک مکان تاریخ از پیش تعریف شده در این مثال نقطه ضعف اصلی جدول است. با چیدمان تاریخ ها در ستون ها، به نوعی بعد این جدول را از پیش تعریف کردیم و به این ترتیب توانایی استفاده از تجزیه و تحلیل با استفاده از جداول محوری را از خود سلب کردیم.

ابتدا باید از شر این اشکال خلاص شوید - یعنی. مکان از پیش تعریف شده یکی از ابعاد داده اصلی را حذف کنید. نمونه ای از جدول معتبر - برگه حراجی.

جدول به شکل گزارش ورود اطلاعات است. در اینجا، تاریخ، بعد همتای داده است. همچنین لازم به ذکر است که برای تحلیل بعدی در جداول محوری، موقعیت نسبی ردیف ها نسبت به یکدیگر (به عبارت دیگر مرتب سازی) کاملاً بی تفاوت است. رکوردها در پایگاه داده های رابطه ای این ویژگی ها را دارند. این در تجزیه و تحلیل حجم زیادی از پایگاه های داده است که رابط جداول محوری در درجه اول متمرکز است. بنابراین، هنگام کار با منبع داده در قالب محدوده سلولی، باید این قوانین را رعایت کنید. در عین حال، هیچ کس استفاده از ابزارهای رابط اکسل را در کار منع نمی کند - جداول محوری فقط داده ها را تجزیه و تحلیل می کنند و قالب بندی، فیلترها، گروه بندی و مرتب سازی سلول های منبع می تواند دلخواه باشد.

از فیلتر خودکار تا گزارش خلاصه

از نظر تئوری، بر روی داده های برگه فروش، می توان در سه بعد کالا، مشتریان و حامل ها تجزیه و تحلیل کرد. هیچ اطلاعاتی در مورد خواص محصولات و خریداران در این برگه وجود ندارد، که بر این اساس، اجازه نمایش آنها را در جدول محوری نمی دهد. در حالت عادی ایجاد PivotTable برای داده‌های منبع، اکسل اجازه نمی‌دهد داده‌های چند جدول توسط فیلدهای خاص به هم مرتبط شوند. شما می توانید این محدودیت را به صورت برنامه ای حل کنید - نمونه مکمل این مقاله را در وب سایت ما ببینید. برای اینکه به روش های نرم افزاری پردازش اطلاعات متوسل نشوید (به ویژه از آنجایی که آنها جهانی نیستند)، ویژگی های اضافی باید مستقیماً به فرم ورود به گزارش اضافه شود - به برگه SalesAnalysis مراجعه کنید.

استفاده از توابع VLOOKUP تکمیل داده های اصلی با ویژگی های از دست رفته را آسان می کند. اکنون با استفاده از AutoFilter می توانید داده ها را در ابعاد مختلف تجزیه و تحلیل کنید. اما مشکل گروه بندی حل نشده باقی مانده است. به عنوان مثال، ردیابی مقدار فقط توسط مارک ها برای تاریخ های خاص کاملاً مشکل است. اگر خود را به فرمول های اکسل محدود می کنید، باید انتخاب های اضافی را با استفاده از تابع SUMIF ایجاد کنید.

حال بیایید ببینیم رابط جدول محوری چه ویژگی هایی را ارائه می دهد. روی برگه کد آنالیزچندین گزارش بر اساس طیف وسیعی از سلول ها با داده های برگه ساخته شد تجزیه و تحلیل فروش.

اولین جدول تجزیه و تحلیل از طریق رابط Excel 2007 ساخته شده است Ribbon \ Insert \ PivotTable(در اکسل 2000-2003 منو داده \ جدول محوری).

جداول دوم و سوم از طریق کپی و سفارشی سازی بعدی ایجاد شدند. منبع داده برای همه جداول یکسان است. شما می توانید این را با تغییر داده های اصلی بررسی کنید، سپس باید داده های گزارش های خلاصه را به روز کنید.

از دیدگاه ما، مزایای وضوح اطلاعات آشکار است. می‌توانید فیلترها، ستون‌ها و ردیف‌ها را عوض کنید و گروه‌های خاصی از مقادیر را برای هر بعد مخفی کنید و کشیدن و رها کردن دستی و مرتب‌سازی خودکار را اعمال کنید.

خواص و قالب بندی

علاوه بر نمایش مستقیم داده ها، گزینه های زیادی برای نمایش ظاهر جداول محوری وجود دارد. داده های اضافی را می توان با استفاده از فیلترها پنهان کرد. برای یک عنصر یا فیلد واحد، استفاده از آیتم منوی زمینه آسانتر است حذف(در نسخه 2000-2003 پنهان شدن).

همچنین توصیه می شود نمایش سایر عناصر جدول محوری را نه از طریق قالب بندی سلول، بلکه از طریق تنظیم فیلد یا عنصر جدول محوری تنظیم کنید. برای انجام این کار، نشانگر ماوس را به عنصر مورد نظر ببرید، منتظر ظاهر شدن یک شکل مکان نما (به شکل فلش) باشید، سپس با یک کلیک عنصر انتخاب شده را انتخاب کنید. پس از انتخاب، می‌توانید نما را از طریق نوار، منوی زمینه، تغییر دهید یا با گفتگوی قالب سلول استاندارد تماس بگیرید:

علاوه بر این، اکسل 2007 بسیاری از سبک های نمایش PivotTable از پیش تعریف شده را معرفی می کند:

توجه داشته باشید که فیلترهای کنترلی و مناطق کشیدن و رها کردن در نمودار فعال هستند.

دسترسی به داده های خارجی

همانطور که قبلاً اشاره شد، شاید بیشترین تأثیر استفاده از جداول محوری را می توان هنگام دسترسی به داده ها از منابع خارجی - مکعب های OLAP و پرس و جوهای پایگاه داده به دست آورد. چنین منابعی معمولاً مقادیر زیادی از اطلاعات را ذخیره می کنند و همچنین ساختار رابطه ای از پیش تعریف شده ای دارند که تعریف ابعاد داده های چند بعدی را آسان می کند (فیلدهای جدول محوری).

اکسل انواع مختلفی از منابع داده خارجی را پشتیبانی می کند:

بیشترین تأثیر استفاده از منابع خارجی اطلاعات را می توان با استفاده از ابزارهای اتوماسیون (برنامه های VBA) هم برای به دست آوردن داده ها و هم برای پیش پردازش آنها در جداول محوری به دست آورد.

کار با مکعب OLAP در MS Excel

1. اجازه دسترسی به مکعب OLAP سرویس های تحلیل سرور SQL (SSAS) را دریافت کنید
2. MS Excel 2016/2013/2010 باید روی رایانه شما نصب شود (MS Excel 2007 نیز امکان پذیر است، اما کار در آن راحت نیست و عملکرد MS Excel 2003 بسیار ضعیف است)
3. MS Excel را باز کنید، ویزارد را برای پیکربندی اتصال با سرویس تحلیلی راه اندازی کنید:

3.1 نام یا آدرس IP سرور OLAP فعلی را مشخص کنید (گاهی اوقات لازم است شماره پورت باز را مشخص کنید، به عنوان مثال، 192.25.25.102:80). احراز هویت دامنه استفاده می شود:

3.2 یک پایگاه داده چند بعدی و یک مکعب تحلیلی را انتخاب کنید (اگر حق دسترسی به مکعب دارید):

3.3 تنظیمات اتصال به سرویس تحلیلی در یک فایل odc در رایانه شما ذخیره می شود:

3.4 نوع گزارش (جدول محوری / نمودار) را انتخاب کنید و مکان قرارگیری آن را مشخص کنید:

اگر قبلاً یک اتصال در کتاب کار اکسل ایجاد شده است، می توانید دوباره از آن استفاده کنید: منوی اصلی "داده ها" -> "اتصالات موجود" -> اتصال را در این کتاب کار انتخاب کنید -> جدول محوری را در سلول مشخص شده وارد کنید.

4. با اتصال موفقیت آمیز به مکعب، می توانید تجزیه و تحلیل داده های تعاملی را شروع کنید:

قبل از شروع تجزیه و تحلیل داده های تعاملی، لازم است مشخص شود که کدام یک از فیلدها در تشکیل ردیف ها، ستون ها و فیلترها (صفحات) جدول محوری شرکت می کنند. به طور کلی جدول محوری سه بعدی است و می توان فرض کرد که بعد سوم عمود بر صفحه قرار دارد و بخش هایی را موازی با صفحه نمایشگر مشاهده می کنیم و مشخص می کنیم که کدام "صفحه" برای نمایش انتخاب می شود. فیلتر کردن را می توان با کشیدن و رها کردن ویژگی های ابعاد مربوطه در قسمت فیلتر گزارش انجام داد. فیلتر کردن فضای مکعب را محدود می کند، بار روی سرور OLAP را کاهش می دهد، بنابراین بهتر است ابتدا فیلترهای لازم را نصب کنید... سپس ویژگی های ابعاد را در سطر، ستون، و اندازه گیری مناطق را در ناحیه داده PivotTable قرار می دهید.

هر بار که PivotTable تغییر می کند، یک دستور MDX به طور خودکار به سرور OLAP ارسال می شود و داده ها را برمی گرداند. هر چه مقدار داده های پردازش شده بزرگتر و پیچیده تر باشد، شاخص های محاسبه شده، زمان اجرای پرس و جو طولانی تر می شود. با فشردن کلید می توانید اجرای درخواست را لغو کنید در رفتن... آخرین عملیات انجام شده را می توان لغو کرد (Ctrl + Z) یا دوباره انجام داد (Ctrl + Y).

به طور معمول، برای رایج‌ترین ترکیبات مورد استفاده از ویژگی‌های ابعاد، مکعب داده‌های انباشته شده از پیش محاسبه‌شده را ذخیره می‌کند، بنابراین زمان پاسخ برای چنین پرسش‌هایی چند ثانیه است. با این حال، محاسبه تمام ترکیبات ممکن از تجمع غیرممکن است، زیرا این می تواند زمان و فضای ذخیره سازی زیادی را به خود اختصاص دهد. اجرای درخواست های انبوه به داده ها در سطح دانه بندی می تواند به منابع محاسباتی سرور قابل توجهی نیاز داشته باشد، بنابراین زمان اجرای آنها می تواند طولانی باشد. پس از خواندن داده‌ها از درایوهای دیسک، سرور آن‌ها را در حافظه نهان RAM قرار می‌دهد، که اجازه می‌دهد چنین درخواست‌های بعدی فوراً اجرا شوند، زیرا داده‌ها از حافظه پنهان واکشی می‌شوند.

اگر فکر می کنید که درخواست شما به طور مکرر مورد استفاده قرار می گیرد و زمان اجرا رضایت بخش نیست، می توانید برای بهینه سازی اجرای درخواست با خدمات پشتیبانی توسعه تحلیلی تماس بگیرید.

پس از قرار دادن سلسله مراتب در ناحیه سطر / ستون، می توان سطوح فردی را پنهان کرد:

ویژگی های کلیدی(کمتر - برای ویژگی های بالاتر در سلسله مراتب) ابعاد می توانند دارای ویژگی هایی باشند - ویژگی های توصیفی که می توانند هم در راهنمای ابزار و هم در قالب فیلدها نمایش داده شوند:

اگر نیاز به نمایش چندین ویژگی فیلد در یک زمان دارید، می توانید از لیست گفتگوی مربوطه استفاده کنید:

کیت های تعریف شده توسط کاربر

اکسل 2010 توانایی ایجاد تعاملی مجموعه های خود (تعریف شده توسط کاربر) از اعضای ابعاد را معرفی می کند:

برخلاف مجموعه‌هایی که در سمت مکعب ایجاد و ذخیره می‌شوند، مجموعه‌های سفارشی به صورت محلی در کتاب کار اکسل ذخیره می‌شوند و می‌توانند بعداً استفاده شوند:

کاربران پیشرفته می توانند مجموعه هایی را با استفاده از ساختارهای MDX ایجاد کنند:

تنظیم خصوصیات جدول محوری

از طریق آیتم "Pivot جدول پارامترهای ..." از منوی زمینه (با کلیک راست در جدول محوری)، می توانید جدول محوری را سفارشی کنید، به عنوان مثال:
- برگه "خروجی"، پارامتر "طرح بندی کلاسیک جدول محوری" - جدول محوری تعاملی می شود، می توانید فیلدها را بکشید (کشیدن و رها کردن).
- برگه "خروجی"، پارامتر "نمایش موارد بدون داده در ردیف" - ردیف های خالی در جدول محوری نمایش داده می شوند که حاوی مقادیر نشانگر برای آیتم های ابعاد مربوطه نیستند.
- برگه "Layout and Format"، پارامتر "حفظ قالب بندی سلول هنگام به روز رسانی" - در جدول محوری، می توانید قالب سلول ها را هنگام به روز رسانی داده ها لغو و حفظ کنید.

نمودارهای محوری ایجاد کنید

برای یک جدول محوری OLAP موجود، می توانید یک نمودار محوری ایجاد کنید - پای، میله، میله، نمودار، پراکندگی و انواع دیگر نمودارها:

در این حالت، نمودار محوری با جدول محوری همگام می شود - هنگامی که ترکیب نشانگرها، فیلترها، ابعاد را در جدول محوری تغییر می دهید، جدول محوری نیز به روز می شود.

ایجاد داشبورد

جدول محوری اصلی را انتخاب کنید، آن را در کلیپ بورد کپی کنید (Ctrl + C) و یک کپی از آن (Ctrl + V) جایگذاری کنید، که در آن ترکیب نشانگرها را تغییر می دهیم:

برای مدیریت چندین جدول محوری به طور همزمان، یک برش را وارد کنید (عملکرد جدید از MS Excel 2010 در دسترس است). بیایید Slicer خود را به جداول محوری وصل کنیم - در داخل برش دهنده کلیک راست کنید، "اتصالات جدول محوری ..." را از منوی زمینه انتخاب کنید. لازم به ذکر است که می تواند چندین پنل محوری وجود داشته باشد که می تواند جداول محوری را در صفحات مختلف به طور همزمان ارائه دهد که امکان ایجاد داشبوردهای هماهنگ را فراهم می کند.

پانل های برش دهنده را می توان سفارشی کرد: باید پانل را انتخاب کنید، سپس موارد "اندازه و خواص ..."، "تنظیمات برش"، "تخصیص یک ماکرو" را در منوی زمینه مشاهده کنید، که با کلیک راست ماوس فعال می شود، یا مورد "گزینه ها" از منوی اصلی. بنابراین، می‌توان تعداد ستون‌ها را برای عناصر (دکمه‌های) برش، اندازه دکمه‌های برش و پانل تنظیم کرد، تا طرح رنگ و سبک طراحی را از مجموعه موجود برای برش تعریف کرد (یا خود را ایجاد کرد. سبک خود)، برای تعریف عنوان پانل خود، برای اختصاص یک ماکرو برنامه که از طریق آن می توانید عملکرد پانل را گسترش دهید.

اجرای یک کوئری MDX از اکسل

1. اول از همه، شما باید عملیات DRILLTHROUGH را روی یک کلید انجام دهید، یعنی. به داده های دقیق بروید (داده های دقیق در یک برگه جداگانه نمایش داده می شود) و لیست اتصالات را باز کنید.
2. ویژگی های اتصال را باز کنید، به تب "تعریف" بروید.
3. نوع دستور پیش فرض را انتخاب کنید و دستور آماده شده را در قسمت متن دستور قرار دهید. MDXاستعلام؛
4. با کلیک بر روی دکمه پس از بررسی نحو درخواست و داشتن حقوق دسترسی مناسب، درخواست بر روی سرور اجرا می شود و نتیجه در شیت فعلی در قالب یک جدول مسطح معمولی ارائه می شود.
   می‌توانید متن درخواست MDX تولید شده توسط اکسل را با نصب یک افزونه رایگان که سایر قابلیت‌های اضافی را نیز ارائه می‌دهد، مشاهده کنید.

ترجمه به زبان های دیگر

مکعب تحلیلی از بومی سازی به روسی و انگلیسی پشتیبانی می کند (در صورت لزوم، بومی سازی به زبان های دیگر امکان پذیر است). ترجمه‌ها برای نام ابعاد، سلسله مراتب، ویژگی‌ها، پوشه‌ها، اندازه‌گیری‌ها و همچنین عناصر سلسله‌مراتب فردی اعمال می‌شوند، اگر ترجمه‌هایی برای آنها در سمت سیستم‌های حسابداری / ذخیره داده‌ها وجود داشته باشد. برای تغییر زبان، باید ویژگی های اتصال را باز کنید و گزینه زیر را به رشته اتصال اضافه کنید:
ویژگی های توسعه یافته = "محلی = 1033"
جایی که 1033 محلی سازی انگلیسی است
1049 - بومی سازی به روسی

افزونه های اضافی اکسل برای Microsoft OLAP

توانایی کار با مکعب های OLAP مایکروسافت افزایش می یابد اگر از افزونه های اضافی استفاده کنید، به عنوان مثال، OLAP PivotTable Extensions، که به لطف آن می توانید از جستجوی سریع بر اساس ابعاد استفاده کنید:

سایت 2011-01-11 16: 57: 00Z آخرین اصلاح: 2017-10-15 16: 33: 59Z مخاطب سنی: 14-70