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.

Perspectives Defined in Analysis Cube not working in Report Builder

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.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.|||I have the same problem. You already know a solution?

Perspectives Defined in Analysis Cube not working in Report Builder

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.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.

Perspectives Defined in Analysis Cube not working in Report Builder

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.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.

Permissions question.. (hey, didja miss me?)

Hi all,

Wow...since I've been gone, the search feature is apparently working again! Kewl!

However, after doing a search and not finding anything pertaining to my current issue, here I am again :D

I access a table on a linked server/database apart from my application's DB.

Every day, a process on the other server/DB drop and recreate the table...which, of course, zaps my user permissions. I then have to go into the Enterprise Mgr and re-enable my SELECT access to the table.

My question is this: Is there a way to auto-magically set my user ID on the linked server to give me access to the table without having to go in explicitly on a daily basis and re-enabling my UID Access rights.

This is the only table my user ID has been given access to on the remote server, so I am not able to simply set up my user ID on the remote system to "allow everything"

Am I missing something obvious? Do I have to create a SP on the remote server to do a daily GRANT SELECT ON Remote_Table TO Paul_Dammit after the table is rebuilt each day?

Suggestions?

It getsYour only option at the database level would be to give a user "carte-blanche" access to all of the tables within that database by making them a memeber of the db_datareader group.

For one specific table, I'd create a job that ran every 6 minutes or so, with one SQL step that tests to see if the table is there then grants permission if it is. I'm a "fewer moving pieces" kind of guy.

-PatP|||That's what I was afraid of, Pat...thanks for the reply! For the time being, I'll do that...I have a control table that I can read for a flag indicating when the drop/create has occurred, so I can simply does the grant when it happens.

I was just hoping to not have to add a job to someone else's DB...oh well...the price they pay for being so selective about the riff-raff they let in, I guess ;)

Thanks again!
Paul|||I guess from my perspective, if they see adding a one step job as too much of a pain, then they need to make you a member of db_datareader. That way they can do it once. Neither task takes over 40 seconds for a competent DBA, and once scheduled the only thing they need to do is watch for errors in the server log, which they'd better be doing anyway!

If they see this as a problem, they REALLY need to reevaluate their attitude. There might be collatoral duties that this job implies, but those are enforced by the organization and should be significantly less of a problem than granting the permissions manually.

-PatP

Permissions granted from Domain credentials

I am working with Visual Studio.net and creating a front end for a SQL datab
ase. Now with the access front end, it uses the domain credentials of the l
ogged in user to determine what permissions they have for editing records in
the SQL (2000) database.
I need the same to be done with the web front end I am creating now. I have
gone into the WEB.CONFIG file and put in my credentials there, identity i
mpersonate="true" userName="domain\johndoe" password="password"
Now this is ok if...I didn't change my password regularly and I wanted every
one to have full access...but obviously I don't. Is there some syntax that
I need there so that it will use the credentials of the logged in user? Or a
m I in the wrong file and s
hould be making changes elsewhere? I am new to this but I am learning. In
the OleDB connection for the datagrid on the page, I have added the line...
Trusted Connection=SSPI. What am I missing? I am using OleDb connection a
nd OleDb Command. Like I s
aid...with my credential in the web.config, it works fine. But that is only
good for now. I need the security set so when I get to creating a page tha
t only administrators have access to I can get those to work correctly.
Thanks All!!I am assuming that with the Trusted Connection = SSPI the connection is
failing. If so, what error message do you get when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||You need to allow Kerberos Authentication to occur. The middle machine
must be trusted for Security Delegation. Also , the SQL Server service
account needs to have the Service Principal Name set.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

permissions for developers not working after 2005 upgrade

Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.

After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?

Can you please elaborate on the access difficulty? Are you encountering errors and, if yes, what are those errors? It would help if you could give us an example of some action that used to work and now doesn't, and of what is the system response in this case.

Thanks
Laurentiu

|||

They are making a connection through Visual Studio using the MS OLE DB Provider for SQL Server with their domain account and receiving the following error...

SELECT permission denied on object 'Contact_Info', database 'GetLean', schema 'dbo'.

|||

Could you check what is the current execution context at the time when this error is obtained? You can use Profiler to figure out what is the current execution context.

There are two possibilities:

(1) current execution context is not a member of db_datareader, so you do not have SELECT permission.

(2) current execution context is explicitly denied SELECT permission on the Contact_Info table.

If you determine the current execution context, then you can check whether it's a member of db_datareader by looking at the sys.database_role_members catalog. You can check for the SELECT permission being denied by looking at the sys.database_permissions catalog.

Thanks
Laurentiu

|||

They are a member of the db_datareader, db_datawriter, db_ddladmin, and db_securityadmin roles. There are no permissions explicitly denied in this database.

Even though they have db_datareader, db_datawriter, db_ddladmin, and db_securityadmin rights....must I still assign them to the dbo schema?

|||

db_datareader grants select on the entire database, hence on the dbo schema as well (http://msdn2.microsoft.com/en-us/library/ms189612.aspx). You don't need to do a special permission grant for the dbo schema.

Can you try a little experiment? Create a test table in the dbo schema and then verify if those members of db_datareader can access it. Also, create a separate schema and a table in it and see if there is the same behavior for it as for the dbo schema.

Also, does this happen for all those developers or only for some? If some of them were added to db_denydatareader role, then that would prevent them from selecting from anything. Are they members of other roles than those four that you mentioned?

Thanks
Laurentiu

|||

It turned out not to be a DBA problem. Sorry, should have posted the resolution earlier.

It turned out to be a developer issue...a combination of failing to practice current standards and inexperience with Visual Studio. Aargh!

permissions

I am working http location and using sql server 2005 ,it is showing an error as " DELETE permission denied on object 'CourseDetails', database 'LOGIN', schema 'dbo'." CourseDetails is my table name and LOGIN is my database name.

Goto SQL Server Management Studio 2005--> Goto your database (LOGIN)--> Right click on the table name (CourseDetails)--> Select Properties

There goto the Permissions tab and click add... You can add the permission here...

But the question is WHY would you want to add delete permissions on your SQL Table? What you should do is write a Stored Procedure to delete the data in there and give the procedure execute permissions.

|||

Thank u deepak

permission denied

iam working with http location and using sql server 2005its getting an error as "SELECT permission denied on object UserDetails' database 'elearning', schema 'dbo'.""UserDetails" is my table name"elearning" is database namei worked same project with filesystem location ,there it is workingThis is obviously a permissions issue. You need to ensure that the user your connected to the database with has select permissions on at least the "UserDetails" table.|||

It is a issue retaled to lack of permission for the user account you are using to connect.

To resolve this issue in SQL management Studio right click the username which is placed under Security => Login.

A modal window will be opened and in User Mapping section setect the desired DB and check db_datareader for that user. Click ok. Now you are done.

Let me know if need any further clarifications

permission denied

iam working with http location and using sql server 2005 its getting an error as "INSERT permission denied on object CourseDetails, database 'mydb', schema 'dbo'." "CourseDetails" is my table name "mydb" is database name i worked same project with filesystem location ,there it is working

This is almost certainly being caused because the account that is running the web site (ASPNET or NETWORK SERVICE, depending on which version of IIS you're running) is not allowed permission to access the SQL Server database. Note that for a file based application, running under the development web server, your user account will have been used.

Check the connection string that is being used in the web site. If it is using integrated security (likely), then you will need to ensure that the ASPNET or NETWORK SERVICE account (or, more precisely, the account that is being used to run the web site) is allowed access to the database.