SQL Centre of Excellence

Just finished the Analysis Services Maestro Week in Redmond. Phew quite a hectic week.

So what’s it like ? Well a lot of the content is NDA, but to be fair most of the content seems to also be in three sources: The Ops Whitepaper, The Perf Whitepaper and the Analysis Services Unleashed Book:




The course is created by the SQLCAT team and aimed to produce Customers and Partners with all the skills to design and support huge scale Analysis Services Cubes. What is huge scale, well according to Denny Lee he doesn’t get out of bed for under 4TB, Myself – I’m still in the hundreds of GB for a cube being big!

Does it deliver ? Well its certainly a fantastic course, the highlights are:
- Deep level 400+ content
- Excellent Instructors: Adam Jorgensen, Stacia Misner,  and SQLCAT members like Denny Lee, John Sirmon and Carl Rabeler.
-  Q & A sessions with guys from the Analysis Services dev Team like Akshai
- If you like hands on there is about 20 hours of labs to squeeze into two days – no one finished the labs, so most people will be taking an image home to complete them.
- Some of the group conversations are incredibly insightful, with folk in the class with various stories of managing up to 25Tb cubes.

Well its still a “beta” programme and you can see that in places, but its free so you can’t really grumble. I heard there is a huge waiting list for the programme, so just getting to go is cool.

The instructors only got the decks a week or so before the course so had been madly trying to put their own stamp on it and rationalise the content. You could see that they weren’t 100% happy with a lot of the content.

I guess my only real frustration is I just want to be better at Analysis Services Generally while the SQLCAT team does focus exclusively on massive scale. Most SSAS customers in Ireland have cubes that quite happily fit 100% into memory, so a lot of the topics like I/O and disk subsystems (while one of my favourites) aren’t really applicable to the mid and lower market to the same degree.

The course doesn't have the scope to cover a lot of the more complex aspects of BI projects like MDX, relational strategies for models to support cubes, SSIS, SSRS, Vertipaq, PowerPivot for SharePoint, BISM and other important aspects


So What’s Next after the course?

Well still have to complete the lab – luckily you can take home the Hyper-V image!

Then there is an exam and a 4,000 word case study. if you complete all this you get the “Maestro” Status and maybe eventual grandfathering into the BI MCM when/if it evolves.

I think the most valuable benefit will be a private discussion group with direct contact with peers and maybe sqlcat and product team members. For a BI Architect in a small country like Ireland without a lot of peers to bounce ideas off, this is fantastic.

Another hopeful benefit is when the product team need some SSAS dudes globally they may involve Maestros as a resource to help on the more fun projectsWinking smile


All in all well worth the time and expenses. To be honest, probably also worth 10k+ if it was chargeable, had another weeks worth of content and helped you obtain a BI MCM certification.

A huge thank you to the effort the Instructors and SQLCAT team put it. I’m pretty sure its going to evolve into a full MCM and maybe even there will eventually be a BI MCA or even the cool sounding “BI Ranger” course Winking smile

I’ve been involved with some customers where partitioning is quite complex (40+ databases with multiple partitioning schemes across multiples tables in each database).

One key issue is automation and auditing of the partitioning which if left to a human gets in a right mess. To help this I have a set of stored procedure that we can drop into the master database and return partition meta data for all databases\tables on the instance with a single stored proc call.

The stored proc is handy for getting familiar with the various meta data DMVs used by partitioning:

  • sys.partitions
  • sys.indexes
  • sys.data_spaces
  • sys.partition_schemes
  • sys.destination_data_spaces
  • sys.partition_range_values

An example is below


Your welcome to play with the stored proc code below

USE [master]
/****** Object:  StoredProcedure [dbo].[sp_partition_show]    Script Date: 06/12/2011 17:08:57 ******/

-- =============================================
-- Author:        Bob Duffy
-- Create date: 02/02/2011
-- Description:    Return Meta data on partitioning for databases and/or tables
-- =============================================
ALTER PROCEDURE [dbo].[sp_partition_show]
    @BoundaryValue sql_variant =null
    ,@TableName sysname =null
    ,@DatabaseName sysname ='ALL'    --Default to current. ALL for all databases
    if @DatabaseName is null set @DatabaseName =DB_NAME()
    if @DatabaseName ='ALL' set @DatabaseName =null
    declare @spool table  (db_name sysname, table_name sysname, partition_number int
    , rows int, boundary_value_on_right int, range_value sql_variant, partition_function sysname)
   insert into @spool
   exec sp_msforeachdb  'use [?]; select db_name() as database_name
       ,object_name(p.object_id) as table_name,
       prv.value as range_value,
       pf.name as partition_function 
       sys.partitions p
    inner join
       sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join
       sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join
       sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join
       sys.partition_functions pf on ps.function_id = pf.function_id
    inner join
       sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
    left outer join
       sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
    --where i.index_id =1  
    order by object_name(p.object_id), p.partition_number'
    select * From @spool 
    where (db_name=@DatabaseName or @DatabaseName is null)
    and (table_name =@TableName or @TableName is null)
    and partition_function not like '%client%'
    and (range_value =CONVERT(datetime,@BoundaryValue) or @BoundaryValue is null)

One of the most common things to need an IF statement for in MDX is to determine if a measure is NULL to ensure that you output Null instead of the actual calculation.

Mosha pioneered an interesting “trick” for avoiding the use of “IF” to account for Null handling in Budget Variance (See http://sqlblog.com/blogs/mosha/archive/2006/11/05/budget-variance-a-study-of-mdx-optimizations-evaluation-modes-and-non-empty-behavior.aspx)

Basically we can abuse the fact that in MDX Null multiplied by any other number is null.

Having used Mosha’s technique for Budget Variance, I realised that this can pretty much be used anywhere, so below I have a calculated member for YTD

SCOPE ([Reporting].[Period].[YTD]);
    this =  iif([Reporting].[Period].[This Period]=null,null,
Aggregate(  [Reporting].[Period].[This Period]  
             PeriodsToDate( [Date].[Calendar].[Year],

We can re-write this without the IF  as below and enjoy  faster performance:

SCOPE ([Reporting].[Period].[YTD]);
    this =  [Reporting].[Period].[This Period] *
Aggregate(  [Reporting].[Period].[This Period]  
             PeriodsToDate( [Date].[Calendar].[Year],
             )) / [Reporting].[Period].[This Period];  

Page List

Page List