صفحهٔ ۲۳۶ فایل PDF — صفحهٔ ۲۱۸ کتاب
PowerPivot for Excel
PowerPivot for Excel یک برنامهٔ Client است که فناوری SQL Server را بهصورت Add-in در Excel 2010 قرار میدهد. نسخهٔ بهروزشدهٔ موجود در SQL Server 2012 چند بهبود کوچک برای سهولت استفاده و چند قابلیت مهم برای هماهنگکردن ساختار PowerPivot Model با Tabular Model دارد.
نصب و ارتقا
نصب Add-in ساده است، اما دو پیشنیاز دارد: نخست Excel 2010 و سپس Visual Studio 2010 Tools for Office Runtime. اگر SQL Server 2008 R2 PowerPivot for Excel نصب است، باید آن را Uninstall کنید، زیرا Add-in مسیر Upgrade ندارد. سپس Add-in نسخهٔ SQL Server 2012 نصب میشود.
سهولت استفاده
بهبودهای Add-in انجام برخی Taskهای توسعهٔ PowerPivot Model را آسانتر میکنند. در Home Tab پنجرهٔ PowerPivot دکمههای Data View و Diagram View برای جابهجایی میان دو نمای Model افزوده شدهاند؛ درست مانند Tabular Model در SSDT. دکمههایی نیز برای نمایش یا پنهانکردن Hidden Columnها و Calculation Area وجود دارند.
شکل ۹-۱۴ — Home Tab در Ribbon پنجرهٔ PowerPivot.تصویر مرجع صفحهٔ ۲۳۶ فایل PDF
صفحهٔ ۲۳۷ فایل PDF — صفحهٔ ۲۱۹ کتاب
دکمهٔ جدید Sort By Column پنجرهای باز میکند که اجازه میدهد Sort دادهٔ یک Column با Valueهای مرتبط در Column دیگری از همان Table کنترل شود.
شکل ۹-۱۵ — پنجرهٔ Sort By Column.Design Tab اکنون دکمههای Freeze و Width را در گروه Columns دارد تا هنگام مرور دادهٔ Model، رابط بهتر مدیریت شود؛ این دکمهها در نسخهٔ قبل روی Home Tab بودند.
شکل ۹-۱۶ — Design Tab در Ribbon پنجرهٔ PowerPivot.دکمهٔ Mark As Date Table نیز جدید است. Table دارای Date را باز کنید و این دکمه را بزنید؛ Dialog از شما Column دارای مقدارهای یکتای DateTime را میخواهد. سپس میتوان DAX Expressionهای دارای Time Intelligence را بدون تمام مراحل نسخهٔ قبل ساخت و نتیجهٔ درست گرفت. پس از افزودن Date Column به PivotTable، Advanced Date Filter بهعنوان Row یا Column Filter قابل انتخاب است.
شکل ۹-۱۷ — Advanced Filterهای قابل استفاده برای Date Field.تصویر مرجع صفحهٔ ۲۳۷ فایل PDF
صفحهٔ ۲۳۸ فایل PDF — صفحهٔ ۲۲۰ کتاب
Advanced Tab بهطور پیشفرض نمایش داده نمیشود. از File در بالا و فرمان Switch To Advanced Mode استفاده کنید. این Tab برای افزودن یا حذف Perspective، تغییر نمایش Implicit Measureها، افزودن Measureهای Aggregate و تنظیم Reporting Propertyهاست. بهجز Show Implicit Measures، Taskها مشابه Tabular Model Development هستند. Show Implicit Measures نمایش Measureهایی را تغییر میدهد که PowerPivot هنگام افزودن Numeric Column به Values یک PivotTable بهطور خودکار میسازد.
شکل ۹-۱۸ — Advanced Tab در Ribbon پنجرهٔ PowerPivot.تصویر مرجع صفحهٔ ۲۳۸ فایل PDF
صفحهٔ ۲۳۹ فایل PDF — صفحهٔ ۲۲۱ کتاب
Numeric Columnها علاوه بر PivotTable Value برای Aggregation، اکنون میتوانند به Row یا Column نیز بهعنوان Distinct Value افزوده شوند. مثلاً [Sales Amount] هم در Row و هم در Value قرار میگیرد تا Sum فروش برای هر Amount دیده شود.
شکل ۹-۱۹ — استفاده از Numeric Value روی Rowها.بهبودهای دیگر این نسخه:
- در PowerPivot Window میتوان Data Type یک Calculated Column را تنظیم کرد.
- با Right-click روی Column و Hide From Client Tools، Field از دسترس User پنهان میشود. Column در Data View با Background خاکستری باقی میماند و Show Hidden روی Home Tab نمایش آن را تغییر میدهد.
- Number Format تعیینشده برای Measure در Excel Window پایدار میماند.
- با Right-click روی Numeric Value در Excel و Show Details، Worksheet جداگانهای از Rowهای سازندهٔ Value باز میشود. این قابلیت برای Calculated Valueهای غیر از Aggregate ساده مانند Sum یا Count کار نمیکند.
- برای Table، Measure، KPI Label، KPI Value، KPI Status یا KPI Target میتوان Description افزود؛ این توضیح در PowerPivot Field List بهشکل Tooltip دیده میشود.
- PowerPivot Field List ابتدا Hierarchyهای هر Table و سپس سایر Fieldها را بهترتیب الفبایی نمایش میدهد.
بهبودهای Model
اکنون قابلیتهای طراحی زیر که در Tabular Model وجود داشتند در PowerPivot Model نیز در دسترساند: Table Relationship، Hierarchy، Perspective و Calculation Area برای طراحی Measure و KPI.
تصویر مرجع صفحهٔ ۲۳۹ فایل PDF
صفحهٔ ۲۴۰ فایل PDF — صفحهٔ ۲۲۲ کتاب
در عمل میتوان همان ساختار را در SSDT بهصورت Tabular Model یا در Excel بهصورت PowerPivot Model ساخت. فرایند Modeling بسیار مشابه است، اما Tabular Model فقط روی Analysis Services Tabular Mode و PowerPivot Model فقط روی PowerPivot for SharePoint مستقر میشود. PowerPivot Model را میتوان برای ساخت Tabular Model Project Import کرد، اما مسیر معکوس برای Import پروژهٔ جدولی به PowerPivot وجود ندارد.
DAX
DAX زبان Expression برای Calculated Column، Measure و KPI در Tabular و PowerPivot Model است. SQL Server 2012 تابعهای آماری، اطلاعاتی، منطقی، Filter و ریاضی جدیدی به DAX میافزاید.
جدول ۹-۴ — تابعهای جدید DAX، بخش اول| نوع | تابع | توضیح |
|---|
| Statistical | ADDCOLUMNS() | Table را با یک یا چند Calculated Column افزوده برمیگرداند. |
CROSSJOIN() | حاصلضرب دکارتی Rowهای دو یا چند Table را برمیگرداند. |
DISTINCTCOUNT() | تعداد Valueهای متمایز یک Column را برمیگرداند. |
GENERATE() | حاصلضرب دکارتی Table اول و Table دومی را که در Context هر Row از Table اول ارزیابی میشود برمیگرداند. Row بدون نتیجه در Table دوم از خروجی حذف میشود. |
GENERATEALL() | مانند GENERATE است، اما Row بدون نتیجه نیز با Null برای Columnهای Table دوم باقی میماند. |
RANK.EQ() | رتبهٔ Value مشخص در برابر فهرست Valueها. |
RANKX() | رتبهٔ Value برای هر Row از Table مشخص. |
ROW() | یک Row دارای یک یا چند جفت Name/Value میسازد که Value نتیجهٔ Expression است. |
STDEV.P() | انحراف معیار Column برای کل Population. |
STDEV.S() | انحراف معیار Column برای Sample Population. |
STDEVX.P() | انحراف معیار Expression ارزیابیشده برای هر Row، با Table بهعنوان کل Population. |
STDEVX.S() | انحراف معیار Expression ارزیابیشده برای هر Row، با Table بهعنوان Sample. |
صفحهٔ ۲۴۱ فایل PDF — صفحهٔ ۲۲۳ کتاب
ادامهٔ جدول ۹-۴ — تابعهای جدید DAX| نوع | تابع | توضیح |
|---|
| Statistical | SUMMARIZE() | Tableی از Valueهای Aggregateشده بر پایهٔ Group By Columnها. |
TOPN() | Table دارای N Row برتر بر اساس Order By Expression. |
VAR.P() | واریانس Column برای کل Population. |
VAR.S() | واریانس Column برای Sample Population. |
VARX.P() | واریانس Expression برای هر Row با Table بهعنوان کل Population. |
VARX.S() | واریانس Expression برای هر Row با Table بهعنوان Sample. |
| Information | CONTAINS() | Boolean نشاندهندهٔ وجود دستکم یک Row با جفتهای Name/Value تعیینشده. |
LOOKUPVALUE() | Value یک Column را که با معیارهای Name/Value منطبق است برمیگرداند. |
PATH() | Identifier همهٔ Ancestorهای یک Identifier در Parent-Child Hierarchy. |
PATHCONTAINS() | وجود Identifier در Path مشخص را بهصورت Boolean نشان میدهد. |
PATHITEM() | Ancestor Identifier در فاصلهٔ مشخص از Starting Identifier. |
PATHITEMREVERSE() | Ancestor Identifier در فاصلهٔ مشخص از Topmost Identifier. |
| Information | PATHLENGTH() | تعداد Identifierهای Path، همراه Starting Identifier. |
| Logical | SWITCH() | Expression را در برابر Conditionهای ممکن ارزیابی و Value جفتشده با Condition را برمیگرداند. |
| Filter | ALLSELECTED() | Expression را با نادیدهگرفتن Row و Column Filter و حفظ سایر Filterها ارزیابی میکند. |
FILTERS() | Valueهایی که اکنون Column را Filter میکنند. |
HASONEFILTER() | Boolean برای وجود یک Filter روی Column. |
HASONEVALUE() | Boolean برای برگشت یک Distinct Value از Column. |
ISCROSSFILTERED() | Boolean برای اعمال Filter روی Column، Columnی از همان Table یا Related Table. |
ISFILTERED() | Boolean برای Filter مستقیم Column. |
USERELATIONSHIP() | Relationship پیشفرض را با Relationship جایگزین و Columnهای مرتبط دو Table Override میکند؛ مثلاً ShippingDate بهجای OrderDate. |
| Math | CURRENCY() | Expression را به Data Type پولی برمیگرداند. |
صفحهٔ ۲۴۲ فایل PDF — صفحهٔ ۲۲۴ کتاب
PowerPivot for SharePoint
تغییرهای کلیدی SQL Server 2012، نصب و Configuration سادهتر و ابزارهای مدیریتی گستردهتری برای Server Environment ارائه میکنند تا Server سریعتر راهاندازی شود و با افزایش استفاده، پایدار بماند.
نصب و Configuration
PowerPivot for SharePoint به مؤلفههای زیادی در SharePoint Server 2010 Farm وابسته است. پیش از نصب باید SharePoint Server 2010 و Service Pack 1 نصب باشند، اما اجرای SharePoint Configuration Wizard الزامی نیست. میتوان ابتدا نمونهٔ PowerPivot for SharePoint را نصب و سپس PowerPivot Configuration Tool را برای Configuration همزمان SharePoint Farm و PowerPivot اجرا کرد.
مدیریت
برای عملکرد بهینه باید مصرف Resource مرتب پایش شود و Server توان پشتیبانی Workload را حفظ کند. SQL Server 2012 ابزارهای مدیریت Disk Space، شناسایی پیشدستانهٔ مشکلهای Server Health و رسیدگی به شکستهای مداوم Data Refresh را گسترش میدهد.
تصویر مرجع صفحهٔ ۲۴۲ فایل PDF
صفحهٔ ۲۴۳ فایل PDF — صفحهٔ ۲۲۵ کتاب
مصرف فضای Disk
Workbook مستقرشده در SharePoint Content Database ذخیره میشود. هنگام درخواست User، PowerPivot آن را بهصورت PowerPivot Database در مسیر \Program Files\Microsoft SQL Server\MSAS11.PowerPivot\OLAP\Backup\Sandboxes\<serviceApplicationName> Cache و سپس در Memory Load میکند. پس از دورهٔ بدون دسترسی، Workbook از Memory حذف میشود ولی برای Load سریعتر روی Disk باقی میماند.
بدون Limit، Cache تا پرشدن Disk ادامه دارد. PowerPivot System Service بهصورت دورهای Workbookهای کماستفاده یا دارای نسخهٔ جدیدتر در Content Database را حذف میکند. در SQL Server 2012 دو ویژگی Server-Level در SharePoint Central Administration، Application Management، Manage Services On Server و SQL Server Analysis Services وجود دارد:
- Maximum Disk Space For Cached Files: مقدار ۰ یعنی استفاده از تمام Disk آزاد؛ مقدار GB مشخص سقف میسازد.
- Set A Last Access Time (In Hours) To Aid In Cache Reduction: هنگام عبور Cache از سقف، Workbookهای غیرفعالتر از این مدت حذف میشوند؛ پیشفرض ۴ ساعت است.
در Default PowerPivot Service Application و Configure Service Application Settings نیز:
- Keep Inactive Database In Memory (In Hours): پیشفرض ۴۸ ساعت پس از آخرین Query؛ Workbook پرتکرار هرگز از Memory خارج نمیشود.
- Keep Inactive Database In Cache (In Hours): پس از خروج از Memory، پیشفرض ۱۲۰ ساعت در Cache باقی میماند.
تصویر مرجع صفحهٔ ۲۴۳ فایل PDF
صفحهٔ ۲۴۴ فایل PDF — صفحهٔ ۲۲۶ کتاب
قواعد سلامت Server
هدف، شناسایی تهدید پیش از بروز مشکل است. وضعیت Ruleها در Central Administration، Monitoring و Review Problems And Solutions دیده میشود. Ruleهای Server-Level:
- Insufficient CPU Resource Allocation: هشدار وقتی CPU مصرفی
msmdsrv.exe طی Data Collection Interval در یا بالاتر از درصد مشخص بماند؛ پیشفرض ۸۰٪. - Insufficient CPU Resources On The System: هشدار برای CPU کل Server؛ پیشفرض ۹۰٪.
- Insufficient Memory Threshold: هشدار وقتی Available Memory از درصد تعیینشدهٔ Memory تخصیصیافته به Analysis Services کمتر شود؛ پیشفرض ۵٪.
- Maximum Number of Connections: هشدار با عبور Connectionها از عدد تعیینشده؛ پیشفرض ۱۰۰ و باید متناسب با Server تنظیم شود.
- Insufficient Disk Space: هشدار وقتی Available Disk روی Drive پوشهٔ Backup زیر درصد تعیینشده رود؛ پیشفرض ۵٪.
- Data Collection Interval: دورهٔ محاسبهٔ Ruleهای Server-Level؛ پیشفرض ۴ ساعت.
Ruleهای Service-Application-Level در Default PowerPivot Service Application:
- Load To Connection Ratio: هشدار وقتی نسبت Load Event به Connection Event از مقدار تعیینشده، پیشفرض ۲۰٪، بیشتر شود؛ عدد بالا ممکن است نشانهٔ Unload سریع Database از Memory یا Cache باشد.
تصویر مرجع صفحهٔ ۲۴۴ فایل PDF
صفحهٔ ۲۴۵ فایل PDF — صفحهٔ ۲۲۷ کتاب
- Data Collection Interval: دورهٔ محاسبهٔ Ruleهای Service Application؛ پیشفرض ۴ ساعت.
- Check For Updates To PowerPivot Management Dashboard.xlsx: اگر فایل Dashboard طی تعداد روز تعیینشده تغییر نکند هشدار میدهد؛ پیشفرض ۵ روز است و در شرایط عادی روزانه Refresh میشود.
Configuration مربوط به Data Refresh
Data Refresh Resource مصرف میکند، پس باید فقط برای Workbook فعال و Refreshهای موفق ادامه یابد. Service Application میتواند Schedule را در صورت نقض این شرایط غیرفعال کند:
- Disable Data Refresh Due To Consecutive Failures: پس از تعداد شکست متوالی، Schedule غیرفعال میشود؛ پیشفرض ۱۰ است و ۰ از غیرفعالسازی جلوگیری میکند.
- Disable Data Refresh For Inactive Workbooks: اگر طی تعداد Cycle تعیینشده هیچ Query روی Workbook انجام نشود، Workbook غیرفعال میشود؛ پیشفرض ۱۰ است و ۰ Refresh را فعال نگه میدارد.
تصویر مرجع صفحهٔ ۲۴۵ فایل PDF
صفحهٔ ۲۴۶ فایل PDF
این صفحه در نسخهٔ اصلی فاقد محتوای آموزشی مستقل است.
تصویر مرجع صفحهٔ ۲۴۶ فایل PDF