Perspectives in Report Models Generated for SSAS Cubes not Working

I never got an answer on this so I am reposting this issue. Also, please note that I have consistently reproduced this issue on multiple perspectives within and accross multiple cubes on 3 different SQL Server Instances. I have also had no problem with perspectives within Report Models that were not generated from an SSAS Cube.

Specifics:

=========

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

I find it very dissapointing that I have not recieved any response to this issue. I am trying to convince a Federal Bureau to move forward with SQL Server 2005 as its defacto BI Technology, and Ad Hoc reporting is very important. I am starting to wonder if this is the correct approach considering the limited support and the amount of issues I have come accross when using Reporting Services with SSAS.sql

Perspectives in Report Models Generated for SSAS Cubes not Working

I never got an answer on this so I am reposting this issue. Also, please note that I have consistently reproduced this issue on multiple perspectives within and accross multiple cubes on 3 different SQL Server Instances. I have also had no problem with perspectives within Report Models that were not generated from an SSAS Cube.

Specifics:

=========

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

I find it very dissapointing that I have not recieved any response to this issue. I am trying to convince a Federal Bureau to move forward with SQL Server 2005 as its defacto BI Technology, and Ad Hoc reporting is very important. I am starting to wonder if this is the correct approach considering the limited support and the amount of issues I have come accross when using Reporting Services with SSAS.

Permissions for SSAS Service Account?

Hi All,

Please help with this.

I'm running a server with Windows Server 2003 R2 Enterprise x64 Edition.

On the box I've performed one named instance installation of SSAS.

I've installed SP1 as well.

There's no firewall running on the machine.

My domain account is in the SSAS Server role.

SSAS Service is running according to SQL Configuration Manager

SQL Browser Service is running according to SQL Configuration Manager

If I run both SSAS and SQL Browser under Local System, everything works OK. But this is not the way I want it, because it's not safe and SSAS is unable to read data from the remote SQL box.

If I change the service account to a domain user account, I can't connect to SSAs anymore. The error message is:

TITLE: Microsoft SQL Server Management Studio

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

ADDITIONAL INFORMATION:

No connection could be made because the target machine actively refused it (System)

BUTTONS:

OK

I've both set the logon account at setup, using SQL Config Mgr and Service Manager.

Using PortQry I can see nothing's listening on port 2382.

It should work. I'm beginning to think this user account doesn't have sufficient rights.

So I've created two local accounts and used these as service accounts. Same thing, untill I added both accounts to the local administrators group, then everything worked fine again.

What am I doing wrong here? Your suggestions are much appreciated.

Thanks,

Coen

Hello. You have another configuration that you do not mention: "Surface area"

You will have to allow remote connections in this configuration tool. That is for each SQL Server 2005 service that you are using.

HTH

Thomas Ivarsson

|||

Couple of things.

One. Make sure you use SQL Configuration Manager to change serivce logon account for both SQL Browser and for Analysis Server.

Second. Take a look at the connectivity troubleshooting guide http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

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

|||

The Surface Afrea Conf tool returns the following error:

TITLE: Surface Area Configuration

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices)

ADDITIONAL INFORMATION:

No connection could be made because the target machine actively refused it (System)

BUTTONS:

OK

|||

I am using the SQL Config Mgr to change the user id's. BTW this is sometimes a bit crooked, and I still need to change the Service Account in the Service Manager as well.

Besides, I don't really think the problem's in there. Setup should set the correct permissions for the Logon Account.

Thanks for pointing me to your post on SQLJunkies. I've used it before.

|||I've narrowed it don a bit. It seems only the Service Account used for SQL browser has to be in the Local Admins group.

Permissions for SSAS Service Account?

Hi All,

Please help with this.

I'm running a server with Windows Server 2003 R2 Enterprise x64 Edition.

On the box I've performed one named instance installation of SSAS.

I've installed SP1 as well.

There's no firewall running on the machine.

My domain account is in the SSAS Server role.

SSAS Service is running according to SQL Configuration Manager

SQL Browser Service is running according to SQL Configuration Manager

If I run both SSAS and SQL Browser under Local System, everything works OK. But this is not the way I want it, because it's not safe and SSAS is unable to read data from the remote SQL box.

If I change the service account to a domain user account, I can't connect to SSAs anymore. The error message is:

TITLE: Microsoft SQL Server Management Studio

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

ADDITIONAL INFORMATION:

No connection could be made because the target machine actively refused it (System)

BUTTONS:

OK

I've both set the logon account at setup, using SQL Config Mgr and Service Manager.

Using PortQry I can see nothing's listening on port 2382.

It should work. I'm beginning to think this user account doesn't have sufficient rights.

So I've created two local accounts and used these as service accounts. Same thing, untill I added both accounts to the local administrators group, then everything worked fine again.

What am I doing wrong here? Your suggestions are much appreciated.

Thanks,

Coen

Hello. You have another configuration that you do not mention: "Surface area"

You will have to allow remote connections in this configuration tool. That is for each SQL Server 2005 service that you are using.

HTH

Thomas Ivarsson

|||

Couple of things.

One. Make sure you use SQL Configuration Manager to change serivce logon account for both SQL Browser and for Analysis Server.

Second. Take a look at the connectivity troubleshooting guide http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

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

|||

The Surface Afrea Conf tool returns the following error:

TITLE: Surface Area Configuration

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices)

ADDITIONAL INFORMATION:

No connection could be made because the target machine actively refused it (System)

BUTTONS:

OK

|||

I am using the SQL Config Mgr to change the user id's. BTW this is sometimes a bit crooked, and I still need to change the Service Account in the Service Manager as well.

Besides, I don't really think the problem's in there. Setup should set the correct permissions for the Logon Account.

Thanks for pointing me to your post on SQLJunkies. I've used it before.

|||I've narrowed it don a bit. It seems only the Service Account used for SQL browser has to be in the Local Admins group.