Tag Archives: MDX

Free eBooks from Microsoft

Microsoft Program Manager Eric Ligman has been posting lists of free eBooks from his blog.  I have not mentioned these books in past blog postings.  So, this posting is a catch-up for the three lists he has already mentioned and subsequently summarized.  I will be commenting on free books which help for Microsoft data analytics and data science.
Continue reading “Free eBooks from Microsoft” »

Architecture of Query Execution — Calculating MDX Expressions

Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 29

This chapter starts with a claim which should make everyone happy: “Optimization of MDX calculations was one of the most important goals of Analysis Services 2008.” (page 527) This focus on MDX performance leads into a discussion of the following topics:

  • Query execution stages
  • Parsing an MDX request
  • Creation of calculation scopes
  • Building a virtual set operation tree
  • Optimizing multidimensional space by removing empty tuples
  • Calculating cell values
  • Cache subsystem

The book mentions a user guide created specifically for SSAS 2008: Performance Improvements for MDX in SQL Server 2008 Analysis Services. I like the fact that they took the essence of their key MDX advice and put it into one free page. This online resource mentions that “increased performance” has been achieved in a number of areas, and that claim is consistent with the essence of chapter 29. Included on this page are the VBA functions ported to SSAS, and even those VBA functions have improved performance. They recommend manually auditing MDX code for maximum performance, and they provide tips and suggestions of what approaches have a performance hit.

Another webpage provides tips on a complementary topic: Querying Multidimensional Data (Analysis Services – Multidimensional Data. I will also add links to the MDX reference:

Queries can be monitored using SQL Server Profiler.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6

Extending MDX with Stored Procedures

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 14

This chapter shows how to extend the native commands within Analysis Services using either managed code assemblies or COM assemblies. I will assume knowledge of assembly creation with either COM or .NET languages, since that assumption follows how this chapter is presented. I realize that this assumption will leave out some people from understanding this chapter.

As has been true for years, COM assemblies are (as a rule) less secure than managed assemblies, and therefore the wisdom is to rewrite any COM assemblies in .NET. I concede that there are still COM developers who can write effective code, but going forward, I recommend using one of the many .NET languages to write any code. COM support is turned off by default (page 245) as an extra security precaution.

Continue reading “Extending MDX with Stored Procedures” »

Cube-Based MDX Calculations

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 12

For this chapter I will summarize the key points:

  • Use MDX scripts to create permanent calculated members, named sets, and assignments (page 191)
  • Session-scope calculated members, named sets, and assignments are possible (pages 193, 201, 209)
  • NON_EMPTY_BEHAVIOR points the optimizer where to look to determine if a calculation will be null (page 197)
  • A subcube is different from a sub_cube_expression, which defines the subspace for assignments (page 199)
  • Scopes and nested scopes provide a way to encapsulate subcubes (pages 203-206)

Continue reading “Cube-Based MDX Calculations” »

Advanced MDX (for Analysis Services)

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 11

The chapter title is only “Advanced MDX” but this chapter focuses on MDX for Analysis Services.  Other vendors have implemented MDX. This entire book part (III) shows how MDX works specifically with Analysis Services 2008.

Continue reading “Advanced MDX (for Analysis Services)” »

MDX Concepts

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 10

We are now entering Part III of the book, using MDX to analyze data. MDX was a Microsoft invention, thus making me conclude that Microsoft wants to be a laguage inventor. This post may be longer than others in the series because my goal is to make MDX accessible for data mining. I believe people can and could create MDX if they wanted to, though I will focus instead on key points of how MDX works and how you could automatically get MDX code from drag-and-drop interfaces. I suspect the vast majority of people will not be writing MDX since the point of SSAS is to be user-friendly, and the point of analysis is making decisions.

Some consider MDX a mix of the SQL (for Microsoft, T-SQL) and Excel, so in this blog post I will be talking about Excel 2010 and SQL Server Management Studio (SSMS) 2008 R2. I will also refer to BIDS (an extension of Visual Studio 2008) and mention some tools which I recommend that you acquire.

Continue reading “MDX Concepts” »