فصل ۹

Analysis Services و PowerPivot

در SQL Server 2005 و SQL Server 2008 فقط یک حالت از SQL Server Analysis Services یا SSAS وجود داشت. سپس در SQL Server 2008 R2، حالت VertiPaq به‌عنوان موتور PowerPivot for SharePoint معرفی شد. این دو حالت سرور با بهبودهایی در SQL Server 2012 باقی مانده‌اند و اکنون می‌توان یک نمونهٔ Analysis Services را در حالت جدولی (Tabular Mode) نیز مستقر کرد. افزون بر این، Microsoft SQL Server 2012 PowerPivot for Excel چند قابلیت جدید دارد که دامنهٔ تحلیل‌های قابل پشتیبانی را گسترش می‌دهد.

Analysis Services

پیش از استقرار یک نمونهٔ Analysis Services باید نوع قابلیت موردنیاز را تعیین و حالت سرور مناسب را نصب کنید. این بخش سه حالت سرور را مقایسه می‌کند، الگوهای مختلف Analysis Services برای آغاز یک پروژهٔ جدید را توضیح می‌دهد، مؤلفه‌های حالت جدولی جدید را معرفی می‌کند، گزینه‌های جدید مدیریت سرور را مرور می‌کند و در پایان به بهبودهای برنامه‌پذیری این نسخه می‌پردازد.

حالت‌های سرور

در SQL Server 2012 یک نمونهٔ Analysis Services می‌تواند در یکی از سه حالت چندبعدی (Multidimensional)، جدولی (Tabular) یا PowerPivot for SharePoint اجرا شود. هر حالت، نوع متفاوتی از پایگاه‌داده را با ساختار ذخیره‌سازی، معماری حافظه و موتور ویژهٔ خود پشتیبانی می‌کند. حالت چندبعدی از موتور Analysis Services موجود در SQL Server 2005 و نسخه‌های بعدی استفاده می‌کند. حالت جدولی و PowerPivot for SharePoint هر دو از موتور VertiPaq معرفی‌شده در SQL Server 2008 R2 استفاده می‌کنند؛ این موتور داده را برای نگه‌داری در حافظه هنگام اجرا فشرده می‌کند. بااین‌حال حالت جدولی، برخلاف PowerPivot for SharePoint، به SharePoint وابسته نیست.

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

عامل دیگر، مجموعهٔ قابلیت‌های طراحی Model است که باید نیازهای کسب‌وکار کاربران در گزارش‌گیری و تحلیل را برآورده کند. جدول ۹-۲ پشتیبانی هر حالت را نشان می‌دهد.

حالت سرور هنگام نصب Analysis Services تعیین می‌شود. در صفحهٔ Setup Role از SQL Server Setup، گزینهٔ SQL Server Feature Installation را برای حالت چندبعدی یا جدولی انتخاب می‌کنید، یا برای PowerPivot for SharePoint گزینهٔ SQL Server PowerPivot For SharePoint را برمی‌گزینید. در حالت Feature Installation، در صفحهٔ Analysis Services Configuration باید Multidimensional And Data Mining Mode یا Tabular Mode را انتخاب کنید. پس از پایان نصب، حالت یک نمونهٔ موجود قابل تغییر نیست.

پروژه‌های Analysis Services

SQL Server Data Tools یا SSDT ابزار توسعهٔ Modelهای چندبعدی، Data Mining و جدولی است. مانند هر پروژهٔ Business Intelligence، از منوی File گزینهٔ New و سپس Project را انتخاب کنید. در New Project و فهرست Installed Templates چند الگوی Analysis Services نمایش داده می‌شود.

پنج الگو برای پروژه‌های Analysis Services وجود دارد:

• Analysis Services Multidimensional and Data Mining Project: برای توسعهٔ پروژهٔ سنتی Analysis Services که اکنون Model چندبعدی نامیده می‌شود و تنها Model دارای قابلیت‌های Data Mining است.
• Import from Server (Multidimensional and Data Mining): برای ساخت پروژه‌ای جدید از روی طراحی Model چندبعدی موجود روی سرور.
• Analysis Services Tabular Project: برای ساخت Model جدولی جدید؛ این Model فقط روی نمونهٔ Analysis Services در حالت جدولی مستقر می‌شود.
• Import from PowerPivot: برای واردکردن Model از Workbook مستقر در PowerPivot for SharePoint. سپس می‌توان آن را با قابلیت‌های Tabular Modeling گسترش داد و فقط روی نمونهٔ جدولی مستقر کرد.
• Import from Server (Tabular): برای ساخت پروژه‌ای جدید بر پایهٔ Model جدولی موجود روی سرور.

مدل‌سازی جدولی

Model جدولی نوع جدیدی از ساختار پایگاه‌داده است که Analysis Services در SQL Server 2012 پشتیبانی می‌کند. هنگام ایجاد Tabular Project، فایل Model.bim به پروژه افزوده و یک Workspace Database روی نمونهٔ تعیین‌شدهٔ Analysis Services ساخته می‌شود. این Database، هنگام Import داده و طراحی Objectهایی برای سازمان‌دهی، غنی‌سازی و امن‌سازی داده، فضای ذخیرهٔ موقت پروژه است.

Workspace Database

هنگام کار با Tabular Model Project در SSDT، Workspace Database متناظر در حافظه قرار دارد و داده‌های افزوده‌شده با Table Import Wizard را نگه می‌دارد. نمایش داده در Diagram View یا Data View نیز از همین Database خوانده می‌شود.

با انتخاب Model.bim در Solution Explorer، ویژگی‌های زیر در Properties در دسترس‌اند:

• Data Backup: مقدار پیش‌فرض Do Not Backup To Disk است. گزینهٔ Backup To Disk در هر ذخیرهٔ Model.bim یک فایل ABF می‌سازد؛ این گزینه برای Workspace روی نمونهٔ Remote قابل استفاده نیست.
• Workspace Database: نام تخصیص‌داده‌شده توسط Analysis Services را نشان می‌دهد و قابل تغییر نیست.
• Workspace Retention: رفتار Database پس از بستن پروژه را تعیین می‌کند. Unload From Memory، Database را روی Disk نگه می‌دارد ولی از Memory خارج می‌کند؛ Keep In Memory بازشدن بعدی را سریع می‌کند؛ Delete Workspace آن را از Memory و Disk حذف می‌کند و به‌دلیل Import دوباره، بیشترین زمان بارگذاری را دارد. مقدار پیش‌فرض از Tools، Options و صفحهٔ Data Modeling در Analysis Server Settings قابل تغییر است.
• Workspace Server: سرور میزبان Workspace Database را مشخص می‌کند؛ برای بهترین Performance از نمونهٔ Local استفاده کنید.

Table Import Wizard

Table Import Wizard داده را از یک یا چند منبع وارد می‌کند. علاوه بر اطلاعات Connection مانند Server و Database در منبع رابطه‌ای، باید صفحهٔ Impersonation Information را تکمیل کنید. Analysis Services برای Import و Process داده از Credentialهای این صفحه استفاده می‌کند؛ می‌توانید Windows Login و Password بدهید یا Service Account خود Analysis Services را تعیین کنید.

گام بعدی روش بازیابی داده است. برای منبع رابطه‌ای می‌توان Table و View را از فهرست انتخاب یا Query ارائه کرد. در همهٔ منبع‌ها امکان Filter پیش از Import وجود دارد. با پاک‌کردن Check Box Header یک Column، کل آن Column حذف می‌شود. با Arrow کنار نام Column نیز می‌توان Rowهای دارای Value مشخص را کنار گذاشت.

با Text Filters یا Numeric Filters نیز می‌توان Filter دقیق‌تری ساخت؛ برای نمونه، فقط Value برابر یک مقدار یا شامل یک رشتهٔ مشخص Import شود.

Tabular Model Designer

پس از Import، Model Designer داده را در Workspace و در Tabهای جدا نمایش می‌دهد. برای خواناترکردن نام Column، روی نام آن Double-click کنید، نام جدید را بنویسید و Enter بزنید.

هنگام Import از منبع رابطه‌ای، Relationshipهای موجود شناسایی و به Model افزوده می‌شوند. برای دیدن آن‌ها به Diagram View بروید؛ از دکمهٔ Diagram در پایین Workspace یا مسیر Model، Model View، Diagram View استفاده کنید. با اشاره به خط اتصال دو Table، Columnهای مرتبط Highlight می‌شوند.

Relationshipها

برای افزودن Relationship جدید، Column یک Table را به Column متناظر در Table دوم Drag کنید. Model به‌طور خودکار Primary Table و Lookup Table را تشخیص می‌دهد، بنابراین ترتیب انتخاب Tableها مهم نیست. از Table، Manage Relationships نیز می‌توان همهٔ Relationshipها را دید و Relationship جدید ساخت یا مورد موجود را ویرایش و حذف کرد.

می‌توان بین دو Table چند Relationship داشت، اما در هر لحظه فقط یکی Active است. Calculationها به‌طور پیش‌فرض از Relationship فعال استفاده می‌کنند، مگر این رفتار با تابع USERELATIONSHIP() تغییر کند.

Calculated Column

Calculated Column از فرمول Data Analysis Expressions یا DAX به‌دست می‌آید. برای نمونه، دو Column را می‌توان در یک Column به هم چسباند. در Data View روی Column راست‌کلیک و Insert Column را انتخاب کنید یا از منوی Column گزینهٔ Add Column را بزنید، فرمول معتبر DAX را در Formula Bar بنویسید و Enter بزنید. Model Designer مقدار هر Row را محاسبه و نمایش می‌دهد.

Measureها

Calculated Column در سطح Row ارزیابی و نتیجه در Model ذخیره می‌شود، اما Measure یک مقدار Aggregate را در Context مربوط به Row، Column، Filter و Slicer در PivotTable محاسبه می‌کند. برای افزودن Measure، Cellی در Calculation Area زیر دادهٔ Table انتخاب، فرمول DAX را در Formula Bar وارد و Enter کنید. نام پیش‌فرض مانند Measure1 را نیز می‌توان در Formula Bar تغییر داد.

برای ساخت Measureی که فقط Valueهای Row را Aggregate می‌کند، Header Column را انتخاب و AutoSum را بزنید. برای مثال، انتخاب Count برای ProductKey فرمول زیر را می‌سازد:

Count of ProductKey:=COUNTA([ProductKey])

شاخص‌های کلیدی عملکرد

Key Performance Indicator یا KPI نوع خاصی از Measure برای سنجش پیشرفت به‌سوی Goal است. ابتدا Base Measure را در Calculation Area بسازید، روی آن راست‌کلیک و Create KPI را انتخاب کنید. سپس Measure یا Absolute Value نمایندهٔ Target و مرزهای Status Threshold را تعیین کنید. Analysis Services با مقایسهٔ Base Measure و Thresholdها Icon مناسب Status را نمایش می‌دهد.

Hierarchyها

Hierarchy برای تحلیل داده در Levelهای متفاوت و حرکت منطقی از یک Level به Level بعدی کاربرد دارد. در Diagram View روی Column مربوط به Parent Level راست‌کلیک و Create Hierarchy را انتخاب کنید، یا از دکمه‌ای که روی Header ظاهر می‌شود استفاده کنید. نام بدهید و Columnها را به Hierarchy بکشید. فقط Columnهای همان Table قابل افزودن‌اند. در صورت نیاز Calculated Columnای با تابع RELATED() بسازید تا Column یک Table مرتبط در Hierarchy استفاده شود.

Perspectiveها

وقتی Model Objectهای زیادی دارد، Perspective زیرمجموعه‌ای از آن‌ها را نمایش می‌دهد تا کاربر سریع‌تر Object موردنیاز را پیدا کند. از Model، Perspectives را باز کنید و Table، Column، Measure، KPI و Hierarchyهای موردنظر را انتخاب کنید.

Partitionها

هر Table حداقل یک Partition دارد، اما برای مدیریت Process جداگانه می‌توان آن را به چند Partition تقسیم کرد؛ مثلاً دادهٔ جاری مرتب Process شود و Historical Data نیازی به Process دوباره نداشته باشد. از Table، Partitions را باز و در Partition Manager گزینهٔ Query Editor را برای افزودن WHERE به SQL Statement انتخاب کنید.

برای Partition مربوط به ماه مارس ۲۰۰۴، شرط نمونه چنین است:

WHERE
    (([OrderDate] >= N’2004-03-01 00:00:00’) AND
    ([OrderDate] < N’2004-04-01 00:00:00’))

پس از ساخت Partitionها، Table را در Data View باز کنید و از Model، Process را انتخاب کنید. Process Partitions دادهٔ Partitionهای انتخابی را Refresh می‌کند و Process Table تمام Partitionها را Refresh می‌کند. پس از Deployment نیز Script برای مدیریت Process هر Partition قابل استفاده است.

Roleها

Tabular Model به‌طور پیش‌فرض Secure است. برای دسترسی باید Analysis Services Database Role ساخته و Windows User یا Group به آن اضافه شود. مجوزهای Role عبارت‌اند از:

• None: عضو هیچ استفاده‌ای از Model ندارد.
• Read: عضو فقط می‌تواند داده را Query کند.

• Read And Process: Query و اجرای Process مجاز است، اما Database در SSMS دیده یا ویرایش نمی‌شود.
• Process: فقط Process مجاز است؛ Query و مشاهدهٔ Database در SSMS مجاز نیست.
• Administrator: دسترسی کامل برای Query، Process، مشاهده و تغییر Model.

از Model، Roles را باز کنید، نام و Permission را بدهید و Memberها را اضافه کنید. اگر User در چند Role با Permission متفاوت باشد، Analysis Services Permissionها را ترکیب و در تعارض، کم‌محدودیت‌ترین را اعمال می‌کند؛ مثلاً ترکیب None و Read، نتیجهٔ Read دارد. Role را می‌توان پس از Deployment در SSMS نیز افزود.

برای Roleهای دارای Read یا Read And Process، Row-Level Filter با Expressionهای DAX ساخته می‌شود. هر Filter باید TRUE یا FALSE برگرداند. مثال زیر فقط داده‌های Bikes را مجاز می‌کند:

=Category[EnglishProductCategoryName]=”Bikes”

برای منع همهٔ Rowهای یک Table:

=FALSE()

Analyze in Excel

پیش از Deployment می‌توان تجربهٔ User را آزمود. Analyze In Excel، Excel نصب‌شده روی همان Computer را باز، Connection به Model Workspace می‌سازد و PivotTable اضافه می‌کند. در Dialog باید User یا Role برای Security Context و Perspective پیش‌فرض یا سفارشی را انتخاب کنید.

ویژگی‌های گزارش‌گیری

برای Power View، Properties مخصوص گزارش‌گیری در هر Table و Column وجود دارد. ویژگی Hidden تعیین می‌کند Object در Report Field List دیده شود یا نه. ویژگی‌های Table عبارت‌اند از:

• Default Field Set: فهرست Column و Measureهایی که با انتخاب Table به Report Canvas افزوده می‌شوند.
• Default Image: Column دارای Image هر Row.
• Default Label: Column دارای Display Name هر Row.
• Keep Unique Rows: نمایش Duplicateها به‌صورت Unique Valueهای جدا یا یک Value.
• Row Identifier: Column دارای Identifier یکتای هر Row.

ویژگی‌های Column عبارت‌اند از:

• Default Label: نشان می‌دهد Column دارای Display Name هر Row است؛ در هر Table فقط برای یک Column True می‌شود.
• Image URL: نشان می‌دهد Column دارای URL تصویر وب یا SharePoint است تا Power View آن را Image بازیابی کند، نه Text.

• Row Identifier: نشان می‌دهد Column دارای Identifier یکتای Row است؛ فقط یک Column در Table می‌تواند True باشد.
• Table Detail Position: ترتیب Column جاری نسبت به Columnهای دیگر Default Field Set را تعیین می‌کند.

DirectQuery Mode

وقتی حجم Model در Memory جا نمی‌شود یا Query باید تازه‌ترین داده را برگرداند، DirectQuery را فعال کنید. Analysis Services به‌جای Cache درون‌حافظه‌ای، داده و Aggregate را مستقیم از Source Database بازیابی می‌کند. Cache سریع‌تر است، اما Refresh پیوستهٔ آن برای دادهٔ حجیم ممکن است پرهزینه باشد. برای فعال‌سازی، Model.bim را انتخاب و DirectQueryMode را از Off به On تغییر دهید.

Deployment

پیش از Deployment، نمونهٔ مقصد Analysis Services باید در Tabular Mode اجرا شود و نام Model در Project Properties تعیین گردد. Query Mode یکی از موارد زیر است و بعداً در SSMS قابل تغییر است:

• DirectQuery: Query فقط از Relational Data Source استفاده می‌کند.
• DirectQuery With In-Memory: Query از Source رابطه‌ای استفاده می‌کند، مگر Client در Connection String خلاف آن را مشخص کند.
• In-Memory: Query فقط از Cache در حافظه استفاده می‌کند.
• In-Memory With DirectQuery: Query از Cache استفاده می‌کند، مگر Client در Connection String خلاف آن را مشخص کند.

پس از تنظیم، از Build یا Right-click پروژه و Deploy استفاده کنید. بعد از Deployment، Partition، Security، Backup و Restore با SSMS مدیریت می‌شود. User مجاز می‌تواند Model را Data Source برای PowerPivot Workbook یا Power View Report قرار دهد.

ذخیره‌سازی Model چندبعدی

فرایند توسعهٔ Model چندبعدی مانند SQL Server 2005 و بعد از آن است، با یک استثنا: موتور MOLAP اکنون برای String Data از Storage مقیاس‌پذیرتری استفاده می‌کند. محدودیت File Size برابر ۴ گیگابایت حذف شده، اما Dimension باید برای Storage جدید تنظیم شود.

در Dimension Designer، Parent Node را در Attributes انتخاب و در Advanced مقدار StringStoreCompatibilityLevel را به 1100 تغییر دهید. این Setting برای Measure Group دارای Distinct Count مبتنی بر String نیز قابل اعمال است. با Process Full، داده در String Store جدید Load می‌شود. File تا هر اندازهٔ لازم رشد می‌کند، اما حداکثر چهار میلیارد String یکتا یا چهار میلیارد Record، هرکدام زودتر رخ دهد، نگه می‌دارد.

مدیریت سرور

SQL Server 2012 جمع‌آوری اطلاعات Performance و Diagnostic را با Capture Event یا Query کردن Dynamic Management Viewها آسان‌تر می‌کند و امکان Configuration برای NUMA یا بیش از ۶۴ Processor را می‌دهد.

Event Tracing

اکنون SQL Server Extended Events می‌تواند هر Event از Analysis Services را به‌جای SQL Server Profiler ثبت کند. برای عیب‌یابی Query Performance در حالت چندبعدی، با XMLA Create Object می‌توان Eventهایی مانند Query Subcube، Get Data From Aggregation، Get Data From Cache و Query End را فعال کرد. Eventهای جدید چندبعدی عبارت‌اند از Locks Acquired، Locks Released، Locks Waiting، Deadlock و LockTimeOut. داده در File ذخیره می‌شود تا با XMLA Delete Object Tracing غیرفعال شود.

برای حالت‌های دیگر Eventهای VertiPaq SE Query Begin، VertiPaq SE Query End، Direct Query Begin و Direct Query End وجود دارد. Resource Usage نیز برای همهٔ حالت‌ها جدید است و اندازهٔ Read/Write بر حسب KB و CPU Usage را ثبت می‌کند.

XML for Analysis Schema Rowsetها

Schema Rowsetهای جدید برای بررسی Metadata مدل جدولی و پایش Server ارائه شده‌اند و از طریق DMV در SSMS قابل Query هستند:

• DISCOVER_CALC_DEPENDENCY: وابستگی میان Column، Measure و Formula.
• DISCOVER_CSDL_METADATA: دریافت Conceptual Schema Definition Language یا CSDL مدل جدولی.
• DISCOVER_XEVENT_TRACE_DEFINITION: پایش SQL Server Extended Events.
• DISCOVER_TRACES: استفاده از Column جدید Type برای Filter بر اساس Category.
• MDSCHEMA_HIERARCHIES: استفاده از Column جدید Structure_Type برای Filter کردن Hierarchy به Natural، Unnatural یا Unknown.

بهبودهای معماری

نمونه‌های جدولی و چندبعدی را می‌توان روی Server دارای NUMA و بیش از ۶۴ Processor مستقر کرد. ویژگی‌های Instance برای Groupهای Processor تنظیم می‌شوند:

• Thread Pools: هر Process، IO Process، Query، Parsing و VertiPaq Thread Pool می‌تواند به Processor Group جدا تخصیص یابد.
• Affinity Masks: مشخص می‌کند Processor در یک Group در عملیات Analysis Services شامل یا مستثنا شود.
• Memory Allocation: Memory Rangeهای اختصاص‌یافته به Processor Groupها را تعیین می‌کند.

برنامه‌پذیری

طرح BI Semantic Model یا BISM در SQL Server 2012 جانشین Unified Dimensional Model یا UDM معرفی‌شده در SQL Server 2005 است. BISM هم رویکرد Entity بر پایهٔ Table و Relationship و هم رویکرد چندبعدی بر پایهٔ Hierarchy و Aggregation را پشتیبانی می‌کند. این نسخه Analysis Management Objects یا AMO و XMLA را برای مدیریت BISM گسترش می‌دهد.

به‌جای رابط گرافیکی SSMS یا Application و Script سفارشی ساخته‌شده با AMO/XMLA، اکنون Windows PowerShell نیز در دسترس است. Cmdletهای Analysis Services برای پیمایش Objectهای Model، Query، Restart Service، تنظیم Memberهای Security Role، Backup و Restore و Process کردن Cube Dimension یا Partition استفاده می‌شوند.

قابلیت برنامه‌پذیری جدید دیگر، Extensionهای Conceptual Schema Definition Language یا CSDL است که تعریف Model جدولی را به Reporting Client ارائه می‌کند. Analysis Services در پاسخ به Client، Entity Definitionها را به XML می‌فرستد. Client از آن برای نمایش Field، Aggregation و Measureهای گزارش‌گیری و گزینه‌های Grouping، Sorting و Formatting استفاده می‌کند. Extensionهای CSDL شامل Elementهای جدید Model، Attribute و Extensionهای Entity و Propertyهای Visualization و Navigation هستند.