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 … Continue reading Engaging the Formula Engine Cache with SSAS Tabular
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) … Continue reading Implementing Logical AND on Members From Same Hierarchy
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 … Continue reading MDX Script: Calculated Members (vs Named Sets)
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 … Continue reading MDX Performance Tip: Move Calculations From the Query to the MDXScript
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 … Continue reading MDX: MemberValue vs Member_Value
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 … Continue reading OLAP Query Log and Subcube Vectors
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: … Continue reading MDX: Scope and Precedence for Calculated Members and Sets
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 … Continue reading A Closer Look at the Coverage Fact Table
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 … Continue reading MDX: ParallelPeriod Is Not Calendar Aware (Part 2)
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 … Continue reading MDX: ParallelPeriod Is Not Calendar Aware