AS2005 Dimension Security paper

There’s a new paper on how dimension security is implemented in AS2005 available on Richard Tkachuk’s site. Not an awful lot seems to have changed although the attribute-based dimensional model has (as you would expect) made things a bit more complex; and you can now define security on a dimenion and have this applied to all cubes that dimension is linked to, rather than have to put the same security definition on each cube.

AS2000 to AS2005 Migration White Paper

Deepak on the newsgroup pointed out the existence of the following white paper on migration from AS2000 to AS2005 by Richard Tkachuk:
(Incidentally not the same as the one that Mosha refers to here). Migration is going to be a tricky issue for a lot of people I reckon: while I’ve been impressed by how good a job the migration wizard does, given the fundamental changes that have taken place between AS2000 and AS2005 any moderately complex AS2000 cube will need a few manual tweaks to make it work properly in AS2005 – a lot of BI consultancies will be rubbing their hands with glee at the thought of this extra revenue, I’m sure. And of course even if you have migrated your cube successfully you might need to think about redesigning it, or rewriting your MDX, to take advantage of all the new features and/or performance that AS2005 offers.
 
All this makes me wonder about how quickly people will migrate their cubes. I had an interesting conversation with Nigel Pendse (of OLAP Report fame) a few months ago and his opinion was that the process of migration will be much slower than expected; I think I agree with him. If an AS2000 cube works fine at the moment and migration involves spending money on internal development or outside consulting, then the cube will probably be left as it is – if it ain’t broke, don’t fix it! After all, support for HOLOS (an older OLAP engine) stopped a few years ago but I know that there are still some HOLOS systems still in production that people are only now thinking of replacing.
 

MemberValue

It’s my 30th birthday today so before I disappear down the pub to buy everyone else a pint (which is the bizarre tradition at my workplace) I thought I’d mark the day with a quick blog entry…
 
The MemberValue property is something I saw in BOL quite a while ago, but didn’t really understand what it was for. It wasn’t until George, Siva et al started discussing it for ‘MDX Solutions’ that I really grasped why it is so useful: it’s for those occasions when you want to tie a value other than a caption to a member, and don’t want to go to the hassle of creating a separate attribute for that value. So, for example, imagine all of your Products have a weight attribute (eg 10Kg, 15Kg, 80Kg) and you want to run queries like a) Show me Sales for products which weigh 10Kg, and b) Show me sales broken down by weight for products which weigh more than 30Kg. In AS2K you might have ended up doing this by having two separate member properties for weight: one which contained a user-friendly string, such as "10Kg", which you might create a virtual dimension from or display in a query and a second, containing just the numeric value such as "10", to make filtering easier (although you’d still have had to cast it to an integer before you could do this). In AS2005 all you need to do is tie the user-friendly description to the NameColumn property and the filter-friendly value to the ValueColumn property (which, incidentally, is typed).The MemberValue property of a member then allows you to get this value back in MDX. Here’s an example from AdventureWorks, showing how the calendar year member has a name like "CY2001" but you can still get at the year as a smallint without the "CY" bit in front:

WITH MEMBER MEASURES.DEMO AS [Date].[Calendar].CURRENTMEMBER.MEMBERVALUE

SELECT

{[Measures].[Internet Sales Amount], MEASURES.DEMO} ON 0,

FILTER

(

[Date].[Calendar].[Calendar Year].

MEMBERS

, [Date].[Calendar].

CURRENTMEMBER.MEMBERVALUE>2001.5)

ON

1

FROM

[Adventure Works]

 
Take a look at Period in the Dimension Editor in Visual Studio to see exactly how it all works. If you don’t define anything in the ValueColumn, MemberValue returns either the key if there is no name binding or if the key and the caption are bound to the same column, or failing that the caption of the member.

 

SAP BW and Analysis Services

A webcast from Microsoft on how they do their internal reporting using SAP BW and Analysis Services. Not seen it all the way through yet but I’ve seen other presentations by Bill Faison and he’s had a lot of experience in this area.
 

Partitioning in AS2K and AS2005

Great post in the microsoft.public.sqlserver.olap newsgroup from Dave Wickert which sums up how partitioning works in AS2K and how it’s even better in AS2005:
This new automatic setting of the data slice for partitions and improved flexibility of the slicing is really cool.
 

Changes to certification for SQL Server

At last, it looks like SQL Server certification is getting an overhaul:
…and as a result I should be able to get a Business Intelligence-flavoured certification from Microsoft for the first time.

Time Ranges in AS2005

I had a mail at the beginning of last week from Mike Melnikov, who had been reading my blog entry on aggregating across a time range in AS2K. He told me he had a similar requirement which he’d implemented using a UDF in AS2K but was wondering if there was an easier way of doing what he wanted in AS2005. Basically he needs two Period dimensions in his cube, from which the user can select individual members or ranges, allowing the user to compare values for a measure across these two different Period selections. For example the user might want to compare Sales in January 1997 with Sales in February and March 1997 combined.
 
My original AS2K solution relied on having separate dimensions to specify the start and end dates of the time ranges, but for Mike’s problem this would mean four separate dimensions – rather a lot. However, OWC and Excel have always allowed multi-select on dimensions and I got the impression Mike was happy with using them, so this meant I could use just two dimensions; also, in AS2005 the ability to put sets in the WHERE clause makes selecting time ranges much easier and I wanted a solution that would work with this feature too.
 
The first step was to have two identical Period dimensions in the same cube, and this shows off one of the cool new features of AS2005 – role-playing dimensions. In AS2K in order to do this you needed two physical copies of the dimension in your cube, which is something of a waste of space and processing effort; in AS2005 you can have the same physical dimension appearing many different times in the same cube with different names. So in my cube, once my Period dimension had been added once I could simply add it again with the name [Compare Period].
 
Then was the question of what relationship to have between this second Period dimension and the cube. In the AS2K solution I went to a lot of effort to have a dimension which had no relationship with the cube, and of course AS2005 allows you to do this out of the box, but I decided not to do that and instead make it a ‘regular’ relationship and use MDX Scripting to do the work instead. If I’d have gone down the ‘no relationship’ approach I wouldn’t have been able to use sets in the WHERE clause (no way to use LINKMEMBER in this case, I don’t think) and I didn’t think I would have been able to get a two-dimension approach to work with OWC.
 
So my first thought on the MDX Script was that it should take the following approach:
  • Create a calculated measure called [Measures].[Sales Compare Period] to show the value of my original measure [Measures].[Sales], but for the second Period dimension and ignoring the first one
  • Populate the values of this new calculated measure by using an assignment like ([Measures].[Sales Compare Period]) = ([Measures].[Sales], ROOT([Period]))
  • Make sure the value of this calculated measure didn’t change as a result of the next assignment by using the FREEZE statement, so: FREEZE([Measures].[Sales Compare Period])
  • Then make sure the values of the original [Measures].[Sales] measure ignored the [Compare Period] dimension by making its value ([Measures].[Sales]) = ([Measures].[Sales], ROOT([Compare Period]))

So the MDX Script ended up looking like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS NULL,

VISIBLE

= 1;

([MEASURES].[Sales Compare Period])=([Measures].[Sales],

ROOT([Period]));

FREEZE([MEASURES].[Sales Compare Period]);

([Measures].[Sales]) = ([Measures].[Sales],

ROOT([Compare Period]));

 

I found several problems though. First of all, the ROOT([MyDimension]) function only did what I wanted it to do (ie produce a tuple of all the All Members of every attribute hierarchy on MyDimension) when I didn’t have a set containing multiple members from MyDimension in the WHERE clause, and also failed for the Grand Total column for the original Sales measure in the OWC cube browser. I thought this was a bit rubbish and have raised this to the dev team, so hopefully it’ll get fixed. What I then did was to hard code the tuple instead of using ROOT(), so that my script looked like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS NULL,

VISIBLE

= 1;

([MEASURES].[Sales Compare Period])=([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All]);

FREEZE

([MEASURES].[Sales Compare Period]);

([Measures].[Sales]) = ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All]);

The next issue was with overwriting the values of [Measures].[Sales] – it seemed to have different problems in the OWC cube browser in the Grand Total cells, gaving me an infinite recursion error, so I guess there was some kind of conflict with the VisualTotals functionality that OWC uses. Anyway at this point I thought I’d simply create another calculated measure to show these values instead, and not overwrite the values of the original measure but just hide it.

So, my MDX Script got changed again:

CALCULATE

;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Original Period]

AS ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All] ),

VISIBLE

= 1 ;

CREATE

MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS ([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All] ),

VISIBLE

= 1 ;

 

Disappointingly old school really, although I suppose I could have done the same thing in a more verbose manner and still managed to use MDX Scripting. At least it works in OWC and I can use sets in the WHERE clause, for example:
 
SELECT {[Measures].[Sales Original Period],[Measures].[Sales Compare Period]}  ON 0,
{[Period].[Month].&[1], [Period].[Month].&[2]}
ON 1
FROM CUBE1
WHERE({[Compare Period].[Month].&[1], [Compare Period].[Month].&[3]})
 
… and it all works. Perhaps a certain MDX guru whose name begins with M can come up with a better way of solving this though?
 
Incidentally, I welcome emails like Mike’s asking me questions. I learn a lot from trying to work out the answers to other people’s questions!

Free AS2005 course from MS

Seen on Euan Garden’s blog:
Free SQL 2005 courses! Including one course on new features in AS2005:
Have to download it and check it out – it looks like there’s some info there which hasn’t made it into BOL and I don’t know about…
 
UPDATE: if you’re trying to view this course from a machine with SQL2005 installed on it (now why would you want to do that?) you’ll find that you might get an ‘unable to initialize viewer’ error. MS Support told me how to get round this – unregister msmxml6.dll – but I don’t know what impact that will have on anything else you might be running.

Measure Expressions

I know this blog has been rather light on useful content (as opposed to links) recently, and I’m sorry – it’s not that I’m not learning anything new, I am, but much of it is going into my chapters for MDX Solutions 2E rather than on here. I’ll try to get round to blogging about stuff like the HAVING clause in MDX and the MemberValue() function sometime soon if I can do so without giving away all the juicy information that you’ll want to buy the book for, and if Mosha (or even BOL) don’t beat me to it…
 
But here’s a cool cube design feature that doesn’t cause any conflict of interest for me – measure expessions. The easiest way to explain what they are is to explain one scenario where they’re useful. In AS2K, if you’ve tried to model currency conversion, you’re probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can’t then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house – they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.
 
So what does a measure expression look like and where do you set it? In Visual Studio, in the cube tab of the cube editor, you’ll see the MeasureExpression property in the properties pane when you highlight a measure. Click on this and you get a text box where you can enter your measure expression. This has to be of the form
M1 op M2
where M1 is a measure from the same measure group as the measure you’re currently editing, M2 is a measure from a different measure group which shares at least one dimension with the current measure group, and op is either the * or / operator. Here’s an example:
[Measures].[Sales]*[Measures].[Currency Rate]
In previous builds AS has been very picky about having square brackets around the measure names and things like that, so if you’re trying this yourself make sure you copy the above example very closely. There are several other restrictions too, such as the fact that neither of the measures can be distinct count measures, so you can see that your options here are quite limited. That said for most scenarios they should offer all the flexibility you need to do the job.
 
How much faster are they than normal MDX, then? That’s the million dollar question, and I have to say that I don’t know yet. Even if I did I’m sure there would be some clause in the beta licence that stopped me from publishing performance data, and anyway it’s not fair to test performance on a product that hasn’t been released yet. Hopefully it will make a big enough improvement to make the headaches that accompanied this kind of requrement in AS2K a thing of the past. If anyone else out there has used this functionality and would care to share their experiences I’d be interested in hearing from them…
 
Measure expressions also allow you to do some really cool things with many-to-many dimensions too, but I’m going to save that for a future blog entry; this one’s already long enough.
 
UPDATE: No need to blog on measure expressions and m2m dimensions, actually, because Richard Tkachuk covers the subject in his white paper on m2m dimensions here.