SQL BI

Excel 2007 PivotTable with calculated members

Today I had a big trouble with one customer.

We migrated an AS2000 solution to SSAS2005, making a lifting to dimensions just to consolidate into attributes what were indipendent dimensions in the old cube.

One of the old dimensions contained only one "dummy" member, and a series of calculated members. With Excel 2003, you had no troubles in selecting only the calculated members you wanted from those available. With Excel 2007, the user experience is bad.

First of all, you need to change PivotTable options to enable the display of calculated members (check PivotTable Tools / Options / Options / Display / Show calculated members from OLAP server). When you do that, you choose to get all calculated members available from a dimension/attribute. Now, if you have 100 calculated members into an attribute, chances are that you want to select only one or two of those members. Unfortunately, Excel 2007 shows you a list of disabled checkbox that cannot be used to select only the members you want.

I found only one workaround to this: use the "Convert to formulas" command into OLAP Tools menu and then delete all unwanted members.

I understand the reason for this behavior: Excel 2007 generates a MDX query using the AddCalculatedMembers function, that returns all calculated members.

However, I hope that this will be fixed in a future release and possibly with a Service Pack, because it's a breaking change (at least from the user point of view) from the previous version.

Analysis Services 2005 Performance Guide

This is the paper we waited for a long long time: the Analysis Services 2005 Performance Guide is available for download from MSDN downloads. This paper is 120 pages of really precious informations. I don't have time to read all now, but I have found that Appendix C mention the Aggregation Utility, that should be included in AS2005 SP2 samples when it will be released, but it is not available in the December CTP.

Great news, now we need the SP2 to be shipped!

MdxScriptUpdater

I wrote a simple C# class that is useful to manage custom MDX scripts (like custom members definitions) on existing cubes, without the need of a new deployment.

I named it MdxScriptUpdater and it is freely available here: http://www.sqlbi.eu/mdxscriptupdater.aspx

This is an excerpt from the whole article that describes the library (and some trick about AMO library):

MdxScriptUpdater is a simple C# class that simplifies updating MDX Scripts into a cube in a production environment. MdxScriptUpdater is provided in form of a sample source code as is.

There are a lot of scenarios where nightly batches would update parts of the MDX Script of a cube. For example, I had a customer with a calculated member for each year with data. We can define the calculated member by hand, but we would need to remember to create a new one each year. Another case is the customer that wants to consolidate his own calculated members, without requiring a new cube deployment.

AMO (Analysis Services Management Objects) is the API that allows you to do these kinds of modifications on a running cube. The problem arises when you want to preserve existing MDX scripts, adding and removing only your own. We tried to solve this problem in a generic way.

I would like to get feedback and also possible bugs...

Slow HTTP access to AS2000 with Win2K3 RTM without SP1

Thanks to Microsoft support for Analysis Services (one of my custored opened an incident), I just discovered an issue that is not indexed anywhere in Google, so I write a post hoping to save time of the next unlucky user...

I you use HTTP access to Analysis Services 2000 and you have Analysis Services installed on Windows 2003 Server RTM, there is a problem with IIS 6.0 described in KB840875. Apparently it is not related to Analysis Services, but when you use HTTP access you are using an ASP page (MSOLAP.ASP) that indirectly uses the API described in the kb article. This issue is solved by Windows Server 2003 SP1.

For this reason, if you have Windows 2003 Server RTM without SP1 and you want to access to Analysis Services via HTTP, you can rely on two choices:

• Upgrade the server to Windows Server 2003 SP1. This is the suggested one.
• If you cannot upgrade to SP1, you can use a workaround modifying the MSOLAP.ASP page: simply change the Response.Buffer line into this:
  

  <%Response.Buffer=TRUE%>
  
  The original value is FALSE. I found only one reference to this fix into an old post in the newsgroups, which didn't correlate the problems (probably it was something else, because at that time Win2K3 Servere were still in beta test.

Analysis Services 2005 is not affected by this issue.

Reporting Services required features

Almost ten years ago (...ouch!) I visited Australia and I had a real love for Sydney.

I got these memories tonight because I found a detailed list when I was looking for some existing request for new features in Reporting Services. One is the Rich TextBox feature. Another is the lack of support for styles: there are many possible implementations, like ASP.NET 2.0 master pages. I found these suggestions on a page full of requests, very well detailed, supported by a Sydney company that also maintain a similar list for many other products very dense but also really understandable. Great work, guys!

I hope that Microsoft will improve support for embedded images into http://connect.microsoft.com too, allowing to easily describe a trouble or a requested feature.

Display the MDX query of an Excel 2007 PivotTable

Often end users use Excel to navigate into the cube. Then they may ask you to produce a report using the same data. It would be useful to get the MDX query used by Excel. Moreover, Excel 2007 offers a good MDX quality of the produced query against Analysis Services 2005 (much better than Excel 2003).

I don't know why Excel doesn't have such a function. I created a simple Excel macro that add an item to the PivotTable menu. A code that add a button in the Ribbon would be very welcome!

If you want to add these macro for all Excel sheets, you have to modify the PERSONAL.XLS file contained into "Documents and Settings\[Username]\Application Data\Microsoft\Excel\XLSTART".

This is the macro Workbook_Open that is executed when you open a workbook.

Private Sub Workbook_Open()
   Dim ptcon As CommandBar
  
   'See the following for list of menus in excel
   'http://support.microsoft.com/support/kb/articles/Q213/5/52.ASP
   'Title: XL2000: List of ID Numbers for Built-In CommandBar Controls
   Set ptcon = Application.CommandBars("PivotTable context menu")

insertDisplayMDX:
   Dim cmdMdx As CommandBarControl
   For Each btn In ptcon.Controls
       If btn.Caption = "MDX Query" Then GoTo doneDisplayMDX
   Next btn
  
   ' Add an item to the PivotTable context menu.
   Set cmdMdx = ptcon.Controls.Add(Type:=msoControlButton, temporary:=True)
  
   ' Set the properties of the menu item.
   cmdMdx.Caption = "MDX Query"
   cmdMdx.OnAction = "DisplayMDX"
      
doneDisplayMDX:

End Sub

And this is the DisplayMDX subroutine, that you can insert in a separate module.

Sub DisplayMDX()
    Dim mdxQuery As String
    Dim pvt As PivotTable
    Dim ws As Worksheet
  
    Set pvt = ActiveCell.PivotTable
    mdxQuery = pvt.MDX
   
    ' Add a new worksheet.
    Set ws = Worksheets.Add
    ws.Range("A1") = mdxQuery
End Sub

When you right-click the PivotTable, the last item of the menu will be "MDX Query". If you click it, a new worksheet is created with the query in A1 cell. This is the fastest way to copy and paste it into other programs. I would like to be able copying directly into clipboard, but I still haven't found a reliable way to do it. If someone has some good idea, please contact me or comment to this post.

I attached a copy of a PERSONAL.XLS for the lazy of you!

Microsoft Business Intelligence Conference 2007 website live

The Microsoft Business Intelligence Conference 2007 website is now live.

The Microsoft Business Intelligence Conference 2007 is scheduled on May 9-11, 2007, in Seattle, WA. It's not a "traditional" Microsoft technical conference like TechEd and PDC: it's targeted more on project managers, decision makers, IT manager... and, yes, also IT professionals, but this indicates to me that only a part of the conference schedule will be dedicated to the (inside) technical side of Microsoft BI Products.

There is call for presentations dedicated to customer success story (I have a few possible proposals, but I will ask them before) and a section for customer awards nominations.

At this time there are no details on session topics, but only a track page containing only track names.

Strangely to me, Microsoft has (still?) not considered to give visibility to user communities, and I think this is not a good decision. In the last years Microsoft has made big investments in an effort to be more open toward their customers. "User communities" (with this term I mean peer-to-peer activities in general, ranging from blogs, forums, user groups, actions and sites that share the knowledge in general) in the BI market are still a rarity and probably only Microsoft has a minimum number of them. This is not to say that I would like to be invited, but only that a BI professional does not have a reference for an "event to attend" where he can will meet his peers. PDC is too much developer oriented, TechEd it too much general purpose, a SQL Conference is too much backend-oriented (we also need the client side, thus an integration with Office is necessary). The name "Microsoft Business Intelligence Conference" would seem perfect for this. I hope Microsoft will not lose the opportunity to leverage on existing peer networks.

Format String on linked measure groups

When you create a linked measure group into a cube, the cube you are creating inherits properties of original measures by copying values into the new cube. If you change the format string into the original cube, you don't modify automatically the linked measure. This is good.

Unfortunately, if you want to update or change the format string of the linked measure, you can't by Cube Editor. You have to manually edit the cube file, modifying the FormatString element value into XML by hand.

I forwarded a feedback on this, vote it if you agree it's a useful feature.

Improve measures display in SSAS 2005

I just submitted a suggestion feedback to SQL Server development team.

It is very bad for the user to be forced to see measures under their physical measure groups. There are a lot of reasons for which a customer would like to see measures organized in different folders.

The most important is related to distinct count measures: each time you define one distinct count measure, a new measure group is created. This physical definition has nothing to do with logical grouping of measures.
Display folder is useless, because it defines only a subfolder under the measure groups.

A possible workaround (define only calculated measures as visible items for the end user) has an unacceptable limitation: you loose the ability to drillthrough cells.

A fast workaround could be to enable the drillthrough, offering a way to define for each calculated measures the corresponding cube space to drillthrough.

A better solution would be to decouple the logical presentation of measures from the physical implementation of them.

If you agree with my suggestion, please vote the "Change measures display: decouple them from measure groups to the client" and the "Enable drillthrough on calculated measures" suggestions (follow the links).

If you don't agree.. well, you never had real users really working with distinct count measures! :-)

Microsoft Business Intelligence Conference (May 9-11, 2007 - Seattle WA)

This is the news for 2007: the Microsoft Business Intelligence Conference will be in Seattle, May 9-11 (I got the link thanks to Chris Webb's post).

I suppose we will see PerformancePoint Server in action and may be some anticipation of Katmai. I'm curious to see if Microsoft will follow the tradition of his technical conferences (like TechEd and PDC) or will be more marketing oriented.

Registrations will be open by February, by now you can only save the date.