Scaling Out with Analysis Services

Scaling Out with Analysis Services     A few months ago, I had the opportunity to work with a customer who had been intermittently encountering a variety of errors when attempting to connect to an Analysis Services server. The production environment consisted of a dedicated processing server and a dedicated query server. Both of the…

5

Notes on Deciding whether to implement a Tabular or Multidimensional Model

Within the Business Intelligence community, when the discussion comes to Microsoft SQL Server Analysis Services, there are a coupleof questions that typically arise. One of those questions is whether it’s possible to easily (read that as programmatically) convert from one model toanother. Typically, the other is whether to implement a solution that uses a Multidimensional…

0

Microsoft Analysis Services using ROLAP storage in Teradata.

I recently had the opportunity to work with a customer who was interested in implementing a Microsoft SQL Server 2012 Analysis Services database, using ROLAP storage, with Teradata as the relational data source. With a week to build a Proof of Concept (POC) solution using Microsoft SQL Server Analysis Services database and having had previous…

0

Using DMVs and XMLA commands to force a disconnect from an Analysis Server

At one time or other, just about every DBA charged with administering a Microsoft SQL Server Analysis Services server has encountered a situation that necessitated forcing a disconnect from either a Multidimensional or Tabular instance of Analysis Services. In the relational world of SQL Server, that’s relatively easily accomplished by using SP_WHO, to determine the…

1

Using SQL Server to Analyze Analysis Services Profiler Traces from Tabular Models.

My last blog post was focused on using SQL Server to analyze profiler traces against Multidimensional Analysis Services databases, so I though it only appropriate to follow-up with a post on using SQL Server to analyze profiler traces from a Tabular Analysis Services database. In July of 2013,  John Sirmon, Greg Galloway (Artis Consulting), Cindy Gross…

0

Processing Analysis Services. Notes on the Care and Feeding of an OLAP Database

Anyone who has had the opportunity to work with Microsoft Analysis Services, in multidimensional mode, knows that processing is a series of steps that are used to load data from a relational data source into a model. By default, processing occurs when a solution is deployed or re-deployed to the server from BI Development Studio or SQL…

0

Using SQL Server to Analyze Analysis Services Profiler Traces

In 2007, Carl Rabeler and Eric Jacobsen wrote a fairly detailed white paper entitled Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services (http://www.microsoft.com/en-us/download/details.aspx?id=661). In that white paper, they describe a method of determining if the bottleneck is in Storage Engine or Formula Engine that involves adding the elapsed time for…

0

Using SSIS to Backup and Restore Extremely Large OLAP Databases

Working in the field of Business Intelligence I get the opportunity to work with some really large (read that as multi-terabyte) OLAP databases. Multi-terabyte OLAP databases, while not yet common place, are being seen with greater frequency and they do present a few interesting challenges to developers and administrators. Performance tuning is one of the…

0

Using DMVs to Monitor and Terminate Long Running MDX Queries

At one time or other, just about every DBA charged with administering a Microsoft SQL Server Analysis Services server has encountered a long running query that seems to consume machine resources with a voracious appetite. Those queries are typically executed by “Power Users” and almost invariably at a time that other users are attempting to…

0

Analysis Services Proactive Caching Operations and Errors

One of the more interesting aspects of being involved in supporting Business Intelligence applications is the opportunity to investigate behaviors that are either unexpected or perhaps not so intuitively understood. Proactive Caching is one aspect of Analysis Services that is not well understood by some and can present some interesting issues, especially when errors related to data quality occur….

1