Month: November 2005

Podcast #1: Nigel Pendse

Chris Webb7 Comments

A while ago I decided that it would be cool to jump on the podcasting bandwagon, and here’s my first attempt. I was very lucky to get Nigel Pendse to chat with me about the impact that SQL2005/AS2005 will have on the BI market as a whole; if you don’t know who Nigel is he’s the man behind the OLAP Report and the OLAP Survey, both of which are great sources of competitive intelligence if you’re a software vendor or consultancy, and also a very popular speaker at conferences and seminars. He has a lot of interesting and intelligent observations to make in my opinion.

 

Anyway, here’s where to get the podcast (it’s about 25 minutes long and in mp3 form):

http://www.ourmedia.org/node/108782

Apologies for the rather amateurish production…

 

If you think this is a worthwhile undertaking, I’ll start thinking of people to ask to interview for future podcasts. Let me know what you think!

 

Excel 12 BI first impressions

Chris Webb14 Comments
After my gushing posts about Excel 12 BI a month or so ago, I was co-opted onto the Office 12 beta program with the invitation to blog all I want about it. So… having downloaded the beta bits and installed them, here’s the first in a series of posts on my experiences with Excel 12. I suppose this makes me a willing tool of the evil Microsoft hype machine (maybe if I blogged about games consoles, exotic holidays or stock tips people would try to bribe me with something better than beta testing) but hey, I’m sure you’ve already worked out that I sold my soul to billg several years back!
 
My first pleasant surprise came with the installation – it was freakishly fast. Having wasted hours installing various versions of SQL2005 on my machine over the last year or so, Office 12 seemed to install in about 5 minutes. Opening Excel it wasn’t hard to get to grips with the new interface and I could appreciate the benefits: it’s a lot more visual, and not having to make your way through several levels of nested menu items does make it faster to use.
 
Creating a connection to a cube was pretty easy, much better than the wizard of previous versions, and I created a pivot table. Then came my first disappointment. As with the pivot tables we’re all familiar with, Measures are treated as a special case and can only be dragged into a ‘Values’ region on the pivot table rather than put on Rows or Columns directly. After a bit of searching I came across a property which let me move my measures to where I wanted them, but I really don’t understand why this is so difficult; no other AS client tool has this problem. Similarly, when selecting members you’re still only given the option to view a hierarchy as a treeview starting at the All Member and then drilling down to the members below; most, if not all other AS client tools (including SQL Management Studio and BI Dev Studio) also give you the option to see a treeview consisting of the levels of each hierarchy, so you can either select a whole level or expand it and select members from it. Admittedly in AS2005 the fact that you’ll have lots of single level attribute hierarchies in your cube makes this slightly less of an inconvenience, but it’s still pretty irritating, and a bit of a step backwards from the functionality offered in Microsoft’s existing Excel addin.
 
However, once I’d run my first query there was a whole load of good new functionality to enjoy. For example, under Field Settings/Show Data As, you can switch between showing the actual values of your measure and various calculated values such as percentage of column – meeting a very common requirement and one which MDX can’t handle well, as this recent newsgroup thread shows. Displaying member property values is handled nicely, and the filtering/sorting functionality available on member names, member property values and measure values is very good indeed – possibly better than in any other AS client tool I’ve come across – although I didn’t seem to be able to filter on the values displayed using ‘Show Data As’, only the real values. Then there’s the formatting functionality, already well covered in the Excel 12 blog and again very good indeed. Query performance seemed ok, in fact better than some other client tools on my test cube, and browsing hierarchies with large numbers of members was not a problem.
 
Like other Excel addins for AS, you can also convert your pivot tables into a set of formulas which return member names and values. I don’t have an installation of the existing Excel addin handy, but from what I remember there are several important improvements here: you can now use formulas to construct the parameters you pass into these functions, eg such as =CUBEMEMBER("localhost MyDB","[Product].[MyUserHierarchy].[Category].&[" & H12 & "]"); there are also some new functions which allow you to declare named sets using whatever MDX you like and then pick members out of them, so that these members can then be passed into the other functions.
 
Obviously this being beta 1 there were some things that didn’t work. I don’t know whether it was because I had to install Excel on a Windows 2003 box and work via Remote Desktop Connection, but the charts looked like they’d been generated on a ZX Spectrum. There were also rather interesting Group/Ungroup buttons which didn’t work… I wonder if they are for creating custom groups of members? Overall though, the build seemed stable and the BI features worked well.
 
What next? I need to download and install the documentation (!) and Excel Services so I can check out how easy it is to create BI dashboards for the intranet. This post was only intended to convey my first impressions so as I learn more I’ll make sure I blog about it, and if I’ve said anything so far which is wrong or inaccurate (which is certainly possible) I’ll be happy to issue a correction. If anyone has any BI-related Excel client features they’d like me to check out then please leave a comment and I’ll do my best to oblige. I’m impressed with what I’ve seen so far; maybe the BI community can exert a bit of people power and lobby to get the less good features changed, so that Excel 12 fulfills its promise.
 

Do you know anyone Scottish?

Chris Webb2 Comments
Here’s something fun to show them:
  1. Open SQL Server Management Studio
  2. Connect to the AdventureWorks cube
  3. Run the following query:
    select [Measures].[Internet Sales Amount]
    on 0,
    [Geography].[Geography].[State-Province].[England].children
    on 1
    from [Adventure Works]
  4. Scroll down the result set and point out that according to AdventureWorks, Scotland is a city in England.

 

SoftArtisans OfficeWriter

Chris Webb1 Comment
You’ve probably realised by now that I’m eagerly awaiting the arrival of Excel Services (I’ve been asked to join the beta program so you’ll be hearing a lot more about it here soon), but for me the Holy Grail of reporting would be something that incorporated the best bits of Excel Services and Reporting Services, where users could design reports in any Office app, connect them live to different data sources, and then make them available over the web and have them rendered in many different formats. While this might be a pipe dream, I did come across a very interesting product that offers another subset of the functionality I want while looking at Brian Welcker’s blog today: OfficeWriter, from a company called SoftArtisans.
 
It’s a tool that allows you to automate the production of Excel and Word reports through Reporting Services. There’s a good Technet webcast which gives you a good overview of how it works here, as well as some online documentation here. You basically create your report in Excel or Word, add some references to data sources using a toolbar, and then publish the whole thing to RS; users can then go to RS, render the report and they get the spreadsheet or document you originally designed (with all the original features like charts, formatting, formulas etc) with the data dynamically injected into it. Definitely worth a look.
 

Some Time Intelligence Wizard-generated calculations don’t work

Chris Webb10 Comments
This is an issue I came across a while ago during beta testing, but now I know it is present in the RTM version and because I know this is going to be a major cause for confusion, I thought I’d blog about it. Put simply, the MDX for certain calculations generated by the Time Intelligence Wizard in BIDS doesn’t work. For example, try using the wizard to add a Year to Date calculation to the [Date].[Calendar] user hierarchy in the Adventure Works cube. You’ll end up with a bit of MDX Script like this:
 

/*
Begin Time Intelligence script for the [Date].[Calendar] hierarchy.
*/
Create Member CurrentCube.[Date].[Calendar Date Calculations].
[Year to Date] AS "NA" ;

( [Date].[Calendar Date Calculations].[Year to Date],
{
[Measures].[Internet Sales Amount]
},
[Date].[Calendar Year].[Calendar Year].

Members ) =
Aggregate(
{ [Date].[Calendar Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
)
) ;
/*
End Time Intelligence script for the [Date].[Calendar] hierarchy.

*/

 
When you try to run any query on this user hierarchy (or indeed on the constituent attribute hierarchies) you’ll find that you only see Year to Date values for the Year level – which isn’t exactly useful. Here’s a query which illustrates this:
 

SELECT

[Measures].[Internet Sales Amount]
*
[Date].[Calendar Date Calculations].[Calendar Date Calculations].ALLMEMBERS
ON 0,
[Date].[Calendar].MEMBERS
ON 1
FROM [Adventure Works]

 
So, how can we fix this? It depends on how complicated your own Date dimension is – luckily/unluckily, the Date dimension in Adventure Works is more complicated than average. For me, any solution would have to meet the following criteria:
  • It should work on the [Date].[Calendar] user hierarchy.
  • It should work on any meaningful combination of attribute hierarchies. So, in Adventure Works, this should mean you should not see values when you are looking at the [Date].[Semester of Year] hierarchy on its own, but if you have crossjoined [Date].[Year] with [Date].[Semester of Year] then it does make sense to see YTD values.
  • It should somehow work around the mysterious ‘internal error’ messages that started appearing in OWC – but not in my own MDX in SSMS – when I started testing my code.

After an awful lot of head-scratching I came up with the following scriptlet which is the only way I could see to meet the first two criteria properly (I tried to find a simpler approach, believe me) and minimised the appearance of the bug in the third bullet:

/*
Begin Time Intelligence script for the [Date].[Calendar] hierarchy.
*/

Create Member CurrentCube.[Date].[Calendar Date Calculations].[Year to Date] AS "NA" ;
( [Date].[Calendar Date Calculations].[Year to Date],
{
[Measures].[Internet Sales Amount]
}
,
[Date].[Date].[Date].Members
) =
Aggregate(
{ [Date].[Calendar Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
)
) ;

SCOPE([Date].[Calendar Date Calculations].[Year to Date], [Measures].[Internet Sales Amount]);
SCOPE([Date].[Calendar Year].[Calendar Year].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Calendar Semester].[Calendar Semester].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Calendar Quarter].[Calendar Quarter].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Month Name].[Month Name].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
/*
End Time Intelligence script for the [Date].[Calendar] hierarchy.
*/

 
Basically, what the first assignment does is perform the YTD calculation at the [Date].[Date].[Date] level only; next, the series of nested SCOPEs returns the YTD value for the last existing member on the [Date].[Date].[Date] level for other meaningful granularities, carefully excluding the combinations of dimensions which don’t make sense. Mosha, if you’re reading, is there a better way to do this?

Reward Beta Testers better!

Chris Webb2 Comments
Before I even write this I know I’m going to sound ungrateful, whingeing, grasping, greedy and all sorts of other things, but here goes…
 
There was a SQL2005 launch event in London yesterday, and one of my colleagues from another team went along. He sent the link to register to the event to me and some others on my team, but we had a look at the agenda and we realised that we’d seen pretty much all of the material before so we didn’t go. I started working with AS2005 in the Spring of last year, and when I joined my current team my colleagues had already been working with AS2005 for more than a year before; as a result we know quite a lot about AS2005 and as beta testers we found a lot of bugs. Anyway, I just spoke to the guy who went to the launch event this morning and he told me that he got a FREE copy of SQL2005 Standard Edition and a FREE copy of Visual Studio Professional just for turning up. And I admit that I was jealous. Yes I got a fleece when I went to my first Yukon airlift back in 2002(?), yes I got a Yukon t-shirt and picture frame last year, and yes I got a *lot* of inside knowledge, help and support as a beta tester which has been invaluable professionally and which I’m very, very grateful for, but copies of SQL2005 and VS are really quite tasty gifts. You’d think that Microsoft would at least treat beta testers as well as the people that go to launch events by giving them a free copy of the product they’ve been testing, wouldn’t you?