Tag Archive For "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 dependent on cache for query …
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 that 4,089 customers bought at …
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 query response time. The first …
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 time we just saved our …
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 admittedly is a bit contrived …
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 a single MDX query might …
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 the cube and establishing a …
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 the factless fact table for …
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 and showed that calculating the …
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] As ( ParallelPeriod( [Date].[Calendar].[Calendar Year] …