Dimension-based MDX Calculations

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 13

In the previous chapter, the book outlines a highest pass wins rule for determining the order of cube calculations. This chapter introduces dimension-based calculations, which will override a cube calculation. The chapter does not start with this argument (it’s on page 233) but I believe this fact is a good segue in the flow of the book.

The normative (default) role of aggregations is summation. Analysis Services has a series of unary operators, mathematical symbols or measure names which indicate how to perform rollup calculations. The default unary symbol is the addition sign + but other symbols are possible.

Continue reading “Dimension-based MDX Calculations” »

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)” »

Data Mining Concepts and DMX

Data Mining with Microsoft SQL Server 2008 Book Review Chapter 3

DMX stands for Data Mining Extensions, though originally was called OLE DB for Data Mining, a name from the pre-.NET days. The book recalls how people on the data mining product team used “guerilla tactics” to encourage Microsoft’s official SQL marketing department to use their preferred acronym. I guess marketing had to have something to do. I’m wondering if someone has a study proving whether DMX is a better marketing phrase than OLE DB for Data Mining. I know for sure I can track how many people will read this article based on my decision to review this book and its technology.

The chapter starts with the premise concluding that “the field is relatively immature” meaning that “there are no standard concepts of mining models, training or predictions”. I have believed that there is a difference between the research-based approach that I term machine learning, and the applied science, I call data mining (I would even use the phrase data mining engineering to be consistent with other engineering disciplines). When the authors talk about the immaturity, they refer to the potential capacity of business intelligence professionals and software architects and database developers to integrate this technology across industries and even across software vendors. By contrast, I have deep respect for the decades of proven mathematical research behind the machine learning algorithms, some of which have proven to be time-tested production-level essential elements of some of the world’s most sophisticated systems.

Continue reading “Data Mining Concepts and DMX” »

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” »

Multidimensional Models and Business Intelligence Development Studio

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 9

For people who like step-by-step, how-to instruction, this chapter is for you. As I have said earlier, I am comfortable with the book’s approach in introducing the theory and vocabulary first, and doing the exercises second. And, this chapter comes just after my advice in the chapter eight review to download and obtain the sample code for the book.

For this review I will make some comments on the standard project editing process. First, I will mention two free products which will help you develop for Analysis Services.

Continue reading “Multidimensional Models and Business Intelligence Development Studio” »

Advanced Modeling

Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 8

This chapter contains some advanced modeling combinations. The advantage of these features might make life easier for an analyst. The disadvantage is either increasing the design complexity or decreasing performance, or both. I like the wisdom that performance is the currency in systems design which can subsequently be traded off for features. This chapter contains examples on how to spend performance currency to achieve hopefully more user-friendly interfaces. Because of this tradeoff, it’s important to make sure that these features are tied to outputs that people want and are using.

Continue reading “Advanced Modeling” »