Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

SQL Server Best Practices Analyzer CTP

with 3 comments

I’ve just downloaded the CTP of the SQL Server Best Practices Analyzer (see Paul Mestemaker’s post here http://blogs.msdn.com/sqlrem/archive/2007/02/21/SQL-2005-BPA-Feb-CTP-released.aspx for more details) and, shock horror, it analyses Analysis Services databases and Integration Services packages too! The advice it gives is quite sensible – things like ‘set up attribute relationships’ and ‘put distinct count measures in their own measure group’ – so it’s definitely worth checking out.

You can download it here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Written by Chris Webb

February 23, 2007 at 2:29 pm

Posted in Analysis Services

3 Responses

Subscribe to comments with RSS.

  1. Hi, I would like to know if it is possible to create a MDX query with a T-SQL like behaviourfor example:SELECTCOL1, COL2FROM TABLEXWHERE COL1 LIKE \’%PARAMETER%\’SELECT[DIM A].[MEMBER].members on 1,[measures].MEASUREX on 0,FROM[CUBE X]WHERE? (LIKE BEHAVIOUR)

    Juan Antonio

    February 27, 2007 at 11:02 pm

  2. Hi, I would like to know if it is possible to create a MDX query with a T-SQL like behaviourfor example:SELECTCOL1, COL2FROM TABLEXWHERE COL1 LIKE \’%PARAMETER%\’SELECT[DIM A].[MEMBER].members on 1,[measures].MEASUREX on 0,FROM[CUBE X]WHERE? (LIKE BEHAVIOUR)

    Juan Antonio

    February 27, 2007 at 11:02 pm

  3. Yes, although you\’re making the common mistake of thinking that the MDX Where clause does the same thing as the SQL Where clause, and MDX doesn\’t have a built-in LIKE operator unfortunately. You need to apply an MDX function to the set you\’re displaying on the Rows axis which does the filtering you want. For example, here\’s a query in Adventure Works which returns all products whose names begin with the letter B:
     
    select [Measures].[Internet Sales Amount] on 0,filter([Product].[Product].[Product].members, left([Product].[Product].currentmember.name, 1)="B")on 1from [Adventure Works]
    You might also want to check out the functions implemented in the Analysis Services Stored Procedure project which offer you more useful string filtering functionality:

    http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters&referringTitle=Home

    Chris

    February 28, 2007 at 10:00 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: