Categories
Uncategorized

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 […]

Categories
Uncategorized

Building an OLAP Farm in the Cloud with Azure Virtual Machines – Part 3

This is the third part in a series of posts on the “OLAP Farm” – which is an Analysis Services architecture that makes use of remote partitions on the backend and linked dimensions and linked measure groups on the front end in order to support solutions where datasets and latency requirements simply can’t be handled […]

Categories
Uncategorized

Building an OLAP Farm in the Cloud with Azure Virtual Machines – Part 2

FYI: the features needed to implement the OLAP Farm architecture (Remote partitions, Remote linked measure groups, Linked dimensions) have been deprecated as of SQL 2014. This is the second part of a series of posts on the “OLAP Farm” – which is an Analysis Services architecture that makes use of remote partitions on the backend and […]

Categories
Uncategorized

Jar-O-Skittles: Benefits of Table Partitioning

Quick note to the regular reader: don’t worry, still planning on finishing the series on Building a Scale-Out OLAP Farm in the Cloud with Azure VMs. But I’m afraid there’s not much time to work on that until we ship some important code (which I’ve been working around the clock on for 2 months). And so […]

Categories
Uncategorized

ADLC: Final Thoughts

This is the 7th, and final, post in a series of posts covering the Aggregation Design Life-Cycle (ADLC). Introduction Create Initial Aggregation Designs Remove Ineffective Aggregations Capture Query Workload Run Usage-Based Optimization (UBO) Wizard Create Custom Aggregations Final Thoughts This post is a catch-all for additional considerations that should be factored in before, during, and […]

Categories
Uncategorized

ADLC Step 5: Create Custom Aggregations

This is the sixth post in a series of posts covering the Aggregation Design Life-Cycle (ADLC). Introduction Create Initial Aggregation Designs Remove Ineffective Aggregations Capture Query Workload Run Usage-Based Optimization (UBO) Wizard Create Custom Aggregations Final Thoughts In the last post, we discussed the Usage-Based Optimization (UBO) Wizard and some of the factors that might […]

Categories
Uncategorized

ADLC Step 4: Run Usage-Based Optimization (UBO) Wizard

This is the fifth post in a series of posts covering the Aggregation Design Life-Cycle (ADLC). Introduction Create Initial Aggregation Designs Remove Ineffective Aggregations Capture Query Workload Run Usage-Based Optimization (UBO) Wizard Create Custom Aggregations Final Thoughts In the previous post, we discussed how to configure the Query Log table and generate a query workload […]

Categories
Uncategorized

ADLC Step 3: Capture Query Workload

This is the fourth post in a series of posts covering the Aggregation Design Life-Cycle (ADLC). Introduction Create Initial Aggregation Designs Remove Ineffective Aggregations Capture Query Workload Run Usage-Based Optimization (UBO) Wizard Create Custom Aggregations Final Thoughts In the previous post, we discussed the characteristics of effective/ineffective aggregations and why it is important to remove […]

Categories
Uncategorized

SQL Profiler EventClass & EventSubClass Column Descriptions for SSAS 2012

Lately, I’ve been spending quite a bit of time troubleshooting some performance issues related to PowerView reports running against a SQL Server Analysis Services 2012 Tabular data source.  One of the first steps in the troubleshooting process involved turning up a SQL Profiler trace on the SSAS Tabular instance and having the users reproduce the […]

Categories
Uncategorized

Real Time OLAP = TRUE

While testing the performance of ROLAP (with a ColumnStore index on the fact table) for another post (I’m still working on), I noticed something that made me stop and think “wtf?” What I noticed was that, while sending an MDX query to the ROLAP cube multiple times, the initial query would show up in both […]