Kursüberblick
Dies ist ein weiterführender MDX-Kurs. Der erste Tag widmet sich möglicher Strategien für das Monitoring und die Optimierung vom MDX. Am zweiten Tag wenden wir uns zuerst fortgeschrittenen MDX-Konzepten und –Funktionen zu. Danach beschäftigen wir uns mit dem effektiven Gebrauch von MDX Script. Dieses Seminar ist relevant für SQL Server 2014, 2012 und 2008.
Zielgruppe
Das Seminar richtet sich an Entwickler und BI-Profis, die multidimensionale Cubes in SQL Server Analysis Services entwickeln und/oder supporten.
Voraussetzungen
Sie sollten über folgendes Vorwissen verfügen:
- Verständnis für MDX Calculations
- Verständnis für Sets und Tuples
- Fähigkeit einfache MDX Funktionen zu nutzen
Kursinhalt
Advanced Functions
- Using VBA and Excel Functions in MDX
- Using the String Functions - StrToMember, StrToSet, StrToTuple, MemberToStr, TupleToStr
- Using the Statistical Functions - StdDev, StdDevP, Var, VarP, Covariance, CovarienceN, LinRegIntercept, LinRegPoint, LinRegR2, LinRegVariance
- Using the Drilldown (UI) Functions - DrilldownLevel, DrilldownLevelBottom, etc.
- Using the KPI Functions - KPIGoal, KPIStatus, KPITrend, KKPIValue, etc.
- Using the LinkMember FunctionUsing the Generate Function to Create a Set
- Using the Generate Function to Create a StringUsing Recursion in MDX
Advanced Set Topics
- The Autoexists Functionality
- The Existing Keyword
- The Exists Function
- Dynamic and Static Sets
- Referencing the Sets on the Axes
The MDX Scripting
- The MDX Script
- The Calculate Command
- Setting the Default Member
- Creating a Calculation Hierarchy
- Scoping and Assignments
- Debugging Formatting in the MDX Script
- Using IF in MDX Scripting
- Adding Values Not Directly in Source Data
- Removing Innacurate Values
- Replacing IIF with MDX Scripting
- Replacing Unary Operators with MDX Script Assignments
Building a Time Calculation Hierarchy
- Understanding Time Calculation Hierarchy
- The Business Intelligence (BI) Wizard
- Customizing Time Calculation Hierarchies
- Current Period Calculations
- Relative Date Period Calculations
- Period To Date and Rolling Average Calculations
- Comparison, Ratio, and Forecasting Calculations
- Assigning Formatting
- Creating a Second Hierarchy to Choose the Type of Date
MDX Performance Analysis
- Optimization Strategy
- SSAS Cache, Windows Cache, and MDX Script Cache
- Using SQL Server Profiler with SSAS
- Determining Storage Engine Time and Formula Engine Time
- Cache Warming Strategies
Enabling Block Computation
- Understanding Block Computation
- Situations that Prevent Block Computation
- Monitoring for Block Computation
- Rewriting Calculations to Use Block Computation (Removing Named Sets from Aggregation Functions, Replacing Filter with Crossjoin or Exists, Replacing Count(Filter) with Sum(IIF)Removing Late Binding, Replacing User-Defined Functions, Replacing LinkMember)
- When Block Computation is Slower
Other Calculation Performance Strategies
- Simplifying Complex Calculations
- Making Sets as Small as Possible
- Ordering in Set Multiplication
- Using Query-Created Cache
- Fixing Non-Varying Expressions
- Other Best Practices
- IIF Function Hints
- Adding Attributes for Specialized Grouping
- Adding Attributes to Avoid Calculations
- Consolidating Attributes into Same Dimension
- Adding Measures to Avoud Querying the Leaf Level
- Creating Dummy Objects in the Cube
- Replacing Calculations with Cube Features
- Removing the Non_Emptyy_Behavior Property
- Aggregation and Partition Strategy
Using Subselects and Subcubes
- Understanding Subselect and Subcubes
- Subselect and Slicing
- Cache and Performance
- Subselect and Subcube Syntax
- Referencing Members Excluded by Subselect
- Client Applications and Multiple Member Slicing
- Writing Calculations for Subselect