MDX with Microsoft SQL Server 2016 Analysis Services Cookbook
Over 70 practical recipes to analyze multi-dimensional data in SQL Server 2016 Analysis Services cubes
3. Auflage
Erschienen am 13. Januar 2025
586 Seiten
978-1-78646-788-1 (ISBN)
Beschreibung
No detailed description available for "MDX with Microsoft SQL Server 2016 Analysis Services Cookbook".
Weitere Details
Personen
Li Sherry :
Sherry Li is an Analytic Consultant who works for a major financial organization with responsibilities in implementing data warehousing, Business Intelligence, and business reporting solutions. She specializes in automation and optimization of data gathering, storing, analyzing and providing data access for business to gain data-driven insights. She especially enjoys sharing her experience and knowledge in data ETL process, database design, dimensional modeling, and reporting in T-SQL and MDX. She has co-authored two books, the MDX with SSAS 2012 Cookbook and MDX with Microsoft SQL Server 2016 Analysis Services Cookbook, which have helped many data professionals advanced their MDX skill in a very short time.Piasevoli Tomislav :
Tomislav Piasevoli is a Business Intelligence (BI) specialist with years of experience working with Microsoft SQL Server Analysis Services (SSAS). He successfully implemented many still-in-use BI solutions, helped numerous people on MSDN forum, achieved the highest certification for SQL Server Analysis Services (SSAS Maestro), and shared his expertise in form of MDX cookbooks. Tomislav currently works as a consultant at Piasevoli Analytics company (www.piasevoli.com) together with his brother Hrvoje. They specialize in Microsoft SQL Server Business Intelligence platform, SSAS primarily, and offer their BI skills worldwide. In addition to his regular work, Tomislav manages to find the time to present at local conferences or to write an article or two for local magazines. His contribution to the community has been recognized by Microsoft honoring him with the Most Valuable Professional (MVP) award for six consecutive years (2009-2015).
Sherry Li is an Analytic Consultant who works for a major financial organization with responsibilities in implementing data warehousing, Business Intelligence, and business reporting solutions. She specializes in automation and optimization of data gathering, storing, analyzing and providing data access for business to gain data-driven insights. She especially enjoys sharing her experience and knowledge in data ETL process, database design, dimensional modeling, and reporting in T-SQL and MDX. She has co-authored two books, the MDX with SSAS 2012 Cookbook and MDX with Microsoft SQL Server 2016 Analysis Services Cookbook, which have helped many data professionals advanced their MDX skill in a very short time.Piasevoli Tomislav :
Tomislav Piasevoli is a Business Intelligence (BI) specialist with years of experience working with Microsoft SQL Server Analysis Services (SSAS). He successfully implemented many still-in-use BI solutions, helped numerous people on MSDN forum, achieved the highest certification for SQL Server Analysis Services (SSAS Maestro), and shared his expertise in form of MDX cookbooks. Tomislav currently works as a consultant at Piasevoli Analytics company (www.piasevoli.com) together with his brother Hrvoje. They specialize in Microsoft SQL Server Business Intelligence platform, SSAS primarily, and offer their BI skills worldwide. In addition to his regular work, Tomislav manages to find the time to present at local conferences or to write an article or two for local magazines. His contribution to the community has been recognized by Microsoft honoring him with the Most Valuable Professional (MVP) award for six consecutive years (2009-2015).
Inhalt
- Cover
- Copyright
- Credits
- About the Authors
- About the Reviewer
- www.PacktPub.com
- Table of Contents
- Preface
- Chapter 1: Elementary MDX Techniques
- Introduction
- Putting data on x and y axes
- Getting ready
- How to do it.
- How it works.
- There's more.
- Putting more hierarchies on x and y axes with cross join
- Skipping axes
- Getting ready
- How to do it.
- How it works.
- There's more.
- The idea behind it
- Possible workarounds - dummy column
- Using a WHERE clause to filter the data returned
- Getting ready
- How to do it.
- How it works.
- There's more.
- Optimizing MDX queries using the NonEmpty() function
- Getting ready
- How to do it.
- How it works.
- There's more.
- NonEmpty() versus NON EMPTY
- Common mistakes and useful tips
- Using the Properties() function to retrieve data from attribute relationships
- Getting ready
- How to do it.
- How it works.
- There's more.
- Basic sorting and ranking
- Getting ready
- How to do it.
- How it works.
- There's more.
- Handling division by zero errors
- Getting ready
- How to do it.
- How it works.
- There's more.
- Earlier versions of SSAS
- Setting a default member of a hierarchy in the MDX script
- Getting ready
- How to do it.
- How it works.
- There's more.
- Helpful tips
- Chapter 2: Working with Sets
- Introduction
- Implementing the NOT IN set logic
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Implementing the logical OR on members from different hierarchies
- Getting ready
- How to do it.
- How it works.
- There's more.
- A special case of a non-aggregatable dimension
- A very complex scenario
- See also
- Iterating on a set to reduce it
- Getting ready
- How to do it.
- How it works.
- There's more.
- Hints for query improvements
- See also
- Iterating on a set to create a new one
- Getting ready
- How to do it.
- How it works.
- There's more.
- Did you know?
- See also
- Iterating on a set using recursion
- Getting ready
- How to do it.
- How it works.
- There's more.
- Earlier versions of SSAS
- See also
- Performing complex sorts
- Getting ready
- How to do it.
- How it works.
- There's more.
- Things to be extra careful about
- A costly operation
- See also
- Dissecting and debugging MDX queries
- Getting ready
- How to do it.
- How it works.
- There's more.
- Useful string functions
- See also
- Implementing the logical AND on members from the same hierarchy
- Getting ready
- How to do it.
- How it works.
- There's more.
- Where to put what?
- A very complex scenario
- See also
- Chapter 3: Working with Time
- Introduction
- Calculating the year-to-date (YTD) value
- Getting ready
- How to do it.
- How it works.
- There's more.
- Inception-To-Date calculation
- Using the argument in the YTD() function
- Common problems and how to avoid them
- YTD() and future dates
- See also
- Calculating the year-over-year (YoY) growth (parallel periods)
- Getting ready
- How to do it.
- How it works.
- There's more.
- ParallelPeriod is not a time-aware function
- See also
- Calculating moving averages
- Getting ready
- How to do it.
- How it works.
- There's more.
- Other ways to calculate the moving averages
- Moving averages and the future dates
- Finding the last date with data
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Getting values on the last date with data
- Getting ready
- How to do it.
- How it works.
- There's more.
- Formatting members on the Date dimension properly
- Optimizing time-non-sensitive calculations
- Calculating today's date using the string functions
- Getting ready
- How to do it.
- How it works.
- There's more.
- Relative periods
- Potential problems
- See also
- Calculating today's date using the MemberValue function
- Getting ready
- How to do it.
- How it works.
- There's more.
- Using the ValueColumn property in the Date dimension
- See also
- Calculating today's date using an attribute hierarchy
- Getting ready
- How to do it.
- How it works.
- There's more.
- The Yes member as a default member?
- Other approaches
- See also
- Calculating the difference between two dates
- Getting ready
- How to do it.
- How it works.
- There's more.
- Dates in other scenarios
- The problem of non-consecutive dates
- See also
- Calculating the difference between two times
- Getting ready
- How to do it.
- How it works.
- There's more.
- Formatting the duration
- Examples of formatting the duration on the Web
- Counting working days only
- See also
- Calculating parallel periods for multiple dates in a set
- Getting ready
- How to do it.
- How it works.
- There's more.
- Parameters
- Reporting covered by design
- See also
- Calculating parallel periods for multiple dates in a slicer
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Chapter 4: Concise Reporting
- Introduction
- Isolating the best N members in a set
- Getting ready
- How to do it.
- How it works.
- There's more.
- The top N members is evaluated in All Periods, not in the context of the opposite query axis
- The top N members will be evaluated in the context of the slicer
- Using a tuple in the third argument of the TopCount() function to overwrite the member on the slicer
- Testing the correctness of the result
- Multidimensional sets
- TopPercent() and TopSum() functions
- See also
- Isolating the worst N members in a set
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Identifying the best/worst members for each member of another hierarchy
- Getting ready
- How to do it.
- How it works.
- There's more.
- Support for the relative context and multidimensional sets in SSAS frontends
- See also
- Displaying a few important members, with the others as a single row, and the total at the end
- Getting ready
- How to do it.
- How it works.
- There's more.
- Making the query even more generic
- See also
- Combining two hierarchies into one
- Getting ready
- How to do it.
- How it works.
- There's more.
- Use it, but don't abuse it
- Limitations
- Finding the name of a child with the best/worst value
- Getting ready
- How to do it.
- How it works.
- There's more.
- Variations on a theme
- Displaying more than one member's caption
- See also
- Highlighting siblings with the best/worst values
- Getting ready
- How to do it.
- How it works.
- There's more.
- Troubleshooting
- See also
- Implementing bubble-up exceptions
- Getting ready
- How to do it.
- How it works.
- There's more.
- Practical value of bubble-up exceptions
- Potential problems
- See also
- Chapter 5: Navigation
- Introduction
- Detecting a particular member in a hierarchy
- Getting ready
- How to do it.
- How it works.
- There's more.
- Important remarks
- Comparing members versus comparing values
- Detecting complex combinations of members
- See also
- Detecting the root member
- Getting ready
- How to do it.
- How it works.
- There's more.
- The scope-based solution
- See also
- Detecting members on the same branch
- Getting ready
- How to do it.
- How it works.
- There's more.
- The query-based alternative
- Children() will return empty sets when out of boundaries
- Various options of the Descendants() function
- See also
- Finding related members in the same dimension
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and trick related to the EXISTING keyword
- Filter() versus Exists(), Existing(), and EXISTING
- A friendly warning
- See also
- Finding related members in another dimension
- Getting ready
- How to do it.
- How it works.
- There's more.
- Leaf and non-leaf calculations
- See also
- Calculating various percentages
- Getting ready
- How to do it.
- How it works.
- There's more.
- Use cases
- The alternative syntax for the root member
- The case of the nonexisting [All] level
- The percentage of leaf member values
- See also
- Calculating various averages
- Getting ready
- How to do it.
- How it works.
- There's more.
- Preserving empty rows
- Other specifics of average calculations
- See also
- Calculating various ranks
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tie in ranks
- Preserving empty rows
- Ranks in multidimensional sets
- The pluses and minuses of named sets
- See also
- Chapter 6: MDX for Reporting
- Introduction
- Creating a picklist
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Using a date calendar
- Getting ready
- How to do it.
- How it works.
- There's more.
- Alternative - allowing users to select by Date hierarchies
- See also
- Passing parameters to an MDX query
- Getting ready
- How to do it.
- How it works.
- There's more.
- Getting the summary
- Getting ready
- How to do it.
- How it works.
- There's more.
- Getting visual totals at multiple levels
- Removing empty rows
- Getting ready
- How to do it.
- How it works.
- Checking empty sets
- There's more.
- Trouble with zeros
- See also
- Getting data on the column
- Getting ready
- How to do it.
- How it works.
- There's more.
- Named set or DIMENSION PROPERTIES has no effect in the shape of the reports
- Creating a column alias in MDX queries can mean data duplication
- Creating a column alias is a must with role-playing dimensions
- Avoiding using the NON EMPTY keyword on the COLUMNS axis
- Query Editor in SSRS only allowing measures dimension in the COLUMNS
- A few more words.
- See also
- Sorting data by dimensions
- Getting ready
- How to do it.
- How it works.
- There's more.
- Taking advantage of hierarchical sorting
- Using the Date type to sort in a non-hierarchical way
- "Break hierarchy" - sorting a set in a non-hierarchical way
- Sorting can be done in the frontend reporting tool
- See also
- Chapter 7: Business Analyses
- Introduction
- Forecasting using linear regression
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and tricks
- Where to find more information
- See also
- Forecasting using periodic cycles
- Getting ready
- How to do it.
- How it works.
- There's more.
- Other approaches
- See also
- Allocating non-allocated company expenses to departments
- Getting ready
- How to do it.
- How it works.
- There's more.
- Choosing a proper allocation scheme
- Analyzing the fluctuation of customers
- Getting ready
- How to do it.
- How it works.
- There's more.
- Identifying loyal customers in a period
- More complex scenario
- The alternative approach
- Implementing the ABC analysis
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and tricks
- See also
- Chapter 8: When MDX is Not Enough
- Introduction
- Using a new attribute to separate members on a level
- Getting ready
- How to do it.
- How it works.
- There's more.
- So, where's the MDX?
- Typical scenarios
- Using a distinct count measure to implement histograms over existing hierarchies
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Using a dummy dimension to implement histograms over nonexisting hierarchies
- Getting ready
- How to do it.
- How it works.
- There's more.
- DSV or DW?
- More calculations
- Other examples
- See also
- Creating a physical measure as a placeholder for MDX assignments
- Getting ready
- How to do it.
- How it works.
- There's more.
- Associated measure group
- See also
- Using a new dimension to calculate the most frequent price
- Getting ready
- How to do it.
- How it works.
- There's more.
- Using a utility dimension to implement flexible display units
- Getting ready
- How to do it.
- How it works.
- There's more.
- Set-based approach
- Format string on a filtered set approach
- Using a utility dimension to implement time-based calculations
- Getting ready
- How to do it.
- How it works.
- There's more.
- Interesting details
- Fine-tuning the calculations
- Other approaches
- See also
- Chapter 9: Metadata - Driven Calculations
- Introduction
- Setting up the environment
- Getting ready
- How to do it.
- How it works.
- There's more.
- Additional information
- Tips and tricks
- See also
- Creating a reporting dimension
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Implementing custom rollups using MDX formulas
- Getting ready
- How to do it.
- How it works.
- There's more.
- Why not a built-in feature?
- Why the Sum() function?
- More complex formulas
- See also
- Implementing format string, multiplication factor, and sort order features
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and tricks
- Additional information
- See also
- Implementing unary operators
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Referencing reporting dimension's members in MDX formulas
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Implementing the MDX dictionary
- Getting ready
- How to do it.
- How it works.
- There's more.
- Additional information
- Tips and tricks
- See also
- Implementing metadata-driven KPIs
- Getting ready
- How to do it.
- How it works.
- There's more.
- Additional information
- Tips and tricks
- See also
- Chapter 10: On the Edge
- Introduction
- Clearing the Analysis Services cache
- Getting ready
- How to do it.
- How it works.
- There's more.
- Objects whose cache can be cleared
- Additional information
- Tips and tricks
- See also
- Using Analysis Services stored procedures
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and tricks
- Existing assemblies
- Additional information
- See also
- Executing MDX queries in T-SQL environments
- Getting ready
- How to do it.
- How it works.
- There's more.
- Additional information
- Useful tips
- Accessing Analysis Services 2000 from a 64-bit environment
- Troubleshooting the linked server
- See also
- Using SSAS Dynamic Management Views (DMVs) to fast-document a cube
- Getting ready
- How to do it.
- How it works.
- There's more.
- Tips and tricks
- Warning!
- More information
- See also
- Using SSAS Dynamic Management Views (DMVs) to monitor activity and usage
- Getting ready
- How to do it.
- How it works.
- There's more.
- See also
- Capturing MDX queries generated by SSAS frontends
- Getting ready
- How to do it.
- How it works.
- There's more.
- Alternative solution
- Tips and tricks
- See also
- Performing a custom drillthrough
- Getting ready
- How to do it.
- How it works.
- There's more.
- Allowed functions and potential problems
- More info
- Other examples
- See also
- Index
