…brew your own Business Intelligence

Posts Tagged ‘MDX’

Engaging the Formula Engine Cache with SSAS Tabular

One of the more subtle architectural differences between tabular and multidimensional instances is caching. Multidimensional is a disk-based technology and performance for query workloads (in many cases) depends heavily on cache utilization. Tabular, on the other hand, is a memory-based technology with (nearly) zero dependence on disk IO and is therefore much less […]

Read more

Implementing Logical AND on Members From Same Hierarchy

The default behavior when filtering on multiple members from the same attribute hierarchy is a logical OR. In the pivot table above, we have the [Internet Customer Count] measure (which is a distinct count measure) sliced by Product Subcategory and filtered for [Mountain Bikes] and [Tires and Tubes]. This shows […]

Read more

MDX Script: Calculated Members (vs Named Sets)

In the previous blog post I discussed the benefits of defining MDX calculations in the cube (instead of in the query using the WITH-clause). The primary benefit was having named sets computed ahead of time (when the MDX Script is executed) and available via the global cache which greatly improves […]

Read more

MDX Performance Tip: Move Calculations From the Query to the MDXScript

In the world of business intelligence, most people prefer to embed as much business logic as possible into the ETL and materialize it in the data model (i.e. data mart, cube, tabular model, etc). After all, if we can take the hit during our batch processing window – then that’s […]

Read more

MDX: MemberValue vs Member_Value

Lately I’ve been doing a bit of MDX performance tuning and during the process I learned a new “trick” that blew my mind. Instead of just outright telling you what it is, I’d like to show you through an example. Original Query Take the following query shown below – which […]

Read more

OLAP Query Log and Subcube Vectors

The OLAP Query Log is a table that collects details of queries executed against the SSAS database. To be more precise, the records in this table represent the details of storage engine requests fired off by the Analysis Services engine during query execution – the often missed implication being that […]

Read more

MDX: Scope and Precedence for Calculated Members and Sets

With calculated members and calculated sets, there are 3 primary levels of scope: Global: calculated members and sets defined at this level (in the MDXScript of the SSAS cube) are available to all queries Session: calculated members and sets defined at this level (as a standalone statement executed after connecting […]

Read more

A Closer Look at the Coverage Fact Table

One of the more interesting types of fact tables is the factless fact table for conditions – also known as Coverage Fact table by Kimball purists. I was recently reminded of this type of fact table while reading Star Schema: A Complete Reference in which Chris Adamson (b | t) describes […]

Read more

MDX: ParallelPeriod Is Not Calendar Aware (Part 2)

In the last post, we explored the ParallelPeriod function in a bit of detail and then used a contrived example to highlight a particular edge case that needs to be explicitly handled when the number of children differs between the 2 periods. Specifically, we looked at a prior month calculation […]

Read more

MDX: ParallelPeriod Is Not Calendar Aware

ParallelPeriod is one of the more commonly used MDX functions out there. It’s primary use is in “prior period” calculations such as Prior Year sales (which in turn can be used to calculate Year-over-Year Change in sales) as well as Rolling Averages: Create Member CurrentCube.[Measures].[Internet Sales Amount – Prior Year] […]

Read more