OLAP Connectivity

Hello all,

We have Analysis Services 2000 with an OLAP cube. The cube has a role allowing access to all Windows domain users. From my development workstation we can connect to the cube, using Excel or a HTML page with the Office Web component, logged on as any user. However, from any other computer in the domain, and logged on with the same users, we cannot create a connection; the OLAP server is not visible!

The client computers are all the same: Windows 2000 Pro and the users all belong to the domain.

Why can we not connect? Has anybody ever experienced this sort of problem? We need to solve this by Monday, our deadline to show our OLAP application to the users. I would really appreciate somebody's help.

Thanks in advance,

Jerome Smith

Have all other workstations pivot table services installed, that is the local client that SSAS 2000 requires? It muste be the same version(service pack) as the server.

It is not sufficient with Excel installed on each client, you also need pivot table services.

You can download this add in for Excel: http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

HTH

Thomas Ivarsson

|||

Hello Thomas,

I thought that PTS was included in Office. At least that's what the manual for the MS-2074 course says, and I have always been able to connect to Analysis Servers from Excel and FrontPage, and in different companies.

The download you referred me to is actually not PTS but the Excel Add-In for Analysis Services, which affords enhanced functionality but does not solve the problem.

What I really need to do is connect to the OLAP cube from a HTML page with the OLAP web component, included in Office and also in FrontPage.

Thank you for your time.

Regards,

Jerome Smith

|||

Like I have told you earlier, PTS is not installed by default. Install it on the machines that do not work and see if this helps.

The download of the Excel add in will include PTS.

If you are trying to do a http-connection then it is a different story. Office web components are ActiveX and do not use http by default.

Regards

Thomas Ivarsson

|||

OK Thomas,

I'll try this.

Thanks,

Jerome

|||

Good luck. If this is not the problem return with more information and we can continue with other causes.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Well, actually I managed to deploy the application on almost all client PCs WITHOUT installing PTS. Just as I thought, PTS comes included with Office, so it is almost never necessary to install PTS.

The problem was never solved on some developer's notebooks in the IT department; we couldn't connect to the AS Server, no matter what we did (including installing the Add-In from the link you gave me).

It beats me why. I wish I knew. Any ideas?

Jerome

sql

OLAP connection problems

I have two systems setup with SQL Server 2000 + Analysis Services. Both servers have been patched with both SP3a patches.

I wrote a trivial command line app that tests connection strings through ADO.

On each system, I can connect locally perfectly using the connection string:
"Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

However, once I change "Data Source" to refer to the other system, the call to ADODB.Connection.Open throws a "System.Runtime.InteropServices.COMException" with message: "Unspecified error". (The simple app is written in C# and talks to ADO through Interop)

I've tried referencing ADO 2.1, ADO 2.5 and ADO 2.7
I've tried a "Provider" of "MSOLAP.2" as well as just "MSOLAP"
I've also tried a variety of different connection strings with many different permissions settings.

It is very frustrating to get an absolutely generic error message.

Any ideas?

Thanks in advance!Have you tried adding "User ID=xxx;Password=xxx"

or

"Integrated Security=SSPI"|||Yes, just retried all of them and they all result in the same System.Runtime.InteropServices.COMException, "Unspecified error"

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"|||Let me reclarify. This one works fine:

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

These (that reference another system) fail with System.Runtime.InteropServices.COMException, "Unspecified error":

adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"

I experience the same effect from two systems; either system can open its own database but can't open a remote one.|||Can't think of anything here...Maybe these links can give you some ideas?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_8wfm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_64fb.asp

OLAP Connection Error

Hi,
Background :
Am trying to connect to the OLAP Cubes from a Web Service.
The Web Service resides on my local machine, while the Analysis Server resid
es on a different domain.
Users have been created on the Analysis Server, and roles have been defined
for the cube am trying to access.
The processModel is configured with proper user id.
Problem :
When I do that I get an error "Undefined Error".
To my knowledge I have all the access to the Analysis Server, but still am n
ot able to connect to cube.
Could someone please help me resolve this as its very crucial and is hamperi
ng our to a great extent.
Thanks,
SrinivasThe user is created on the Analysis Server, and a role too has been defined
for the user, giving access to the cube.
For example :
Let us suppose my network user id is "ABC". Then a user called "ABC" is crea
ted on the Analysis server, and has been assigned a role.
Srinivas|||microsoft.public.sqlserver.olap is probably a better newsgroup for this, but
my initial guess is that your problem revolves around the statement below
"different domain". AS only supports Windows authentication in direct
client-server mode. You have a few options. First, you could use the old NT
matching-username password trick. If you have *local accounts* on both
systems with the same username password, then you will be able to connect
even if the two machines are in untrusted domains. You will have
administrative issues keeping the two passwords in-sync, but the connection
will go through OK. Second, if you are running on Enterprise Edition, you
can use http access. This will require you to run IIS on the AS server (see
BOL on how to configure it and look at the white paper
http://msdn.microsoft.com/library/e...ql_datapump.asp for
more technical details.
Hope that helps.
Dave Wickert [MS]
dwickert@.online.microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Srinivas" <svinjamoor@.worldbank.org> wrote in message
news:19E0A695-14C5-4E9C-8BB8-FB7C26A310BC@.microsoft.com...

quote:

---

> Hi,
> Background :
> Am trying to connect to the OLAP Cubes from a Web Service.
> The Web Service resides on my local machine, while the Analysis Server

---

resides on a different domain.

quote:

---

> Users have been created on the Analysis Server, and roles have been

---

defined for the cube am trying to access.

quote:

---

> The processModel is configured with proper user id.
> Problem :
> When I do that I get an error "Undefined Error".
> To my knowledge I have all the access to the Analysis Server, but still am

---

not able to connect to cube.

quote:

---

> Could someone please help me resolve this as its very crucial and is

---

hampering our to a great extent.

quote:

---

> Thanks,
> Srinivas

---

OLAP Cluster SQL 2005

Hi dear friends,
I must to install an cluster active-active on SQL Server 2005 with reporting
services and analysis services, can any one help me with references to do it
or links about how do it?.
I know that is no possible to do under SQL 2000 cluster.
A lot of thanks in advange
Clustering Documents
Original SS2K:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
Updated SS2K5:
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
http://msdn2.microsoft.com/en-us/library/ms143736.aspx
http://msdn2.microsoft.com/en-us/library/ms157293.aspx
Sincerely,
Anthony Thomas

"WalidSQL2k5" <WalidSQL2k5@.discussions.microsoft.com> wrote in message
news:E4ACCF02-33F8-4B80-9896-978D78854B49@.microsoft.com...
> Hi dear friends,
> I must to install an cluster active-active on SQL Server 2005 with
reporting
> services and analysis services, can any one help me with references to do
it
> or links about how do it?.
> I know that is no possible to do under SQL 2000 cluster.
> A lot of thanks in advange
>
|||I'm sorry; and for SS2K Analysis Service (yes; you can).
http://support.microsoft.com/kb/308023
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OwQHuEGOHHA.536@.TK2MSFTNGP02.phx.gbl...
> Clustering Documents
> Original SS2K:
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
> Updated SS2K5:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en[vbcol=seagreen]
> http://msdn2.microsoft.com/en-us/library/ms143736.aspx
> http://msdn2.microsoft.com/en-us/library/ms157293.aspx
> Sincerely,
>
> Anthony Thomas
>
> --
> "WalidSQL2k5" <WalidSQL2k5@.discussions.microsoft.com> wrote in message
> news:E4ACCF02-33F8-4B80-9896-978D78854B49@.microsoft.com...
> reporting
do
> it
>
|||Dear Anthony,
A lot of thanks i thing is more simple to install in this case than SQL
2000.
Best Regards
"Anthony Thomas" wrote:

> I'm sorry; and for SS2K Analysis Service (yes; you can).
> http://support.microsoft.com/kb/308023
>
> Anthony Thomas
>
> --
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OwQHuEGOHHA.536@.TK2MSFTNGP02.phx.gbl...
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
> http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
> do
>
>
|||Yes, quite a bit actually, but it is still doable, kind of like clustering
SQL Server on 6.5 or 7.0.
Anthony Thomas

"WalidSQL2k5" <WalidSQL2k5@.discussions.microsoft.com> wrote in message
news:5BD4CE2B-E878-4329-90F4-30E300059F61@.microsoft.com...[vbcol=seagreen]
> Dear Anthony,
> A lot of thanks i thing is more simple to install in this case than SQL
> 2000.
> --
> Best Regards
>
> "Anthony Thomas" wrote:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx[vbcol=seagreen]
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx[vbcol=seagreen]
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en[vbcol=seagreen]
to[vbcol=seagreen]
sql

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>

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>

OLAP based reports not rendering

I hope someone with a bit of knowledge about Analysis Services and Reporting
Services can give me some help here, please.
A client has the following setup:
One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services sp
2 and IIS 5.
One Windows 2003 server running Analysis Services sp 3.
--
1 role has been created in AS, with full access to everything. This is an AD
group, and we've tried adding all sorts of AD users to this group, to give
them access to the cube. We haven't tried adding "Everyone" and given it
full access.
--
When developing reports, we can access the OLAP cube with the usual NT login
of the developer. The report report renders perfectly on the dev box.
In Report Manager, reports based on SQL Server queries works perfectly.
Reports based on OLAP will work when run on the server running Reporing
Services, but NO OTHER computer. So when we access the Reporing Services
server with Remote Desktop, the OLAP report will render. From any other
computer, all in the same domain, it won't work.
The error message we get is basically "Cannot create a connection to data
source '<our datasource>'". But the "explanation" varies depending on what
user we try to use in our connection settings. The most common was "Database
'<our database>' could not be found." The other one was "Unknown error
E_(bunch of characters)".
So... any suggestions? I've found posts from other people who seem to have
had the same problem as ours, but no solution. We haven't figured out where
the problem is. Is it the credentials sent from Internet Explorer? Is it the
identity used in the connection string? Is it a new bug in SP 2? Reporting
Services has just been installed, and SP2 was installed immedeately. We
don't know if it would have worked without SP2, and I guess we won't find
out.
All help is really appreciated on this one!
Yours,
Kaisa M. Lindahloh yes please - we are having the same problems and noone seems to be able
to help !!!!
Please can someone help ?
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
>I hope someone with a bit of knowledge about Analysis Services and
>Reporting Services can give me some help here, please.
> A client has the following setup:
> One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> sp 2 and IIS 5.
> One Windows 2003 server running Analysis Services sp 3.
> --
> 1 role has been created in AS, with full access to everything. This is an
> AD group, and we've tried adding all sorts of AD users to this group, to
> give them access to the cube. We haven't tried adding "Everyone" and given
> it full access.
> --
> When developing reports, we can access the OLAP cube with the usual NT
> login of the developer. The report report renders perfectly on the dev
> box.
> In Report Manager, reports based on SQL Server queries works perfectly.
> Reports based on OLAP will work when run on the server running Reporing
> Services, but NO OTHER computer. So when we access the Reporing Services
> server with Remote Desktop, the OLAP report will render. From any other
> computer, all in the same domain, it won't work.
> The error message we get is basically "Cannot create a connection to data
> source '<our datasource>'". But the "explanation" varies depending on what
> user we try to use in our connection settings. The most common was
> "Database '<our database>' could not be found." The other one was "Unknown
> error E_(bunch of characters)".
> So... any suggestions? I've found posts from other people who seem to have
> had the same problem as ours, but no solution. We haven't figured out
> where the problem is. Is it the credentials sent from Internet Explorer?
> Is it the identity used in the connection string? Is it a new bug in SP 2?
> Reporting Services has just been installed, and SP2 was installed
> immedeately. We don't know if it would have worked without SP2, and I
> guess we won't find out.
> All help is really appreciated on this one!
> Yours,
> Kaisa M. Lindahl
>|||Provider=MSOLAP.2;Client Cache Size=25;Data
Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
Period=20000
Look into setting up Kerberos/trust betweent he servers and check the
connection string above "Kerberos"
--
Gary Foster
"Kathy" wrote:
> oh yes please - we are having the same problems and noone seems to be able
> to help !!!!
> Please can someone help ?
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> >I hope someone with a bit of knowledge about Analysis Services and
> >Reporting Services can give me some help here, please.
> >
> > A client has the following setup:
> >
> > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > sp 2 and IIS 5.
> >
> > One Windows 2003 server running Analysis Services sp 3.
> > --
> > 1 role has been created in AS, with full access to everything. This is an
> > AD group, and we've tried adding all sorts of AD users to this group, to
> > give them access to the cube. We haven't tried adding "Everyone" and given
> > it full access.
> > --
> >
> > When developing reports, we can access the OLAP cube with the usual NT
> > login of the developer. The report report renders perfectly on the dev
> > box.
> > In Report Manager, reports based on SQL Server queries works perfectly.
> > Reports based on OLAP will work when run on the server running Reporing
> > Services, but NO OTHER computer. So when we access the Reporing Services
> > server with Remote Desktop, the OLAP report will render. From any other
> > computer, all in the same domain, it won't work.
> >
> > The error message we get is basically "Cannot create a connection to data
> > source '<our datasource>'". But the "explanation" varies depending on what
> > user we try to use in our connection settings. The most common was
> > "Database '<our database>' could not be found." The other one was "Unknown
> > error E_(bunch of characters)".
> >
> > So... any suggestions? I've found posts from other people who seem to have
> > had the same problem as ours, but no solution. We haven't figured out
> > where the problem is. Is it the credentials sent from Internet Explorer?
> > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > Reporting Services has just been installed, and SP2 was installed
> > immedeately. We don't know if it would have worked without SP2, and I
> > guess we won't find out.
> >
> > All help is really appreciated on this one!
> >
> > Yours,
> >
> > Kaisa M. Lindahl
> >
>
>|||For our reports that use Analysis Services we had to do the following.
First, we ended up was using a single domain account hard-coded in the
data-link properties for the datasource. This account must have at least a
read-only role on the AS box in Analysis Manager. We were not allowed to use
Kerberos delegation as out SA team thought it was a security reisk on AD2k.
We may go this route once they have upgraded to AD2k3 as that release is
supposed to have the ability to provide scope limited Kerberos delegation.
Anyway, from here, it seemed like it would work. Our users are given access
or not based on their domain accounts being in a group ReportingServices on
the Report Server machine. What we found though was this still did not work.
Finally we found that after every deploy, we always have to go to the
web-based report manager, find the datasource that points to AS and check the
box "Use as Windows credentials when connecting to the data source" and then
retype the password and apply. After this our reports worked fine. Be aware
that this check box gets unchecked each time you deploy. I have not found
any other workaround. If you do, please let me know.
Hope this helps.
"garyf@.someone.com" wrote:
> Provider=MSOLAP.2;Client Cache Size=25;Data
> Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
> Period=20000
> Look into setting up Kerberos/trust betweent he servers and check the
> connection string above "Kerberos"
> --
> Gary Foster
>
> "Kathy" wrote:
> > oh yes please - we are having the same problems and noone seems to be able
> > to help !!!!
> > Please can someone help ?
> >
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> > >I hope someone with a bit of knowledge about Analysis Services and
> > >Reporting Services can give me some help here, please.
> > >
> > > A client has the following setup:
> > >
> > > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > > sp 2 and IIS 5.
> > >
> > > One Windows 2003 server running Analysis Services sp 3.
> > > --
> > > 1 role has been created in AS, with full access to everything. This is an
> > > AD group, and we've tried adding all sorts of AD users to this group, to
> > > give them access to the cube. We haven't tried adding "Everyone" and given
> > > it full access.
> > > --
> > >
> > > When developing reports, we can access the OLAP cube with the usual NT
> > > login of the developer. The report report renders perfectly on the dev
> > > box.
> > > In Report Manager, reports based on SQL Server queries works perfectly.
> > > Reports based on OLAP will work when run on the server running Reporing
> > > Services, but NO OTHER computer. So when we access the Reporing Services
> > > server with Remote Desktop, the OLAP report will render. From any other
> > > computer, all in the same domain, it won't work.
> > >
> > > The error message we get is basically "Cannot create a connection to data
> > > source '<our datasource>'". But the "explanation" varies depending on what
> > > user we try to use in our connection settings. The most common was
> > > "Database '<our database>' could not be found." The other one was "Unknown
> > > error E_(bunch of characters)".
> > >
> > > So... any suggestions? I've found posts from other people who seem to have
> > > had the same problem as ours, but no solution. We haven't figured out
> > > where the problem is. Is it the credentials sent from Internet Explorer?
> > > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > > Reporting Services has just been installed, and SP2 was installed
> > > immedeately. We don't know if it would have worked without SP2, and I
> > > guess we won't find out.
> > >
> > > All help is really appreciated on this one!
> > >
> > > Yours,
> > >
> > > Kaisa M. Lindahl
> > >
> >
> >
> >sql

Olap and reporting service problem

Hi.
This is an OLAP question.
I have a dimension which is a father-son dimension.
I built the cube in analysis server.
In the analysis browser I can see the dimension fine,
I hant my reporting services report to show that dimension.
The problem is that the report won't show me that dimension in
hierarchy tree (even if I check drill down) but flatten it down.
Does some one know if report service can handle those kind of reports?
Thanks.Hi Nick,
I am having the same problem. Have you managed to get an answer? Would love
to know how to solve this.
Anusha
"nicknack" wrote:
> Hi.
> This is an OLAP question.
> I have a dimension which is a father-son dimension.
> I built the cube in analysis server.
> In the analysis browser I can see the dimension fine,
> I hant my reporting services report to show that dimension.
> The problem is that the report won't show me that dimension in
> hierarchy tree (even if I check drill down) but flatten it down.
> Does some one know if report service can handle those kind of reports?
>
> Thanks.
>|||Hi Anusha,
I did managed to get an answer.
You can't do this!
RS is not really an OLAP tool and it doesn't know how to deal with
parent-child dimension and thats why it flatten down the dimension.
Currently, I saw that there isn't any simple way to solve this.
Sorry...
Anusha =D7=9B=D7=AA=D7=91:
> Hi Nick,
> I am having the same problem. Have you managed to get an answer? Would lo=ve
> to know how to solve this.
> Anusha
> "nicknack" wrote:
> > Hi.
> > This is an OLAP question.
> >
> > I have a dimension which is a father-son dimension.
> > I built the cube in analysis server.
> > In the analysis browser I can see the dimension fine,
> >
> > I hant my reporting services report to show that dimension.
> > The problem is that the report won't show me that dimension in
> > hierarchy tree (even if I check drill down) but flatten it down.
> >
> > Does some one know if report service can handle those kind of reports?
> >
> >
> > Thanks.
> >
> >|||Thanks, At least I can stop pulling my hair out!!!
Anusha
"nicknack" wrote:
> Hi Anusha,
> I did managed to get an answer.
> You can't do this!
> RS is not really an OLAP tool and it doesn't know how to deal with
> parent-child dimension and thats why it flatten down the dimension.
>
> Currently, I saw that there isn't any simple way to solve this.
> Sorry...
> Anusha ×?ת×?:
> > Hi Nick,
> >
> > I am having the same problem. Have you managed to get an answer? Would love
> > to know how to solve this.
> >
> > Anusha
> >
> > "nicknack" wrote:
> >
> > > Hi.
> > > This is an OLAP question.
> > >
> > > I have a dimension which is a father-son dimension.
> > > I built the cube in analysis server.
> > > In the analysis browser I can see the dimension fine,
> > >
> > > I hant my reporting services report to show that dimension.
> > > The problem is that the report won't show me that dimension in
> > > hierarchy tree (even if I check drill down) but flatten it down.
> > >
> > > Does some one know if report service can handle those kind of reports?
> > >
> > >
> > > Thanks.
> > >
> > >
>sql

olap and hierarchy problem

Hi.
This is an OLAP question.

I have a dimension which is a father-son dimension.
I built the cube in analysis server.
In the analysis browser I can see the dimension fine,

I hant my reporting services report to show that dimension.
The problem is that the report won't show me that dimension in hierarchy tree (even if I check drill down) but flatten it down.

Does some one know if report service can handle those kind of reports?

Thanks.I assume the dimension is a parent-child dimension. RS is not an OLAP browser. As you have noticed, it flattens the parent-child dimenion. In this presentation download, you will find two approaches for dealing with parent-child dimensions in RS. The first one uses the RS recursive support. The second bypasses the SSAS 2005 provider in order to use straight MDX to expand the hierarchy. Needless to say, both approaches have their limitations and we can expect the support of parent-child dimensions and UDM in general to improve in future releases.|||Hi Teo,
Thanks for your replay, Its hard to find help and support for Olap and for Olap and SSRS on the net.

Its very disappointing to hear that RS does not support OLAP (and for me, if it can't handle parent-child dimension it can't handle olap).

Looks like we will have to use some other programs for working with cubes and creating reports.

Thanks for your post and I will have a look at the link you supplied to check if by any chance there is a way to work with parent-child with out any work arounds.

Regards,
Roy.|||Just to clarify. I didn't say it doesn't support OLAP. I said that it flattens the parent-child hierachies when converting them to two-dimensional datasets.|||I know you didn't - I did ("and for me, if it can't handle

parent-child dimension it can't handle olap").

I think Its a must when working with olap.

Thank for the reply :)
|||

How would you go about using OLAP with SSRS?

Do you purchase OLAP?

I've seen it used, but I know little to nothing about it.

|||You can build OLAP solutions (cubes) with analysis services and then deploy the cube to the analysis server.

After that you can create a report and as a data source tell him to connect to the cube on the analysis server.

But as you can see, reporting services can really work with olap :(

OLAP and ASP.NEt

Hi,
I am looking at deploying an ASP.NET site which accesses an OLAP database - Microsoft Analysis Services
My question is how is this normally done:
Straight SQL / Stored Procedures3rd party Adhoc pluginAnything else
If there is anyone out there with experience in using ASP.NET andAnalysis Services I would be interested in hearing how they did it -
Database ArchitectureSystem Architecture
and anypitfalls etc...
Thanks in Advance
Jerry

Following article will provide you some initial thoughts;

http://www.aspfree.com/c/a/MS-SQL-Server/Accessing-OLAP-using-ASP-dot-NET/

|||Hey Jerry,
No stored procedures or SQL for OLAP databases.
We used Office Web COmponents which generates the multi-dimensional queries (MDX) for the user. The architecture is similar to the approach in the following article:
Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis Services and Office XP
http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/default.aspx

OLAP Analysis Services: Errors in Encryption Library

Hi!

I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:

"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."

Thanks in advance,

--Dimitris Doukas

You might have changed the account for your Analysis Server service.

To solve this problem you will have to stop Analysis Server. Remove all the files from the Data folder ( located under installation folder) and then start Analysis Server again.

Create a backup for your existing databases before doing that in case you cannot re-process them.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

It worked. Thanks a lot.

--Dimitris

OLAP Analysis Services - Remote Connection issue

Hi!

does anybody tell me how to connect remotely to an OLAP Analysis Server?

I have defined a local windows account on the OLAP server and added it to AS cube roles.

Despite that I still can't connect remotely to the AS server.

Thanks,

Chris

If this is about connecting with SQL Management Studio (or BI Development Studio) to Analysis Services 2005:

- you need to have a Windows account in the domain of the 2 machines (the AS2005 machine and the client machine from where you want to connect remotely)

- temporarily make the user a server administrator on the AS2005 (to eliminate the potential permission problems); you can do this with SQL Management Studio, right click on the server item -> Properties -> Security

- run SQL Management Studio on the client machine as that user ("Run As..." should be fine, no need to login to Windows as that user)

- if the connection fails, then please check the firewall(s), allow incoming connections for msmdsrv.exe (the AS2005 server)

- if the connection succeeds, then downgrade the user from server administrator; it is enough to grant read definition permission for the database intended; to do this, run SQL Management Studio from an AS2005 admin account and then add the user as a member of the database role

Adrian Dumitrascu

OLAP administrators group.?

hi all..

i have a problem about sql server analysis. i try to connect sql server for OLAP but i can't. sql server analysis give this error "unable to connect to the registry on the server or you are not members of OLAP administrator groups on this server" i use Windows Xp Pro and Sql Server Developer Edition. how can i solve this problem..

Please provide more details? Same machine for Analysis Manager and Analysis Services or different machines? Service Pack 3 or greater on both machines if different machines?

OLAP administrators group.?

hi all..

i have a problem about sql server analysis. i try to connect sql server for OLAP but i can't. sql server analysis give this error "unable to connect to the registry on the server or you are not members of OLAP administrator groups on this server" i use Windows Xp Pro and Sql Server Developer Edition. how can i solve this problem..

Please provide more details? Same machine for Analysis Manager and Analysis Services or different machines? Service Pack 3 or greater on both machines if different machines?

OLAP Administrators group in Analysis server

I'm trying to register server in my Analysis Manager. Help says that you should be a member of OLAP Administrators group . Plz tell me where is this group and how can I add members in this group.
Thanks a lot
SandyYou will find this group on the computer with analysis server running on, you can view members and edit the group via windows - administrative tools- computer management-users and user groups|||thanks a lot friend...I was about to lose my hopes...Thanks a lot...I have already done the same.|||Hi There...I did whatever you said...but when I try to register server I'm getting this new error...
"error: '\\server_name\MSOLAPRepository$\msmdrep.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."

Could you plz tell me what can be the problem and what could be the solution for the same.
Thanks in Advance
Regards
Sandy|||I have never come across this problem, so this is only guess, the problem could be that when you are registering a remote server, you don't have a permission to access msmdrep repository file which is by default located in Program Files\Microsoft Analysis Services\Bin directory on the server computer, I would try to check whether the file exists and whether you have a permission to access it|||I have put myslef in the OLAP Admin group along with "Everyone", "Administrators" and I still get the following error message: Unable to connect to the registry on the server
(server name) or you are not a memeber of the OLAP
administrators on this server."
I am running this on my local machine and am not trying to connect with another server. I am running sql server 2000. Any ideas?

Landon|||Sandy - I think I came across that problem and it's what mojza is saying. Are you trying to get to the Analysis Manager remotely? If so, try logging into the machine where AM is running.

prdbrat - Your problem sounds like the registry for AS was migrated to SQL Server. If so, make sure you are able to access the OLAP Registry on the SQL Server. I think I had to add my ID to our SQL server before this error went away.|||I know! I know! I had the same problem. Go to your registry under olap server --> delete the remoterepositoryconnectionstring which is a regz_binary.

Than try your analysis manager again to register. It worked for me.

OLAP / Analysis services version checking by client

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

Hello,

I'm going to move your question to one of the SQL Server forums were they should be able to help you.

Daniel Roth

|||

Erkki wrote:

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

I would like to know that too. Anyway, how is it possible to verify the version of OLAP ? How can it be done? Maybe to somehow connect to server where the olap is being used?

olap

hi,
i need "microsoft(r) sql server(tm) 2000 analysis services step by step" in digital,can you help me ?
shawnlee@.cena.com.cnI don't know of such a beastie. You might try to contact Microsoft Press to see if they can help you, but I think it would be easier to just buy the book than to try to get a digital copy.

-PatP

Offline cube cannot open

I built a cube by Microsoft SQL Server 2005 Analysis Services and deploy into the server. The deployment was successful. I extract the cube by Microsoft Excel 2007 to view data. That also successful. Then I built an offline cube from there. I can successfully create the offline cube. And store into a thumb drive and open the offline cube by another pc through microsoft excel 2007/2003 both. I cannot open the offline cube and got the error as 'This datasource contains no visible tables'... and in another pc, it is asking the log in credentials.

The offline cube is disconnected from the datasource and can open anywhere else right? Then how can I got all these issues? Please help me on this. Thanks.

> The offline cube is disconnected from the datasource and can open anywhere else right?

Yes, the offline cube is disconnected from the relational data source, unless you have ROLAP cubes or partitions.

Adrian

|||Thanks a lot Adrian for your reply. But, how can I deploy the cube without partition? Can you eloborate more on this please... thanks|||

If the storage mode is ROLAP for your cube or a partition or dimension that you will query from Excel, then you will need to have access to the relational data. To solve this, use the MOLAP storage mode. To check what storage mode you have, use either SQL Management Studio or BI Development Studio. With SQL Management Studio, use the properties dialogs to check each dimension, cube and partition. With BI Development Studio, connect to the database in online mode (File -> Open -> Analysis Services Database).

Adrian

|||

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

|||

grmohan68 wrote:

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

Offline cube cannot open

I built a cube by Microsoft SQL Server 2005 Analysis Services and deploy into the server. The deployment was successful. I extract the cube by Microsoft Excel 2007 to view data. That also successful. Then I built an offline cube from there. I can successfully create the offline cube. And store into a thumb drive and open the offline cube by another pc through microsoft excel 2007/2003 both. I cannot open the offline cube and got the error as 'This datasource contains no visible tables'... and in another pc, it is asking the log in credentials.

The offline cube is disconnected from the datasource and can open anywhere else right? Then how can I got all these issues? Please help me on this. Thanks.

> The offline cube is disconnected from the datasource and can open anywhere else right?

Yes, the offline cube is disconnected from the relational data source, unless you have ROLAP cubes or partitions.

Adrian

|||Thanks a lot Adrian for your reply. But, how can I deploy the cube without partition? Can you eloborate more on this please... thanks|||

If the storage mode is ROLAP for your cube or a partition or dimension that you will query from Excel, then you will need to have access to the relational data. To solve this, use the MOLAP storage mode. To check what storage mode you have, use either SQL Management Studio or BI Development Studio. With SQL Management Studio, use the properties dialogs to check each dimension, cube and partition. With BI Development Studio, connect to the database in online mode (File -> Open -> Analysis Services Database).

Adrian

|||

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

Offline Capabilities

Hello All,

I have developed a solution where the user can generate pivot based reports using excel with Analysis Services in the background.

Now the requirement is that the user should also be able to go offline.

I want to understand how can this be done. Given the fact that the data in the cube is of financial nature so we need to have very secure solution.

(I read about local cubes, but how will I secure them? today the customer is generating excel sheets and protects it with windows DRM.... the problem with that solution is that we have too much data to put in excel).

Securing local cubes:

http://sqljunkies.com/WebLog/mosha/archive/2006/03/08/local_cube_password.aspx
|||

Passwords don't provide a good security solution because they can be shared, they are known to the person.

Let's say an employee who had the excel sheet is leaving the company, he takes it along to his new employer and uses it there because he has the password.

Is it possible to DRM (digital rights management) protect a local cube. So that everytime someone opens a cube it makes contact to the DRM server to decide what can be done and read/write to the cube?

As I said that our security requrirement is very critical because the cube contains a lot of financial data.

regards,

Abhishek.

|||There is no DRM integration with local cube files.