ODBC Isolation level: March 2012

Friday, March 30, 2012

OLAP cube - formula length

dear all,

does anybody know the maximum length for an mdx formula for a calculated member in SSAS?

Kind regards

Frederikm

No hard limit in SSAS 2005.|||

i find this kind of hard to believe...

I have a formula with over 4000 characters... processing this cube is impossible

the formula is a formula in the form of iif(errorcheck, 0, iif(errorcheck,0... etc

if i use the basic formula without the iif's the cube processes without any problems....

Kind regards

|||There is a syntax error in your formula somewhere, and the error message should give you an idea about location of this error. The syntax error is the real cause of why your cube doesn't deploy, not the length of the formula.|||

about the max length: is this tied to any service pack version? we are using sp1
also the thing is that there is no error in the formula,
for when we use it in a normal mdx query it works without any problem...
only when in the cube things go wrong

|||The fact that it works inside MDX query means that there is no problem with the length. What is the exact error message you are getting when you are trying to deploy cube with such calculated member ?

OLAP cube - errorhandling

Dear all

i have inherited a SSAS 2k5 cube with a large number of calculated members.

I have a number of problems:

sometimes the calculated amounts are very very small like: 0.00000000000034.

This however, does not play nicely with excell... heximal display and so on. anyone know of a way to show these as being 0?

secondly

I have a number of calculated members who themselves contain calculated measures.

When one of their parents is 0, they resort into div/0 erros, etc

For my current project it is unacceptable that these errors are visible for the end user

is there any way that i can catch all the errors at once? or do in eed to include iif statements in the calculations of each calculated member?

Hi, unfortunately you need to put IIF statements around them. For example direct queries from the cube.

with

member measures.test as

0.000000003

member measures.test2 as

IIF(measures.test<0.00001,0,measures.test)

member measures.test3 as

member measures.test4 as

IIF(measures.test3=0,0,measures.test/measures.test3)

select {measures.test2,measures.test4} on 0 from [mycube]

or calculated members on the cube

CREATE MEMBER CURRENTCUBE.[MEASURES].[PercentUnavailable] as

IIF([Measures].[Total Count]=0,0,[Measures].[Total Unavailable])*100/[Measures].[Total Count]),

visible=1;

John

|||

Alternative which will perform better in cases where you have a large number of these is to use the format statement

member measures.test as

0.000000003,format='#.00'

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 in c#

Hello,

I'm trying to connect to my OLAP Cube with a c# project.

This is my code:

private void Page_Load(object sender, System.EventArgs e)

{

ADODB.ConnectionClass conn=new ConnectionClass();

conn.Open(ConfigurationSettings.AppSettings["connection_string"].ToString(),ConfigurationSettings.AppSettings["user_conn"].ToString(),ConfigurationSettings.AppSettings["password_conn"].ToString(),0);

try

{ADOMD.CellsetClass cs=new CellsetClass();

cs.ActiveConnection=conn;

string szMDX="";

szMDX += "SELECT ";

szMDX += "{[Measures].members} ON COLUMNS,";

szMDX += "[CAMPAGNE].[Dim CAMPAGNE].members ON ROWS";

szMDX += " FROM [My Roi]";

cs.Open(szMDX,conn); // here there is an error

}

I've receive this error message:

"Impossible to use this connexion to make this operation, the connexion is closed or invalid in this context".

Can you help me please, because I can't find the problem.

Thanks,

Have you tried changing the provider in the connection string to MSOLAP.1 or MSOLAP.2 and did you verify that either TRUSTED_CONNECTION=YES or INTEGRATED SECURITY=SSPI is included?

OLAP Connection error

Below code at web.config file, when i change Data Source to other pc name....an error occur 'The data provider didn't supply any further error information'...
let said change the datasource 'localhost' to 'webserver' error will occur...so i cannot run the program at my pc and link the olap database at server...

<add key="OLAPConnectionString" value="Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=Foodmart 2000;" />The other PC that you are trying to connect should have the same users and user rights as the first one.

In addition to that, the Analysis Server on your second PC should know about these users as well.

I found that security with Analysis Server is very tight.

Good Luck

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 - your suggestions

Hello,

I am trying to Upgrade my AS 2000 to AS 2005 but end up hitting wall. Our clients is Excel 2002/2003 on Windows 2000 OS. I managed to convince group about advantage to moving AS 2005. At last I managed to convince advantage to moving Excel 2007 which added new project of upgrading office 2007 in selected workstations. Then I find out we can not install Excel 2007 in Windows 2000. Upgrading Clients workstations to XP is too much to ask for, so whole thing end up to ground zero. What you guys think what options I have. Is there any cheep out of box client/ or option of min. development which I can use in Windows 2000 workstations so that I can move on with AS 2005 migration.

I know I can still use excel 2002/2003 in client with AS 2005 but then all new features and measure groups are not coming ordered in Excel 2002/Excel2003.

Thanks for you advice,

-Ashok

This add-in can enhance ExcelXP and Excel2003 clients:

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

It is not as good as Excel 2007 but still better than the basic functionality in these clients.

HTH

Thomas Ivarsson

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.

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

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

Thank you - Ashok

Thanks again - Ashok

|||

Hi Ashok,

|||

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.

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.

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 calculated membres and Report Model/Builder

My question is related to Report Builder but my data model is build using OLAP database so I am posting my question in OLAP group in case this issues is related to OALP side.

I have OLAP database. There are 5 cubes and one linked cube. My one linked cube has mapped all 5 cubes (like virtual cube in AS 2000) I am using AS 2005.

In my linked cube I have calculated members. I created a Report model using my olap database in reporting services.

In report builder I am not able to see my calculated members when I select linked cube. Is there any thing I am missing?

Thanks for help - Ashok

I think this may be related to whether the calculations are associated with a measure group. The Report Model only seems to list measures that are associated with a measure group. Try going into the calculations tab for your cube clicking on the calculation properties button (looks like a little window with a yellow hand over it) as associating your calculations with a measure group. Unfortunately I think you will need to delete and re-generation your report model.

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 AS Connection Weirdness

I have a stand alone win2k3 report server(i.e. SQL is not on the box).
RS SP1 is installed
I have a SQLRS report developed in visual studio (also loaded on RS
box) that runs MDX against an analysis services cube. I am using the
OLE DB provider for OLAP services 8.0. (Note that we had to run
ptsfull.exe to get the pivot table service and 8.0 provider to be
available)
I can successfully preview the report in visual studio.
When I upload the report to Report Manger, the report bombs with...
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot create a connection to data source 'AAHP'.
(rsErrorOpeningConnection) Get Online Help
Database '<source name>' does not exist.
Am I missing something on the report server?
Thanks!Jim wrote:
> When I upload the report to Report Manger, the report bombs with...
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot create a connection to data source 'AAHP'.
> (rsErrorOpeningConnection) Get Online Help
> Database '<source name>' does not exist.
> Am I missing something on the report server?
The report bombs while uploading?
Do you tried to open the AS-DB in the analysis-manager on the reportserver?
regards
Frank|||Sorry, no. The report bombs when I try to run it via report manager. I
can open the AS-DB and cube in Analysis Manger (which is Not on the
report server). I have a role setup that should allow the stored
credentials to successfully authenticate.|||Jim wrote:
> Sorry, no. The report bombs when I try to run it via report manager. I
> can open the AS-DB and cube in Analysis Manger (which is Not on the
> report server). I have a role setup that should allow the stored
> credentials to successfully authenticate.
Jim,
lets have a look into the eventlog of the server where the analysis cube
resides.
Source MSSQLSERVEROLAPSERVER.
If there errors with something like "NT-Authority/Anonymous" your
security-settings on the report-server are not valid (especially the
settings in internet service manager)
If there aren't any errors it become more difficult...Do you run ptsfull on
reportserver either?
regards
Frank|||Thanks for your replies.
No errors unfortunately.
I ran ptsfull on the reportserver.
As I mentioned I can connect to the AS server via Visual Studio. As
soon as I deploy the report (using a stored connection or shared
connection) to report manager it won't run. I'm running MDAC 2.8 on the
report server.|||Jim wrote:
> As I mentioned I can connect to the AS server via Visual Studio.
Yes..i know. You can connect to the AS from YOUR workstation and your
reportserver can't as you wrote.
The problem should be between reportserver and AS-server but wich problem? I
dont know!
regards
frank|||Just to clarify...
Visual Studio - where I am developing the reports - is on the report
server.|||Jim,
I am having same problems and was wondering if you found solution of this
one. Thanks.
"Jim" wrote:
> Just to clarify...
> Visual Studio - where I am developing the reports - is on the report
> server.
>

OLAP and/or data mining?

Hello,

If I wrote the next ebay (yes I know, yawn-snore) and I had a database
with 5 million auction items in it, what would be a really good
strategy to get a search done very quickly? Would it involve
something called OLAP and/or "data mining"? The only technology I am
familiar with is simply SQL Server databases with stored procedures.
I think I'd be guessing correctly and say that this technology simply
wouldn't be fast enough *on it's own* to do super fast queries against
massive amounts of data.

Any insights would be of great interest. Thanks.

-Frameworker."Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> Hello,
> If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> with 5 million auction items in it, what would be a really good
> strategy to get a search done very quickly? Would it involve
> something called OLAP and/or "data mining"? The only technology I am
> familiar with is simply SQL Server databases with stored procedures.
> I think I'd be guessing correctly and say that this technology simply
> wouldn't be fast enough *on it's own* to do super fast queries against
> massive amounts of data.

OLAP is exactly what you'd want.

Data mining may be useful post-auction to see who bought what, what things
need more promotion etc.

I don't know why you don't think SQL Server wouldn't be fast enough for a
small setup like 5 million auctions.

I have a database with 14 million rows that's probably as complex as an
auction database and most queries are a couple of seconds or less. (some
are longer because of some fairly complex floating point math that has to be
done along the way.)

I have another database that handles at least that many inserts per day w/o
a major problem. And this is all on 3+ y.o. equipment.

Give me a quad Xeon MP box and the right disk subsystem and this thing could
scream.

> Any insights would be of great interest. Thanks.
> -Frameworker.|||"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > Hello,
> > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > with 5 million auction items in it, what would be a really good
> > strategy to get a search done very quickly? Would it involve
> > something called OLAP and/or "data mining"? The only technology I am
> > familiar with is simply SQL Server databases with stored procedures.

Using stored procedures as component building blocks
you can create all forms of data mining and analytical
processing applications.

> > I think I'd be guessing correctly and say that this technology simply
> > wouldn't be fast enough *on it's own* to do super fast queries against
> > massive amounts of data.
> OLAP is exactly what you'd want.
> Data mining may be useful post-auction to see who bought what, what things
> need more promotion etc.
> I don't know why you don't think SQL Server wouldn't be fast enough for a
> small setup like 5 million auctions.

Seconded.

If you write your procs in the right manner, and maintain
the data integrity in your database then you should have
null performance problems.

> I have a database with 14 million rows that's probably as complex as an
> auction database and most queries are a couple of seconds or less. (some
> are longer because of some fairly complex floating point math that has to
be
> done along the way.)
> I have another database that handles at least that many inserts per day
w/o
> a major problem. And this is all on 3+ y.o. equipment.
> Give me a quad Xeon MP box and the right disk subsystem and this thing
could
> scream.
>
> > Any insights would be of great interest. Thanks.
> > -Frameworker.|||Hi Greg,

I have one follow up question please:

I'd like to incorporate OLAP in to my design. Do I go ahead and
create a sensibly normalised relational database design, with many
dozens of stored procedures to query the tables in "the usual manner",
*then* add on OLAP as a kind of "bolt on", or does OLAP technology
require a totally different database/stored procedures design strategy
right from the word go?

Thanks!

- Frameworker.

"Greg D. Moore $Strider$" <mooregr@.greenms.com> wrote in message news:<fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com>...
> "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > Hello,
> > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > with 5 million auction items in it, what would be a really good
> > strategy to get a search done very quickly? Would it involve
> > something called OLAP and/or "data mining"? The only technology I am
> > familiar with is simply SQL Server databases with stored procedures.
> > I think I'd be guessing correctly and say that this technology simply
> > wouldn't be fast enough *on it's own* to do super fast queries against
> > massive amounts of data.
> OLAP is exactly what you'd want.
> Data mining may be useful post-auction to see who bought what, what things
> need more promotion etc.
> I don't know why you don't think SQL Server wouldn't be fast enough for a
> small setup like 5 million auctions.
> I have a database with 14 million rows that's probably as complex as an
> auction database and most queries are a couple of seconds or less. (some
> are longer because of some fairly complex floating point math that has to be
> done along the way.)
> I have another database that handles at least that many inserts per day w/o
> a major problem. And this is all on 3+ y.o. equipment.
> Give me a quad Xeon MP box and the right disk subsystem and this thing could
> scream.
>
> > Any insights would be of great interest. Thanks.
> > -Frameworker.|||I'm a speed freak! If I can get a query to run .25 seconds faster
using OLAP technology, I'm all for it. :o)

"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message news:<f3WFb.62838$aT.6089@.news-server.bigpond.net.au>...
> "Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
> news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> > "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> > news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > > Hello,
> > > > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > > with 5 million auction items in it, what would be a really good
> > > strategy to get a search done very quickly? Would it involve
> > > something called OLAP and/or "data mining"? The only technology I am
> > > familiar with is simply SQL Server databases with stored procedures.
>
> Using stored procedures as component building blocks
> you can create all forms of data mining and analytical
> processing applications.
>
> > > I think I'd be guessing correctly and say that this technology simply
> > > wouldn't be fast enough *on it's own* to do super fast queries against
> > > massive amounts of data.
> > OLAP is exactly what you'd want.
> > Data mining may be useful post-auction to see who bought what, what things
> > need more promotion etc.
> > I don't know why you don't think SQL Server wouldn't be fast enough for a
> > small setup like 5 million auctions.
>
> Seconded.
> If you write your procs in the right manner, and maintain
> the data integrity in your database then you should have
> null performance problems.
>
> > I have a database with 14 million rows that's probably as complex as an
> > auction database and most queries are a couple of seconds or less. (some
> > are longer because of some fairly complex floating point math that has to
> be
> > done along the way.)
> > I have another database that handles at least that many inserts per day
> w/o
> > a major problem. And this is all on 3+ y.o. equipment.
> > Give me a quad Xeon MP box and the right disk subsystem and this thing
> could
> > scream.
> > > > Any insights would be of great interest. Thanks.
> > > > -Frameworker.|||"Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
news:f109ac80.0312240543.63e644ac@.posting.google.c om...
> I'm a speed freak! If I can get a query to run .25 seconds faster
> using OLAP technology, I'm all for it. :o)

I think you completely misunderstand what OLAP is or does. (And I mispoke
below (forgive me, I was tired). You want an OLTP system, not an OLAP.

OLAP and getting better performance are orthogonal to each other. i.e.
using OLAP is not a way to increase performance.

>
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:<f3WFb.62838$aT.6089@.news-server.bigpond.net.au>...
> > "Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
> > news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> > > > "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> > > news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > > > Hello,
> > > > > > If I wrote the next ebay (yes I know, yawn-snore) and I had a
database
> > > > with 5 million auction items in it, what would be a really good
> > > > strategy to get a search done very quickly? Would it involve
> > > > something called OLAP and/or "data mining"? The only technology I
am
> > > > familiar with is simply SQL Server databases with stored procedures.
> > Using stored procedures as component building blocks
> > you can create all forms of data mining and analytical
> > processing applications.
> > > > I think I'd be guessing correctly and say that this technology
simply
> > > > wouldn't be fast enough *on it's own* to do super fast queries
against
> > > > massive amounts of data.
> > > > OLAP is exactly what you'd want.
> > > > Data mining may be useful post-auction to see who bought what, what
things
> > > need more promotion etc.
> > > > I don't know why you don't think SQL Server wouldn't be fast enough
for a
> > > small setup like 5 million auctions.
> > Seconded.
> > If you write your procs in the right manner, and maintain
> > the data integrity in your database then you should have
> > null performance problems.
> > > I have a database with 14 million rows that's probably as complex as
an
> > > auction database and most queries are a couple of seconds or less.
(some
> > > are longer because of some fairly complex floating point math that has
to
> > be
> > > done along the way.)
> > > > I have another database that handles at least that many inserts per
day
> > w/o
> > > a major problem. And this is all on 3+ y.o. equipment.
> > > > Give me a quad Xeon MP box and the right disk subsystem and this thing
> > could
> > > scream.
> > > > > > > > Any insights would be of great interest. Thanks.
> > > > > > -Frameworker.

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 anallysis - overseas or in the US?

Like all US developers, I am concerned about the future of my career as a
software developer, considering the number of jobs going overseas and the
declining rates. For the past 8 years or so I have made my living
developing business solutions, primarily using Microsoft tools. I have
worked on all develpment tiers, but I prefer to work on databases. However,
I have worked almost exclusively on OLTP databases.
Lately I have been thinking that a good strategegy for me would be to become
an expert in OLAP and data mining. My thinking is that this type of work
would need to be done close to the business as opposed to overseas, thus
providing some job security. In addition to that, it would allow me to
continue to work on databases, which I enjoy, and to analyze data, which I
also enjoy.
Looking for a reality check here. Does this sound sensible to others?PS sorry about the spelling error in the subject line.
"Curly" <XeveryidiwantiskenX@.yahoo.com> wrote in message
news:SB6Eb.8913$0s2.3355@.newsread2.news.pas.earthlink.net...
> Like all US developers, I am concerned about the future of my career as a
> software developer, considering the number of jobs going overseas and the
> declining rates. For the past 8 years or so I have made my living
> developing business solutions, primarily using Microsoft tools. I have
> worked on all develpment tiers, but I prefer to work on databases.
However,
> I have worked almost exclusively on OLTP databases.
> Lately I have been thinking that a good strategegy for me would be to
become
> an expert in OLAP and data mining. My thinking is that this type of work
> would need to be done close to the business as opposed to overseas, thus
> providing some job security. In addition to that, it would allow me to
> continue to work on databases, which I enjoy, and to analyze data, which I
> also enjoy.
> Looking for a reality check here. Does this sound sensible to others?
>
>
>|||Just for fun (or out of dispare) send this question to:
SQLWish@.microsoft.com
hope you can see the irony in it :( :~).
RTF|||Sorry I am not getting your point/joke. Can you please explain?
"Rufus T. Firefly" <nospam@.aol.com> wrote in message
news:OcmVwuRxDHA.2136@.TK2MSFTNGP10.phx.gbl...
> Just for fun (or out of dispare) send this question to:
> SQLWish@.microsoft.com
> hope you can see the irony in it :( :~).
> RTF
>
>|||"Curly" <XeveryidiwantiskenX@.yahoo.com> wrote in message
news:xFbEb.9311$0s2.3379@.newsread2.news.pas.earthlink.net...
> Sorry I am not getting your point/joke. Can you please explain?
Think about just who might be reading your e-mail!
RTF
> "Rufus T. Firefly" <nospam@.aol.com> wrote in message
> news:OcmVwuRxDHA.2136@.TK2MSFTNGP10.phx.gbl...
> > Just for fun (or out of dispare) send this question to:
> > SQLWish@.microsoft.com
> >
> > hope you can see the irony in it :( :~).
> >
> > RTF
> >
> >
> >
>|||Data mining is, largely, a statistical process. My observation has
been that some I.T. types "get" statistics, while others don't. If
you believe that you can handle things like correlation, regression,
clustering and outliers, as opposed to SQL, Perl, E-R diagrams and
Visual BASIC, then, yes- consider data mining as a possible career
target.
-Will Dwinnell, MBA
http://will.dwinnell.com
"Curly" <XeveryidiwantiskenX@.yahoo.com> wrote in message news:<SB6Eb.8913$0s2.3355@.newsread2.news.pas.earthlink.net>...
> Like all US developers, I am concerned about the future of my career as a
> software developer, considering the number of jobs going overseas and the
> declining rates. For the past 8 years or so I have made my living
> developing business solutions, primarily using Microsoft tools. I have
> worked on all develpment tiers, but I prefer to work on databases. However,
> I have worked almost exclusively on OLTP databases.
> Lately I have been thinking that a good strategegy for me would be to become
> an expert in OLAP and data mining. My thinking is that this type of work
> would need to be done close to the business as opposed to overseas, thus
> providing some job security. In addition to that, it would allow me to
> continue to work on databases, which I enjoy, and to analyze data, which I
> also enjoy.
> Looking for a reality check here. Does this sound sensible to others?sql

OLAP Administrators issue

Hi,

I moved my AS 2000 olap database to new hardware also new OS WS 2003 32 bit to 64 bit.

I added domain/loging ID of sql server/job server to "OLAP Administrators" user group

which runs the DTS package to process cubes. This was fine in my old server OS WS 2003 32 bit.

But in new server WS 2003 64 bit cube process failed because job server was not able to connect to AS server.

I had to add job server's domain\login ID to "Administrators" user group in new server and then I was able to process the cube.

I don't want to give administrator access to job server to my olap server is this some thing I can do or missing?

Thank you - Ashok

Here is some information for you from http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx

Take a look at the Administrator Security section there.

In general, DSO that client application like Analysis Manager, or DTS task based upon tries couple of things.

One, it remotely accesses registry on server machine and reads RemoteRepositoryConnectionString http://support.microsoft.com/kb/330244

Then it uses it to open AS2000 repository.

Try and see at what point DSO running under user account with fewer privileges fails to acces information it needs.

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

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..

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 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!sql

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!

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 - Uniqueness for the quarter

Hi All,

We are encountering problems with quater count of records.

Uniqueness is accurate for month; not if you aggregate for quarter or year; for example, user is active in Jan, Feb and Mar The system would accurately show 1 active user for each of these months, but would show 3 active users for the 1st quarter (doesnt recognize that its the same unique user).

We want to count the active user only once for that quarter.

Is the way to count that active users only once for that quarter instead three times. Please help and your input is appreciated.

Thanks and have a nice day.Use this format for your count:

Select Count(Distinct UserID)
From YourTable
Where.........

blindman

OLAP - EXCEL problem - urgent

Hello,

I have a dimension hierarchy with 2 levels, like this:

A - X

- Y

- Z

B - U

- V

- T

And I have a cube with the dimension above and one measure (sum).

The fact table of this cube has 2 columns (level2,sumvalue) and i have the values:

Level2 SumValue

X 1

Y 2

T 6

When I access this cube from Excel and use multiple selection weird stuff happends.

So, I create a Pivot table, in which i select only X,Y,U:

Level1

Level2

Total

A Total

Grand Total

If i give a refersh to this pivot table i get :

SumValue

Level1

Level2

Total

A Total

Grand Total

Why i see the B in the pivot table, if i selected only X,Y,U and U has no value in the fact table? I need an urgent answer because end-users use a lot the refresh option and in this case they see wrong values.

I'm using Microsoft Analysis Server build 9.00.2153 (SP1) and Microsoft Office Excel 2003 (11.6560.6568) SP2. I made the test with Microsoft Analysis Server SP2 and same version of Excel and i got the same result.

Any help will be appreciated.

Thanks,

Vulcano

A couple of things to check, that I can think of:

Is "Visual Totals" still enabled after refresh (ie. "Totals include Hidden Items" is disabled) - otherwise, these results would be expected?|||Thanks Deepak for your answer.

The property of the pivot table "Include hidden members in totals" is disable all the time. So no hidden members are included. In my dimension i don't have hidden members.

I run a Profiler and I discovered something strange:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[T] , [Dimension].[Hierarchy].[Level1].[ V ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ T ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

Why the two queries are different? Why in the second query [Dimension].[Hierarchy].[Level1].[ B ] disappears?

Thanks,

Vulcano|||

The member which disappeared in the 2nd query is: [Dimension].[Hierarchy].[Level1].[ V ] , not [Dimension].[Hierarchy].[Level1].[ B ] (though maybe these are place-holder, not real, names?).

When you look at the drop-down list of members selected in the pivot table, do you find that this missing member is now selected? If so, this could be a side-effect of refreshing the pivot table (though I'm not sure why the refresh would change the selected members).

|||

My mistake Deepak, the queries are wrong, they are missing the A, B Members of the dimension. I post again the queries:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] , [Dimension].[Hierarchy].[Level1].[ B ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[ V ], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

And you are right again: when the first pivot is created the X,Y,U members are selected, after the pivot is refresh the U member is not selected anymore ( so the member selected are X,Y,B). Is this the right behaviour, or maybe is a bug?

Thanks,Vulcano

Friday, March 30, 2012

OLAP cube - formula length

OLAP cube - errorhandling

OLAP Connectivity

OLAP connection problems

OLAP Connection in c#

OLAP Connection error

OLAP Connection Error

OLAP Cluster SQL 2005

OLAP Client - your suggestions

OLAP Client

OLAP Client

OLAP calculated membres and Report Model/Builder

OLAP based reports not rendering

OLAP AS Connection Weirdness

OLAP and/or data mining?

Olap and reporting service problem

olap and hierarchy problem

OLAP and ASP.NEt

OLAP Analysis Services: Errors in Encryption Library

OLAP Analysis Services - Remote Connection issue

OLAP anallysis - overseas or in the US?

OLAP Administrators issue

OLAP administrators group.?

OLAP administrators group.?

OLAP Administrators group in Analysis server

OLAP 2005 Calculated Measure using Field/Dimension

OLAP 2005 Calculated Measure using Field/Dimension

OLAP / Analysis services version checking by client

OLAP - Uniqueness for the quarter

OLAP - EXCEL problem - urgent

ODBC Isolation level

Blog Archive

About Me