Search results matching tags 'Analysis Services', 'PivotTable', and 'Excel'

Gemini is now PowerPivot

sqlbi — Fri, 23 Oct 2009 00:59:00 GMT

The project codenamed Gemini has an official name now: PowerPivot. The complete name should be “PowerPivot for Excel 2010”, but PowerPivot is simpler. I didn’t like that name yesterday, but today it seems better.

Microsoft announced this name at the Microsoft SharePoint Conference 2009 held in Las Vegas. As usual, demos are good and people are impressed. But, remember, this is a SharePoint conference, not a SQL Server (or Business Intelligence one). What I mean is that many of the demos shown this week are not so different from what have been shown 4 or 5 years ago. And, guess what? Microsoft never finished the job of the vision where UDM (Unified Dimensional Model) should have been the center of the universe and every reporting query should have posted to this central repository of information.

Today, the solution is giving power (using PowerPivot) to the end user, integrating the visualization with every single flavor of Office 2010 (Visio included!). You know, only SQL Server 2008 R2 has a Reporting Services component that makes it possible to display data in maps (something I did in 2000 with MapPoint – why they didn’t implemented a simpler way to integrate geographical data of a cube in MapPoint 8-9 years ago?). A lot of things could have been possible in 2002, or at least in 2005, or with Excel 2007, but unfortunately many times I had to abandon a possible way to show data just because there was the lack of the “last mile” to show data. And this created a lack of credibility in the Microsoft client tools for BI.

So, to be honest, today I’m skeptical. I don’t want to deceive my customers showing wonderful demos just to get customer complains when something will not work and MS Support answer will be “it’s not a bug, it’s by design”. This time I’ll wait and I will not risk my face. Probably, I’m wrong – but, you know, I don’t see any evidence that grants me that this time things will be different. We still have a big wave of new Office products, the next one will be in 2013 or something like that and if something is not good in 2010, you will have to wait 3 years or you will have to write some work-around just to make your solution working. I mean, I can survive to a problem in Excel where the color of a border doesn’t display well, but my customer will not be so happy if the drillthrough will not work as expected (it’s just an example – not something that has real issues).

My hope is that PowerPivot will have a shorter release cycle, with a v2 in 2011 and a v3 in 2012 (or they could be deployed with Office 2010 Service Packs if there is some version dependency). But, at this time, we don’t have any information about that. Technically, it should be possible to do, because it’s an AddIn separated from Excel codebase. Thus, it’s a “political” decision. I hope that a clear roadmap for PowerPivot will be announced. Shorter release cycle. Otherwise, too many missing feature will delay its adoption until (at least) the next version of Office.

Microsoft, please, don’t repeat the same mistakes.

Excel 2007 PivotTable with calculated members

sqlbi — Wed, 31 Jan 2007 17:52:00 GMT

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.

UPDATE February 20, 2010: SQL Server 2008 R2 and Excel 2010 have solved the issue - you need both. Chris Webb posted a description of the fix.

Display the MDX query of an Excel 2007 PivotTable

sqlbi — Thu, 18 Jan 2007 10:51:00 GMT

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!