The last word on AS205 local cubes

After my recent posts on AS2005 local cubes I heard from local cube expert Tim Peterson again, who told me about the work he’d been doing in this area. As is always the case there’s a lot more complexity to this topic than can be described in a short blog posting but he’s written up his findings in a white paper which you can download here:
He also mentioned that you can register to download the beta of the latest version of cubeslice here:
http://cubeslice.com/dnldfr1ocms.htm

UPDATE: Tim has just let me know that he’s posted up a demo of CubeSlice which uses Federal Election Commission data, which you can download here:
http://www.cubeslice.com/fecsharefile.htm
Clever marketing move on his part, I think – there’s nothing like relevant data (well, relevant for all of you in the US at least) to catch people’s interest.

Time Utility Hierarchies (again) and Attribute Overwrite

A few weeks ago I got emailed by my friend and ex-colleague Jon Axon (who is now – quick plug – working for a startup run by another friend of mine, Data Intelligence, doing cool stuff with pharmaceutical market research data and Analysis Services) highlighting some weird behaviour with calculations generated by the Time Intelligence Wizard. What he did was use the Time Intelligence Wizard to create a time utility hierarchy on the Date dimension in Adventure Works and then add the following, very straightforward previous period growth calculation to it:

CREATE MEMBER CurrentCube.[Date].[Calendar Date Calculations].[Previous Period] AS
([Date].[Calendar Date Calculations].DefaultMember, [Date].[Calendar].CurrentMember.PrevMember);

He then noticed that while the calculation worked as expected most of the time, some of the time he was unexpectedly getting null values. So, for example, the following query works ok:

select

{([Date].[Calendar Date Calculations].[Previous Period], [Measures].[Internet Sales Amount])}
on 0,
[Date].[Calendar].members on 1
from
[Adventure Works]

But this time it doesn’t, returning null at the Date level:

with member measures.test as ([Measures].[Internet Sales Amount],[Date].[Calendar Date Calculations].[Previous Period])
select
{[Measures].[Internet Sales Amount], measures.test}
on 0,
[Date].[Calendar].members on 1
from
[Adventure Works]
 
Now I had no idea what was going on here and neither did he, but he came to the conclusion after reading Richard Tkachuk’s paper on attribute relationships that there was some attribute overwriting going on here – you don’t have the same problem if you create a separate time utility dimension rather than a new hierarchy on the Date dimension – and he emailed Richard to ask him. Richard explained that the calculated measure in the example above was overwriting the [Calendar Date Calculations] hierarchy so the call to [Date].[Calendar].CurrentMember.PrevMember was always acting on the All member of [Date].[Calendar] and therefore always returning null, and that in order to get the correct results the tuple used in the calculated measure would have to include [Date].[Date].Currentmember so:

with

member measures.test as
([Measures].[Internet Sales Amount],[Date].[Calendar Date Calculations].[Previous Period],[Date].[Date].Currentmember)
select
{[Measures].[Internet Sales Amount], measures.test}
on 0,
[Date].[Calendar].members on 1
from
[Adventure Works]

In fact, I remember Jon and I coming across something similar a while ago and being confused about it then and I’m still not sure that I understand this behaviour properly now in order to use it in future. Although you can work around the problem in this particular situation, as I mentioned, by creating a separate time utility dimension, it seems contrary to everything I’ve ever known about MDX that adding [Dimension].[Hierarchy].Currentmember into a tuple should change the result it returns. And it’s not only me who’s confused: certainly calculations such as Year-to-Dates generated by the Time Intelligence Wizard suffer from the same problem, and I was also recently involved in a thread on the newsgroup where someone was getting quite irate about this topic:
(choice quote: "After working over 20 years with multidimensional analysis reading [Richard’s attribute relationships paper] makes me feel sick").
 
While I understand that there isn’t a simple solution to this problem (see the thread above for why) and that some complexity had to be introduced with the move to attribute-based dimensions, I still think there must be a better way of handling this. Yesterday I read this very perceptive post on Kathy Sierra’s blog:
…and I felt like I’d fallen into the ‘I suck’ trough in the graph. Microsoft did an awful lot of the things Kathy suggests to make an upgrade successful with AS2005 but I feel like one thing they didn’t do was "Try not to break things that were previously important to [users]" like the simple time series calculations discussed in this blog entry. At least I still have enough faith to believe that it will be fixed sometime in the future…

SAP BI Accelerator

Via Mark Rittman, news (and good analysis of) SAP’s recent announcement of its BI Accelerator product:
Anyone who’s read the OLAP Report or the OLAP Survey on SAP’s BI offerings over the last few years, or indeed talked to anyone on the sharp end of a SAP BI implementation, will probably know that query performance has been a real problem for SAP BI. If, as Mark suggests, SAP have gone down the route of using COP database technology (see the entry here http://www.olapreport.com/glossary.htm for some more info) then perhaps they are going to solve this problem at last – which, from a competitor’s point of view, is pretty scary because even though SAP’s BI has got poor reviews for its technology, SAP have a formidable sales force and have been able to sell it regardless.
 
The only place I disagree with Mark is when he says "Where it falls short, I’d say, compared to products such as the OLAP Option is probably around calculations, time-series analysis, multi-dimensional queries and so on" – isn’t it the case that you can query SAP Infocubes with MDX? In which case, problem solved.
 
This is good news too for Panorama, who, as you probably know, have fled to the arms of SAP after Microsoft acquired Proclarity. Here are three interesting blog postings from earlier this year from Oudi Antebi, ex-Microsoft and now of Panorama, on Microsoft, SAP BW and BI:
He makes some very valid points here, especially the one about SAP ‘owning the data’.
 
Note to self: time to start learning SAP-flavour MDX too…

Creating local cubes from relational sources using XMLA

As a follow-up to my recent post on creating local cubes from server cubes with XMLA, I’ve just been asked on the newsgroup about options for creating local cubes direct from relational sources without going via a server cube. Since you can’t use the CREATE GLOBAL CUBE syntax to do this, you’ve got two options: use the old CREATE CUBE syntax (which is very unpleasant and, I believe, only creates ‘AS2K’ local cubes and not the new-improved ‘AS2005’ local cubes which seem to perform so well and have all the cool new features) or use XMLA – which seems to be the best choice by default. Here are the steps to get the XMLA needed to create your local cube:
  • Design a server database/cube (not using any features like partitions or distinct count, which aren’t supported in local cubes) in BIDS. Deploy it but don’t process it.
  • In SSMS script the database you’ve just created to a new query editor window.
  • Still in SSMS, right-click on the database again and select Process, select ‘Process Full’ then instead of clicking OK click the Script button and choose to script to a new query editor window.
  • You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the ‘Batch’ command) and before the XMLA that actually does the processing.
  • Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.

 

Many-to-Many Dimension White Paper

Marco Russo has just released his white paper on many-to-many dimensions and all the cool things you can do with them. Here’s the announcement on his blog:
…and you can download it here:
It’s really detailed and full of good information, definitely worth a look.

Creating AS2005 Local Cubes with XMLA

Although I’ve mentioned the fact that you can create AS2005 local cubes with XMLA (as opposed to the old CREATE CUBE and CREATE GLOBAL CUBE DDL statements) in the past, I don’t think I’ve actually detailed the steps to go through to see an example of this XMLA. Here we go:
 
  • Open MDX Sample app – yes, this is another one of those areas where SQLMS won’t do the job. Connect to the Adventure Works database
  • Paste a CREATE GLOBAL CUBE statement into a query pane. Don’t run it yet though…
  • Start a new Profiler trace on your target database
  • Run the CREATE GLOBAL CUBE statement, then stop the trace
  • Inside your trace, you’ll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement
  • Copy the XMLA batch statement out to MDX Sample App (if you find you can’t do this, you might need to save the trace as an XML file and open it in Notepad or something) and
  • Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).
  • Run the Batch statement, and your local cube is created!

The problem with local cubes in AS2K were numerous: they were hard to create, the creation process was so buggy/difficult that you could never be sure you’d succeed, and the query performance of local cubes greater than around 100Mb was atrocious. AS2005 local cubes have, in my understanding, been completely rewritten though and apart from the new way of creating them they’re also a lot more functional (see  http://sqljunkies.com/WebLog/mosha/archive/2006/03/08/local_cube_password.aspx for example – they now have security!) and I recently did some testing on query performance that showed that a local cube of approx 230Mb actually performed better than the equivalent server cube. Unfortunately I also found out that trying to build a larger cube – at least with the cube design I had – led to some unpleasant crashes: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189679.

But perhaps in a few service packs time local cubes will be ready for use. I guess local cubes have also been neglected because the promise of 100% network connectivity has always been just around the corner, so the thinking has been that there’s no point investing in local cubes when in a few years everyone will always be able to connect to a server cube. Increasingly, though, the fact that imperfect connectivity will be a fact of life at least in the medium term is sinking in and new tools are appearing which deal with this – think of the kind of BI solutions you could build with local cubes and Groove 2007, for example, where you could build you local cubes centrally and distribute them to your sales force using Groove’s file synchronisation features (which I assume is possible – I’ve not tested this!).

This might also be a good point to link to Tim Peterson’s site on local cubes:
http://www.localcubetask.com/
When I last talked to him, he mentioned that he was working on an AS2005 version of localcubetask and it seems that there was indeed a release earlier this year.

PerformancePoint TAP

Darren Gosbell has just told me that the application form for the PerformancePoint Server TAP is available at Microsoft Connect:
At the time of writing, I can see a link to the form from the front page of Connect, but if that goes away in the near future then you will probably need to sign in and go to ‘Available Connections’.

Attribute Relationships article

I check Richard Tkachuk’s site every few weeks for new material (Richard, if you’re reading, any chance you could implement an RSS feed?) and have just noticed he’s put up a great article that explains the behaviour of attribute relationships in a lot of detail; there also seems to be a new version of his CellsetGrid control. His site is here:
 
The MDX Performance tips article, also updated, is definitely worth a read too. One thing I’ve been wondering about recently to do with the whole multiple measure groups vs multiple cubes issue (see Teo’s post on this here http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx for more details) is whether using the VALIDMEASURE function, which Richard suggests is good for performance, is going to give as good performance as splitting a cube with multiple measure groups up into multiple cubes that use linked measure groups. Anyone got any thoughts?

Microcharts

Data visualisation is definitely this year’s hot topic in BI: I’ve already blogged about how much I like Tableau (though they seem to be pretty much in bed with Hyperion now), and now here come Microcharts – another cool way of looking at your Analysis Services data:
Take a look at the online gallery:
To be honest I’d never heard of sparklines and so on before I saw an early beta of this stuff, but I’m definitely a convert now and with the focus on Microsoft BI now switching heavily to Excel with Office 2007 this is a timely release.  

Deployment Server Edition

One of the strange omissions in BOL, even in its new improved July update, is that there doesn’t seem to be a page showing which features of Analysis Services are available in which editions (if there is please send me the link!). For example, I recently found out the hard way that Perspectives and Translations are not allowed in Standard Edition but I couldn’t actually find that fact documented anywhere; so how do you know, when you’re developing a cube (especially if like me you use Developer Edition) what features you’re allowed to use?
 
What you need to do is set the Deployment Server Edition property on your project appropriately. You can find it by right clicking on your project in the Solution Explorer in Visual Studio – it’s on the Build page of the Configuration properties dialog. If you have a project which uses features that are only available in Enterprise Edition and you have set Deployment Server Edition to ‘Standard’ you will get a list of errors when you build your project telling you that these features are unavailable in Standard Edition.