MDX Calculations Part 4 – COUNT Aggregation Function

MDX Numeric Aggregation Function COUNT()

There are a number of important functions in MDX that return numeric values. Lets start with the Count() function, which takes a set and returns the number of items in the set. Even if this function looks similar to a SQL Count(), it cannot be used in an axis definition—you can only use the Count() function, and other numeric functions, source inside a calculated member! Read more MDX Calculations Part 4 – COUNT Aggregation Function

MDX Calculations Part 3 – Subcube Vectors, Solve Order, Block vs Cell By Cell Computation

Understanding Block vs Cell By Cell Computation

The key idea behind subspace computation is best introduced by contrasting it with a cell-by-cell evaluation of a calculation. (This is also known as a naïve calculation.) Consider a trivial calculation RollingSum that sums the sales for the previous year and the current year, and a query that requests the RollingSum for 2005 for all Products. Read more MDX Calculations Part 3 – Subcube Vectors, Solve Order, Block vs Cell By Cell Computation

Optimizing Bitmap Indexes during Partition Processing in SSAS Multidimensional

When MDX query is issued on SSAS Server it goes through Formula Engine which request data from Storage Engine, and it either retrieves data either from cache or aggregations or from partition/fact data files (see this article for Understanding MDX Query Execution process in detail). When data is not available in cache and aggregations there is only one way for storage engine to provide data requested by formula engine and that is to scan MOLAP partition data files and partition segments. This is the most expensive way to provide data. To speed up this process SSAS provides http://lillyshummus.com/grab-n-go-hummus-2/classic-hummus-crackers/lillys-classic-grab-n-go-ingredients/ Bitmap Indexes, these are used to eliminate rows from partition files so that very less number of partitions segments are scanned resulting in better performance. Let us see with the help of example how bitmap indexes work. Read more Optimizing Bitmap Indexes during Partition Processing in SSAS Multidimensional

Optimizing (Fact) Partition Processing in SSAS Multidimensional

The performance goal of partition processing is to refresh Cube/OLAP data in an efficient manner that does not negatively impact the query performance of dependent partitions. The following techniques for accomplishing this goal are discussed in this section: Read more Optimizing (Fact) Partition Processing in SSAS Multidimensional

Understanding Partition(Fact) Processing in SSAS Multidimensional

In Analysis Services, when we first  order doxycycline canada DEPLOY a database it contains only objects but not data. When we  PROCESS a database, it directs the server to recursively process those objects that store data in the model, such as dimensions(see Understanding Dimension Processing in SSAS Multidimensional) and partitions. Partition processing refreshes or populates fact data from the source systems and relational data warehouse in the cube and store data into fact data stores, it also creates indexes and aggregates if any defined in the model. The storage engine perform these processes in phases, so we will first try to understand these different phases.  Read more Understanding Partition(Fact) Processing in SSAS Multidimensional

MDX Calculations Part 2 – CurrentMember Function

The CurrentMember function

The CurrentMember function is the most important function in MDX, understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, including time intelligence calculations.
What it does is this? when you call it from inside a calculation, it tells you where the calculation is being executed inside the space of the cube. In other words a calculation is executed inside a cell, and a cell has a tuple that points to it, the CurrentMember function allows you to inspect that tuple, and to find which member from any hierarchy on any dimension in the cube is present in that tuple.
The CurrentMember function can only be used with a hierarchy object, and it returns a member object. Using this function is essential while building calculations which include related metrics, such as sales values from different time periods.
In most real-world calculations, you use the CurrentMember function to construct tuple or set expressions to find values elsewhere in the cube, relative to the cell you are currently in.
Read more MDX Calculations Part 2 – CurrentMember Function

Understanding MDX Calculations Part 1 – Overview

What are Calculations?

Calculations are items in a cube that are evaluated at runtime (at query-time instead of during processing).

There are three types of calculations:

  • Calculated Members / Calculated Measures (Tuple Based Calculations)
  • Named Sets (Set Based Calculations)
  • Script Commands (normally for SCOPE)

Read more Understanding MDX Calculations Part 1 – Overview

MDX Solutions: SORTING using ORDER and TOPCOUNT functions

As with most applications there are many different ways to complete a task and sorting in MDX is no exception. Furthermore, Analysis Services utilization of hierarchies further complicates the method and implementation of sorting routines. SSAS actually has several potential layers of sorting, we can set the sorting at the dimension level or force sorting during query run time. For dimension attributes, we can actually set the Order By property in SSDT to either a dimension attribute Name or a Key as displayed below for the Product attribute under the DimProduct dimension. Additionally, the sort order defined on the dimension attribute property screen can only be the name or the key and is always in ascending order. Read more MDX Solutions: SORTING using ORDER and TOPCOUNT functions

Optimizing Dimension Processing in SSAS Multidimensional

The performance goal of dimension processing is to refresh dimension data in an efficient manner that does not negatively impact the query performance of dependent partitions. The following techniques for accomplishing this goal are discussed in this section:

  • Reducing attribute overhead.
  • Optimizing SQL source queries.

Read more Optimizing Dimension Processing in SSAS Multidimensional

Understanding Dimension Processing in SSAS Multidimensional

In Analysis Services, when we first DEPLOY a database it contains only objects but not data. When we PROCESS a database, it directs the server to recursively process those objects that store data in the model, such as dimensions and partitions. Dimension processing refreshes or populates dimension data in the cube and store data into series of dimension stores and to create these dimension stores, the storage engine uses the series of jobs to extract, index, and persist data in these data stores. So we will first try to understand these jobs, below diagram shows these jobs in the order they are executed.  Read more Understanding Dimension Processing in SSAS Multidimensional