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
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)
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)
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