Chris Webb's BI Blog

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

Archive for February 2005

New formatting functionality in Yukon

with one comment

Anyone who has looked at the AdventureWorks sample cube will probably have picked up by now that all the intrinsic member properties like FORMAT_STRING can now be defined with an MDX expression (in AS2K it was only FORE_COLOR and BACK_COLOR that you could do this with), but something else caught my eye today when I was looking at the tutorials in BOL: there are new MDX statements which allow you to set these properties on any area within a cube from a script. So you can now do things like:
BACK_COLOR(THIS)=RGB(255,100,100)
…to give everything in the current subcube a red background, or this:
FORMAT_STRING(THIS)="\H\e\l\l\o"
…to change the format of everything in the current subcube. I would imagine that FORE_COLOR, FONT_SIZE, FONT_FLAGS and FONT_NAME also exist although I’ve not had a chance to see whether they do indeed work. This makes it much easier to do funky traffic-light analysis-type stuff compared to having to set the property on a per-member basis.

 

Written by Chris Webb

February 14, 2005 at 3:20 pm

Posted in MDX

BI Best Practices Blog

with one comment

Although this has been around for a while, I thought I’d write a quick post about the BI Best Practices Blog in case some of you haven’t seen it. Unfortunately it doesn’t seem to have been updated since last summer but there are plenty of interesting articles on there (including a few that I wrote when I was at Microsoft – look in the MDX category).

Written by Chris Webb

February 14, 2005 at 11:43 am

Posted in Analysis Services

AS2K SP4

with one comment

SQL Server SP4 is in beta (you can sign up here), and I’ve been looking at the long list of fixes that have gone into Analysis Services here. One thing that really leaps out at me are the number of fixes for dynamic security, one of my pet subjects – if you’ve currently implemented it, you really should take a look. The other thing of interest is the first official documentation of two connection string properties that I’ve found very useful for query performance tuning: Cache Ratio2 and Cache Policy. The writeup of Cache Ratio2 is fairly detailed and gives a good insight into the workings of the engine (when is someone going to write an ‘Inside Analysis Services’ book?); the writeup for Cache Policy doesn’t really explain what it does, unfortunately, and just discusses a fixed bug. I have a draft of a so-far unreleased whitepaper which goes into much more detail about Cache Policy, and which in my opinion ought to get an official release.

Written by Chris Webb

February 9, 2005 at 1:03 pm

Posted in Analysis Services

Bucketisation

with 3 comments

I couldn’t think of a better title for this entry, but I’m sure there must be a better term for what I’m writing about… Anyway, the original solution I posted for the ‘get the names of the currentmembers on all dimensions’ problem last week reminded me of some MDX I came up with last year, for dynamic bucketisation. By this I mean the ability to run queries in MDX whereby you can count (or sum, or whatever) the members that fall within dynamically defined ranges. Imagine that you’re the manager of the Foodmart 2000 stores and you want to see a histogram showing the number of product categories that had sales of between $0 and $500, $500 and $1000, $1000 and $1500 and so on (each of these ranges being the ‘buckets’ of the title). Now, in AS2K, the standard approach would be to create these calculated members manually either on the cube or in the WITH clause, which is pretty time-consuming if you have lots of buckets. But what if you want a report with this histogram on where you can dynamically define the number of buckets, and the range that each bucket contained? That makes things even more difficult!

Here’s my solution in a Foodmart 2000 query:

WITH
/*FIRST DEFINE THE SIZE OF EACH BUCKET*/
MEMBER MEASURES.SIZE AS ‘500’
/*THEN, CHOOSE A DIMENSION LEVEL WHICH YOU AREN’T USING IN YOUR QUERIES AND WHICH HAS MORE
MEMBERS THAN THE NUMBER OF BUCKETS YOU WANT. THEN DEFINE A SET WITH THE SAME NUMBER
OF MEMBERS IN AS THE NUMBER OF BUCKETS DESIRED*/
SET BUCKETHACK AS ‘HEAD([Promotions].[Promotion Name].MEMBERS, 10)’
/*NEXT, CREATE A CALCULATED MEMBER THAT FINDS THE RANK OF THE CURRENTMEMBER WITHIN THE SET JUST CREATED*/
MEMBER MEASURES.RANKING AS ‘RANK([Promotions].CURRENTMEMBER, BUCKETHACK) – 1′
/*NOW, USE THE CREATEPROPERTYSET FUNCTION TO CREATE A SET OF CALCULATED MEMBERS WHICH REPRESENT
THE BUCKETS. THE NAME OF EACH CALCULATED MEMBER IS DERIVED FROM THE RANK OF EACH MEMBER OF THE BUCKETHACK SET
I AM USING THE CUSTOMERS DIMENSION HERE, BUT YOU SHOULD USE A NEW DIMENSION CREATED SPECIFICALLY FOR THIS PURPOSE
WITH ONLY AN ALL MEMBER*/
SET MYBUCKETS AS ‘CREATEPROPERTYSET([Customers].[All Customers], BUCKETHACK,
"$" + CSTR(MEASURES.RANKING * MEASURES.SIZE) + " – $" + CSTR((MEASURES.RANKING+1) * MEASURES.SIZE))’
/*THEN CREATE A NEW CALCULATED MEASURE WHICH FINDS THE RANK OF THE CURRENT BUCKET WITHIN THE LIST OF ALL BUCKETS
TO MAKE THE MDX EASIER TO READ*/
MEMBER MEASURES.RANKING2 AS ‘RANK(CUSTOMERS.CURRENTMEMBER, MYBUCKETS)-1′
/*AND FINALLY CREATE A NEW CALCULCATED MEASURE WHICH COUNTS THE NUMBER OF PRODUCT CATEGORIES IN EACH BUCKET*/
MEMBER MEASURES.[PROD CAT COUNT] AS ‘COUNT(
FILTER([Product].[Product Category].MEMBERS,
(MEASURES.[UNIT SALES], [Customers].[All Customers])>=(MEASURES.RANKING2*MEASURES.SIZE)
AND
(MEASURES.[UNIT SALES], [Customers].[All Customers])<((MEASURES.RANKING2+1)*MEASURES.SIZE)
))’
/*JUST TO SHOW WHICH MEMBERS FALL INTO WHICH BUCKETS*/
MEMBER MEASURES.[BUCKET MEMBERS] AS ‘GENERATE(
FILTER([Product].[Product Category].MEMBERS,
(MEASURES.[UNIT SALES], [Customers].[All Customers])>=(MEASURES.RANKING2*MEASURES.SIZE)
AND
(MEASURES.[UNIT SALES], [Customers].[All Customers])<((MEASURES.RANKING2+1)*MEASURES.SIZE)
), PRODUCT.CURRENTMEMBER.NAME, ", ")’
/*THE DEMO QUERY*/
SELECT {MEASURES.RANKING2, MEASURES.[PROD CAT COUNT], MEASURES.[BUCKET MEMBERS]} ON 0,
MYBUCKETS ON 1
FROM SALES

You could adapt this MDX for a various other similar types of analysis – Pareto curves come to mind – and I guess it would come in handy for situations where generating the query itself dynamically is either difficult or impossible, such as in a Reporting Services report.

I admit that it’s not elegant, but I thought it was interesting nonetheless and worth sharing. It manages the problem of the dynamic number of buckets by the same method as the MDX last week – simulating a for/next loop by creating a set with a given number of members and then using GENERATE to iterate over it. Of course, this suffers from the need to have a level with at least as many members on as you need iterations. It then manages the problem of creating calculated members for each of the buckets dynamically by using the undocumented CREATEPROPERTYSET MDX function. Hopefully there will be much better solutions to both of these problems in Yukon – I assume a stored procedure would do the job, but I’m not sure.

Written by Chris Webb

February 4, 2005 at 12:01 pm

Posted in MDX

Follow

Get every new post delivered to your Inbox.

Join 3,296 other followers