I mentioned this thread from the microsoft.public.sqlserver.olap ng on ‘Limitations of AS2005’ the other day, and it looks like it has pretty much finished. It turned into an interesting discussion on drillthrough in AS2005. Something I hadn’t realised was that in AS2005 any values you wish to see returned by drillthrough must already be defined in the cube – which, when you consider how you use drillthrough in AS2K seems a bit bizarre. However if you read the thread you’ll see what Mosha and Deepak have say on the subject, namely that for fields you only want to access via drillthrough you should create a hidden, ROLAP Fact dimension. It should be hidden because it probably wouldn’t make any sense at all to your users if they saw it, and it should be ROLAP so that it has no impact on the performance of the rest of your cube until it is queried in the drillthrough. Certainly a different way of handling the issue compared to AS2K, maybe not as intuitive and maybe it will take a little bit longer to set up, but to me quite an elegant way of solving the problem. It also fits in well with the thinking that the UDM should model everything in your data warehouse that you want to query on.
Monthly Archives: August 2005
OLAP Jokes
With the cricket rained off we had nothing to waste our time with today at work, so Jon decided to challenge the rest of the team to come up with some OLAP jokes for my blog. Naturally Colin and I were up for the challenge and 50 (yes, 50) jokes later here are some of the best – although I thought a few were too risque to post and they were, of course, the funniest. If you aren’t a fan of puerile humour then now’s the time to stop reading…
Q: What does a calculated member have in common with a eunuch?
A: They both can’t have children
Q: Why couldn’t the hierarchy become a tightrope-walker?
A: It was unbalanced
Q: Why did [Year-Quarter-Month] beat Measures at an arm wrestle?
A: Because it was a strong hierarchy
Q: What does tired cube do?
A: Has a KPI
Q: Why did the dimension go to prison?
A: Because it was degenerate
A cube walks into a bar, pops himself on a bar stool and says "Whisky please".
"Double?" says the barman
"No thanks replies the cube "Just a measure"
A girl goes speed dating and after a run of duff prospects a dimension sits down opposite her and they engage in sprightly conversation. After the event is over, her friend asks "Did you get that dimension’s number? You two really seemed to hit it off"
"No" replies the girl "He was really deep at times – had a lot of levels – but you know parent-child dimensions, just too much of a mummy’s boy"
Q: Was the cube straight or gay?
A; Neither, it was BI.
Have you heard that the [Date Shipped] dimension is playing Hamlet at the National Theatre? It’s a role-playing dimension!
Q: What kind of a dimension is [Dictionary]?
A: A reference dimension
Q: What do pre-menstrual BI developers suffer from?
A: PTS
Q: How did the police catch the serial-killer query?
A: They used a Profiler
Did you know that Disney want to turn the Adventure Works cube into a multi-million dollar blockbuster movie? They’ve already optioned the MDX Script.
Q: What on earth was the cube trying to do when it set fire to its wallet?
A: It must have been trying to warm its cache.
Q: What’s the angriest MDX function?
A: Crossjoin
Q: What’s a dentist’s favourite MDX function?
A: Extract
Q: Why did the dimension take all day to take off its suit and put on a pair of jeans?
A: It was a slowly-changing dimension
Q: Why did the dimension keep itself to itself?
A: It was a private dimension.
Q: Why wasn’t the Level allowed in the fascist cube?
A: It was disabled
Q: What’s the porn director’s favourite MDX function?
A: BottomCount
Q: Why did the YTD calculation always finish last?
A: It always lagged too far back.
Q: Why were the other hierarchies frightened of Year-Week-Quarter?
A: It was unnatural
Q: Did you hear about the badger whose home wasn’t allowed in the subcube?
A: It was an arbitrary shaped set.
Q: Why was the MDX expression never able to get a date?
A: It was rank.
Q: What MDX function makes Previous Year Growth and Market Share blush?
A: StripCalculatedMembers
Q: How did the BI developer send his backed-up database to a colleague on the other side of London?
A: In a .cab file
Q: What do cellsets use to cut down trees?
A: Axes
Q: What did the cube leave on the floor when it fell over in the toilet?
A: A minidump
Q: What function would you use to retrieve [Product].[All].[Dried Fruit].[Raisins]?
A: CurrentMember
Q: Why did the cube have nothing to do after the meeting?
A: It had no actions
Q: Whilst walking through the MDX Script mountain range, why did the assignment not mind reports of a FREEZE on the peaks?
A: He was in a lower pass
UPDATE: if you think you can do better then leave your jokes in a comment. We tried to think of a prize for the best one but baulked at the idea of giving away something worth having and paying for the postage.
AS2005 Processing Architecture
Marco Russo points to this very interesting paper on "Analysis Services 2005 Processing Architecture" by TK Anand, which I hadn’t seen before; he also has a good discussion of how this all relates to slowly changing dimensions. Particularly fascinating for me was the section on push-mode processing – I can think of a whole bunch of interesting applications for this functionality when it comes to creating cubes from non-standard data sources and you don’t want to use SQLIS.
If I could make a criticism of such a good white paper it’s that it makes no mention of local cubes. As I noted a while ago here, in AS2005 you can create local cubes using this functionality – indeed, if you run a CREATE GLOBAL CUBE statement to create a local cube and run a Profiler trace, you can see it translated into the equivalent XMLA batch command which defines and then populates the local cube. I’m covering this topic in MDX Solutions 2nd Edition but I can tell there’s a bit more research to do to see exactly how much of what’s in the paper is relevant to local cubes.
Editing an AS2005 database ‘live’
Here’s an interesting bit of functionality I didn’t know existed in the BI Development Studio (following on from Mosha’s reply to my post on this interesting ng thread) – as well as making changes to an AS2005 database in VS in the default ‘project’ mode, where the changes you make are only propagated to the server when you click Deploy, you can also edit the database directly on the server by using ‘online’ mode and get a very similar experience to what you had with Analysis Manager in AS2K. To do this, instead of opening the project file in VS, go to File->Open->Analysis Services Database, type in your server name and select the server. Now when you make your changes they are implemented on the server when you click ‘Save’.
You too can work in Redmond!
Bookending the week by pointing to Richard Tkachuk’s site again, he links to a site (which he’s been linking to for ages but which has been down every time I’ve tried to look at it) containing details of BI-related development jobs at Microsoft. There are quite a few openings on there….
Creating members with multiple parents
Monday rolls around again, and there’s another new paper on Richard Tkachuk’s site about creating members with multiple parents.
Three AS2005/BI Events in the UK
Courtesy of http://www.sqlserverfaq.com/ I’ve found out about three upcoming AS2005/BI events in the UK – click on the Events tab to see details. Although the first date clashes with me frantically trying to meet a book deadline and I’m on holiday for the second, I’m tempted to turn up for the third. Anyone else going?
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.MEMBERVALUESELECT
{[Measures].[Internet Sales Amount], MEASURES.DEMO} ON 0,FILTER
([Date].[Calendar].[Calendar Year].
MEMBERS, [Date].[Calendar].
CURRENTMEMBER.MEMBERVALUE>2001.5)ON
1FROM
[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.