OLAP Client

Upgrading to Analysis Services 2005 has put me in difficult position because of lack of good client from Microsoft and using Excel 2002/2003.

We are small shop and use Excel (Pivot Table) big time as client tool. We upgraded to Analysis Services 2005 and we all know on server side it helped a lot.

BUT, I was expecting Excel 2002/2003 to work "at least same" even it can not use new stuff in 2005. What's killing now is Excel 2002/2003 Pivot Table client using Analysis Services 2005 on server "Not able to display Measure Names in alphabetical order" - Big problem for users.

Upgrading to Excel 2007 is not easy at least let me put this way Upgrading to Analysis Services 2005 and Excel 2007 same time is not possible. It's a very big project with big risk if you are in financial company. It worse for us because we still use windows 2000 so upgrade OS for what? cube access.

Now what. Think about buying new client with one of these companies BO/Crystal, ProClarity etc. is also hard choice because it's not Excel and hard to sell to business group to pay for it when you ask for ......

one of the big business guy asked me yesterday "Can we go back to Analysis Services 2000 because I don't know what you got in Server but it's not giving me what I had before...."

what you will do?

- Ashok

Hello. This add in for older versions of Excel(2002 and 2003) might help:

http://www.microsoft.com/downloads/details.aspx?familyid=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

HTH

Thomas Ivarsson

|||

Hi Thomas,

As I was typing my question I knew this coming as first reply. This is not very helpful Add-in and just wandering any one using this in production let me know.

As you visit page first line you see this -

Microsoft does not provide any support for this solution accelerator and has no plans to release future versions. This add-in is not designed to work with with Office 2007.

That's ok

The number one reason to move to Analysis Services 2005 is to takeoff load from client. Remember when you run large query from client to Analysis Services 2000 your whole machine hang for some time. With excel "Cube Analysis" I don't know technologies behind but it's slow and uses client side memory. If you run large query and like to cancel (Esc key), you can not.

Interface is hard to use. Not looking for too much but mainly it's slow and using client memory.

One thing can help me if any one knows if this is possible. If it is possible to write VB code/macro which can run when excel file gets open and change the order in pivot table cube field list using Office APIs. Not looking for code just yes or no.

Thank you - Ashok

|||The Excel Add-in for Analysis Services is a good work around until you upgrade to Office 2007. There is no need for this tool in Office 2007 because PivotTable features in 2007 have been enhanced to include all the functionality that the Excel Add-In for Analysis Services includes. If you're thinking of using VB macros, I think it's a very bad road to take. You would be much better off using Reporting Services directly against the cube.|||

Thanks Martin. I understand using Add-in is good choice for now. Only issue I have with that it is taking client side memory and it will slow down not only cube query whole users box while they are using cube.

Reporting Services I can use what are the options we have in Reporting services to expose cube to end users? I know OLAP can be one data source to reporting services but then we need to design flat reports. Is there any control which allow users can drag drop fields and see data. I know about ReportBuilder.

Thanks again - Ashok

|||

Hi Ashok,

I wouldn't think that the Add-In would use more client-side memory than PivotTable services but I can't say I've done an exhaustive study either. ReportBuilder is another option but limited. If you have measure groups that are associated to dimensions at non-leaf levels, it likely won't be your solution. Additionally, users will only be able to construct reports using measures from a single measure group at a time so the produced reports are going to have to be simple. If your user's reporting requirements aren't complex, it's a good approach and very easy to implement and I'd recommend it.

|||

Thank's Martin for the comments.

Ashok, the add-in is a close you can get to the interface in Excel2007. It is not perfect but it works.

You can also choose to upgrade only the OLEDB-provider from 8 to 9 and use Excel2003 pivot tables. It will not look good but it will work.

If you choose to stay with older versions of clients you are left with that choice.

I have no advice of a cheap add-in for Excel that will solve your problem.

HTH

Thomas Ivarsson

|||

It may sound funny but it helped my cube users, when they really don't want any major change in the way they use current excel 2002/2003. so I had to find solution some thing they can accept.

I stored all my Analysis Services 2005 cubes measures from different measures groups in one xml file and created an excel form. Understanding all limitation in this way and giving some thing that can help cube users, trade off worked for this solution.

They have original Pivot table Field List to get Dimensions and for measures they open this excel form and get measures in much easy way. This is helping because we have over 200 measures.

Private Sub CommandButton1_Click()
'Try
For l = 1 To ListBox1.ListCount - 1
If ListBox1.Selected(l) = True Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[" & ListBox1.List(l) & "]"), ListBox1.List(l)
'MsgBox (ListBox1.List(l))
End If
Next l
'Catch ex As Exception
'MsgBox ("Error in getting records: " + ex.Message)

End Sub

Private Sub TextBox1_Change()
For l = 1 To ListBox1.ListCount - 1
If UCase(ListBox1.List(l)) = UCase(TextBox1.Text) Then
ListBox1.Selected(l) = True
Else
ListBox1.Selected(l) = False
End If
Next l
End Sub

Private Sub UserForm_Initialize()
Dim oXML As Object, oXSL As Object
Set oXML = CreateObject("MSXML.DOMDocument")
oXML.Load "T:\IT\Cubes\CubeMeasures.xml"
Dim t As String
For Counter = 1 To oXML.documentElement.childNodes.Length - 1
ListBox1.AddItem (oXML.documentElement.childNodes.Item(Counter).Text)
Next Counter
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

T:\IT\Cubes\CubeMeasures.xml File

<All>
<MeasureName>Measure 1</MeasureName>
<MeasureName>Measure 2</MeasureName>
<MeasureName>Measure 3</MeasureName>
<MeasureName>Measure 4</MeasureName>
</All>