A while ago I blogged about making the decision on whether to attend the Microsoft BI Conference or PASS. Well, the decision has been made for me: I forgot to submit a session abstract for the BI Conference but got my session accepted for PASS, so it's PASS I'm going to. You can see all the content on the BI track at PASS here:
http://summit2008.sqlpass.org/program-sessions.html#business
I've never been to one of the big PASS summits in the US before so it'll be quite exciting.

My session is called "Creating an SSIS, SSAS and SSRS Monitoring Solution with SSIS, SSAS and SSRS" - basically how to build a BI solution that allows you to capture and analyse the performance and usage of your BI solution using your favourite Microsoft BI tools. It's a topic that people have tackled bits of in the past but I want to be a bit more ambitious and demo a full, end-to-end solution. If anyone has any thoughts or tips to offer on what I should be showing then please leave a comment!

Meanwhile I see that the Microsoft BI Conference still hasn't published its agenda. To me this is poor marketing - how can you make a decision to attend a conference without seeing the agenda? It's like going into a restaurant without seeing the menu.

I was using SQLMS with AS2008 the other day and noticed when right-clicking on the server node in the Object Explorer window some policy-based management options seemed to be available:

Now policy-based management is one of the new features of the relational engine in SQL2008; you can read more about it here:
http://www.mssqltips.com/tip.asp?tip=1492

For a moment I was excited, then I took a look at what facets were actually available for Analysis Services: there's only one, and that gives you the same options to set as you got in the old Surface Area Configuration tool. Maybe in a future release we'll get some more functionality - it would be great to be able to enforce policies like "Always associate a partition with an aggregation design" and so on.

Yes, it's time for yet another SQLBits! SQLBits III (aka SQLBits Cubed) will be taking place on Saturday September 13th at the de Havilland Conference Centre in Hatfield, and we are looking for sponsors and speakers.

If your company is interested in sponsoring SQLBits, please drop an email to Tony Rogerson at tonyrogerson@torver.net and he'll send you a sponsor pack. With over 300 SQL Server professionals attending the last two conferences, if you've got a SQL Server-related product or service to sell then SQLBits sponsorship is a great way of reaching your target audience. Remember, it's only through sponsorship that we can keep SQLBits going as a free event!

If you're interested in speaking on any SQL Server related topic (and that includes BI - SSAS, SSRS, SSIS, or PerformancePoint), then you can submit a session on the SQLBits site here:
http://www.sqlbits.com/information/sessionsubmission.aspx

The session submission deadline is July 4th. We actively encourage new speakers, so if you've never presented a session at a conference before but it's something you think you'd like to do, then why not give it a try? If you've got any questions, drop Allan Mitchell an email on allan.mitchell@konesans.com

For your listening pleasure: the latest edition of the SQL Down Under Show is me talking to Greg Low about MDX and Analysis Services. You can download it here -

http://www.sqldownunder.com/PreviousShows/tabid/98/Default.aspx

One of the many minor improvements in AS2008 MDX is the ability to specify a separate caption for calculated members. Vidas already did a good writeup of the other new properties that you can specify on calculated members, but for me the ability to specify captions is interesting because we should really be setting captions on calculated members as a matter of best practice.

First, how does it work? Well, it's very straightforward - here's an example query and output:

You can see that although we've declared a calculated measure whose unique name is measures.test, when the query is run the end user sees the caption "This is a test measure". Note you have to use a hard-coded string as a caption, you don't seem to be able to use an MDX expression.

But why should we be doing this? Because calculated member captions are something that change quite often, especially during development, and if you don't use the caption property then you'll end up having to change the unique name of the calculated member all the time - and this of course will break any other calculations in the MDX Script that refer to this calculation, and worse it will also break any existing queries in reports that refer to this calculation. Also since calculated member captions can be quite long, the ability to write MDX expressions that refer to a much shorter unique name means your code will be much more concise and readable.

Of course with real members on non-measures dimensions, if you have specified different columns for the Key and Name properties of the attribute hierarchy you already get this separation between the unique name of the member and the caption that the user sees. The only missing bit of functionality now is a separate Caption property for real (as opposed to calculated) measures.

Is it that time of year again? Yes, here's the link for the latest BI Survey:
http://www.eu-survey.com/BI_8/BI.asp?lan=1&lin=23

Here's the blurb:

We would very much welcome your participation in The BI Survey, conducted annually by Nigel Pendse. This is the largest independent survey of OLAP users worldwide. The Survey will obtain input from a large number of organizations to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical users, as well as vendors and consultants, are welcome.

The BI Survey is strictly independent. While vendors assist by inviting users to participate in the Survey, the vendors do not sponsor the survey, nor influence the questionnaire design or survey results. You will be able to answer questions on your usage of a BI product from any vendor. Your data will only be used anonymously, and no personal details will be passed to vendors or other third parties.

As a participant, you will not only have the opportunity to ensure your experiences are included in the analyses, but you will also receive a summary of the results from the full survey. You will also have a chance of winning one of ten $50 Amazon vouchers.

I heard this was going to be possible back in March, but now I've seen it in RC0 - you can now set connection string properties in SQL Management Studio when opening an MDX query window. Just click the new MDX query button, then click Options and you'll see a third tab:

Although it's much less common to need to set connection string properties, there are still a few scenarios where it's useful. One example is the 'Cube' connection string property, which allows you to write session scoped script assignments in the way Mosha does here:
http://sqlblog.com/blogs/mosha/archive/2008/06/08/mdx-answer-to-nextanalytics-challenge.aspx

Another good example is when you're using the Roles or EffectiveUserName properties to test out how queries behave with security.

There's going to be another one of the UK SQL Server Community's BI Evening events happening at Microsoft UK's offices at TVP in Reading on the evening of July 17th. It's been far too long since the last one, I know - we've all been a bit distracted by SQLBits (and there's going to be another one of those on September 13th - do you or our company want to sponsor it?).

You can sign up here:
http://www.sqlserverfaq.com/

Here's what we've got lined up:

"PEL vs MDX - what are the differences between the two languages?"
Jeremy Kashel, Adatis

Jeremy Kashel from Adatis  presents an introduction to the PerformancePoint Expression Language (PEL). The content will be geared towards MS BI developers, and will highlight the differences between PEL and MDX, with the aim that those with MDX experience will be able to make a fast start with PEL

"Using Excel Services with Analysis Services and MOSS"
Jeremy Kirkup, TAH

If you are just starting to explore delivering BI solutions with Excel Services and Sharepoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part.
The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through.  However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services.

Andrew Fryer has the details here:
http://blogs.technet.com:80/andrew/archive/2008/06/06/sql-server-2008-rc0.aspx

If we've got a release candidate, then this means RTM isn't too far away...

While Jamie (who I saw through a window today, although I didn't manage to say hello) is wondering about whether SQL Server Data Services will ever include a cloud-based OLAP engine, Panorama have just announced their own equivalent called Panorama PowerApps:
http://www.panorama.com/blog/?p=111
http://panorama.com/powerapps/

And guess what, it's queryable through MDX! That means that not only will you be able to query it through Google Apps but also Excel. I've signed up to be a beta tester, so I'll blog more when I have a chance to check it out.

Meanwhile, in the parallel universe inhabited by Oracle users, a few weeks ago Mark Rittmann interviewed the architect for Oracle Business Intelligence Enterprise Edition, Phil Bates, and asked readers to suggest some questions for him. Of course I couldn't resist asking about MDX, even though I got a very nondescript response:
http://www.rittmanmead.com/2008/06/04/phil-bates-answers-your-questions/

Interesting that many of the other questions concerned data access (eg "Will Oracle reverse Hyperion’s undocumented strategy for making it almost impossible to get data out of Essbase successfully?") as well.

Hands up who remembers OLAP@Work? If you do you've been working with Analysis Services for a long time, back when it was still OLAP Services... for those of you who don't, it was one of about four options you had if you wanted a client tool circa 1999; it was an Excel addin and it was pretty good. Anyway, I've just seen this article on Intelligent Enterprise on what Ward Yaternick, the guy who founded OLAP@Work, has been up to since leaving Business Objects (which bought and eventually killed OLAP@Work):
http://www.intelligententerprise.com/blog/archives/2008/06/bi_innovation_f.html

He's been working on something called nextanalytics:
http://www.nextanalytics.com/

Poking around on the site it looks quite interesting; certainly there are lots of mentions of MDX so I guess it supports Analysis Services as a data source (although it supports a lot of other data sources too). The key thing is that it allows you to create complex queries and calculations using a scripting language. Clearly this scripting language allows you to do the same kind of things you can do with MDX and indeed one particular entry on the nextanalytics blog caught my eye:
http://www.nextanalytics.com/component/option,com_myblog/Itemid,342/show,Can-a-business-intelligence-product-be-used-to-answer-analytic-questions-.html/

I was about to leave a comment when I saw that Mosha had beaten me to it. Mosha's right that contrary to what the original entry says, what Ward is describing is certainly possible in MDX, but Ward also has a point that it's not something that someone with an average knowledge of MDX could accomplish. Can nextanalytics prove itself to be easier to use than MDX? Time will tell. I'll have to download the open source version of it (available here: http://www.codeplex.com/nextanalyticsOS) to try it out. When I have a spare moment, of course, which at the current rate is going to be some time next year.