Excel 12 BI first impressions

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?

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.

 

SQL 2005 Service Manager

Courtesy of Mike Gunderloy, I’ve just come across the download for SQL2005 Service Manager. It does exactly what you think, ie it’s a tool that does for SQL2005 (and AS2005 and all the rest) what SQL Server Service Manager did for SQL2000. You can get it here:
 
Now why didn’t Microsoft provide this?

SoftArtisans OfficeWriter

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

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!

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? 

Report Builder support for Analysis Services

I’ve been meaning to blog about the quality of support for Analysis Services in Report Builder for some time now, but to be honest the thought of doing so depressed me – I prefer to blog about functionality that I like and works well (even though, looking at my usage stats it’s the negative posts on this blog that get the most attention). However Pablo Mugica has just brought to my attention the following KB article which details the ‘known issues that may occur’ when you try to use Report Builder with AS:
 
So, no support for parent/child attributes, ordering of members or KPIs amongst other things. And it doesn’t even mention the bizarre (although it makes sense for relational data sources) method of selection/navigation and the rules on where you can put attributes and measures on a report that I still haven’t worked out. Hohum. I hope they make more of an effort for the next version.

More on Excel Services

Two more entries on the Excel 12 blog about Excel Services:
 
As I’ve probably said far too many times already, the BI dashboarding stuff is going to be very, very important if it lives up to the hype…

Where to get the OLE DB for OLAP v9.0 Provider

The other day I was looking for the OLE DB for OLAP v9.0 Provider to install on a client machine but couldn’t find it on the AS2005 install CD (which is where it was in the June CTP, and where you got PTS for AS2000); I then found a newsgroup post stating that it was only going to be available as a separate download from now on. Here’s where you can now get it and other useful stuff like ADOMD.Net, the latest PTS for AS2000, various other OLE DB providers etc:
It’s called the ‘Feature Pack for Microsoft SQL Server 2005’. One to bookmark, I think.

Handling Time Zones

As a general rule, whenever several different people ask me about the same problem I begin to think that it’s time to blog about it. In this particular case the problem is how to display data by different time zones; I’ve been asked about this a few times recently, possibly because AS2005’s ability to handle multiple languages and currency conversion means that people now feel like they can build ‘international’ cubes and this requirement arises as a result. For example imagine you were collecting usage stats for your web application and were interested in seeing how many visitors you had at different times of the day, but users in your UK and US offices wanted to analyse this data by their local time. In your cube you’d need to have a Time Zone dimension and/or attribute which, when you chose the GMT member, would mean that the selection 3:00 on 1st November 2005 on your Time dimension would show the same values as if you’d chosen the EST Time Zone and selected 22:00 on 31st October 2005. Note that this isn’t the same problem that Ralph Kimball talks about here, which is doesn’t allow you to analyse data in anything other than the local time zone of the event or the standardised time zone.
The obvious first step towards solving this problem is to hold the data in your fact table at one common time zone, probably GMT. However doing the time zone translation isn’t as simple as finding how many hours + or – each time zone is with regard to GMT, of course. The big complicating factor is Daylight Saving Time, and as Wikipedia shows it’s a pretty big complicating factor – in my opinion you wouldn’t want to write code to model these rules, you’d be much better off having a table which for every hour of every day in every time zone you were interested in would give the equivalent hour and day in GMT. So if you were interested in 20 time zones and had 5 years of data in your fact table, you would have approximately 5*365*24*20=876000 rows in this table. Populating this table would be a pain though… I wonder if there’s a web service out there which can do this conversion for you, operated by people who keep on top of all the changes surrounding Daylight Saving Time such as the recent ones Wikipedia mentions in Indiana?
How you can then use this table in your AS2005 cube design is the really interesting question. My first thought was that even though this isn’t a many-to-many relationship (unless you wanted the duplicated hours that occur when DST moves backwards to be modelled as one member rather than two, which doesn’t seem sensible) you could model it as such – the table descibed in the previous paragraph would become your intermediate measure group, which joins to the fact table via a GMT Time dimension; this time dimension could then be reused (as a role-playing dimension) as your Time in Time Zone dimension, joined to just the intermediate measure group along with a separate Time Zone dimension. You could then hide your GMT Time dimension and your users would just have to select which ever time zone they wanted to analyse by on the Time Zone dimension and the Time in Time Zone dimension would behave exactly as described in my first paragraph.
However there are some drawbacks to this approach. First and foremost is that you couldn’t make use of any aggregations above the hour attribute, because the many-to-many relationship is resolved at this level. This would mean that if you wanted to run queries at the Month or Year level they might not perform as fast as you’d like. Also, the larger the table you’ve built your intermediate measure group from, the slower it will be to resolve the many-to-many relationship, although partitioning this measure group by Time Zone and possibly Year to will mitigate this effect.
The alternative is to model all this as a single regular dimension. You’d have a GMT Hour attribute, an Hour in Time Zone attribute (with a one-to-many relationship between the two) and a Time Zone attribute; the key thing to note here is that every hour in a time zone becomes a single member, so the overall dimension size is much larger than in the many-to-many approach. Since AS2005 can (so I’m told) handle dimensions of many million members even in 32-bit the size issue wouldn’t be the showstopper it would have been in AS2000, although it would presumably have some impact on overall performance. The fact that you could now use aggregations above the Hour level would, on the other hand, be a big factor in improving performance which I would guess would more than make up for the negative impact of dimension size. Judicious hiding of attributes and the use of user hierarchies (for example Time Zone->Year->Month->Day->Hour) would mean that the complexity of the dimension could be hidden from the user; the only real drawback I can think of is that because every Hour, Month, Year etc would be a separate member on the dimension, it would be difficult for users to create reports in an ad-hoc tool which selected a couple of months on a visible axis and then allowed them to slice by time zone – something that wouldn’t be an issue with the many-to-many approach.
 
One other approach I thought about was using a stored procedure called from an MDX Script assignment to perform the time zone translation – but as I said earlier, trying to handle all the wrinkles of DST changes in code would be a real nightmare, and calling a stored procedure multiple times to get values for each Hour probably wouldn’t perform well either.
I’d be interested to hear other people’s thoughts on how to solve this problem; even better, it would be good to hear from anyone who’s had to actually implement a solution. So post some comments! It would be useful to know whether the regular dimension approach does in fact perform better than the many-to-many approach, for instance…
UPDATE (March 2015)  – almost ten years after writing this post, I’ve been asked to revise it. With rather more experience of SSAS than I had back then, I can say that my proposed solution for a single dimension is just wrong and wouldn’t work, and using a stored proc wouldn’t be feasible either. The only workable solution would be the many-to-many approach.