Chris Webb's BI Blog

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

Archive for March 2006

AS2005 Dynamic Security Performance

leave a comment »

There’s an interesting post on AS2005 dynamic security performance over on David Gustafson (of Proclarity)’s blog:
 
UPDATE: see also the follow-up post:

Written by Chris Webb

March 23, 2006 at 12:21 pm

Posted in On the internet

Redundant Attribute Relationship Health Warning!

with 6 comments

My ex-colleague Jon might spend far too much time pseudonymously posting silly comments on blogs, but he deserves an extra helping of chocolate mousse for finding this problem to do with redundant attribute relationships – this is something everyone designing AS2005 cubes needs to be aware of!
 
Imagine you had a Geography dimension with three attributes: City (the key attribute), State and Country, where each Country has many States, and each State has many Cities. Now you probably already know that if you have built your dimension with the wizard, the chances are that your dimension will have two relationships pre-built – between City and State and City and Country – and that you need to change this so that there are relationships between City and State and State and Country in order to get optimal performance and to help aggregation design. AS2005 understands transitive relationships between attrbutes, and so even though there are just two relationships built in your dimension it knows that because City is related to State and State is related to Country, there is a transitive relationship between City and Country.
 
BUT, did you know that bad things can happen if you actually build the relationship between City and Country in your dimension? Before Jon found out about this, I had understood that the only effect was that having this redundant relationship might increase the size of the dimension and increase processing times. However, it turns out there are two other potentially serious side-effects:
1) MDX Script Scopes may include unexpected extra cells. So, for example, if you’re assigning values to a subcube in your MDX Script (and most of the calculations created by wizards in BI Dev Studio do this) then you may see incorrect results with the redundant attribute relationships in place.
2) Querying fact data may ignore some slices or return Nulls on certain related attributes when the measure group has granularity on an attribute different from the key attribute.
 
Apparently what’s happening is that, using our example, the redundant relationship between City to Country would cause AS to ignore the legitimate relationship between State and Country. Even more confusingly, if you were to create a user hierachy from Country to State to City then the incorrect behaviour would go away.
 
So the moral of all this is to be extra-careful when designing your dimensions and not to let any redundant attribute relations remain in there. This problem won’t be fixed in SP1 but there will be a warning in the dimension editor UI when redundant relationships are detected, though, which should make more people aware of it.
 
Apart from Jon, thanks also to Matt Carroll and Marius Dumitru on the AS dev team for taking the time to explain what’s going on here to me.
 
 

Written by Chris Webb

March 23, 2006 at 11:17 am

Posted in Analysis Services

SQL2005 SP1 CTP Released

with one comment

The CTP of SP1 for SQL2005 is now out:
There’s also an updated version of BOL. No idea what fixes exactly have been included though because the link to the KB article in the readme file is dead! However, for Analysis Services, I’ve heard about one potentially interesting change with solve order – see the following thread on MSDN forums for the full story:
I wonder if the ability to cache the results of MDX Script calculations is also going to be there?
 
UPDATE: The KB article is now up and there are lots of AS fixes in there. Here’s the link:

Written by Chris Webb

March 17, 2006 at 10:22 am

Posted in Analysis Services

Announcing Crossjoin Consulting

with 7 comments

After two years in my current job I’ve decided to move on and set myself up an independent consultant. I’ve set up my own company and if you go to the website:
…you can see the formal write-up of what I want to be doing (cool domain name isn’t it?); it’s basically short engagements working with Analysis Services and MDX. My thinking is that there are lots of people out there who are working with Microsoft BI tools and for the most part being successful with them, but who might from time-to-time run into problems writing difficult calculated members or designing their cube and who need someone with my specialised skillset to help out for a few days or weeks. This is the kind of thing I enjoy doing most – in fact, it’s what I’ve been doing for free for people who post questions on the newsgroups, leave comments here or who email me direct for the past few years. I also want to add a bit of variety to my work: apart from the consulting, I want to do some training (I’m going to be an associate mentor with Solid Quality Learning), speak at conferences (see you at SQL Connections in Nice), and maybe develop for sale some Microsoft BI-based packaged solutions in conjunction with my friends in the UK BI mafia. I’d also be interested in working with MDX on other OLAP servers that support it – I’m sure there can’t be many Essbase or SAP BW consultants out there that know MDX, for instance.
 
Since I’m based in the UK that’s where I’d prefer to do most of my work, but I’m willing to travel anywhere necessary. However I’m going to experiment with attractively discounted rates for customers who will let me work from home: I know that in the BI world the data we work with is a precious commodity that you can’t simply email to all and sundry, but on the other hand I feel that some problems can be solved without ever seeing the data, sometimes without seeing the cube, so I’d like to see if I can make that model work.
 
Usually at this point in the "I’ve got a new job" blog entry the writer apologises for the fact that he’s going to be much busier in future and won’t be able to blog as much as he used to be able to do. Not true for me: I intend to blog more. I’ll be building into my schedule days of sitting at home, doing R&D, reading, blogging and answering questions on newsgroups. There are three reasons for this: 1) if I was away working all week, every week my wife would kill me, 2) it’s the only way I’ll be able to keep up with all the new developments in the world of Microsoft BI, and 3) what better way is there than blogging to reach my potential customer base?
 
So if you’ve got a problem and no-one else can help, and if I’m available, maybe you can hire me. Drop me a mail on info@crossjoin.co.uk to find out!
 
 

Written by Chris Webb

March 16, 2006 at 5:46 pm

Posted in Business

Analysis Services, Visio and Project

with 3 comments

While doing some browsing this morning I had a look for any new Microsoft BI-related blogs that I hadn’t so far seen (see updated blogroll – the newest are at the bottom – if you have a relevant blog I don’t list please let me know). I found some good stuff, but one there was one thing I thought deserved a special mention: Eugene Asahara on his ‘KPI Cause and Effect Graph’
 
I also came across the Project 12 blog which, while not really to do with BI, has a post on Analysis Service integration in Project 12:
Nice to see that other product teams inside Microsoft are adding BI functionality; I guess other ISVs who use SQL Server as their relational source must be planning on doing the same thing.
 

Written by Chris Webb

March 14, 2006 at 12:26 pm

Posted in On the internet

My first Analysis Services stored procedure

with 6 comments

Several people have asked me about Analysis Services stored procedures recently and somewhat shamefully I’ve had to admit to them that I hadn’t actually done anything with them yet. So this week I’ve vowed to dust off my C# (I can’t believe how much I’ve forgotten), write some stored procs and post the code here. I’ve got some ideas on where they can be useful and hopefully I’ll be able to implement what I need.
 
The first problem I tried to solve was this: how do you return the name of the currentmember on all dimensions in your cube in a query? I did a series of postings on this last year and although it’s possible to do in pure MDX it isn’t elegant:
In AS2005 where the currentmember on one attribute hierarchy can change depending on the currentmember on another on the same dimension, knowing what the currentmember is on all your hierarchies can be useful for debugging. Luckily, it’s extremely easy to create a stored procedure to return this information. Here are the steps:
 
1) Create a new .NET dll project, making sure the resulting assembly is called FindCurrentMember.
2) Add a reference to Microsoft.AnalysisServices.AdomdServer
3) Copy the following code into your project:

using System;

using

System.Collections.Generic;

using

System.Text;

using Microsoft.AnalysisServices.AdomdServer;

namespace

MDXDebuggingUtilities

{

public class MDXDebuggingUtilities

{

public static string FindCurrentMember()

{

string output="";

foreach(Dimension d in Context.CurrentCube.Dimensions)

{

foreach (Hierarchy h in d.AttributeHierarchies)

{

output +=

"Dimension: " + d.Caption + " Hierarchy: " + h.Caption + " CurrentMember: " + h.CurrentMember.Caption + " *** ";

}

}

return output;

}

}

}

 

4) Build the dll
5) Go to SQL Management Studio, connect to your server, expand a database (we’ll use Adventure Works DW here) and right click on Assemblies
6) Select ‘New Assembly…’  and add ‘FindCurrentMember.dll’
7) Run the following query to check it’s working – beware that because Adventure Works is a pretty complex cube there are an awful lot of currentmembers to look at! It might be easier to double-click on the cell you want to look at so that the Properties window appears, click copy and then paste everything into notepad so you can look at the values.
 

WITH

MEMBER MEASURES.TEST AS

FINDCURRENTMEMBER.FINDCURRENTMEMBER()

SELECT

MEASURES.TEST ON 0,

[Customer].[Customer Geography].[Postal Code].&[2450]&[Coffs Harbour].

CHILDREN

ON 1

FROM

[Adventure Works]

 
Obviously there are a lot of improvements/optimisations to be made in this stored proc (for example, it would be good to be able to specify just one dimension to look at the currentmembers on) but I thought I’d post it up as a simple example of using Context and the Dimension and Hierarchy objects anyway.
 
 

Written by Chris Webb

March 13, 2006 at 1:32 pm

Posted in MDX

OLAP Report 2005 Market Shares Out

with 2 comments

Watch out for the tidal wave of press releases from Microsoft trumpeting their continued reign at the top.

Written by Chris Webb

March 7, 2006 at 1:17 pm

Posted in On the internet

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers