Tag Archives: MDX

Architektúra vykonania dotazu — Výpočet výrazov MDX

510cHjGCsUL. SL160 Architecture of Query Execution Calculating MDX ExpressionsMicrosoft SQL Server 2008 Analýza služby rozpútala Ďalšiu kapitolu 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.” (stránka 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, JA., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analýza služby rozpútala. Indianapolis, V: Pearson vzdelanie, Inc.
ISBN: 0-672-33001-6

Extending MDX with Stored Procedures

510cHjGCsUL. SL160 Extending MDX with Stored ProceduresMicrosoft SQL Server 2008 Analýza služby rozpútala Kniha recenziu kapitola 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 (stránka 245) as an extra security precaution.

Pokračovať v čítaní “Extending MDX with Stored Procedures” »

Cube-Based MDX Calculations

510cHjGCsUL. SL160 Cube Based MDX CalculationsMicrosoft SQL Server 2008 Analýza služby rozpútala Kniha recenziu kapitola 12

For this chapter I will summarize the key points:

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

Pokračovať v čítaní “Cube-Based MDX Calculations” »

Advanced MDX (for Analysis Services)

510cHjGCsUL. SL160 Advanced MDX (for Analysis Services)Microsoft SQL Server 2008 Analýza služby rozpútala Kniha recenziu kapitola 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.

Pokračovať v čítaní “Advanced MDX (for Analysis Services)” »

MDX pojmy

510cHjGCsUL. SL160 MDX ConceptsMicrosoft SQL Server 2008 Analýza služby rozpútala Kniha recenziu kapitola 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) a 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.

Pokračovať v čítaní “MDX pojmy” »