Month: September 2006

Time Utility Hierarchies (again) and Attribute Overwrite

Chris Webb4 Comments
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

Chris Webb2 Comments
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

Chris Webb15 Comments
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.

 

Creating AS2005 Local Cubes with XMLA

Chris Webb8 Comments
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.

Attribute Relationships article

Chris Webb5 Comments
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?