در یک جدول محوری استاندارد، اطلاعات منبع در هارد دیسک محلی ذخیره می شود. به این ترتیب، شما همیشه می توانید آنها را مدیریت و سازماندهی مجدد کنید، حتی بدون دسترسی به شبکه. اما این به هیچ وجه در مورد جداول محوری OLAP صدق نمی کند. در OLAP PivotTables، کش هرگز در هارد دیسک محلی ذخیره نمی شود. بنابراین، بلافاصله پس از قطع شدن از شبکه محلیجدول محوری شما غیر قابل استفاده خواهد شد. شما نمی توانید هیچ فیلدی را در آن جابجا کنید.
اگر پس از جدا شدن از شبکه همچنان نیاز به تجزیه و تحلیل داده های OLAP دارید، یک مکعب داده آفلاین ایجاد کنید. یک مکعب داده آفلاین است فایل جداگانهکه یک کش جدول محوری است و داده های OLAP مشاهده شده پس از قطع شدن از شبکه محلی را ذخیره می کند. داده های OLAP کپی شده در جدول محوری را می توان چاپ کرد، در وب سایت http://everest.ua با جزئیات توضیح داده شده است.
برای ایجاد یک مکعب داده آفلاین، ابتدا یک OLAP PivotTable ایجاد کنید. مکان نما را در PivotTable قرار دهید و روی دکمه OLAP Tools در برگه Tools contextual کلیک کنید، که بخشی از گروه برگه های متنی PivotTable Tools است. دستور Offline OLAP را انتخاب کنید (شکل 9.8).
برنج. 9.8. یک مکعب داده آفلاین ایجاد کنید
کادر گفتگوی تنظیمات مکعب داده آفلاین OLAP ظاهر می شود. بر روی دکمه Create Offline Data File کلیک کنید. شما New Data Cube File Wizard را راه اندازی کرده اید. برای ادامه مراحل روی دکمه Next کلیک کنید.
ابتدا باید ابعاد و سطوحی که در مکعب داده گنجانده می شود را مشخص کنید. در کادر محاوره ای، باید داده هایی را که قرار است از پایگاه داده OLAP وارد شوند، انتخاب کنید. ایده این است که تنها ابعادی را که پس از جدا کردن رایانه از شبکه محلی مورد نیاز است، مشخص کنیم. هر چه ابعاد بیشتری را مشخص کنید، اندازه بزرگتریک مکعب داده مستقل خواهد داشت.
روی دکمه Next کلیک کنید تا به کادر محاوره ای بعدی ویزارد بروید. این امکان را به شما می دهد تا اعضا یا موارد داده ای را که در مکعب گنجانده نمی شوند را مشخص کنید. به طور خاص، شما به اندازه گیری میزان فروش اینترنتی نیاز ندارید، بنابراین کادر بررسی آن در لیست پاک می شود. چک باکس پاک شده نشان می دهد که مورد مشخص شده وارد نمی شود و فضای اضافی را در هارد دیسک محلی اشغال می کند.
در مرحله آخر مکان و نام مکعب داده را ارائه دهید. در مورد ما، فایل مکعب MyOfflineCube.cub نامیده می شود و در پوشه Work قرار می گیرد.
فایل های مکعب داده دارای پسوند هستند توله
پس از مدتی، اکسل مکعب داده آفلاین را در پوشه مشخص شده ذخیره می کند. برای آزمایش آن، روی فایل دوبار کلیک کنید، که به طور خودکار یک کتاب کار اکسل ایجاد می کند که حاوی یک جدول محوری مرتبط با مکعب داده انتخاب شده است. پس از ایجاد، می توانید مکعب داده آفلاین را بین همه کاربران علاقه مندی که در حالت LAN آفلاین کار می کنند، توزیع کنید.
پس از اتصال به شبکه محلی خود، می توانید فایل مکعب داده آفلاین را باز کرده و آن و همچنین جدول داده مربوطه را به روز کنید. اصل اصلی این است که یک مکعب داده مستقل فقط برای کار در زمانی که شبکه محلی قطع می شود استفاده می شود، اما پس از بازیابی اتصال، به روز رسانی آن الزامی است. تلاش برای به روز رسانی مکعب داده آفلاین پس از قطع اتصال از اتصال منجر به شکست می شود.
کار با فایل های مکعب آفلاین
یک فایل مکعب آفلاین (. cub) داده ها را در قالب یک مکعب OLAP (پردازش تحلیلی آنلاین) ذخیره می کند. این داده ها می توانند بخشی از پایگاه داده OLAP را در سرور OLAP نشان دهند، یا می توانند مستقل از پایگاه داده OLAP تولید شوند. از یک فایل مکعب آفلاین برای ادامه کار با PivotTable و گزارش های PivotChart زمانی که سرور در دسترس نیست یا زمانی که آفلاین هستید استفاده کنید.
نکته ایمنی:هنگام استفاده یا توزیع یک فایل مکعب آفلاین که حاوی اطلاعات محرمانه یا شخصی است، مراقب باشید. توصیه می شود که داده ها را به جای فایل مکعبی در یک کتاب کار ذخیره کنید تا بتوانید با استفاده از مدیریت حقوق، دسترسی به داده ها را کنترل کنید. اطلاعات اضافیرا می توان در زیر مدیریت حقوق اطلاعات در دفتر یافت.
اگر با یک گزارش PivotTable یا PivotChart بر اساس داده های منبع سرور OLAP کار می کنید، می توانید از Offline Cube Wizard برای کپی کردن داده های منبع در یک فایل مکعب آفلاین جداگانه در رایانه خود استفاده کنید. این فایل‌های آفلاین به یک ارائه‌دهنده داده OLAP برای پشتیبانی از این قابلیت نیاز دارند، مانند MSOLAP از Microsoft Services SQL Serverخدمات تجزیه و تحلیل نصب شده بر روی کامپیوتر.
توجه داشته باشید:ایجاد و استفاده از فایل‌های مکعب آفلاین از سرویس‌های تجزیه و تحلیل مایکروسافت SQL Server منوط به شرایط و مجوز است. تاسیسات مایکروسافت SQL Server. اطلاعات مربوط به مجوز نسخه SQL Server را مرور کنید.
کار با جادوگر مکعب مستقل
برای ایجاد یک فایل مکعب آفلاین، می توانید زیر مجموعه ای از داده ها را در پایگاه داده OLAP با استفاده از جادوگر مکعب آفلاین انتخاب کنید و سپس آن زیر مجموعه را ذخیره کنید. نیازی نیست که گزارش شامل تمام فیلدهای موجود در فایل باشد و همچنین نیازی نیست که هیچ یک از آنها و فیلدهای داده موجود در پایگاه داده OLAP را انتخاب کنید. برای حداقل نگه داشتن فایل، فقط می‌توانید داده‌هایی را که می‌خواهید در گزارش نشان داده شوند، اضافه کنید. شما می توانید همه ابعاد را حذف کنید و برای اکثر انواع ابعاد می توانید جزئیات و اعضای فرعی را نیز حذف کنید سطح بالاکه نیازی به نمایش ندارند. برای همه مواردی که شامل می‌شوید، فیلدهای ویژگی موجود در پایگاه داده برای آن موارد نیز در یک فایل آفلاین ذخیره می‌شوند.
انتقال داده ها به حالت آفلاینو ارتباط معکوس آنها
برای این کار ابتدا باید یک گزارش PivotTable یا PivotChart بر اساس پایگاه داده سرور ایجاد کنید و سپس یک فایل مکعب آفلاین از گزارش ایجاد کنید. پس از آن، می توانید گزارش را بین پایگاه داده سرور و فایل آفلاین در هر زمان تغییر دهید. مثلا اگر استفاده می کنید کامپیوتر قابل حملبرای سفرهای خانگی و ویدیویی، و سپس کامپیوتر را دوباره به شبکه وصل کنید.
در زیر مراحل اساسی برای دنبال کردن آمده است کار مستقلبا داده ها، و سپس داده ها را به اینترنت برگردانید.
یک گزارش PivotTable یا PivotChart بر اساس داده های OLAP ایجاد یا باز کنید که می خواهید به صورت آفلاین به آن دسترسی داشته باشید.
یک فایل مکعب آفلاین در رایانه خود ایجاد کنید. در فصل یک فایل مکعب آفلاین از پایگاه داده سرور OLAP ایجاد کنید(در ادامه این مقاله).
از شبکه جدا شده و با فایل مکعب آفلاین کار کنید.
آنلاین شوید و فایل مکعب را به صورت آفلاین وصل کنید. بخش را بررسی کنید اتصال مجددفایل مکعب آفلاین به پایگاه داده سرور OLAP(در زیر در این مقاله).
به روز رسانی فایل مکعب آفلاین با داده های جدید و ایجاد مجدد فایل مکعب آفلاین. بخش را بررسی کنید به روز رسانی و ایجاد مجدد فایل مکعب آفلاین(در ادامه این مقاله).
وبلاگ
فقط پست های با کیفیت بالا
جداول محوری اکسل و مکعب های OLAP چیست؟
ویدیوی مقاله را تماشا کنید:
OLAPانگلیسی هست. پردازش تحلیلی آنلاین، فناوری تحلیلی برای پردازش داده ها در زمان واقعی. به عبارت ساده - یک ذخیره سازی با داده های چند بعدی (مکعب)، حتی ساده تر - فقط یک پایگاه داده که می توانید از آن داده ها را در اکسل دریافت کنید و با استفاده از ابزار Excel - Pivot Tables - تجزیه و تحلیل کنید.
جداول محورییک رابط کاربری برای نمایش داده های چند بعدی است. به عبارت دیگر، نوع خاصی از جداول است که می توان از آنها برای تهیه تقریباً هر گزارشی استفاده کرد.
برای روشن شدن موضوع، بیایید «جدول عادی» را با «جدول محوری» مقایسه کنیم.
جدول معمولی:
جدول جمع بندی:
تفاوت اصلی جداول محوریآیا وجود پنجره " لیست فیلدهای PivotTable"، که از آن می توانید فیلدهای مورد نیاز را انتخاب کنید و هر جدولی را به طور خودکار دریافت کنید!
نحوه استفاده
باز کن فایل اکسلکه به یک مکعب OLAP، به عنوان مثال "BIWEB" متصل است:
حالا این به چه معناست و چگونه از آن استفاده می کنید؟
فیلدهای مورد نیاز را بکشید تا به عنوان مثال جدولی مانند این بدست آورید:
« علائم بعلاوه»به شما این امکان را می دهد که گزارش را بررسی کنید. در این مثال، "برند" به "نام های اختصاری"، و "ربع" به "ماه"، یعنی. بنابراین:
توابع تحلیلی در اکسل (توابع مکعبی)
مایکروسافت به طور مداوم در حال اضافه کردن قابلیت های جدید به اکسل در زمینه تجزیه و تحلیل و تجسم داده ها است. کار با اطلاعات در اکسل را می توان به عنوان سه لایه نسبتا مستقل نشان داد:
- داده های منبع "به درستی" سازماندهی شده است
- ریاضیات (منطق) پردازش داده ها
- ارائه داده ها
برنج. 1. تجزیه و تحلیل داده ها در اکسل: الف) داده های اولیه، ب) اندازه گیری ج پاور پیوتج) داشبورد؛ برای بزرگ شدن تصویر روی آن کلیک کنید کلیک راستماوس را انتخاب کنید تصویر را در یک برگه جدید باز کنید
دانلود یادداشت با فرمت ورد یا pdf نمونه ها با فرمت اکسل
توابع مکعب و جداول محوری
جداول محوری ساده ترین و در عین حال بسیار قدرتمندترین ابزار ارائه داده ها هستند. آنها می توانند از داده های موجود ساخته شوند: الف) در یک کاربرگ Excel، ب) یک مکعب OLAP، یا ج) یک مدل داده Power Pivot. در دو مورد اخیر، علاوه بر جدول محوری، می‌توانید از توابع تحلیلی (توابع مکعبی) برای تولید گزارش در صفحه اکسل استفاده کنید. جداول محوری ساده تر هستند. عملکرد مکعب‌ها پیچیده‌تر هستند، اما انعطاف‌پذیری بیشتری را به خصوص در طراحی گزارش‌ها فراهم می‌کنند، بنابراین به طور گسترده در داشبورد استفاده می‌شوند.
آنچه در زیر می آید مربوط به فرمول های مکعب و جداول محوری بر اساس مدل Power Pivot و در چند مورد، بر روی مکعب های OLAP است.
یک راه آسان برای به دست آوردن توابع مکعب ها
هنگامی که (اگر) شروع به یادگیری کد VBA کردید، یاد گرفتید که ساده ترین راه برای دریافت کد، ضبط یک ماکرو است. سپس می‌توانید کد را ویرایش کنید، حلقه‌ها، چک‌ها و غیره را اضافه کنید. به همین ترتیب، ساده‌ترین راه برای به دست آوردن مجموعه‌ای از توابع مکعب، تبدیل جدول محوری است (شکل 2). روی هر سلول جدول محوری بایستید، به برگه بروید تحلیل و بررسی، روی دکمه کلیک کنید امکانات OLAPو فشار دهید تبدیل به فرمول.
برنج. 2. تبدیل جدول محوری به مجموعه ای از توابع مکعبی
اعداد حفظ خواهند شد، نه مقادیر، بلکه فرمول هایی که داده ها را از مدل داده های Power Pivot می کشند (شکل 3). می توانید جدول به دست آمده را قالب بندی کنید. از جمله، می توانید ردیف ها و ستون ها را در جدول حذف و وارد کنید. برش باقی می ماند و بر داده های جدول تأثیر می گذارد. وقتی داده های اصلی به روز می شوند، اعداد جدول نیز به روز می شوند.
برنج. 3. جدول بر اساس فرمول مکعب
تابع CUBEVALUE ().
این شاید عملکرد اصلی مکعب ها باشد. معادل مساحت است ارزش هاجدول محوری. CUBEVALUE داده ها را از یک مکعب یا مدل Power Pivot بازیابی می کند و آن را خارج از PivotTable نمایش می دهد. این بدان معناست که شما محدود به PivotTable نیستید و می توانید گزارش هایی با امکانات بی شمار ایجاد کنید.
نوشتن فرمول از ابتدا
شما نیازی به تبدیل PivotTable تمام شده ندارید. شما می توانید هر فرمول مکعبی را از ابتدا بنویسید. به عنوان مثال، فرمول زیر در سلول C10 وارد شده است (شکل 4):
برنج. 4. تابع CUBEVALUE () در سلول C10 فروش دوچرخه را برای تمام سال ها برمی گرداند، درست مانند جدول محوری
یک ترفند کوچک. برای سهولت در خواندن فرمول های مکعب، مطلوب است که در هر خط فقط یک آرگومان قرار گیرد. می توانید پنجره اکسل را کوچکتر کنید. برای انجام این کار، روی نماد کلیک کنید کوچک کردن به پنجرهواقع در سمت راست گوشه بالاییصفحه نمایش و سپس اندازه پنجره را به صورت افقی تنظیم کنید. یک گزینه جایگزین این است که متن فرمول را مجبور کنید تا به آن بپیچید خط جدید... برای انجام این کار، در نوار فرمول، مکان نما را در محلی که می خواهید انتقال دهید قرار دهید و Alt + Enter را فشار دهید.
برنج. 5. پنجره را به حداقل برسانید
نحو تابع CUBEVALUE ()
راهنمای اکسل برای مبتدیان کاملاً دقیق و کاملاً بی فایده است:
CUBEVALUE (اتصال؛ [member_expression1]؛ [member_expression2]؛ ...)
ارتباط- استدلال لازم؛ یک رشته متن که نشان دهنده نام اتصال به مکعب است.
Member_expression- آرگومان اختیاری؛ یک رشته متنی که بیانگر یک عبارت MDX است که یک آیتم یا چند تایی را در یک مکعب برمی گرداند. علاوه بر این، "member_expression" می تواند مجموعه ای باشد که با استفاده از تابع CUBESET تعریف می شود. از Member_expression به عنوان یک برش برای تعریف بخشی از مکعب استفاده کنید که می‌خواهید یک مقدار تجمیع شده را برای آن برگردانید. اگر هیچ اندازه ای در Member_expression مشخص نشده باشد، اندازه پیش فرض برای آن مکعب استفاده خواهد شد.
قبل از رفتن به توضیح نحو تابع CUBEVALUE، چند کلمه در مورد مکعب ها، مدل های داده و رمزنگاری مجموعه.
برخی از اطلاعات در مورد مکعب های OLAP و مدل های داده محوری قدرت
مکعب های داده OLAP ( O n ل ine آتحلیلی پپردازش - تجزیه و تحلیل داده های عملیاتی) به طور خاص برای پردازش تحلیلی و استخراج سریع داده ها از آنها توسعه یافته است. یک فضای سه بعدی را تصور کنید که در آن دوره های زمانی، شهرها و کالاها در امتداد محورها ترسیم شده اند (شکل 5a). گره های چنین شبکه مختصاتی حاوی مقادیر هستند اقدامات مختلف: حجم فروش، سود، هزینه ها، تعداد واحدهای فروخته شده و ... حالا تصور کنید که ده ها یا حتی صدها بعد ... و همچنین اندازه گیری های زیادی وجود دارد. این مکعب چند بعدی OLAP خواهد بود. ایجاد، پیکربندی و نگهداری مکعب های OLAP در اختیار متخصصان فناوری اطلاعات است.
برنج. 5a. مکعب سه بعدی OLAP
فرمول های تحلیلی اکسل (فرمول های مکعبی) نام محورها را استخراج می کنند (به عنوان مثال، زمان، نام عناصر در این محورها (مرداد، شهریور)، مقادیر اقدامات در تقاطع مختصات. این ساختار است که به جداول محوری مبتنی بر مکعب و فرمول های مکعبی اجازه می دهد تا بسیار انعطاف پذیر و پاسخگو به نیازهای کاربر باشند. PivotTable های مبتنی بر کاربرگ اکسل از معیارها استفاده نمی کنند، بنابراین برای اهداف تجزیه و تحلیل داده ها انعطاف پذیر نیستند.
Power Pivot یک ویژگی نسبتا جدید مایکروسافت است. این یک محیط اکسل داخلی و تا حدودی مستقل با رابط کاربری آشنا است. Power Pivot بسیار برتر از PivotTable های استاندارد است. با این حال، توسعه مکعب ها در Power Pivot نسبتا آسان است و مهمتر از همه، نیازی به متخصص IT ندارد. مایکروسافت در حال اجرای شعار خود است: "هوش تجاری برای توده ها!". اگرچه مدل‌های Power Pivot 100% مکعب نیستند، اما می‌توان به آنها به عنوان مکعب نیز اشاره کرد (برای اطلاعات بیشتر به دوره مقدماتی Mark Moore مراجعه کنید. Power Pivot و نسخه بزرگتر توسط Rob Colley. DAX Formulas for Power Pivot).
اجزای اصلی یک مکعب ابعاد، سلسله مراتب، سطوح، اعضا (یا اعضا) و اندازه ها هستند. اندازه گیری -ویژگی اصلی داده های تحلیل شده به عنوان مثال، دسته بندی محصول، دوره زمانی، جغرافیای فروش. بعد چیزی است که می توانیم روی یکی از محورهای جدول محوری قرار دهیم. هر بعد جدا از ارزش های منحصر به فردشامل عضوی است که همه اعضای این بعد را جمع می کند.
اندازه گیری ها بر اساس رسم می شوند سلسله مراتب... به عنوان مثال، یک دسته محصول را می توان به زیر شاخه ها، سپس به مدل ها و در نهایت به نام محصول تقسیم کرد (شکل 5b) سلسله مراتب به شما امکان می دهد داده های خلاصه ایجاد کنید و آن ها را در سطوح مختلف ساختار تجزیه و تحلیل کنید. در مثال ما، سلسله مراتب دسته بندیشامل 4 مرحله.
المانها(انفرادی اعضا) در تمامی سطوح حضور دارند. به عنوان مثال، در سطح دسته، چهار عنصر وجود دارد: لوازم جانبی، دوچرخه، لباس، اجزا. سطوح دیگر عناصر خاص خود را دارند.
معیارهایمقادیر محاسبه شده مانند فروش هستند. اندازه‌های مکعبی در ابعاد خود به نام ذخیره می‌شوند (شکل 9 را در زیر ببینید). اندازه گیری ها سلسله مراتبی ندارند. هر اندازه گیری مقداری را برای همه ابعاد و همه اعضا محاسبه و ذخیره می کند و بر اساس اعضای بعدی که روی محور قرار می دهیم برش داده می شود. آنها همچنین می گویند که کدام مختصات تنظیم می شود یا کدام زمینه فیلتر تنظیم می شود. به عنوان مثال، در شکل. 5а در هر مکعب کوچک همان اندازه محاسبه می شود - سود. و مقدار برگردانده شده توسط اندازه گیری به مختصات بستگی دارد. در سمت راست در شکل 5a نشان داده شده است که سود (در سه مختصات) در مسکو در ماه اکتبر در سیب = 63000 r. اندازه گیری را می توان به عنوان یکی از ابعاد تعبیر کرد. به عنوان مثال، در شکل. 5a به جای محور محصولات، محور مکان معیارهایبا عناصر حجم فروش, سود, واحدهای فروخته شده... سپس هر سلول مقداری ارزش دارد، به عنوان مثال، مسکو، سپتامبر، حجم فروش.
تاپل- عناصر متعدد ابعاد مختلف، مشخص کردن مختصات در امتداد محورهای مکعب که در آن اندازه را محاسبه می کنیم. به عنوان مثال، در شکل. 5a تاپل= مسکو، اکتبر، سیب. همچنین یک تاپل معتبر Perm, apples است. یکی دیگر سیب است، اوت. ابعادی که در یک تاپل گنجانده نشده اند به طور ضمنی در یک تاپل وجود دارند و توسط یک عضو پیش فرض نشان داده می شوند. بنابراین، یک سلول از یک فضای چند بعدی همیشه تعریف می شود مجموعه کاملمختصات، حتی اگر برخی از آنها در تاپل حذف شوند. شما نمی توانید دو عضو از یک بعد را در یک تاپل قرار دهید، نحو اجازه نمی دهد. به عنوان مثال، یک تاپل نامعتبر مسکو و پرم، سیب. برای اجرای چنین MDX به مجموعه ای از دو تاپل نیاز دارید: مسکو و سیب + پرم و سیب.
مجموعه ای از عناصر- چندین عنصر یک بعدی. مثلا سیب و گلابی. مجموعه ای از تاپل ها- چند تاپل، که هر کدام از ابعاد یکسان در یک دنباله تشکیل شده است. به عنوان مثال، مجموعه ای از دو تاپل: مسکو، سیب و پرم، موز.
تکمیل خودکار برای کمک
بیایید به نحو تابع CUBEVALUE برگردیم. بیایید از تکمیل خودکار استفاده کنیم. شروع به تایپ فرمول در سلول کنید:
اکسل تمام اتصالات موجود در کتاب کار اکسل را پیشنهاد می کند:
برنج. 6. اتصال به Power Pivot Data Model همیشه ThisWorkbookDataModel نامیده می شود
برنج. 7. اتصالات به مکعب ها
اجازه دهید به وارد کردن فرمول (در مورد ما، برای مدل داده) ادامه دهیم:
تکمیل خودکار تمام جداول و معیارهای موجود مدل داده را پیشنهاد می کند:
برنج. 8. عناصر سطح اول موجود - نام جدول و مجموعه ای از اقدامات (برجسته شده)
نماد را انتخاب کنید معیارهای... نکته ای را قرار دهید:
CUBEVALUE ("ThisWorkbookDataModel"؛ ".
تکمیل خودکار همه اقدامات موجود را پیشنهاد می کند:
برنج. 9. آیتم های سطح دوم موجود در مجموعه اندازه گیری
اندازه گیری را انتخاب کنید. علامت نقل قول اضافه کنید، پرانتز را ببندید، Enter را فشار دهید.
CUBEVALUE ("ThisWorkbookDataModel"؛ ".")
برنج. 10. فرمول CUBEVALUE در یک سلول اکسل
به طور مشابه، می توانید یک آرگومان سوم را به فرمول خود اضافه کنید:
VBA در Excel Excel.PivotTable Object و کار با Pivot Tables و OLAP Cubes در Excel
10.8 کار با جداول محوری (شیء PivotTable)
شی Excel.PivotTable، کار برنامه‌نویسی با جداول محوری و مکعب‌های OLAP در اکسل با استفاده از VBA، شی PivotCache، ایجاد طرح‌بندی جدول محوری
در جریان فعالیت اکثر شرکت ها، به اصطلاح داده های خام در مورد فعالیت ها انباشته می شود. به عنوان مثال، برای یک تاجر، داده های مربوط به فروش کالاها را می توان جمع آوری کرد - برای هر خرید به طور جداگانه، برای مشاغل سلولی- آمار بار روی ایستگاه های پایهو غیره. اغلب اوقات، مدیریت یک شرکت به اطلاعات تحلیلی نیاز دارد که بر اساس اطلاعات خام تولید می شود - به عنوان مثال، برای محاسبه سهم هر نوع محصول در درآمد شرکت یا کیفیت خدمات در یک منطقه خاص. ایستگاه. استخراج چنین اطلاعاتی از اطلاعات خام بسیار دشوار است: شما باید پرس و جوهای SQL بسیار پیچیده ای را اجرا کنید که زمان زیادی می برد و اغلب با کار فعلی تداخل دارد. بنابراین، امروزه، بیشتر و بیشتر، داده های خام ابتدا در انبار داده - انبار داده، و سپس - در مکعب های OLAP، که برای تجزیه و تحلیل تعاملی بسیار راحت هستند، ادغام می شوند. ساده ترین راه برای در نظر گرفتن مکعب های OLAP مانند جداول چند بعدی است که در آن به جای دو بعد استاندارد (ستون ها و ردیف ها، مانند جداول معمولی)، ابعاد زیادی وجود دارد. اصطلاح "برش" معمولاً برای توصیف ابعاد در یک مکعب استفاده می شود. به عنوان مثال، بخش بازاریابی ممکن است به اطلاعاتی از نظر زمان، منطقه ای، نوع محصول، کانال های فروش و غیره نیاز داشته باشد. با استفاده از مکعب ها (برخلاف پرس و جوهای استاندارد SQL) به راحتی می توان به سوالاتی مانند "چند کالا از این نوع در سه ماهه چهارم سال گذشته در منطقه شمال غرب از طریق توزیع کنندگان منطقه ای فروخته شد."
البته چنین مکعب هایی را نمی توان در پایگاه های داده معمولی ایجاد کرد. مکعب های OLAP به نرم افزار تخصصی نیاز دارند. SQL Server با یک پایگاه داده OLAP از مایکروسافت به نام خدمات تحلیلی عرضه می شود. راه حل های OLAP از Oracle، IBM، Sybase و غیره وجود دارد.
برای کار با چنین مکعب هایی، یک کلاینت خاص در اکسل تعبیه شده است. در روسی به آن می گویند جدول محوری(روی صفحه نمایش گرافیکیاز طریق منو در دسترس است داده ها -> جدول محوری) و به زبان انگلیسی - جدول محوری... بر این اساس، شیئی که این کلاینت نشان می دهد PivotTable نامیده می شود. لازم به ذکر است که او می داند که چگونه نه تنها با مکعب های OLAP، بلکه با داده های معمولی در جداول اکسل یا پایگاه های داده کار کند، اما بسیاری از فرصت ها از دست می روند.
شی PivotTable و PivotTable محصولات نرم افزار پانوراما هستند که توسط مایکروسافت خریداری شده و در اکسل ادغام شده اند. بنابراین، کار با شی PivotTable کمی متفاوت از کار با سایر اشیاء اکسل است. حدس زدن اینکه چه کاری باید انجام شود اغلب دشوار است. بنابراین، توصیه می شود به طور فعال از ضبط کننده ماکرو برای دریافت نکات استفاده کنید. در عین حال، هنگام کار با جداول محوری، کاربران اغلب باید همان عملیات تکراری را انجام دهند، بنابراین اتوماسیون در بسیاری از موقعیت ها ضروری است.
کار برنامه نویسی با جدول محوری چگونه است؟
اولین کاری که باید انجام دهیم این است که یک شی PivotCache ایجاد کنیم که مجموعه رکوردهای بازیابی شده از منبع OLAP را نشان دهد. به طور معمول، این شی PivotCache را می توان با QueryTable مقایسه کرد. فقط یک شی PivotCache در هر PivotTable قابل استفاده است. شی PivotCache با استفاده از روش Add () مجموعه PivotCaches ایجاد می شود:
کم نور PC1 به عنوان PivotCache
تنظیم PC1 = ActiveWorkbook.PivotCaches.Add (xlExternal)
PivotCaches یک مجموعه استاندارد است و از روش هایی که شایسته بررسی دقیق هستند، تنها متد Add () را می توان در آن نام برد. این روش دو پارامتر دارد:
- SourceType- مورد نیاز، نوع منبع داده را برای جدول محوری مشخص می کند. می توانید تعیین کنید که یک PivotTable بر اساس یک محدوده در اکسل، داده های یک پایگاه داده، در یک منبع داده خارجی، PivotTable دیگر و غیره ایجاد کنید. در عمل، معمولاً استفاده از OLAP فقط زمانی منطقی است که داده های زیادی وجود داشته باشد - بر این اساس، شما به یک حافظه خارجی تخصصی (به عنوان مثال، خدمات تحلیل مایکروسافت) نیاز دارید. در این شرایط، xlExternal انتخاب شده است.
- داده ی منبع- در همه موارد اجباری است، به جز مواردی که مقدار پارامتر اول xlExternal است. به بیان دقیق، محدوده داده ای را که PivotTable بر اساس آن ایجاد می شود، تعریف می کند. معمولاً یک شی Range می گیرد.
وظیفه بعدی پیکربندی پارامترهای شی PivotCache است. همانطور که قبلا ذکر شد، این شی بسیار شبیه به QueryTable است و دارای مجموعه ای از ویژگی ها و روش های بسیار مشابه است. برخی از مهمترین خواص و روش ها:
- اتصال ADOC- امکان برگرداندن یک شیء اتصال ADO که به طور خودکار برای اتصال به منبع داده خارجی ایجاد می شود. استفاده شده برای سفارشی سازی اضافیویژگی های اتصال
- ارتباط- دقیقاً به همان روشی عمل می کند که ویژگی همنام شی QueryTable. می تواند رشته اتصال، آبجکت آماده Recordset را بپذیرد، فایل متنی، درخواست وب. فایل مایکروسافتپرس و جو. اغلب، هنگام کار با OLAP، یک رشته اتصال مستقیماً نوشته می شود (زیرا دریافت یک شی Recordset، به عنوان مثال، برای تغییر داده ها منطقی نیست - منابع داده OLAP تقریباً همیشه فقط خواندنی هستند). به عنوان مثال، تنظیم این ویژگی برای اتصال به پایگاه داده Foodmart ( پایگاه آموزشیداده های خدمات تجزیه و تحلیل) در سرور LONDON ممکن است به شکل زیر باشد:
PC1.Connection = "OLEDB؛ ارائه دهنده = MSOLAP.2؛ منبع داده = LONDON1؛ کاتالوگ اولیه = FoodMart 2000"
- خواص CommandTypeو CommandTextبه طور مشابه نوع دستوری را که به سرور پایگاه داده ارسال می شود و متن خود فرمان را توضیح دهید. به عنوان مثال، برای دسترسی به مکعب فروش و وارد کردن آن به طور کامل در حافظه پنهان مشتری، می‌توانید از کدهایی مانند
- ویژگی اتصال محلیبه شما امکان می دهد به یک مکعب محلی (* فایل cub.) ایجاد شده توسط ابزارهای اکسل متصل شوید. البته، استفاده از چنین فایل هایی برای کار با حجم داده های "تولید" توصیه نمی شود - فقط به منظور ایجاد طرح بندی و غیره.
- ویژگی MemoryUsedمقدار را برمی گرداند حافظه دسترسی تصادفیتوسط PivotCache استفاده می شود. اگر PivotTable مبتنی بر این PivotCache هنوز ایجاد یا باز نشده باشد، 0 را برمی‌گرداند. برای بررسی اینکه آیا برنامه شما روی کلاینت‌های ضعیف اجرا می‌شود، می‌توان از آن استفاده کرد.
- ویژگی OLAPاگر PivotCache به سرور OLAP متصل باشد، مقدار True را برمی‌گرداند.
- OptimizeCache- قابلیت بهینه سازی ساختار کش. بارگیری اولیه داده ها بیشتر طول می کشد، اما ممکن است سرعت کار افزایش یابد. برای منابع OLE DB کار نمی کند.
بقیه خصوصیات شی PivotCache مانند شی QueryTable است و بنابراین در اینجا مورد بحث قرار نخواهد گرفت.
متد اصلی شی PivotCache متد CreatePivotTable () است. با استفاده از این روش، مرحله بعدی انجام می شود - ایجاد جدول محوری (شیء PivotTable). این روش چهار پارامتر دارد:
- TableDestinationتنها پارامتر مورد نیاز است. یک شی Range را در گوشه سمت چپ بالای آن می گیرد که جدول محوری قرار می گیرد.
- نام جدول- نام جدول محوری. اگر مشخص نشده باشد، نام نوع "PivotTable1" به طور خودکار ایجاد می شود.
- ReadData- اگر روی True تنظیم شود، کل محتوای مکعب به طور خودکار در حافظه پنهان قرار می گیرد. شما باید در مورد این پارامتر بسیار مراقب باشید، زیرا استفاده نادرست از آن می تواند به طور چشمگیری بار روی مشتری را افزایش دهد.
- نسخه پیش فرض- این خاصیت معمولاً مشخص نمی شود. نسخه جدول محوری در حال ایجاد را تعیین می کند. آخرین نسخه به طور پیش فرض استفاده می شود.
ایجاد یک جدول محوری در اولین خانه از صفحه اول کتاب کار ممکن است به شکل زیر باشد:
PC1.CreatePivotTable Range ("A1")
ما یک جدول محوری ایجاد کرده ایم، اما بلافاصله پس از ایجاد خالی است. این چهار منطقه را فراهم می کند که در آنها می توانید فیلدهایی را از منبع قرار دهید (در صفحه گرافیکی، همه اینها را می توان با استفاده از پنجره پیکربندی کرد. لیست فیلدهای PivotTable- به طور خودکار یا با استفاده از دکمه باز می شود چیدماندر آخرین صفحه از Create PivotTable Wizard):
- منطقه ستون- شامل آن ابعاد است ("برش" که در آن داده ها تجزیه و تحلیل می شود) که تعداد اعضای آن کمتر است.
- منطقه خط- آن ابعاد با اعضای بیشتر.
- منطقه صفحه- اندازه گیری هایی که فقط باید فیلتر کنید (مثلاً داده ها را فقط برای یک منطقه خاص یا فقط برای یک سال خاص نشان دهید).
- منطقه داده- به طور دقیق، بخش مرکزی جدول. آن داده های عددی (به عنوان مثال، میزان فروش)، که ما تجزیه و تحلیل می کنیم.
تکیه بر کاربر برای قرار دادن صحیح اقلام در هر چهار منطقه دشوار است. همچنین ممکن است مدتی طول بکشد. بنابراین، اغلب لازم است که داده ها را به صورت برنامه ریزی شده در جدول محوری مرتب کنیم. این عملیات با استفاده از شی CubeField انجام می شود. ویژگی اصلی این شی Orientation است، تعیین می کند که این یا آن فیلد در کجا قرار خواهد گرفت. برای مثال، اجازه دهید بعد Customers را در ناحیه ستون قرار دهیم:
PT1.CubeFields ("") .Orientation = xlColumnField
سپس - اندازه گیری زمان در ناحیه رشته ها:
PT1.CubeFields ("") .Orientation = xlRowField
سپس - اندازه گیری محصول در ناحیه صفحه:
PT1.CubeFields ("") .Orientation = xlPageField
و در نهایت، اندازه گیری (داده های عددی برای تجزیه و تحلیل) واحد فروش:
PT1.CubeFields ("."). جهت گیری = xlDataField
اولین رابط جداول محوری، همچنین به نام گزارش های محوری، در اکسل در سال 1993 (نسخه اکسل 5.0) گنجانده شد. با وجود کاربردهای فراوان عملکرد، عملاً در کار اکثر کاربران اکسل استفاده نمی شود. حتی کاربران باتجربه اغلب از اصطلاح "گزارش خلاصه" چیزی را می‌گویند که با فرمول‌های پیچیده ساخته شده است. بیایید سعی کنیم استفاده از جدول محوری را در کار روزانه اقتصاددانان رایج کنیم. این مقاله مبانی نظری ایجاد گزارش های خلاصه را مورد بحث قرار می دهد، توصیه های عملی برای استفاده از آنها ارائه می دهد و همچنین نمونه ای از دسترسی به داده ها را بر اساس جداول متعدد ارائه می دهد.
اصطلاحات تجزیه و تحلیل داده های چند متغیره
اکثر اقتصاددانان اصطلاحات "داده های چند بعدی"، "مکعب مجازی"، "فناوری های OLAP" و غیره را شنیده اند. اما با یک مکالمه دقیق، معمولاً معلوم می شود که تقریباً همه واقعاً نمی دانند چیست در سوال... یعنی منظور از مردم چیز پیچیده ای است و معمولاً به فعالیت های روزانه آنها مربوط نمی شود. در حقیقت، موضوع این نیست.
داده های چند بعدی، اندازه گیری ها
به جرات می‌توان گفت که اقتصاددانان تقریباً دائماً با داده‌های چند بعدی مواجه هستند، اما سعی می‌کنند آنها را به شکلی از پیش تعریف‌شده با استفاده از صفحات گسترده نمایش دهند. چند بعدی بودن در اینجا به معنای توانایی وارد کردن، مشاهده یا تجزیه و تحلیل همان اطلاعات با تغییر است ظاهر، استفاده از گروه بندی های مختلف و انواع داده ها. به عنوان مثال، یک طرح فروش را می توان با توجه به معیارهای زیر تجزیه و تحلیل کرد:
- انواع یا گروه کالاها؛
- برندها یا دسته بندی های محصول؛
- دوره (ماه، سه ماهه، سال)؛
- خریداران یا گروهی از خریداران؛
- مناطق فروش
- و غیره.
هر یک از معیارهای فوق از نظر تحلیل داده های چند متغیره «بعد» نامیده می شود. می توان گفت که یک اندازه گیری اطلاعات را برای مجموعه ای از مقادیر مشخص مشخص می کند. نوع خاصی از اندازه گیری اطلاعات چند بعدی «داده» است. در مثال ما، داده های طرح فروش می تواند به صورت زیر باشد:
- حجم فروش؛
- قیمت فروش؛
- تخفیف فردی
- و غیره.
در تئوری، داده ها همچنین می توانند بعد استاندارد اطلاعات چند بعدی باشند (به عنوان مثال، می توانید داده ها را بر اساس قیمت فروش گروه بندی کنید)، اما معمولاً داده ها هنوز هم نوع خاصی از ارزش هستند.
بنابراین، می توان گفت که اقتصاددانان در کار عملی از دو نوع اطلاعات استفاده می کنند: داده های چند بعدی (اعداد واقعی و برنامه ریزی شده با ویژگی های فراوان) و کتاب های مرجع (ویژگی ها یا اندازه گیری داده ها).
OLAP
مخفف OLAP (پردازش تحلیلی آنلاین) به معنای واقعی کلمه به عنوان "پردازش تحلیلی در زمان واقعی" ترجمه شده است. این تعریف خیلی مشخص نیست، تقریباً هر گزارشی از هر محصول نرم افزاری را می توان در زیر آن خلاصه کرد. در مفهوم OLAP، به معنای فناوری کار با گزارش های خاص، از جمله نرم افزار، برای به دست آوردن و تجزیه و تحلیل دقیق داده های ساختاری چند بعدی است. یکی از نرم افزارهای محبوبی که فناوری های OLAP را پیاده سازی می کند SQL Server Analysis Server است. حتی برخی به اشتباه آن را تنها نماینده پیاده سازی نرم افزاری این مفهوم می دانند.
مکعب داده های مجازی
"مکعب مجازی" (مکعب چند بعدی، مکعب OLAP) یک اصطلاح خاص است که توسط برخی از فروشندگان تخصصی ابداع شده است. نرم افزار... سیستم‌های OLAP معمولاً داده‌ها را در ساختارهای خود آماده و ذخیره می‌کنند و رابط‌های تجزیه و تحلیل ویژه (مثلاً گزارش‌های محوری Excel) به داده‌های این مکعب‌های مجازی دسترسی دارند. در عین حال، استفاده از چنین فضای ذخیره سازی اختصاصی برای پردازش اطلاعات چند بعدی اصلا ضروری نیست. به طور کلی، مکعب مجازی- این آرایه ای از داده های چند بعدی بهینه سازی شده است که برای ایجاد گزارش های خلاصه استفاده می شود. می توان آن را هم از طریق ابزارهای نرم افزاری تخصصی و هم از طریق دسترسی ساده به جداول پایگاه داده یا هر منبع دیگری مانند جدول اکسل به دست آورد.
جدول محوری
Pivot Table یک رابط کاربری برای نمایش داده های چند بعدی است. با استفاده از این رابط می توانید داده ها را گروه بندی، مرتب سازی، فیلتر و تغییر مکان دهید تا نمونه های تحلیلی مختلف را به دست آورید. به روز رسانی گزارش با استفاده از رابط کاربری ساده انجام می شود، داده ها به طور خودکار طبق قوانین مشخص شده جمع می شوند و نیازی به ورودی اضافی یا مکرر هیچ اطلاعاتی نیست. رابط محوری صفحات گسترده اکسلشاید محبوب ترین محصول نرم افزاری برای کار با داده های چند بعدی باشد. هم از منابع داده خارجی (مکعب های OLAP و پایگاه های داده رابطه ای) و هم از محدوده های صفحه گسترده داخلی به عنوان منبع داده پشتیبانی می کند. با شروع نسخه 2000 (9.0)، اکسل همچنین از فرم گرافیکی نمایش داده های چند بعدی پشتیبانی می کند - نمودار محوری (Pivot Chart).
رابط PivotTable اکسل به شما امکان می دهد ابعاد داده های چند بعدی را در یک ناحیه از یک کاربرگ قرار دهید. برای سادگی، می‌توانید جدول محوری را به عنوان گزارشی در بالای طیفی از سلول‌ها در نظر بگیرید (در واقع، اتصال خاصی از قالب‌های سلول به فیلدهای جدول محوری وجود دارد). جدول محوری اکسل دارای چهار ناحیه برای نمایش اطلاعات است: فیلتر، ستون ها، ردیف ها و داده ها. ابعاد داده نامگذاری شده است فیلدهای جدول محوری... این فیلدها ویژگی ها و فرمت نمایش خود را دارند.
یک بار دیگر توجه شما را به این نکته جلب می کنم که جدول محوری اکسل صرفاً برای تجزیه و تحلیل داده ها بدون امکان ویرایش اطلاعات در نظر گرفته شده است. از نظر معنایی نزدیک تر، استفاده همه جا از اصطلاح "Pivot Report" است و این رابط تا سال 2000 به این ترتیب نامیده می شد. اما به دلایلی، در نسخه های بعدی، توسعه دهندگان آن را رها کردند.
ویرایش جداول محوری
طبق تعریف آن، فن آوری OLAP، در اصل، توانایی تغییر داده های منبع را در هنگام کار با گزارش ها ندارد. با این وجود، یک طبقه کامل در بازار شکل گرفته است سیستم های نرم افزاری، امکان تجزیه و تحلیل و ویرایش مستقیم داده ها در جداول چند بعدی را درک می کند. اساساً چنین سیستم هایی بر حل مشکلات بودجه متمرکز هستند.
با استفاده از ابزارهای اتوماسیون داخلی اکسل، می توانید بسیاری از کارهای غیر استاندارد را حل کنید. نمونه ای از اجرای ویرایش برای جداول محوری اکسل بر اساس داده های کاربرگ را می توانید در وب سایت ما بیابید.
آماده سازی داده های چند بعدی
بریم به کاربرد عملیجداول محوری بیایید سعی کنیم داده های فروش را در جهات مختلف تجزیه و تحلیل کنیم. فایل pivottableexample.xlsاز چندین ورق تشکیل شده است. ورق مثالحاوی اطلاعات اولیه در مورد فروش برای یک دوره خاص است. برای سادگی مثال، ما یک شاخص عددی واحد - حجم فروش در کیلوگرم را تجزیه و تحلیل خواهیم کرد. ابعاد داده های کلیدی زیر وجود دارد: محصول، مشتری و حامل (شرکت حمل و نقل). علاوه بر این، چندین بعد داده اضافی وجود دارد که مشخصه یک محصول است: نوع، نام تجاری، دسته بندی، تامین کننده و مشتری: نوع. این داده ها در برگه مراجع جمع آوری می شوند. در عمل، چنین اندازه گیری های بیشتری می تواند وجود داشته باشد.
ورق مثالشامل درمان استانداردتجزیه و تحلیل داده ها - فیلتر خودکار. با نگاهی به مثال پر کردن جدول، بدیهی است که داده های مربوط به فروش بر اساس تاریخ (آنها در ستون ها مرتب شده اند) خود را به تجزیه و تحلیل عادی می رساند. علاوه بر این، با استفاده از یک فیلتر خودکار، می توانید سعی کنید داده ها را با ترکیبی از یک یا چند معیار کلیدی خلاصه کنید. مطلقاً هیچ اطلاعاتی در مورد مارک ها، دسته ها و انواع وجود ندارد. هیچ راهی برای گروه بندی داده ها با خلاصه سازی خودکار توسط یک کلید خاص (مثلاً توسط مشتریان) وجود ندارد. ضمناً مجموعه تاریخ ها ثابت است و امکان مشاهده خلاصه اطلاعات برای مدت معینی مثلاً 3 روز به صورت خودکار وجود نخواهد داشت.
به طور کلی وجود یک مکان تاریخ از پیش تعریف شده در این مثال نقطه ضعف اصلی جدول است. با چیدمان تاریخ ها در ستون ها، به نوعی بعد این جدول را از پیش تعریف کردیم و به این ترتیب توانایی استفاده از تجزیه و تحلیل با استفاده از جداول محوری را از خود سلب کردیم.
ابتدا باید از شر این اشکال خلاص شوید - یعنی. مکان از پیش تعریف شده یکی از ابعاد داده اصلی را حذف کنید. نمونه ای از جدول معتبر - برگه حراجی.
جدول به شکل گزارش ورود اطلاعات است. در اینجا، تاریخ، بعد همتای داده است. همچنین لازم به ذکر است که برای تحلیل بعدی در جداول محوری، موقعیت نسبی ردیف ها نسبت به یکدیگر (به عبارت دیگر مرتب سازی) کاملاً بی تفاوت است. رکوردها در پایگاه داده های رابطه ای این ویژگی ها را دارند. این در تجزیه و تحلیل حجم زیادی از پایگاه های داده است که رابط جداول محوری در درجه اول متمرکز است. بنابراین، هنگام کار با منبع داده در قالب محدوده سلولی، باید این قوانین را رعایت کنید. در عین حال، هیچ کس استفاده از ابزارهای رابط اکسل را در کار منع نمی کند - جداول محوری فقط داده ها را تجزیه و تحلیل می کنند و قالب بندی، فیلترها، گروه بندی و مرتب سازی سلول های منبع می تواند دلخواه باشد.
از فیلتر خودکار تا گزارش خلاصه
از نظر تئوری، بر روی داده های برگه فروش، می توان در سه بعد کالا، مشتریان و حامل ها تجزیه و تحلیل کرد. هیچ اطلاعاتی در مورد خواص محصولات و خریداران در این برگه وجود ندارد، که بر این اساس، اجازه نمایش آنها را در جدول محوری نمی دهد. در حالت عادی، یک جدول محوری برای منبع ایجاد کنید داده های اکسلاجازه نمی دهد داده ها از چندین جدول توسط فیلدهای خاص پیوند داده شوند. می توانید از این محدودیت عبور کنید توسط نرم افزار- نمونه مکمل این مقاله را در وب سایت ما ببینید. برای اینکه به روش های نرم افزاری پردازش اطلاعات متوسل نشوید (به ویژه از آنجایی که آنها جهانی نیستند)، ویژگی های اضافی باید مستقیماً به فرم ورود به گزارش اضافه شود - به برگه SalesAnalysis مراجعه کنید.
استفاده از توابع VLOOKUP تکمیل داده های اصلی با ویژگی های از دست رفته را آسان می کند. اکنون، با استفاده از AutoFilter، می توانید داده ها را در آن تجزیه و تحلیل کنید ابعاد مختلف... اما مشکل گروه بندی حل نشده باقی مانده است. به عنوان مثال، ردیابی مقدار فقط توسط مارک ها برای تاریخ های خاص کاملاً مشکل است. اگر خود را به فرمول های اکسل محدود می کنید، باید انتخاب های اضافی را با استفاده از تابع SUMIF ایجاد کنید.
حال بیایید ببینیم رابط جدول محوری چه ویژگی هایی را ارائه می دهد. روی برگه کد آنالیزچندین گزارش بر اساس طیف وسیعی از سلول ها با داده های برگه ساخته شد تجزیه و تحلیل فروش.
اولین جدول تجزیه و تحلیل از طریق رابط Excel 2007 ساخته شده است Ribbon \ Insert \ PivotTable(در اکسل 2000-2003 منو داده \ جدول محوری).
جداول دوم و سوم از طریق کپی و سفارشی سازی بعدی ایجاد شدند. منبع داده برای همه جداول یکسان است. شما می توانید این را با تغییر داده های اصلی بررسی کنید، سپس باید داده های گزارش های خلاصه را به روز کنید.
از دیدگاه ما، مزایای وضوح اطلاعات آشکار است. می‌توانید فیلترها، ستون‌ها و ردیف‌ها را عوض کنید و گروه‌های خاصی از مقادیر را برای هر بعد مخفی کنید و کشیدن و رها کردن دستی و مرتب‌سازی خودکار را اعمال کنید.
خواص و قالب بندی
علاوه بر نمایش مستقیم داده ها، گزینه های زیادی برای نمایش ظاهر جداول محوری وجود دارد. داده های اضافی را می توان با استفاده از فیلترها پنهان کرد. برای یک عنصر یا فیلد واحد، استفاده از آیتم منوی زمینه آسانتر است حذف(در نسخه 2000-2003 پنهان شدن).
همچنین توصیه می شود نمایش سایر عناصر جدول محوری را نه از طریق قالب بندی سلول، بلکه از طریق تنظیم فیلد یا عنصر جدول محوری تنظیم کنید. برای انجام این کار، نشانگر ماوس را به عنصر مورد نظر ببرید، منتظر ظاهر شدن یک شکل مکان نما (به شکل فلش) باشید، سپس با یک کلیک عنصر انتخاب شده را انتخاب کنید. پس از انتخاب، می‌توانید نما را از طریق نوار، منوی زمینه، تغییر دهید یا با گفتگوی قالب سلول استاندارد تماس بگیرید:
علاوه بر این، اکسل 2007 بسیاری از سبک های نمایش PivotTable از پیش تعریف شده را معرفی می کند:
توجه داشته باشید که فیلترهای کنترلی و مناطق کشیدن و رها کردن در نمودار فعال هستند.
دسترسی به داده های خارجی
همانطور که قبلاً اشاره شد، شاید بیشترین تأثیر استفاده از جداول محوری را می توان هنگام دسترسی به داده ها از منابع خارجی - مکعب های OLAP و پرس و جوهای پایگاه داده به دست آورد. چنین منابعی معمولاً حجم زیادی از اطلاعات را ذخیره می کنند و همچنین دارای یک اطلاعات از پیش تعریف شده هستند ساختار رابطه ای، که تعریف ابعاد داده های چند بعدی را آسان می کند (فیلدهای PivotTable).
اکسل انواع مختلفی از منابع داده خارجی را پشتیبانی می کند:
بیشترین تأثیر استفاده از منابع خارجی اطلاعات را می توان با استفاده از ابزارهای اتوماسیون (برنامه های VBA) هم برای به دست آوردن داده ها و هم برای پیش پردازش آنها در جداول محوری به دست آورد.
شگفت انگیز - نزدیک ...
در طول کار خود اغلب نیاز به تهیه گزارش های پیچیده داشتم، دائماً در تلاش بودم تا چیزی مشترک در آنها بیابم تا آنها را ساده تر و جهانی تر بسازم، حتی مقاله ای در مورد این موضوع "درخت اوسیپوف" نوشتم و منتشر کردم. با این حال، مقاله من مورد انتقاد قرار گرفت و گفت که تمام مشکلاتی که من مطرح کردم مدتهاست در 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.
فرمت فایل متنی:
خط اول فایل متنی سرصفحه ستون هایی است که با "|" از هم جدا شده اند، بقیه خطوط حاوی مقادیر این ستون ها هستند که با "|" از هم جدا شده اند.
برای وارد کردن فایل های متنی به اکسل، مایکروسافت کوئری ( جزء EXCEL) برای عملکرد آن، فایل shema.ini باید در دایرکتوری import (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 برای مقابله با تخلیه حجم زیادی از داده ها بهبود یافته است.
مشکلات تجزیه و تحلیل، 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 در 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 از اکسل
- اول از همه، شما باید عملیات DRILLTHROUGH را روی یک کلید انجام دهید، یعنی. به داده های دقیق بروید (داده های دقیق در یک برگه جداگانه نمایش داده می شود) و لیست اتصالات را باز کنید.
- ویژگی های اتصال را باز کنید، به تب "تعریف" بروید.
- نوع دستور پیش فرض را انتخاب کنید و دستور آماده شده را در قسمت متن دستور قرار دهید. MDXاستعلام؛
- با کلیک بر روی دکمه پس از بررسی نحو درخواست و داشتن حقوق دسترسی مناسب، درخواست بر روی سرور اجرا می شود و نتیجه در شیت فعلی در قالب یک جدول مسطح معمولی ارائه می شود.
می‌توانید متن درخواست MDX تولید شده توسط اکسل را با نصب یک افزونه رایگان که سایر قابلیت‌های اضافی را نیز ارائه می‌دهد، مشاهده کنید.
ترجمه به زبان های دیگر
مکعب تحلیلی از بومی سازی به زبان روسی و زبان انگلیسیو (در صورت لزوم، بومی سازی به زبان های دیگر امکان پذیر است). ترجمه‌ها برای نام ابعاد، سلسله مراتب، ویژگی‌ها، پوشه‌ها، اندازه‌گیری‌ها و همچنین عناصر سلسله مراتب فردی اعمال می‌شوند، اگر ترجمه‌هایی برای آنها در سمت سیستم‌های حسابداری / ذخیره داده‌ها وجود داشته باشد. برای تغییر زبان، باید ویژگی های اتصال را باز کنید و گزینه زیر را به رشته اتصال اضافه کنید:
ویژگی های توسعه یافته = "محلی = 1033"
جایی که 1033 محلی سازی انگلیسی است
1049 - بومی سازی به روسی
افزونه های اضافی اکسل برای Microsoft OLAP
توانایی کار با مکعب های OLAP مایکروسافت در صورت استفاده از پسوندهای اضافی افزایش می یابد، به عنوان مثال، OLAP PivotTable Extensions، که به لطف آن می توانید از جستجوی سریع بر اساس ابعاد استفاده کنید:
