As I think I've probably said before, I don't get many freebies as a blogger but one that I do get and I really appreciate is my review copy of the BI Survey



. It being that time of year, I got the latest edition - the BI Survey 8 - last week along with an email encouraging me to blog about it, and who am I to refuse a request to blog about something as fascinating as this? It's a whopping 489 pages long so I can't even begin to summarise it, but there are some points regarding Microsoft BI that I'd like to pick up on.

In general, the findings show the Microsoft BI stack as a solid and successful suite, extremely good value for money, but by no means a stellar performer. There's a long section at the end of the report showing a wide range of technical and project-related KPI ratings (for example query performance, business benefits achieved, quality of support, cost of deployment and so on) and in almost every respect Analysis Services and Reporting Services come out in the middle of the rankings. This doesn't really surprise me much: let's face it, what we've got works well, but from a technical perspective there's not been much new and exciting in the world of Microsoft BI for a while now (although with Gemini and Madison coming soon that will change), the platform in general still has some glaring holes and overlaps, and as the PerformancePoint debacle showed recently Microsoft's overall BI strategy is somewhat confused. In fact, I suspect Microsoft's entire BI strategy is not a BI strategy at all but a get-people-to-upgrade-to-the-latest-version-of-Office strategy, but I digress...

For Analysis Services in particular, the survey showed 15% of those surveyed were on AS2K, 79.6% were on AS2005 and 3.5% on AS2008. Given that most people must have been surveyed last summer, well before the RTM of AS2008 that's pretty good and with the migration path from 2005 to 2008 very smooth I should think the AS2008 percentage will look much better next year. Analysis Services remains the top-ranked BI tool used against Microsoft's own databases, as you'd expect since it's essentially free with SQL Server, but it also comes in a close third for Oracle (while Oracle's own BI tools come in 12th!), second for IBM, third for Teradata and top for Open Source databases.

In terms of the client tools used with Analysis Services, just over 70% of people were using one of Microsoft's own tools - mostly pivot tables and Reporting Services. 25% of SSAS users had Proclarity, and interestingly there were more people using the old free Excel addin than PerformancePoint , and a sizeable minority still using Data Analyzer

. There's clearly a lot of demand for a client tool from Microsoft but from the looks of things most people are still stuck with Office 2003; this just adds weight to my argument that Microsoft coupling its BI strategy so closely to Office might help Office adoption rates but has a serious negative impact on the success of BI strategy itself... sorry... there I go again... As far as third-party tools go Panorama is still the #1 vendor, but it only has an 11% share and only 22% of SSAS users overall had any kind of third party tool in use. Incidentally Panorama was treated as a BI vendor in its own right for the purposes of the survey and came out top in a lot of the high-level KPIs including the overall KPI ranking - they obviously have some very enthusiastic customers.

So who, apart from Panorama, seems to be doing well? Qlikview certainly is, which at least validates Microsoft's decision to go after that market with Gemini; Microstrategy does well too. Essbase comes out badly with great query performance offset by poor support and product quality; Cognos, apart from TM1, doesn't seem to do too well either. Clearly it's the vendors who are 100% focused on BI that are the most successful, which is as you'd expect.

Here's a companion-piece to my post last week about query context and the formula engine cache - another scenario where you can easily stop caching taking place without knowing it, which has already been documented (although there is at least one important point to add) but again needs more visibility. This time the problem is that when you write an MDX query with an arbitrary-shaped set in the Where clause it stops Analysis Services using the storage engine cache. Queries that suffer from this will always read from disk and always perform as well or as badly as they did the first time they were run - so if cold cache performance is a problem for you, then this is an issue you need to understand and avoid.

Rather than repeat the information, let me direct you to the blog entry where I first found out about this problem, on Thomas Keyser's blog from 2006:
http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!135.entry

I can confirm that everything he says is still relevant on SSAS2008 except for the last query, where he has the whole of the Product.[Product Categories] hierarchy in the Where clause - run it twice and the second time you run it you'll see it does hit the storage engine cache. One other point I picked up on Mosha's MDX seminar in November is that it is possible for Analysis Services to think a set is arbitrary-shaped when it really isn't. Take the following query:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
WHERE
({[Product].[Category].&[1], [Product].[Category].&[3]}
,[Customer].[Country].&[Australia])

This does not have an arbitrary-shaped set in the Where clause, and as a result the second time you run it you'll see it hit the storage engine cache. However, if you rewrite the query so you have a set of tuples in the Where clause as follows:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
WHERE
({([Customer].[Country].&[Australia], [Product].[Category].&[1]),
([Customer].[Country].&[Australia], [Product].[Category].&[3])})

Even though you might think this query is equivalent to the first one, you'll see that it does not use the storage engine cache.

What can we do about this then? Not a lot with most client tools; I've not checked, but I'd be surprised if any of them generated their MDX to avoid this situation. If your users frequently use certain arbitrary-shaped sets the only thing you could maybe do is hack your dimension data to make them non-arbitrary - but that would almost certainly end up being a bad compromise; otherwise you'd just have to build aggregations to make cold cache queries fast.

However, if you're using SSRS then of course you can rewrite the MDX yourself. Let's build a quick report on AdventureWorks that displays this problem:

As you can see, I've got a multiselect parameter on the slicer that has a default selection of members from two different levels from [Product].[Product Categories] - an arbitrary shaped set. Here's the MDX that gets generated:

SELECT
NON EMPTY
{ [Measures].[Internet Sales Amount] }
ON COLUMNS,
NON EMPTY
{ ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM (
SELECT ( STRTOSET(@ProductProductCategories, CONSTRAINED) )
ON COLUMNS FROM [Adventure Works])
WHERE (
IIF( STRTOSET(@ProductProductCategories, CONSTRAINED).Count = 1,
STRTOSET(@ProductProductCategories, CONSTRAINED),
[Product].[Product Categories].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

And here's how I would rewrite it:

SELECT
{ [Measures].[Internet Sales Amount] }
ON COLUMNS,
NON EMPTY
{ ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) }
ON ROWS
FROM
[Adventure Works]
WHERE(
DESCENDANTS(
STRTOSET(@ProductProductCategories, CONSTRAINED),
[Product].[Product Categories].LEVELS(
MAX(STRTOSET(@ProductProductCategories, CONSTRAINED),
[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL))
)
)
CELL PROPERTIES VALUE

What I've done here is:

• Get rid of the completely useless Non Empty on columns (Why is it there? We don't even want to remove empty columns - surely doing that would break the report?)
• Removed the subselect and used a Where clause instead to do the slicing, so if we needed it we could use the formula engine cache (see here for why).
• Removed any cell or dimension properties that aren't used by the report (see here for why, although it's only relevant for really big reports).
• Used an expression in the Where clause to find the descendants of all members in the parameter set at the level of the lowest member of the set, using the Descendants, Levels and Max functions. This I think will turn all arbitrary-shaped set selections into non-arbitrary-shaped sets.

So I have been using Windows Internet Explorer 8 since the first beta was released in March of 2008. As the development of this major update reaches the finishing line, I would like to take a look at some of the features I enjoy using and some things I wish had made it in:

1. A more informative address bar, as you can see in the above screenshot, Internet Explorer 8 details information such as the type of application protocol along with the domain name and top level domain name. It might not seem readily useful but makes it easier to identify the security of a website along with the domain name.

2. A more a manageable Address Bar history. Firefox had this for a while now, the ability to delete URL’s in the address bar history list, thank goodness, its in IE 8!

3. Address bar suggestions, Internet Explorer 8 provides an end to end experience when it comes to web browsing. You can also use this feature to search for recently browsed web sites, as you can see, keyboard tips are also presented when working the address bar area.

4. A Search box that’s more intelligent, providing not only a quick way to get results from your search engine, but actually deliver useful information. The above screenshot shows how Microsoft is really innovating when it comes to Search and synergies. This is a great way of saying there is more to Google than Search.

5. Color coordinated tabs is a fun, useful way to keep track your many websites you might have open in the web browser. When ever you open a link a in a tab from within an existing tab, it creates a color set indicating the tab from which it was opened. Coordinated Tabs also works in ‘Quick Tabs’ view and ‘Tabs List’.

6. InPrivate browsing - helps prevent Internet Explorer from storing data about your browsing session. This includes cookies, temporary Internet files, history, and other data. Toolbars and extensions are disabled by default.

7. You gotta love a feature like this, especially for persons who want to know more about the web find similar sites of interest about a certain topic, Web site suggestions does just that and results are surprisingly accurate!

8. There is always that little snippet of information you always go back to a website just get the latest info from. Now let that website bring the information you through Web slice. A great way to know whats going on by simply cropping portion of the website. It doesn’t work sometime for me, for example, I tried to get the weather update from a web slice I created, but IE refused to display the results, instead, displaying only a ‘page not available message’.

9. Accelerators provides useful contextual links for common activities with text that you select on a webpage to perform such tasks as opening a street address in a mapping website or looking up the dictionary definition for a word. You can also choose the web services or websites that Accelerators use to handle different types of tasks.

10. A cleaner, organized way to manage all your Internet Explorer add-ons in simplified categories. This is one area, I had hoped the IE Team would have also applied to the Internet Explorer 8 Options dialog.

So those are just some of the things I like about Internet Explorer 8, there are many more I didn’t even mention such as Smart Screen Filtering and Compatibility View, improved restrictions for ActiveX Controls, enhanced add-on management, improved reliability (including automated crash recovery
and tab restoration), and enhanced support for accessibility

. Here are some things I would have like to see in IE 8:

• Download Manager - trust me, it needs it, I find it very handy in FireFox and Safari - Pause/Resume, Manage.
• Non-adjacent Selection of text on web pages.
• Customizable toolbar, I personally wouldn't mind if buttons such as Home, Feeds, Print, Page, Tools and Help were on a another toolbar group such as the Address bar group, I need more real estate for Tab's that's much cleaner. The new Favorites Bar in particular eats up a lot of space.
• List View layout for Options, similar to Office 2007 Options dialog. Also make the Advance settings a part of the list view Options, this includes: Accessibility, Browsing, International, Multimedia.
• Make Print Preview utilize a Tab instead of opening a separate window.
• Close button Tabs like Firefox.
• One unified 32-bit and one unified 64-bit installer.

Overall Internet Explorer 8 is a major update. Its an innovative release that introduces features users can immediately take advantage of. I hope to have a more formal review when its released along with my own performance test against Firefox 3 which I regularly use. Internet Explorer 8 supports Windows Vista Server 2008 32 and 64 bit, Windows XP Professional 32 and 64 bit, Windows Server 2003 32 and 64 bit.

Resources:

Internet Explorer Blog