ایجاد یک مکعب اولاپ در اکسل. اتصال به یک مکعب داده OLAP. انواع منابع داده

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

برنج. 1. برای اتصال یک مکعب OLAP به یک کتاب کار اکسل، از دستور استفاده کنید از خدمات آنالیز

دانلود یادداشت با فرمت یا

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

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

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

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

اتصال به یک مکعب داده OLAP

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

وقتی دستور مشخص شده Data Connection Wizard را انتخاب می کنید (شکل 2). وظیفه اصلی آن کمک به شما در برقراری ارتباط با سرور مورد استفاده است. برنامه اکسلدر مدیریت داده ها

1. ابتدا باید اطلاعات ثبت نام را در اکسل ارائه دهید. همانطور که در شکل نشان داده شده است، نام سرور، نام ورود و رمز دسترسی به داده ها را در فیلدهای کادر محاوره ای وارد کنید. 2. روی دکمه کلیک کنید به علاوه. اگر در حال ارتباط با حسابسپس ویندوز سوئیچ را تنظیم کنید از احراز هویت ویندوز استفاده کنید.

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

برنج. 3. انتخاب کنید پایه کارداده ها و مکعب OLAP که قصد دارید برای تجزیه و تحلیل داده ها استفاده کنید

3. در کادر محاوره ای بعدی جادوگر، نشان داده شده در شکل. 4، از شما خواسته می شود که اطلاعات توصیفی در مورد اتصالی که ایجاد می کنید وارد کنید. تمام فیلدهای کادر محاوره ای نشان داده شده در شکل. 4 اختیاری هستند. شما همیشه می توانید گفتگوی فعلی را بدون پر کردن آن نادیده بگیرید و این به هیچ وجه بر اتصال تأثیر نمی گذارد.

برنج. 4. اطلاعات توصیفی در مورد اتصال را تغییر دهید

4. روی دکمه کلیک کنید آمادهبرای تکمیل اتصال یک کادر محاوره ای روی صفحه ظاهر می شود. واردات داده(شکل 5). سوئیچ را تنظیم کنید گزارش PivotTableو برای شروع ایجاد PivotTable روی OK کلیک کنید.

ساختار مکعب OLAP

در فرآیند ایجاد یک PivotTable بر اساس پایگاه داده OLAP، متوجه خواهید شد که پنجره Task Pane فیلدهای جدول محوریبا جدول محوری معمولی متفاوت خواهد بود. دلیل آن در مرتب سازی PivotTable نهفته است به گونه ای که ساختار مکعب OLAP متصل به آن را تا حد امکان نشان دهد. برای پیمایش هرچه سریعتر مکعب OLAP، باید با اجزای آن و نحوه تعامل آنها آشنا شوید. روی انجیر شکل 6 ساختار اصلی یک مکعب OLAP معمولی را نشان می دهد.

همانطور که می بینید، اجزای اصلی یک مکعب OLAP عبارتند از ابعاد، سلسله مراتب، سطوح، اعضا و معیارها:

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

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

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

محدودیت در جدول محوری OLAP

هنگام کار با OLAP PivotTables، به یاد داشته باشید که با منبع داده PivotTable در یک محیط OLAP Services Analysis تعامل دارید. این بدان معنی است که هر جنبه رفتاری یک مکعب داده، از ابعاد گرفته تا اندازه گیری هایی که در مکعب گنجانده شده است، توسط سرویس های تحلیلی OLAP نیز کنترل می شود. به نوبه خود، این منجر به محدودیت هایی در عملیاتی می شود که می توان در OLAP PivotTables انجام داد:

• شما نمی توانید فیلدهایی غیر از اندازه گیری ها را در ناحیه VALUES یک PivotTable قرار دهید.
• تغییر تابع مورد استفاده برای خلاصه کردن غیرممکن است.
• شما نمی توانید یک فیلد محاسبه شده یا آیتم محاسبه شده ایجاد کنید.
• هر گونه تغییر در نام فیلدها بلافاصله پس از حذف آن فیلد از جدول محوری لغو می شود.
• تغییر پارامترهای فیلد صفحه مجاز نیست.
• دستور در دسترس نیست نمایش دهیدصفحات;
• گزینه غیرفعال نمایش دهیدامضاهاعناصروقتی هیچ فیلدی در ناحیه مقدار وجود ندارد.
• گزینه غیرفعال جمع های فرعیتوسط عناصر صفحه انتخاب شده توسط فیلتر؛
• گزینه موجود نیست زمینهدرخواست;
• پس از دوبار کلیک کردن در قسمت VALUES، تنها 1000 رکورد اول از کش جدول محوری برگردانده می شود.
• چک باکس در دسترس نیست بهینه سازی کنیدحافظه.

مکعب های داده آفلاین ایجاد کنید

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

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

یک کادر محاوره ای روی صفحه ظاهر می شود. تنظیمات عمر باتری OLAP(شکل 9). روی دکمه کلیک کنید فایل داده آفلاین ایجاد کنید. اولین پنجره از Data Cube File Creation Wizard روی صفحه ظاهر می شود. روی دکمه کلیک کنید به علاوهبرای ادامه رویه

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

روی دکمه کلیک کنید به علاوهبرای رفتن به مرحله سوم (شکل 11). در این پنجره، اعضا یا آیتم های داده ای را انتخاب می کنید که در مکعب قرار نخواهند گرفت. اگر چک باکس انتخاب نشده باشد، مورد مشخص شده وارد نمی شود و فضای اضافی را در هارد دیسک محلی اشغال می کند.

محل و نام مکعب داده را مشخص کنید (شکل 12). فایل‌های مکعب داده دارای پسوند cub. هستند.

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

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

اعمال توابع مکعب داده در PivotTables

توابع مکعب داده که در پایگاه داده های OLAP استفاده می شوند را می توان از یک PivotTable نیز اجرا کرد. که در نسخه های قدیمیاکسل، شما فقط پس از نصب افزونه Analysis ToolPak به توابع مکعب داده دسترسی پیدا کردید. در اکسل 2013، این توابع در برنامه تعبیه شده اند و بنابراین برای استفاده در دسترس هستند. برای آشنایی کامل با قابلیت های آنها، یک مثال خاص را در نظر بگیرید.

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

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

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

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

برنج. 16. به نوار فرمول نگاهی بیندازید: سلول ها حاوی فرمول های مکعب داده هستند

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

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

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

Excel 2013 ابزارهای جدید OLAP را معرفی می کند - اندازه گیری های محاسبه شده و اعضای MDX محاسبه شده. شما دیگر محدود به استفاده از معیارها و اعضا در یک مکعب OLAP نیستید که توسط مدیر پایگاه داده ارائه شده است. با ایجاد محاسبات سفارشی، قابلیت های تجزیه و تحلیل اضافی را دریافت می کنید.

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

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

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

تحلیل و بررسی کار با جداول محوری. فهرست کشویی ابزارهای OLAPمورد را انتخاب کنید (شکل 18).

برنج. 18. آیتم منو را انتخاب کنید اندازه گیری محاسبه شده MDX

یک کادر محاوره ای روی صفحه ظاهر می شود. یک اندازه گیری محاسبه شده ایجاد کنید(شکل 19).

موارد زیر را انجام دهید:

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

3. در میدان MDX(MDX) کدی را وارد کنید که معیار جدیدی را تعریف می کند. برای سرعت بخشیدن به فرآیند ورود، از لیست سمت چپ برای انتخاب معیارهای موجود برای استفاده در محاسبات استفاده کنید. بر روی اندازه مورد نظر دوبار کلیک کنید تا به فیلد MDX اضافه شود. برای محاسبه قیمت میانگینفروش واحد، عبارت MDX زیر استفاده می شود:

4. روی OK کلیک کنید.

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

هنگامی که ایجاد یک اندازه گیری محاسبه شده جدید را به پایان رساندید، به لیست بروید فیلدهای جدول محوریو آن را انتخاب کنید (شکل 20).

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

اعضای محاسبه شده MDX ایجاد کنید.یک عضو محاسبه شده MDX نسخه OLAP یک عضو محاسبه شده معمولی است. ایده ایجاد یک عنصر داده جدید بر اساس برخی است عملیات ریاضیبر روی عناصر OLAP موجود اجرا شده است. در مثال نشان داده شده در شکل. 22 از یک جدول محوری OLAP استفاده می کند که شامل داده های فروش برای سال های 2005-2008 (فصل سه ماهه) است. فرض کنید می خواهید با ایجاد، داده های فصل اول و دوم را جمع آوری کنید عنصر جدیدنیمه اول سال (نیمه اول سال). ما همچنین داده های مربوط به سه ماهه سوم و چهارم را با هم ترکیب می کنیم و عنصر جدیدی را در نیمه دوم سال (نیمه دوم سال) تشکیل می دهیم.

برنج. 22. ما می خواهیم اعضای جدید محاسبه شده MDX، نیمه اول سال و نیمه دوم سال را اضافه کنیم.

مکان نما را در هر نقطه از جدول محوری قرار دهید و برگه متنی را انتخاب کنید تحلیل و بررسیاز مجموعه ای از برگه های متنی کار با جداول محوری. فهرست کشویی ابزارهای OLAPمورد را انتخاب کنید MDX Computed Member(شکل 23).

یک کادر محاوره ای روی صفحه ظاهر می شود. (شکل 24).

برنج. 24. پنجره یک عضو محاسبه شده ایجاد کنید

موارد زیر را انجام دهید:

1. برای اندازه گیری محاسبه شده یک نام بگذارید.

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

3. در پنجره MDXسینتکس MDX را وارد کنید. برای صرفه جویی در زمان، از لیست نمایش داده شده در سمت چپ برای انتخاب اعضای موجود برای استفاده در MDX استفاده کنید. روی مورد انتخاب شده دوبار کلیک کنید و اکسل آن را به پنجره اضافه می کند MDX. در مثال نشان داده شده در شکل. 24، مجموع سه ماهه اول و دوم محاسبه می شود:

..&& +

.. && +

.. && + …

4. روی OK کلیک کنید. اکسل عضو محاسبه شده MDX تازه ایجاد شده را در PivotTable نمایش می دهد. همانطور که در شکل نشان داده شده است. 25، آیتم محاسبه شده جدید به همراه سایر آیتم های محاسبه شده جدول محوری نمایش داده می شود.

روی انجیر 26 روند مشابهی را نشان می دهد که برای ایجاد عضو محاسبه شده نیمه دوم سال استفاده می شود.

توجه داشته باشید که اکسل حتی سعی نمی کند عناصر اصلی MDX را حذف کند (شکل 27). جدول محوری همچنان رکوردهای سال های 2005-2008 را نشان می دهد که هر سه ماه یکبار تجزیه می شود. در این مورد، این یک مشکل نیست، اما در اکثر سناریوها، برای جلوگیری از درگیری، باید عناصر "اضافی" را پنهان کنید.

برنج. 27. اکسل عضو محاسبه شده MDX تولید شده را به همراه اعضای اصلی نمایش می دهد. اما باز هم بهتر است عناصر اصلی را حذف کنید تا درگیری نداشته باشید.

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

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

مدیریت محاسبات OLAP.اکسل رابطی را ارائه می دهد که به شما امکان می دهد اندازه های محاسبه شده و عناصر MDX را در جدول های محوری OLAP مدیریت کنید. مکان نما را در هر نقطه از جدول محوری قرار دهید و برگه متنی را انتخاب کنید تحلیل و بررسیاز مجموعه ای از برگه های متنی کار با جداول محوری. فهرست کشویی ابزارهای OLAPمورد را انتخاب کنید مدیریت محاسبات. در پنجره مدیریت محاسباتسه دکمه موجود است (شکل 28):

• ايجاد كردن.یک اندازه گیری محاسبه شده جدید یا عضو محاسبه شده MDX ایجاد کنید.
• تغییر دادن.محاسبه انتخاب شده را تغییر دهید.
• حذف.محاسبه انتخاب شده را حذف کنید.

برنج. 28. جعبه گفتگو مدیریت محاسبات

تجزیه و تحلیل what-if را روی داده های OLAP انجام دهید.در اکسل 2013، می توانید تجزیه و تحلیل what-if را بر روی داده هایی که در OLAP PivotTables هستند انجام دهید. با تشکر از این فرصت جدیدمی توانید مقادیر را در یک PivotTable تغییر دهید و معیارها و اعضا را بر اساس تغییرات خود دوباره محاسبه کنید. همچنین می توانید تغییرات را به مکعب OLAP بازگردانید. برای استفاده از تجزیه و تحلیل what-if، یک OLAP PivotTable ایجاد کنید و برگه متنی را انتخاب کنید تحلیل و بررسی کار با جداول محوری. فهرست کشویی ابزارهای OLAPیک تیم انتخاب کنید چه می شود اگر تجزیه و تحلیل –> تحلیل what-if را فعال کنید(شکل 29).

از این پس می توانید مقادیر جدول محوری را تغییر دهید. برای تغییر مقدار انتخاب شده در جدول محوری، روی آن کلیک راست کرده و انتخاب کنید منوی زمینهمورد را انتخاب کنید (شکل 30). اکسل همه محاسبات را در PivotTable بر اساس ویرایش های شما، از جمله اندازه گیری های محاسبه شده و اعضای MDX محاسبه شده، دوباره اجرا می کند.

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

به‌طور پیش‌فرض، ویرایش‌های انجام‌شده در PivotTable در حالت تجزیه و تحلیل what-if محلی هستند. اگر می خواهید تغییرات را در سرور OLAP منتشر کنید، دستور انتشار تغییرات را انتخاب کنید. برگه متنی را انتخاب کنید تحلیل و بررسی، در مجموعه برگه های متنی قرار دارد کار با جداول محوری. فهرست کشویی ابزارهای OLAPموارد را انتخاب کنید چه می شود اگر تجزیه و تحلیل – > انتشار تغییرات(شکل 31). این دستور "نوشتن" را در سرور OLAP فعال می کند، به این معنی که تغییرات را می توان به مکعب OLAP اصلی منتشر کرد. (به منظور انتشار تغییرات در سرور OLAP، باید مجوزهای مناسب برای دسترسی به سرور را داشته باشید. با DBA خود تماس بگیرید تا به شما کمک کند مجوز دسترسی نوشتن به پایگاه داده OLAP را دریافت کنید.)

یادداشت بر اساس کتاب Jelen, Alexander نوشته شده است. . فصل 9

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

سوالات نظری عمومی در مورد کار با جداول محوری و تجزیه و تحلیل داده های چند بعدی در مقاله دیگری در وب سایت ما توضیح داده شده است.

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

استفاده از جداول محوری

واکشی ارزش های منحصر به فرد

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

در نمونه برگه نمونهفهرستی از کشورها و تعداد ذکر شده در آرایه داده را نشان می دهد.

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

جمع مقادیر

یکی دیگر از کارهای محبوب برای استفاده از رابط PivotTable، دریافت مجموع رکوردهای منحصر به فرد در یک مجموعه داده است.

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

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

برای حل مشکل با استفاده از فرمول های استاندارد، می توانید از تابع SUMIF استفاده کنید. بدیهی است که مشکل در تثبیت ارزش ها نیست، بلکه مانند مثال قبلی، در انتخاب یک لیست منحصر به فرد (در مثال، نام کشورها) ایجاد می شود.

تجزیه و تحلیل دو بعدی

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

در نمونه برگه جدولگزارشی بر اساس کشور و تاریخ ایجاد کرد که تغییر تعداد سفارش‌ها را در طول زمان نشان می‌داد. لطفاً توجه داشته باشید که گروه بندی اضافی برای فیلد نوع تاریخ اعمال شده است: بر اساس ماه و سال.

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

تحلیل چند متغیره

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

نمونه ورق فیلتر کنیدتوانایی مشاهده داده‌های شرکت‌ها در یک کشور را با استفاده از ناحیه فیلتر جدول محوری نشان می‌دهد:

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

مشابه استفاده از فیلترهای PivotTable با استفاده از فرمول های کاربرگ، در بیشتر موارد، فرمول های پردازش آرایه هستند.

نمونه های ورق محور 1و pivot2گزینه هایی را برای نمایش همان اطلاعات با استفاده از تنظیمات ابعاد مختلف PivotTable نشان دهید.

کار با داده ها

به روز رسانی داده ها

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

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

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

می توانید محدوده منبع داده را برای یک جدول محوری موجود از طریق یک گفتگوی ویژه در Excel 2007-2010 تغییر دهید. در نسخه های قبلی اکسل، این ویژگی رابط زمانی که از یک PivotTable فعال راه اندازی می شد، در PivotTable Wizard پیاده سازی می شد. پس از باز کردن ویزارد، باید یک مرحله به عقب برگردید:

اصلاحات منبع داده را می توان به صورت برنامه نویسی نیز انجام داد. به عنوان مثال، از طریق پنجره محاسبه ویرایشگر VBA (فوری):

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

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

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData، "!")) & _ Range(Application.ConvertSheet.Pvoteet(_iactive (1).SourceData، xlR1C1، xlA1) _).Worksheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

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

ماکرو را می توان در رویداد فراخوانی کرد Worksheet_Activate، یا یک کلید داغ تنظیم کنید.

کار با نتایج تجزیه و تحلیل

PivotTable در محدوده ای از سلول ها در یک کاربرگ اکسل قرار دارد. نوشتن فرمول‌های کاربرگ در محدوده‌های جدول محوری چه هنگام وارد کردن دستی و نه با روش‌های برنامه‌نویسی مجاز نیست. از نظر تئوری، می توان با سلول های واقع در مرزهای جدول محوری با استفاده از پیوندهایی برای فرمول های خارجی کار کرد. اغلب در عمل، تابع VLOOKUP همچنین برای جستجو توسط ستون جدول محوری استفاده می شود. این روش باید با دقت زیادی مورد استفاده قرار گیرد - رابط Pivot Report تغییر موقعیت داده های نمایش داده شده را نسبت به مختصات مستطیلی کاربرگ بدون هیچ تاثیری بر منبع این داده ها فرض می کند. یعنی هیچ تضمینی وجود ندارد که لینک مشخص شده در فرمول داخل جدول محوری مقدار صحیح را در حین کار بیشتر با فایل نمایش دهد. در این مورد، منبع داده ممکن است تغییر نکند.

در دسترس راه های جایگزینپردازش نتایج جدول محوری:

1. کپی و چسباندن مقادیر جدول محوری در صفحه دیگری (با استفاده از تابع "Paste Special") با جستجوی بیشتر برای داده هایی که قبلاً در این محدوده تشکیل شده از سلول ها وجود دارد. نقض یکپارچگی داده ها در یک جدول ساده بسیار دشوارتر از جدول محوری است. بدیهی است که عیب اصلی این روش کار، استفاده از عملیات دستی پس از هر به روز رسانی منبع داده است.
2. از قدرت تابع GETPIVOTDATA (اکسل 2002 و جدیدتر) استفاده کنید. این تابعشامل دسترسی به داده ها نه با مختصات کاربرگ، بلکه با ابعاد جدول محوری است. برای منابع داده از نوع مکعب OLAP، توابع ویژه برای دسترسی به داده ها و ابعاد ارائه شده است: CUBEVALUE، CUBEMEMBER، و دیگران (Excel 2007-2010). این روشکار ناخوشایند است، و همچنین در صورت نیاز به دریافت زیاد، کار را به طور قابل توجهی کند می کند معانی مختلفجدول محوری.
3. جدول محوری را برای نتایج رها کنید. در عوض، از فرمول های کاربرگ استفاده کنید (به بخش 1 مراجعه کنید). اگر چه پیاده سازی این روش دشوار است، اما اگر محاسبات دیگر بر اساس نتایج باشد و منبع داده به طور مکرر به روز شود، می تواند راحت ترین باشد.

نسخه های رابط PivotTable

فرمت فایل xlsx جدید (Excel 2007-2010) قابلیت های رابط PivotTable را به طور قابل توجهی تغییر داده است. که در نسخه های قبلیرابط (97-2003) فقط تغییرات "زیبایی" ایجاد شد:

• Excel 2000 (9.0) نسخه اصلی رابط جدول محوری است.
• Excel XP (10.0) - عملکرد جدید GETPIVOTDATE
• اکسل 2003 (11.0) - به نظر می رسد که هیچ تغییری ایجاد نشده است
• Excel 2007 (12.0) - یک نسخه جدیدرابط جدول محوری با پشتیبانی از محدوده های گسترده. عملکرد بهبود یافته، تغییر کرده است ظاهررابط. سازگاری با فرمت قدیمی حفظ شده است.
• Excel 2010 (14.0) - پشتیبانی از افزونه PowerPivot. کار با مکعب های OLAP قابل به روز رسانی

تغییرات اصلی در قالب فایل جدید (2007-2010):

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

برای درک بهتر تفاوت ها، فایل های نمونه را دانلود و باز کنید nwdata_pivot1.xlsxو nwdata_pivot2.xlsx(در آرشیو nwdata_pivot.zip). فایل اول حاوی گزارش در قالب قدیمی است، فایل دوم - در فایل جدید، داده های اولیه یکسان است.

سازماندهی داخلی رابط PivotTable

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

حافظه پنهان PivotTable

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

با کلیک روی دکمه "Refresh" رابط جدول محوری (دکمه روی روبان یا در منوی زمینه)، یا در یک بازه زمانی مشخص، اگر چنین تنظیمی در پارامترها مشخص شده باشد، داده های حافظه پنهان به روز می شوند. حالت محاسبه اکسل (اتوماتیک یا دستی) به هیچ وجه بر جدول محوری تأثیر نمی گذارد.

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

اشیاء VBA

دسترسی به داده ها با روش های برنامه نویسی در سطح اشیاء جدول محوری - شی امکان پذیر است جدول محوری. سایر اشیاء PivotTable مسئول چیدمان و نمایش بصری عناصر و داده ها هستند. اینها شامل مجموعه ای از زمینه ها می شود: PivotFields, ColumnFields, RowFields, فیلدهای صفحه, DataFields. انواع مقادیر فیلد از طریق مجموعه ای از اشیاء در دسترس هستند PivotItems.

توانایی جهانی برای دسترسی مستقیم به داده ها در حافظه پنهان (شیء حافظه پنهان محوری) به دلایلی توسط توسعه دهندگان اکسل ارائه نشده است. منطق پشت این موضوع کاملاً روشن نیست. همانطور که قبلا ذکر شد، داده های کش به طور جداگانه ذخیره می شوند و حتی در فایل قابل مشاهده هستند فرمت xlsxاگر این فایل را به صورت یک آرشیو فشرده باز کنید. بسته به نوع منبع داده، می توانید سعی کنید از ویژگی استفاده کنید داده ی منبع(برای PivotTables مبتنی بر محدوده) یا مجموعه رکورد(برای منابع از نوع "پرس و جو پایگاه داده").

فیلدهای محاسبه شده و اشیاء جدول محوری ( CalculatedFields, اقلام محاسبه شده) موتور محاسباتی و درخت وابستگی فرمول خود را دارند که به فرمول های کاربرگ اکسل مربوط نمی شود. در عمل، توصیه می کنیم تا حد امکان از تعداد زیادی فیلدهای محاسبه شده در جداول محوری اجتناب کنید، زیرا این امر منجر به کاهش قابل توجهی در محاسبات می شود. برای منابع داده در قالب محدوده سلول‌ها، اغلب می‌توانید یک ستون با یک فرمول معمولی را به داده‌های منبع اضافه کنید، و برای پرس و جوهای پایگاه داده، می‌توانید محاسبات را مستقیماً به بدنه پرس و جوی SQL اضافه کنید.

انواع منابع داده

در سطح جهانی، منابع داده را می توان به 3 نوع تقسیم کرد:

1. محدوده سلولی
2. پرس و جوهای پایگاه داده
3. مکعب های OLAP و PowerPivot2010 به عنوان یکی از گزینه های پیاده سازی موتور OLAP.

محدوده ها

گزینه اول در عمل رایج ترین است. توضیحات قبلی نمونه ها به طور خاص به داده های ذخیره شده در محدوده ای از سلول ها اشاره دارد.

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

پرس و جوهای پایگاه داده

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

مکعب های OLAP

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

قبل از Office 2007، یک مکعب OLAP ساده می‌توانست توسط آن تهیه شود کمک از مایکروسافتپرس و جو، اما آخرین نسخه هااین گزینه به دلایل نامعلوم غیرفعال شد. توسعه دهندگان اکیدا استفاده از سرویس تحلیل سرور SQL را برای ایجاد و سفارشی کردن مکعب های OLAP توصیه می کنند. توصیه مفید است، اما، اولا، این سرویس فقط در نسخه های پولی SQL Server گنجانده شده است، و ثانیا، نیاز به مطالعه جدی هر دو رابط و زبان پردازش پرس و جو MDX دارد.

مثال برای مقاله یک آرشیو را ارائه می دهد nwdata_cube.zipبا دو فایل nwdata_cube.cube, nwdata_cube.xls. هنگام استفاده از مکعب OLAP به عنوان منبع داده، به تغییرات در رابط PivotTable توجه کنید:

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

powerpivot

برای Excel 2010، یک افزونه PowerPivot ویژه در دسترس است که به طور کلی، مکانیزمی جایگزین برای پیاده‌سازی مکعب‌های OLAP است. با PowerPivot، می توانید میلیون ها رکورد مختلف را پردازش کنید فایل های اطلاعاتیو پایگاه های داده با عملکرد عالی. در عین حال، رابط کاربری برای تجزیه و تحلیل داده های نهایی در Excel 2010 پیاده سازی شده است.

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

وب سایت رسمی PowerPivot.

به عنوان بخشی از این کار، سوالات زیر در نظر گرفته خواهد شد:

• مکعب های OLAP چیست؟
• معیارها، ابعاد، سلسله مراتب چیست؟
• چه نوع عملیاتی را می توان بر روی مکعب های OLAP انجام داد؟

مفهوم مکعب OLAP

فرض اصلی OLAP چند بعدی بودن در ارائه داده است. در اصطلاح OLAP، مفهوم مکعب یا ابر مکعب برای توصیف فضای داده گسسته چند بعدی استفاده می شود.

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

داده ها- این اطلاعات مربوط به اشیاء و رویدادهای شرکت است که مورد تجزیه و تحلیل قرار می گیرد. حقایق از همان نوع اندازه گیری ها را تشکیل می دهند. اندازه گیری نوعی مقدار در یک سلول مکعبی است.

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

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

شکل 1 نمونه ای از مکعب طراحی شده برای تجزیه و تحلیل فروش فرآورده های نفتی توسط یک شرکت خاص را بر اساس منطقه نشان می دهد. این مکعب دارای سه بعد (زمان، محصول و منطقه) و یک اندازه (ارزش فروش به صورت پولی) است. مقادیر اندازه گیری در سلول های مربوطه (سلول) مکعب ذخیره می شود. هر سلول به طور منحصربه‌فرد توسط مجموعه‌ای از اعضا از هر یک از ابعاد مشخص می‌شود که تاپل نامیده می‌شود. به عنوان مثال، سلولی که در گوشه سمت چپ پایین مکعب قرار دارد (شامل مقدار 98399 دلار است) توسط تاپل [ژوئیه 2005، شرق دور، دیزل] داده می شود. در اینجا ارزش 98399 دلار حجم فروش (بر حسب پولی) دیزل در خاور دور در ژوئیه 2005 را نشان می دهد.

همچنین توجه داشته باشید که برخی از سلول ها هیچ مقداری ندارند: این سلول ها خالی هستند زیرا جدول واقعیت حاوی داده ای برای آنها نیست.

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

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

یکی دیگر از ویژگی های خاص مکعب های چند بعدی دشواری در تعیین نقطه مبدا است. به عنوان مثال، چگونه نقطه 0 را برای بعد محصول یا مناطق تعیین می کنید؟ راه حل این مشکل، معرفی یک ویژگی خاص است که تمام عناصر بعد را ترکیب می کند. این ویژگی (به طور خودکار تولید می شود) فقط شامل یک عنصر است - همه ("همه"). برای توابع سادهتجمیع، مانند یک مجموع، عنصر All معادل مجموع مقادیر همه عناصر فضای واقعی بعد داده شده است.

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

عملیات بر روی مکعب های OLAP

عملیات زیر را می توان بر روی یک مکعب OLAP انجام داد:

• تکه؛
• چرخش؛
• تحکیم؛
• جزئیات

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

برنج. 2.برش مکعب OLAP

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

حاشیه نویسی: این سخنرانی اصول طراحی مکعب داده برای انبارهای داده OLAP را پوشش می دهد. مثال نشان می دهد که چگونه با استفاده از ابزار CASE یک مکعب داده بسازیم.

هدف از سخنرانی

پس از مطالعه مطالب این سخنرانی خواهید دانست:

• مکعب داده در چیست انبار داده OLAP ;
• چگونه یک مکعب داده برای انبارهای داده OLAP ;
• بعد مکعب داده چیست ;
• چگونه واقعیت با مکعب داده مرتبط است.
• ویژگی های ابعادی چیست
• سلسله مراتب چیست
• متریک مکعب داده چیست.

و آموختن:

• ساختن نمودارهای چند بعدی ;
• طراحی ساده نمودارهای چند بعدی.

معرفی

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

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

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

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

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

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

OLAP روی کلاینت و سرور

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

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

اگر داده های منبع در یک DBMS دسکتاپ موجود باشد، داده های انبوه توسط خود ابزار OLAP محاسبه می شود. اگر منبع داده منبع یک DBMS سرور باشد، بسیاری از ابزارهای OLAP کلاینت پرس و جوهای SQL حاوی بند GROUP BY را به سرور ارسال می کنند و در نتیجه داده های انبوه محاسبه شده روی سرور را دریافت می کنند.

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

زیاد ابزارهای توسعهشامل کتابخانه‌هایی از کلاس‌ها یا مؤلفه‌هایی است که به شما امکان می‌دهد برنامه‌هایی ایجاد کنید که ساده‌ترین عملکرد OLAP را پیاده‌سازی کنند (مانند مؤلفه‌های مکعب تصمیم در Borland Delphi و Borland C++Builder). علاوه بر این، بسیاری از شرکت ها ارائه می دهند کنترل ها ActiveX و سایر کتابخانه هایی که عملکرد مشابهی را پیاده سازی می کنند.

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

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

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

مزایای استفاده از ابزارهای OLAP سرور در مقایسه با ابزارهای OLAP سرویس گیرنده مشابه مزایای استفاده از DBMS سرور در مقایسه با دسکتاپ است: در مورد استفاده از ابزارهای سرور، محاسبه و ذخیره داده های انبوه روی سرور و برنامه مشتری انجام می شود. فقط نتایج پرس و جوها را به آنها دریافت می کند که به V اجازه می دهد مورد کلیكاهش دادن ترافیک شبکه, زمان بین شروع و اتمام فرآیند تولیددرخواست ها و منابع مورد نیاز مصرف شده توسط برنامه مشتری. توجه داشته باشید که تجزیه و تحلیل و پردازش داده در مقیاس سازمانی، به عنوان یک قاعده، دقیقاً بر اساس ابزارهای OLAP سرور است، به عنوان مثال، مانند Oracle Express Server، Microsoft SQL Server 2000 Analysis Services، Hyperion Essbase، محصولات Crystal Decisions، Business Objects، Cognos. ، موسسه S.A.S. از آنجایی که تمام تولید کنندگان پیشرو سرور DBMS ابزارهای OLAP سرور خاصی را تولید می کنند (یا از شرکت های دیگر مجوز گرفته اند)، انتخاب آنها بسیار گسترده است و تقریباً در همه موارد می توانید یک سرور OLAP را از همان سازنده ای که خود سرور پایگاه داده خریداری کنید.

توجه داشته باشید که بسیاری از ابزارهای OLAP کلاینت (به ویژه، Microsoft Excel 2003، Seagate Analysis و غیره) به شما امکان می دهند به حافظه های ذخیره سازی OLAP سرور دسترسی داشته باشید و در این مورد به عنوان برنامه های مشتری عمل می کنند که چنین درخواست هایی را انجام می دهند. علاوه بر این، محصولات بسیاری وجود دارد که از تولید کنندگان مختلف، برنامه های مشتری برای ابزارهای OLAP هستند.

جنبه های فنی ذخیره سازی داده های چند بعدی

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

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

داده های منبع و کل را می توان در ساختارهای رابطه ای یا چند بعدی ذخیره کرد. بنابراین، در حال حاضر سه راه برای ذخیره داده ها وجود دارد.

• MOLAP(OLAP چند بعدی) - منبع و داده های انبوه در یک پایگاه داده چند بعدی ذخیره می شود. ذخیره داده ها در ساختارهای چند بعدی به شما امکان می دهد داده ها را به عنوان دستکاری کنید آرایه چند بعدیبه طوری که سرعت محاسبه مقادیر کل برای هر یک از ابعاد یکسان باشد. با این حال، در این مورد، پایگاه داده چند بعدی اضافی است، زیرا داده های چند بعدی کاملاً حاوی داده های رابطه ای اصلی هستند.
• ROLAP(OLAP رابطه ای) - داده های اصلی در همان پایگاه داده رابطه ای که در ابتدا در آن قرار داشتند، باقی می مانند. داده های انبوه در جداول خدماتی که مخصوص ذخیره سازی آنها در همان پایگاه داده ایجاد شده است قرار می گیرد.
• HOLAP(Hybrid OLAP) - داده های اصلی در همان پایگاه داده رابطه ای که در ابتدا در آن قرار داشتند باقی می مانند، در حالی که داده های انبوه در یک پایگاه داده چند بعدی ذخیره می شوند.

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

همچنین توجه می کنیم که اکثریت قریب به اتفاق ابزارهای مدرن OLAP مقادیر "خالی" را ذخیره نمی کنند (نمونه ای از مقدار "خالی" عدم فروش کالاهای فصلی خارج از فصل است).

مفاهیم اولیه OLAP

تست فامسی

فن آوری تجزیه و تحلیل داده های چند بعدی پیچیده، OLAP (پردازش تحلیلی آنلاین) نامیده می شود. OLAP جزء کلیدی یک سازمان انبار داده است. مفهوم OLAP در سال 1993 توسط Edgar Codd، محقق مشهور پایگاه داده و نویسنده مدل داده های رابطه ای، توصیف شد. در سال 1995، بر اساس الزامات تعیین شده توسط Codd، به اصطلاح تست FASMI(تحلیل سریع اطلاعات چند بعدی مشترک) - تجزیه و تحلیل سریع اطلاعات چند بعدی مشترک، از جمله الزامات زیر برای برنامه های کاربردی برای تجزیه و تحلیل چند بعدی:

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

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

نمایش چند بعدی اطلاعات

کوبا

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

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

برنج. 26.1.

"برش" مکعب

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

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

(سطوح). به عنوان مثال، برچسب های ارائه شده در توسط همه ابزارهای OLAP پشتیبانی نمی شوند. به عنوان مثال، هر دو نوع سلسله مراتب در Microsoft Analysis Services 2000 پشتیبانی می شوند، در حالی که تنها انواع متوازن در Microsoft OLAP Services 7.0 پشتیبانی می شوند. در ابزارهای مختلف OLAP می‌تواند تعداد سطوح سلسله مراتبی و حداکثر تعداد مجاز اعضای یک سطح و حداکثر تعداد ممکن ابعاد باشد.

معماری اپلیکیشن OLAP

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

چند بعدی بودن در برنامه های OLAP را می توان به سه سطح تقسیم کرد.

• نمایش داده های چند بعدی - ابزارهای کاربر نهایی که تجسم چند بعدی و دستکاری داده ها را ارائه می دهند. لایه نمایش چند بعدی از ساختار فیزیکی داده ها انتزاع می کند و داده ها را چند بعدی می داند.
• پردازش چند بعدی ابزاری (زبان) برای فرمول بندی پرس و جوهای چند بعدی (رابطه ای سنتی) است. زبان SQLمعلوم می شود که در اینجا غیر قابل استفاده است) و پردازنده ای که قادر به پردازش و اجرای چنین درخواستی است.
• ذخیره سازی چند بعدی - ابزاری برای سازماندهی فیزیکی داده ها که اجرای کارآمد پرس و جوهای چند بعدی را فراهم می کند.

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

محصولات خاص OLAP معمولاً یا یک ابزار ارائه داده چند بعدی هستند (کلاینت OLAP - مانند Pivot Tables در Excel 2000 مایکروسافتیا ProClarity توسط Knosys) یا یک DBMS چند بعدی سمت سرور (سرور OLAP مانند Oracle Express Server یا Microsoft OLAP Services).

لایه پردازش چند بعدی معمولاً در سرویس گیرنده OLAP و/یا سرور OLAP ساخته می‌شود، اما می‌تواند در خالص‌ترین شکل آن، مانند مؤلفه Pivot Table Service مایکروسافت، جدا شود.

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

اطلاعات اضافیدر مورد مکعب های آفلاین

اگر روی یک گزارش PivotTable یا PivotChart کار می‌کنید که مبتنی بر منبع داده از یک سرور OLAP است، از Offline Cube Wizard برای کپی کردن داده‌های منبع در یک فایل مکعب آفلاین جداگانه در رایانه خود استفاده کنید. برای ایجاد این فایل‌های آفلاین، باید یک ارائه‌دهنده داده OLAP که از این ویژگی‌ها پشتیبانی می‌کند، مانند MSOLAP از Microsoft SQL Server Analysis Services، روی رایانه نصب شده داشته باشید.

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

با استفاده از جادوگر مکعب آفلاین

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

انتقال داده ها به صورت آفلاین و سپس انتقال مجدد داده ها به صورت آنلاین

برای این کار ابتدا باید یک گزارش PivotTable یا گزارش PivotChart که مبتنی بر پایگاه داده سرور است ایجاد کنید و سپس یک فایل مکعب مستقل از گزارش ایجاد کنید. متعاقباً، در حین کار با گزارش، می توانید در هر زمان بین پایگاه داده سرور و فایل مستقل جابجا شوید (به عنوان مثال، هنگام کار بر روی لپتاپدر خانه یا در جاده و سپس اتصال مجدد کامپیوتر خود به شبکه).

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

توجه داشته باشید:

روی گزارش PivotTable کلیک کنید. اگر این یک گزارش PivotChart است، گزارش PivotTable مرتبط را انتخاب کنید.

روی برگه " تحلیل و بررسی" در گروه محاسباتدکمه را فشار دهید سرویس OLAPو دکمه را فشار دهید OLAP آفلاین.

یک مورد را انتخاب کنید OLAP هنگام اتصالو سپس روی دکمه کلیک کنید خوب.

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

روی یک گزارش PivotTable بر اساس یک فایل مکعب آفلاین کلیک کنید.

در اکسل 2016: در برگه داده ها" در گروه درخواست ها و اتصالات همه را بروز رسانی کنو دکمه را فشار دهید تازه کردن.

در اکسل 2013: در برگه داده ها" در گروه اتصالاتروی فلش کنار دکمه کلیک کنید همه را بروز رسانی کنو دکمه را فشار دهید تازه کردن.

روی برگه " تحلیل و بررسی" در گروه محاسباتدکمه را فشار دهید سرویس OLAPو دکمه را فشار دهید OLAP آفلاین.

روی دکمه کلیک کنید OLAP آفلاین، و سپس - .

توجه داشته باشید: متوقف کردندر کادر محاوره ای

هشدار:

یک فایل مکعب آفلاین از پایگاه داده سرور OLAP ایجاد کنید

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

مشکل: هنگام ذخیره یک مکعب، فضای دیسک کامپیوتر من تمام شد.

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

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

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

پیوست کردن یک فایل مکعب آفلاین به پایگاه داده سرور OLAP

به روز رسانی و ایجاد مجدد یک فایل مکعب آفلاین

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

مشکل: هنگام بازخوانی، داده های جدید در گزارش نمایش داده نمی شوند.

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

بررسی داده های جدیدبا سرپرست پایگاه داده خود بررسی کنید تا ببینید آیا داده هایی که باید در گزارش گنجانده شود به روز شده اند یا خیر.

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

شامل سایر داده ها در فایل مکعب آفلاین

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

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

یک گزارش PivotTable که بر اساس یک فایل مکعب آفلاین یا یک گزارش PivotTable مرتبط برای یک گزارش PivotChart است، کلیک کنید.

روی زبانه گزینه هادر گروه سرویسدکمه را فشار دهید سرویس OLAPو دکمه را فشار دهید OLAP آفلاین.

روی دکمه کلیک کنید OLAP آفلاین، و سپس - ویرایش فایل داده آفلاین.

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

توجه داشته باشید:برای لغو ذخیره یک فایل، روی دکمه کلیک کنید متوقف کردندر کادر محاوره ای ایجاد فایل مکعب - پیشرفت.

حذف یک فایل مکعب آفلاین

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

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

که در ویندوز مایکروسافتفایل مکعب آفلاین (فایل cub) را پیدا کرده و حذف کنید.

اطلاعات اضافی

همیشه می‌توانید از انجمن فناوری اکسل سؤال بپرسید، در انجمن پاسخ‌ها کمک بخواهید و همچنین پیشنهاد دهید. خصوصیت جدیدیا بهبود در وب سایت