SQL Server Analysis Services            SCL Home
Cube Development Offerings

Offering: Analysis Services Cube Design and Implementation

I have twelve years of experience with Analysis Services, SQL Server, and even more years with software development (nine on the .NET Framework). That combination of deep OLAP/MDX, T-SQL, and C# experience means I'm expertly qualified to implement the most complex Business Intelligence systems on the Microsoft BI platform.


My experience with Analysis Services include:

Offering: Analysis Services Scalability and Performance Review

Building Analysis Services cubes can be deceptively simple. Most cubes can be created with the Cube Wizard quite easily and appear to work very well under development loads. However, as requirements beyond what is configured in the Cube Wizard are incorporated and the cubes are placed under production loads, performance issues are exposed. This offering is intended for those who have already built their cubes, but lack the deep experience with Analysis Services to confidently deploy their cubes.

The nature of Business Intelligence means that Analysis Services cubes must be extensible and scalable. Extensibility is fundamentally important to BI because the real goal is to help information workers make better decisions. That nebulous goal results in the constant need for data from an increasing number of sources. The inability to easily add data sources is a major hinderance towards achieving the maximum value from a BI system.

Extensibility also means problems with scalability. Scalability problems can arise due to the accumulation of more data of the same data as well or the addition of more users. No matter how pressure on a BI system increases it is inevitable, therefore one must ensure the architecture is solid from the very beginning.


Offering: Analysis Services OLAP Training


This five-day workshop is designed to build a solid core understanding of the mechanics of the concepts and components of Analysis Services 2005 (SSAS) which are complex on both counts. BI systems must be flexible and performant, which are usually competing goals. Therefore, optimization of complex cubes can become a web of trade-offs.

Because of this complexity, mastery of SSAS requires months of applicable practice and exploration of the multitudes of configurable object properties. To overcome this complexity and provide profound value to the attendees within the short one-week period, the training focuses on building an understanding of the internals of Analysis Services. How does the query engine work? Why does it do what it does? Such understanding sheds light on the rhyme and reason behind the maze of object properties and configuration settings. Therefore, I like to say that the goal for the workshop is, “After I leave, the SSAS books make sense."

This training is based on training I developed while a Lead Architect at MCS that has been delivered to over 100 MCS consultants and over 300 customer attendees.

Day 1 - Fundamentals of OLAP: SSAS Internals

This day centers around an Instructor-Led Lab building a very simple cube. However, this day is much more than a simple tutorial. The internals are explored for each step from defining data sources through configuring the cube. What happens when we process a dimension or cube? What structures are created and how are they used to facilitate performant queries? Such an understanding enables the engineer to see how object property settings (through the Business Intelligence Development Studio) affect these internal structures.

Day 2 - MDX

MDX (Multi-Dimensional Expressions) is the language of OLAP. Mastery of Analysis Services cannot be achieved without fluency with MDX. Even though front-end clients construct most MDX, that MDX is fairly simple and must often be tweaked (which can result in magnitudes of performance gain). Equally important is the fact that MDX Expressions define calculations, KPIs, actions (such as drillthrough), and advanced security. The day begins with a review of the concepts of multi-dimensional space and taxonomies, which is an OLAP cube. As with how Day 1 focused on internals, MDX is explored “through the eyes" of the Formula Engine, the SSAS component that parses the MDX, constructs an optimal query plan, and retrieves the data. This technique ensures that the developer of MDX builds MDX purposefully as opposed to a manner that I call “MDX alchemy".

Day 3 - Cube Optimization and Deployment

SSAS provides many features for optimization, most importantly aggregation, storage options and partitioning. These topics will comprise about half the day and will again involve exploring the internals of these features. Other topics include implementation techniques such as low-latency design, scale-out, and performance tuning techniques.

Day 4 - Solving Real-World Problems with Advanced MDX

Now that we are familiar with the rhyme and reason behind OLAP cube optimization, we return to the topic of MDX where we cover advanced MDX functions which will be applied in exercises designed to familiarize the attendee with effective methodologies for composing MDX. This includes instructor-led labs; first building the MDX with the instructor, then with the instructor presenting problems for the attendees to solve. The day concludes with an exploration of advanced techniques such as dynamic security and rapidly changing dimensions.

Day 5 - Mentoring

This optional day is open for any purpose. Some suggestions are:


Please email Eugene Asahara at eugene@softcodedlogic.com to discuss these OLAP offerings further.


Lightbulb Articles - Simple Analogies that Seemed to Flip a Switch in my SSAS Workshops: