Null values: March 2012

Friday, March 30, 2012

Role Playing dimensions Currency Conversion issue

Hi,

Having issues with Currency Conversion in SSAS when using role playing dimensions of Time.

When the Dim Usage in the cube is set for the role playing dimensions,
i.e. defining the relation between the Exchange rate fact and the role playing time dims.
the base $ measures (no calculations) return a null value.

The generated conversion script in Calculations has leaves(Time), not the leaves for each role playing dim.
With leaves for each along with leaves(time), the base measures return null.

Regards

does ssas 2005 currency conversion work when using the linkmember function in calculations.

Role Playing Dimensions

What is the proper way to include the date dimension table into your DSV if you plan on using multiple role-playing dimensions? It seems if you can use this dimension as a role playing dimension, and assign the "join" using the Dimension Usage tab, then it doesn't need to be joined to the fact table in the DSV... is that correct? Or should you add the Date Dim table to the DSV and join it to every date-specific fact foreign keys? I see three possibilities for the DSV:

1) Include the Date Dim table but not join to any other table in the DSV

2) Include the Date Dim table and join to at least one fact foreign key

3) Include the Date Dim table and join to all fact foreign keys for dates in the DSV

Which method would be the most appropriate (or what are the consequences of each method)?

Also, if you use a role-playing dimension, is there anyway of manipulating the levels to display the type of date. For example, of the user picks the Order Date dimension, the year level says "Order Year" but if they use the Shipped Date dimension, the year level would say "Shipped Year"... etc.?

Thanks

Kory

Hi KoryS. Check the Adventure Works sample BI project that clearly shows that no 3 is the correct way of doing it. It is a very simple concept, instead of using three tables of views for time you join one table to the different time keys in the fact table. There have been some concerns regarding using role playing dimensions like here(http://mgarner.wordpress.com/2006/06/27/role-playing-dimensions-not-materialized/) but now it is OK regarding performance.

Regards

/Thomas Ivarsson

|||

Thanks- I'll follow the example from the Adventureworks db.

Do you know if I can rename some of the levels in my role-playing dimensions (see second half of my original thread)?

-Kory

|||

I think you will have to use the same names but you name each dimension like DueDate, OrderDate, ShipDate and so on. You can see this is in the Adventure Works AS2005 project.

Regards

/Thomas

sql

Role playing dimension and member naming question.

I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

I had the same problem earlier on. I had to bite the bullet and change my MDX when I had multiple 'date' dimensions. Can you globally change the MDX or are you using a third party tool?

Unless someone knows better.

|||

I am using ProClarity as a front end to the cube. I have found a sort of work around, rather than creating a role-playing dimension. I created a whole new date dimension on the same date table and it doesn't mess up the naming. The only downside I can see to this right now is that if I have one date dimension on rows and one date dimension on columns your result could look like:

may june july august
may
june
july
august

so it's not really clear which dimension is where, but I could get around this by changing the names of the members in the new date dimension.

-Preston

|||

Hi,

Although this solution will ultimately work, I guess we are duplicating process and space by repeating a dimension.

I've not tested it thorougly yet, but, the cube dimension has a property named HierarchyUniqueNameStyle, that allows for two values: IncludeDimensionName and ExcludeDimensionName. Using the later in the "default" dimension, let's say "Date" vs. "Delivery Date", the MDX will run fine as it was originally, without need to edit all of them.

Jordi Rambla

SQL Server MVP

Certia (http://www.certia.net)

SolidQualityLearning (http://www.solidqualitylearning.com)

Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

Role Permissions

Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra

" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Pra

>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Pra

" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[[url]http://schemas.microsoft.com/SQL/Notifications/EventNotificationService[
/url]]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra

" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
PrasadNot all securables are objects. What class_desc have those objects in
sys.database_permissions?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>> Hi,
>> I am trying the following on SQL Server 2005.
>> I want to find out the permissions for different roles on database and
>> database objects.
>> So, selected all the records from the "sys.database_permissions" catalog
>> view and joined it with the "sys.all_objects" catalog view.
>> But I found some objects that have the permissions assigned to them but
>> don't have an entry in sys.all_objects.
>> This behavior was for the "master" database.
>> The object ids for the objects is as follows :
>> -233346666
>> -282896470
>> -986524149
>> -369557355
>> The permission assigned is Select for all the above objects for public
>> role.
>> I want to know what these objects are.
>> TIA
>> Prasad
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]
TO [Public];
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>> Not all securables are objects. What class_desc have those objects in
>> sys.database_permissions?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
>> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>> Hi,
>> I am trying the following on SQL Server 2005.
>> I want to find out the permissions for different roles on database and
>> database objects.
>> So, selected all the records from the "sys.database_permissions" catalog
>> view and joined it with the "sys.all_objects" catalog view.
>> But I found some objects that have the permissions assigned to them but
>> don't have an entry in sys.all_objects.
>> This behavior was for the "master" database.
>> The object ids for the objects is as follows :
>> -233346666
>> -282896470
>> -986524149
>> -369557355
>> The permission assigned is Select for all the above objects for public
>> role.
>> I want to know what these objects are.
>> TIA
>> Prasad
>>
>

Role of login specified in GRANT UNSAFE...

For registering UNSAFE assemblies, we have to sign the dll and register it in SQL 2005. Also, the permission needs to be granted to a login..

Example code;

CREATE ASYMMETRIC KEY MyKey FROM EXECUTABLE FILE = 'C:\Assemblies\Interop.SQLDMO.dll'

CREATE LOGIN MyLogin

FROM ASYMMETRIC KEY MyKey

GRANT UNSAFE ASSEMBLY To MyLogin

What is the purpose of creating this MyLogin and how it serves to access the unsafe assembly.

Can anyone throw more light on this.

Thanks

Baskar

In SQL Server it is not possible to grant permissions to a key, only users or groups, so in order to keep the security model simple a way is provided to associate a key with a login. The reason why this is useful is that it provides a way for an administrator to selectively pick which assemblies he or she trusts enough to permit UNSAFE or EXTERNAL_ACCESS operations. You can mark a database as trustworthy, but that is not recommended.

--Bonnie

Role of login specified in GRANT UNSAFE...

For registering UNSAFE assemblies, we have to sign the dll and register it in SQL 2005. Also, the permission needs to be granted to a login..

Example code;

CREATEASYMMETRICKEY MyKey FROM EXECUTABLE FILE='C:\Assemblies\Interop.SQLDMO.dll'

CREATELOGIN MyLogin

FROMASYMMETRICKEY MyKey

GRANT UNSAFE ASSEMBLYTo MyLogin

What is the purpose of creating this MyLogin and how it serves to access the unsafe assembly.

Can anyone throw more light on this.

Thanks

Baskar

--Bonnie

Role of Distributors in transactional replication with queued updating

I am trying to understand what the value of installing the distribution
database on its own server. Performance is going to be an issue for clients
accessing the subscribing databases and I want to minimize the impact of the
replication process as much as possible. And by performance, I am not
referring to the speed of the replication process; it is acceptable for
there to be delays in replicating the data. With that being said, what is
the value of installing the distribution database on its own server for the
central subscriber as well as the regular subscribers?
Thanks
Hello William,
I think this depend on the performance of publication server. If it is not
heavy load, putting distributor on the same box shall have the best
performance because log reader agent and queue read agent can access
distributor locally. This will make clients have least impact of the
replication process.
However, if publication server is under heavy load, it is best that you
setup a standalone distributor. It has standalone process ability and
network bandwidth.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "William F. Kinsley" <bacardi@.noemail.nospam>
| Subject: Role of Distributors in transactional replication with queued
updating
| Date: Wed, 8 Jun 2005 11:55:03 -0400
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#WnJwJEbFHA.2520@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: host-67-62-103-180.cavtel.net 67.62.103.180
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:14186
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| I am trying to understand what the value of installing the distribution
| database on its own server. Performance is going to be an issue for
clients
| accessing the subscribing databases and I want to minimize the impact of
the
| replication process as much as possible. And by performance, I am not
| referring to the speed of the replication process; it is acceptable for
| there to be delays in replicating the data. With that being said, what
is
| the value of installing the distribution database on its own server for
the
| central subscriber as well as the regular subscribers?
|
|
|
| Thanks
|
|
|
sql

role not restricting as expected

On my Analysis Server, I had a user set up in the server role. I then added her to a role on one of my cubes restricting her by attribute on a certain dimension. The restriction did not take place since she was part of the server role.

I then removed her from the server role, and she is still seeing everything. The restriction is not taking place! I have duplicated these same actions for a different user, and the restrictions take place properly. I can also browse using the role (instead of the user), and the restrictions take place as expected.

Is there somewhere else (besides the server role setting) where something can be set giving a user "all-seeing rights"?

Also, is there a way to display the rights any given user has?Is she machine administrator ? Or perhaps member of database role with administrative permissions over the database ?|||It doesn't look like she had admin permissions on the db. I can't tell if she's a machine admin, but I assume that she it not. If you're a machine admin does that really give you rights to all the data in the cube?|||Yes - by default being machine admin also makes you a server admin. It is possible to turn this default behavior off however.

Role Nesting

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
|
boss
|
worker
|
subworker

Nested Role

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.

What is MarkingRoleName evaluated to for each user?

Also, ownership is not the same as nesting. To make a role a member of another role, you can use sp_addrolemember.

For example,

sp_addrolemember role1, role2

will make role2 a member of role1.

Thanks
Laurentiu

Role Nesting

Hi,

So i build a admin
|
boss
|
worker
|
subworker

Nested Role

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

What am i doing wrong?

Thanks.

What is MarkingRoleName evaluated to for each user?

Also, ownership is not the same as nesting. To make a role a member of another role, you can use sp_addrolemember.

For example,

sp_addrolemember role1, role2

will make role2 a member of role1.

Thanks
Laurentiu

Role Manager Feature? What is it? Error in Connecting to db

I keep getting this error. I am using Visual Web Developer Express 2005 (Visual Basic) and SQL Server 2005 Express.

"There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled."

This is in the ASP.NET Configuration.

How do I enable role manager?

Hi, you can take a look at this link:

http://msdn2.microsoft.com/en-us/library/ms178402.aspx

role is not working

Hi,

I have done 2 roles in my analysis services project. one can see all (Role1) and the other can see only a portion of the dimension data (Role2). i tested these in analysis services browser using the Change User > Roles. They're both working ok. I tried the Change User > Current User. Supposedly, since I assigned my domain name in Role 2, I should be able to see only a portion of the data. But then, I could still see all. I also tried calling my cube from excel. Still, I could see everything..

Have I missed to set anything for this to work? Before I tested, I had processed the whole project...

cherriesh

You are probably also a server administrator if you are creating roles. Security is not applied to server administrators (you could give yourself any rights anyway). Try testing the role with a different account.sql

role in sql2000

Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
Thanks
On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db same
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegrou ps.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

role in sql2000

Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db same
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
>> Hi,
>> I have a role created in one db. I need to copy that role to another db
>> same
>> strucxture in the same server.
>> How do I do that. Role without allthe permissions
>> Thanks
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

role in sql2000

Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db sa
me
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Role for alowing replication

Hello there
If i need to replicate two servers that i don't want that the subsriber user
won't be member of sysadmin and db_owner.
Which role i need to declare in order to allow transactional replication?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
For push IIRC it needs to be in the ddl_admin role (as it needs to create
tables), and data_reader, data_writer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23qA5HisFGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello there
> If i need to replicate two servers that i don't want that the subsriber
> user won't be member of sysadmin and db_owner.
> Which role i need to declare in order to allow transactional replication?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
On the current computer who have problem none of the servers rols are active
The user i'm use to access the other computer has access only to this
database, and it is members of db_public, db_ddladmin, db_datawriter,
db_datareader
And it still cause problems
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OqtwqRuFGHA.3036@.tk2msftngp13.phx.gbl...
> For push IIRC it needs to be in the ddl_admin role (as it needs to create
> tables), and data_reader, data_writer.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23qA5HisFGHA.1312@.TK2MSFTNGP09.phx.gbl...
>

Role for access just information schema.

Which db fixes roles can I use to give a user permission to see all table
information but not the data itself ?
The db_datareader can select on everything, that's not what I want ! I want
to give access just to table information also, give access to the list of
logins and database users.
All readonly mode without seeing data.
Can anybody help on this ?
Thanks in advance,I just did a test that took me about 3 minutes. I added a login named
"test". I then added him as user to a db. No perimssions to do anything and
no roles involved. Just a user. I logged in as him. He can run sp_help, see
logins, and see db users. Am I not understanding your question correctly?
"Tim Conner" <timconner@.hotmail.com> wrote in message
news:#99Z6kPaDHA.1832@.TK2MSFTNGP10.phx.gbl...
> Which db fixes roles can I use to give a user permission to see all table
> information but not the data itself ?
> The db_datareader can select on everything, that's not what I want ! I
want
> to give access just to table information also, give access to the list of
> logins and database users.
> All readonly mode without seeing data.
> Can anybody help on this ?
>
> Thanks in advance,
>sql

Role design issue

I have a strange roles problem.

Is there a way we can accomplish this:

Say, the fact table has 100 records. each record having a new account number (100 accounts in 100 records).

The problem is we have users who have to see only some accounts. For example, user 1 has to see only accounts 1-10, user 2 has to see only accounts 11-20, user 3 has to see only accounts 21-30. and so on...

I know we can create different roles and create perspectives for these roles, but the problem is the list that defines what they can see is a table of 1000 user entries, each row has its own conditions for each user type. (Actually, each condition is a combination of different dimensions. For example, user 1 has to see account 1-10 and products A-D)

Does anyone have any idea on how to do this?

Thanks in advance

Luckily the problem you describe is very common. So common it has it's own name, it is oftenly referred as "Dynamic Dimension Security".

There are quite a few articles about it. Here is one for you http://sqljunkies.com/WebLog/mosha/archive/2004/12/16/5605.aspx

Search for it, you will find quite a bit information.

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

|||Thanks Edward, I looked at it and I think thats what am looking for. will come back for your help if I cant figure something out :-)

Role deleted

I get this error when I try to build my cube.

Error 1 The 'Role' with 'ID' = 'Sales_Marketing' doesn't exist in the collection. 0 0

I had added and deleted some roles, now I receive this message, does anyone have any idea where do I go find this code to delele this role, it no longer exists in my cube? in the designer tool or in management studio.

thank you!

Analysis Services security is based on the role objects that belong to a database. At the same time, individual objects like cubes and dimensions also contain additional security related objects that reference database role objects. If you set permission on a dimension and then you get XMLA script of dimensions defition, you will see DimensionPermission object as part of a XMLA script.

This DimensionPermission object would look like:

<ID>DimensionPermission</ID>

<Name>DimensionPermission</Name>

<ReadDefinition>Allowed</ReadDefinition>

<Read>Allowed</Read>

</DimensionPermission>

As you see it references RoleID.

Most likely in your case there is a leftover permission object that references non-existent role. Script your database as XMLA and try to find which Permission object is referencing your Sales_Marketing role.

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

|||

Thank you, my role is defined in my desktop tool, not on the server which will not allow me to build or deploy my cube. My permission looks like this, one is object ID Role and the second is Role 1. The one I deleted was probably Role 2 but I don't see where that is documented in the xml, I guess I could bring my deployed cube back to my desk top but I wanted to resolve the issue since I had made calculation changes there; Any ideas on how I can identify it? thank you!

</Dimension>

</Dimensions>

<ID>CubePermission</ID>

<Name>CubePermission</Name>

<ReadDefinition>Basic</ReadDefinition>

<Read>Allowed</Read>

<ReadSourceData>Allowed</ReadSourceData>

<CubeDimensionID>Measures</CubeDimensionID>

<Read>Allowed</Read>

</DimensionPermission>

<CubeDimensionID>Dim TIME Fiscal</CubeDimensionID>

<Read>Allowed</Read>

<AttributeID>Calendar Quarter</AttributeID>

</AttributePermission>

</AttributePermissions>

</DimensionPermission>

</DimensionPermissions>

</CubePermission>

</CubePermissions>

|||

You can try to fix your project by opening the project files directly and finding reference to the offending permission object there.

Find the .cub file that contains definition of your cube and see if you can fix it.

Word of caution, it is very easy to mix up XML when editing it. Please make sure you make a copy of your project files before you edit them.

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

|||

Hi,

Thank you for the information, I just rebuilt it from the server, I thought it would be a good thing to know how to repair, but don't have the time for reviewing any more xml, you are correct, it is very easy to mix it up.

Thank you for the help.

Role change in log shipping

As a last step to implementing a log shipping role change...i..e making the
secondary the primary and the primary the secondary, do users change the
servernames so that apps that are referring the SQL server by host name
reflect the actual name .
We would like to implement some kind of disaster recovery using SQL 2000 log
shipping and wanted to know when we do role changes , what are the best ways
to allow the clients to still communicate to the now secondary server that
has been promoted to be the primary.I prefer the application being prepared for this. The app can for instance read the server name in a
configuration file somewhere and you quite simply change the config file to change roles.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%232MdTFolDHA.372@.TK2MSFTNGP11.phx.gbl...
> As a last step to implementing a log shipping role change...i..e making the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
> We would like to implement some kind of disaster recovery using SQL 2000 log
> shipping and wanted to know when we do role changes , what are the best ways
> to allow the clients to still communicate to the now secondary server that
> has been promoted to be the primary.
>
>|||Heelo
> As a last step to implementing a log shipping role change...i..e making
the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
Sure. I'd recommend you to make an alias on DNS server
wich will always lead to your production server. In this case
you'll need to change only this alias. (But if you use large
network you'll need to propagate modifications across all
DNS servers your clients use).
Serge Shakhov

Role based security question

First Off:
SQL Server 2005 Std edit.
RS 2005
Windows Server 2003 Std edit.
Active Directory

I seem to have a unique problem with my Report Server setup. Right now when any of our users attempt to access Report Server, they are prompted for username and password. They enter their username as: domain\username and then enter their password (active directory is enabled). We have no issue with authentication, everyone can get into the server without a problem.

Where the problem lies is after login. No matter what roles I assign to any folder in report server, it seems as though everyone has administrator access to RS. For example, under Site Settings I click Configure Site-Wide Security and add the BUILTIN\Users group as System Users. After applying the change, users are still able to login and change any system level setting they wish to. I've tried using Domain\Domain Users and no luck. I tried adding an individual user as a System User and when logging in as them I can change any system settings I want.

Same goes for setting security on folders in RS. Right now on the Home folder I have security set so that the BUILTIN\Users group has Browser access, yet all users can edit/delete/set properties and generally just behave like admins on any items within the Home folder. I tried adding individual users and limiting their access, other active directory groups, everything but the kitchen sink and yet they are always able to behave like an admin.

The documentation states that after installation of RS, no one but the BUILTIN\Administrator account can access the server. I did not find this to be the case though, as users I had test the installation were able to access it right off the bat. In fact if I remove all groups but BUILTIN\Administrator from the Home folder, everyone can still see all of the reports without issue.

I'll be the first to admit that I am new to all of this and I am wearing many hats at the moment, but this security issue has me stumped. I've searched Google, the forums, read MSDN up and down, have several RS books and yet here I am. Can anyone shed some light on this for me?

Thanks in Advance.

EDIT - I just checked the virtual directories for Reports and ReportServer and the BUILTIN\Users group has the following access: Read, List Folder Content and Read & Execute. Could this be why I can't set role permissions in Report Server?

OMG, nevermind. It seems that after some deep investigation all of the ID's I was using to access the Report Server were buried in a groupthat was nested in another group that was nested within the Administrators group. I got so fed up I created a new ID and logged into RS and security was working as expected. Once again I post, only to discover it is all user error. Sorry all.

Role based security issues

I am attempting to set up a new user that has only the ability to run reports in the report manager.

I have created a new ActiveDirectory entry for DOMAIN\ReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.

I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTIN\Administrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAIN\ReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.

How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?

I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.

Mark

Hello Mark,

I'll tell you how I have mine setup for users only being able to run certain reports.

First off, I created a new item-level role (Site Settings -> Configure item-level role definitions) called Report Viewer and assigned it the 'View reports' & 'View Folders' task. I created my own role just to give the basic amount of permission needed. Then, go to the folder with the reports in it and hit Properties -> Security. Select 'New Role Assignment', check the box for 'Report Viewer', and type the name of your user in the 'Group or user name:' box.

Now, the specific user/group can view the folder and run the reports underneath.

You could also change the security on specific reports instead of the folder if you only want the user(s) to run certain ones.

Hope this helps.

Jarret

|||

Jarret,

This is along the lines as to what I have tried.

Here is a little more about what I am experiencing:

I have a RS management site (e.g. report1.myurl.com/reports) the when I log in (i.e. I get the windows "Connect to report1.myurl.com" login form) with the builtin\administrator account I get full access to all report folders and functionality to manage the site.

I have a ActiveDirectory user Domain\ReportUser1, who is configured in RS as a 'Report Viewer' as you have described in your previous post. However, when I login with this User (i.e. I get the windows "Connect to report1.myurl.com" login form) I enter my Domain\ReportUser1 credentials, I have the same access to the RS management evironment as the builtin\administrator. It does not limit this user to only the folder I have given permissions to. I have checked the entire site, every configuration form, and through SQL2005 Management Studio for RS, and as far as I can tell, this user is set to only see the reports in this directory and does not have Content Manager status.

As an alternative, I have created a parallel named instance of reporting services (e.g report2.myurl.com/reports) and have set up the Directory Security Authentication on the Reports and ReportServer virtual directories to log in as the Domain\ReportUser1. These virtual sites point to the same SQL2005 instance of reporting services as the Default web site virtual directories, and I have access to the report uploaded there.

When I log into RS manager for this alternative site, I am now limited to view only the report folder that was configured for this user, however, I still have the ability to upload files, create folders and database connections (Which I should not, based on the role-based permissions set for this user). In addition, when attempting to run the reports, I do not have access to run them. I.e. Normally the parameters have are in the ReportViewer ActiveX-based control, along with the export, print, refresh, etc. (this has your report parameters in a gray background). When I run for this alternative site, the parameters are visible as what looks like HTML controls and any attempt to run the report returns message:

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

The security involved in Reporting services is extremely frustrating to set up properly, as you not only have the windows, sql and IIS security stars to align to even gain access to the RS site, you then need to configure these Roles, which do not seem to be filtering a ActiveDirectory user's access to these reports as I would expect.

What am I missing?

|||I'm having the same issue. Can you keep me in the loop....sql

Role based security in SSRS

Hi,

could any one please tell me

how to implement the role based security in SSRS

regards

kumar

Thisis good tutorial explain how you can implement this

http://msdn2.microsoft.com/en-us/library/aa337491.aspx

Role Based Security and Child Groups

Hi All,

I have a report running and I am attempting to assign role based security. I added a group to the site level security. The group I added contains child groups. It doesn't seem that report server is looking into the child groups to see if the logged in user is a member of the child group. Is there anyway to get this to work instead of adding all the groups directly? I suspect that report server is using cominterop and cominterop is not traversing the directory tree?

Thanks,

Darren

Interestingly, if you add a group to a report server that contains logins, then remove that group from Management Studio, everyone in the group still has access to the reports.

Role Based Security and Child Groups

Hi All,

Thanks,

Darren

Interestingly, if you add a group to a report server that contains logins, then remove that group from Management Studio, everyone in the group still has access to the reports.

Role based Attribute Hierarchy Visible

Hi,

Is it possible with SQL Server 2005 Standard Edition to set the Attribute Hierarchy Visible property based on a SSAS role? For example the role finance can see some Attribute Hierarchies and the role Sales can't see those hierarchies.

Thanks,

Marc

You can create pespectives. There you can choose what you want to have in any Role based Dimension.

|||

Ok, but that is only an Enterprise edition feature. We use standard edition.

Thanks.

Role Base Security

Does anyone know how to set up the to where when the user access the Report
Manager all they can see under their user name is the the folder with their
reports only. ALso... I want to be able to hide the Properties Tab and the
Site Settings link.Hi Doug:
I'm not entirely clear on your question, but I do have an article here
that might help:
Introduction To Role-Based Security In SQL Server Reporting Services
http://odetocode.com/Articles/215.aspx
The Site Settings link will not appear for users who are not in a role
with authorization to manage the site settings.
--
Scott
http://www.OdeToCode.com
On Thu, 19 Aug 2004 14:27:01 -0700, "doug"
<doug@.discussions.microsoft.com> wrote:
>Does anyone know how to set up the to where when the user access the Report
>Manager all they can see under their user name is the the folder with their
>reports only. ALso... I want to be able to hide the Properties Tab and the
>Site Settings link.sql

Role Assignment without using Report Manager

Hello,
I'm having some problems using the Report Manager, but I *really* need
to make a role assignment now. Can't wait to have the problem with
Report Manager solved.
So, I would like to make this role assignment directly in the
ReportServer database, using the SP SetPolicy or some other SP.
I'm looking for the equivalent of making the "New Role Assignment" and
then
Group or user name: Everyone
Role "Browser" checked
OK
Can anyone tell me exactly how to do this?
Thanks in advance,
Filipe Henriques
PS: By the way, the problem I've got using the Report Manager is that
always appear "The request failed with HTTP status 404: Not Found.",
although I can see perfectly the Report Server page.
Does anyone knows what's the problem?Ok, I found this link:
http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
for "Sample Script for Setting Item-level Security in Reporting Services".
It's all I need.

Role Assignment Group Question

My home folder gives Everyone access to the Browser role. Then I have a
Test folder that contains a Test Report.
I created a TestUser and added them to a TestGroup.
The problem is that the TestUser cannot see my TestReport.
I removed Everyone from the TestReport. TestFolder still has Everyone as a
Browser role assignment. Then I added in my TestGroup, which TestUser is
a part of. I can't figure out why my TestUser can't see the report.
Of course if I leave Everyone on the TestReport then the TestUser can see
it, or if I add the TestUser individually to the Browser role, then he
can see the report. I tried adding both domain\TestGroup and TestGroup
by itself for a role assignment and neither worked.
Does anyone have any ideas?
-RandyIt appears that there might be a bug. It seems to work as documented when
using groups with no spaces in the name. When a group name contains a
space, I haven't been able to get reporting services to recognise the
group.
-Randy
"Randy" <randyvATitolDOTcom> wrote in message
news:eTr6gezbEHA.2388@.TK2MSFTNGP11.phx.gbl...
> My home folder gives Everyone access to the Browser role. Then I have a
> Test folder that contains a Test Report.
> I created a TestUser and added them to a TestGroup.
> The problem is that the TestUser cannot see my TestReport.
> I removed Everyone from the TestReport. TestFolder still has Everyone as
a
> Browser role assignment. Then I added in my TestGroup, which TestUser
is
> a part of. I can't figure out why my TestUser can't see the report.
> Of course if I leave Everyone on the TestReport then the TestUser can see
> it, or if I add the TestUser individually to the Browser role, then he
> can see the report. I tried adding both domain\TestGroup and
TestGroup
> by itself for a role assignment and neither worked.
> Does anyone have any ideas?
> -Randy
>

Role - Read access - Analysis Services 2005

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A (#I/T in danish ? ).

Everything else seems to work fine - There is access to the database and all
dimensions. I tried the test cube security button and it work fine, but from
Excel the measures is not displayed correctly. Excel is showing the measures for users with Administrator privilege.

Best regards,

Ejnar Jakobsgaard
TDC Denmark

I am currently using the ProClarity tool to access the data from the analysis services 2005 - even I am facing the same problem of getting #N/A as values for the measures. I have provided the read only access to the users at the cube and dimension level and it doesn't work - Do have any solution?

Regards,

Narayan Indran

|||

Hi Narayan

Have a look at your "Cell Data" Filters , these should all be ampty. Only use MDX expressions in "Dimension Data" filtering.

That worked for me

Louis

Role - Read access - Analysis Services 2005

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A (#I/T in danish ? ).

Best regards,

Ejnar Jakobsgaard
TDC Denmark

Regards,

Narayan Indran

|||

Hi Narayan

Have a look at your "Cell Data" Filters , these should all be ampty. Only use MDX expressions in "Dimension Data" filtering.

That worked for me

Louis

Role

hi,
The issue for using a role is everytime if I create a new stored
procedure/view/table/udf, I need to go to the role and grant permissions for
that new role.
Am I right?
EdIf all the users in the role need to execute the stored procedure, then yes,
you will have to grant the EXECUTE previleges to the role. You can do this
with a simple GRANT statement like:
GRANT EXECUTE ON usp TO public
where usp is the procedure & public is the role.
Anith|||Thanks for your answer, that helps...
I am thinking in the Production Server, we don't want to grant Execute
permission to the uers, right?
"Anith Sen" wrote:

> If all the users in the role need to execute the stored procedure, then ye
s,
> you will have to grant the EXECUTE previleges to the role. You can do this
> with a simple GRANT statement like:
> GRANT EXECUTE ON usp TO public
> where usp is the procedure & public is the role.
> --
> Anith
>
>|||>> I am thinking in the Production Server, we don't want to grant Execute
I am not sure if I follow you. Stored procedures are meant to be executed by
database users. Without execute permission, regular database users cannot
run any procedures which they do not own.
Btw, I used public as an example in my previous post, all users belong to
public role by default.
Anithsql

ROLAP Vs MOLAP

Hi there:
Does anyone has any suggestions when building a ROLAP Cube? should I go with
ROLAP or go MOLAP with analysis Services? which one is better?
Thanks a lot.
--
JairoIt's usually better to go with MOLAP as it's much faster but will require
more disk space for storing the cube (fact and dimension data).
ROLAP will be slower as the user is basically executing SQL queries againt
the underlying relational database when slicing and dicing.
For more info read up in BOL or check this link
http://businessintelligence.ittoolb...-and-holap-2934
"Jportelas" <Jportelas@.discussions.microsoft.com> wrote in message
news:C1121799-7088-41F9-9EEB-5EBFD4AF2E70@.microsoft.com...
> Hi there:
> Does anyone has any suggestions when building a ROLAP Cube? should I go
> with
> ROLAP or go MOLAP with analysis Services? which one is better?
> Thanks a lot.
> --
> Jairo|||Refer to the following article, especially the section titled "Choose MOLAP
to Improve Query Responsiveness"
Microsoft SQL Server 2000 Analysis Services Performance Guide:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
Generally speaking, MOLAP provides better responsiveness and performance
than ROLAP, except for real time partitions and perhaps very large ( > 2 GB)
sized partitions.
"Jportelas" <Jportelas@.discussions.microsoft.com> wrote in message
news:C1121799-7088-41F9-9EEB-5EBFD4AF2E70@.microsoft.com...
> Hi there:
> Does anyone has any suggestions when building a ROLAP Cube? should I go
> with
> ROLAP or go MOLAP with analysis Services? which one is better?
> Thanks a lot.
> --
> Jairo|||molap always.
a terrabyte database isn't big enough to use rolap. this i know.
maybe if you got a bigger data set, but i really really doubt it.

ROLAP storage and data source impersonation

Hi,

I created a cube with ROLAP storage mode (no proactive caching). My data source impersonation is setup to use the credentials of the current user b/c I want to leverage the object level permission setup in the relational DB object. The expected result is returned when a TSQL statement is executed, however, the cell returned "#Error" when a MDX query is executed. I turned on Profiler but no TSQL statement is executed as a result of the MDX query. If I change the impersonation to use the service account, I can see the TSQL statement in Profiler when I execute the MDX query. But the login id is the service account and the desired relational DB object permission is not applied.

The other challenge is cube processing is not possible when impersonation is not set to service account. Is there a way to get the best of both world - ability to process the cube and pass user credential from the MDX query to the TSQL query?

Thanks.

It is not possible to use ImpersonateCurrentUser for neither cube processing nor ROLAP queries. Sorry.

ROLAP and partitioning questions

I have done all my dev work on a new AS database and now I need to start tuning processing/partitioning for deployment in production. The database needs to be able to drill down to leaf level and so I have implemented a fact dimension to do so. Testing with this dimension as MOLAP yields a 2.8 GB for 20 days of data partitioned by day. With the same partition scheme, if I change the dimension to ROLAP, the database goes down to 124 MB.

With MOLAP, after the cube is processed, it opens nearly immediately in browse or in Proclarity by opening the cube. Good performance, but too big. With ROLAP, it has been "Loading..." for over an hour. Good size, but terrible performance. Is this normal behavior? My guess is that since it is trying to load the default row/column view, it thinks it has to sum all the ROLAP leaves? I'll have to try some MDX against it for comparison.

Should it take just as long to process a db with a ROLAP dim as opposed to a MOLAP dim? The fact dimension itself processes instantaneously, but when processing the database it spends quite a bit of time in the ROLAP fact dimension. How can I see where all the time is being spent? Both seem to take ~1.5 hours to build.

With partitioning, is it best practice to keep the solution with a single partition and create the partition scheme programatically against the server instance?

I have a case where I need to delete and add an arbitrary number of daily partitions each day depending on what data comes in. I am looking at using ASCMD to do this as our ETL is third party. I can see how to delete and create the objects, but how can I tell what items need to be processed after the partition work is done. Is there any easy way to figure out the list/order of items that needs to be processed?

Thanks,

I will always recommend MOLAP berfore ROLAP. Have you 100 percent aggregation level on your partions? If so you should always start with a lower percentage for aggregations, like 20-30 percent. You can also get large cubes with many dimensions, like 20-30 or more, in one cube.

ROLAP builds summary tables i the relational database and it is always slower than MOLAP.

Use SQL Server profiler to study cube processing.

Regards

Thomas Ivarsson

|||

If your cube is partitioned by day, you need to run a distinct query on day and iter through the partitions to be processed.

MOLAP cubes are larger but if they are partitioned and you have usage based optimization implemented, you will get good performance. Also, go ahead and prune any unnecessary attributes from the dimensions, i.e. ETL load date, etc. This will reduce the size of the dimensions and the cube. A customer or a product dimension would normally have a lot of attributes that you can prune, i.e. BOM info, address, etc.

|||

Agreed. That is why I was using MOLAP. With the fact dimension as MOLAP, my partitions were only at 13% aggregation and my db size is still 2.8 GB.

If I move the fact dimension to ROLAP the db size goes down to 153 MB. I do seem to be having better luck with performance now though. It appears that it is best to redesign your aggregations and reprocess when you change a dimension's storage model. I have changed my aggregations, which are now at 24%. The browse feature in VS is still impossibly slow to come up, but Proclarity and SQL Server Management Studio now appear to be playing nice. It looked like before all 3 were taking > 1 hours to load the metadata.

The thing I still don't understand is when I process the ROLAP dimension, it runs nearly instantaneously as expected. However, when I process the database it still takes 75 minutes to process the ROLAP dimension as part of the cube? Does that make sense? I guess I was thinking using ROLAP would speed the cube build up as well as save on storage.

Keehan

|||That is why I was using MOLAP. When I realized how much space the fact dimension is consuming, I am forced to shift. I have already trimmed all the unnecessary attributes. The fact dimension now only contains items that users may need to drill down to like item_number, equipment_id, etc.|||

I have not used ROLAP in SQL2005 apart from with Microstrategy.

I have a large cube that is 6gb and performance is pretty good if it is designed correctly and aggregated. The SQL database is 50gb.

If you want performance, then MOLAP is the way to go. Partitioning does help with processing multiple partitions and query performance.

You also do not specify how many days data are you planning to keep. It should really be a sliding window. The expectation of the end user to be able to drill down to the leaf level comes from using SQL query tools. I have a similar battle. You should set the expectation that they will only be allowed to drill down to leaf level for the last 30 to 90 days. Beyond that, it becomes unmanageable. The first thing to ask the end users is what is the use case and what decisions are they going to base on the transactional data. A typical scenario in the decision making process is multi-steps:

An analytics view highlights outliers|||

The last line seems to be the key, we have a similar cube which use to grow at about 200,000 records a day. My Data modeller and me

decided to keep the transactions out of the cube, this meant our fact size grows 7100 records a day, peformance has hugely improved, downside is that we

have to drilltrough into the transaction system now, if you need mass datastore contact EMC in Hopkinton,MA !!

thanks

Adi

ROLAP and partitioning questions

With partitioning, is it best practice to keep the solution with a single partition and create the partition scheme programatically against the server instance?

Thanks,

ROLAP builds summary tables i the relational database and it is always slower than MOLAP.

Use SQL Server profiler to study cube processing.

Regards

Thomas Ivarsson

|||

If your cube is partitioned by day, you need to run a distinct query on day and iter through the partitions to be processed.

|||

Agreed. That is why I was using MOLAP. With the fact dimension as MOLAP, my partitions were only at 13% aggregation and my db size is still 2.8 GB.

Keehan

I have not used ROLAP in SQL2005 apart from with Microstrategy.

I have a large cube that is 6gb and performance is pretty good if it is designed correctly and aggregated. The SQL database is 50gb.

If you want performance, then MOLAP is the way to go. Partitioning does help with processing multiple partitions and query performance.

An analytics view highlights outliers|||

The last line seems to be the key, we have a similar cube which use to grow at about 200,000 records a day. My Data modeller and me

decided to keep the transactions out of the cube, this meant our fact size grows 7100 records a day, peformance has hugely improved, downside is that we

have to drilltrough into the transaction system now, if you need mass datastore contact EMC in Hopkinton,MA !!

thanks

Adi

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1
)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECTHi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') =
1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>sql

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECTHi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>> We had difficulty dropping a table the other day as the system stated
>> that another object was dependant on it - this is staging database so we
>> don't do that kind of thing.
>> Investigation showed that a view _hypmv_0 had been created, this must
>> have been generated by the system as the box is tightly controlled and
>> only used by me, plus the name is a bit left-field for us.
>> The view had been created with the SCHEMABINDING attribute, partial DDL
>> follows. Can anybody shed any light on this?
>> Thanks,
>> Paul
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
>> drop view [dbo].[_hypmv_0]
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> SET ANSI_NULLS ON
>> GO
>> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
Hi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>
|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>

Friday, March 30, 2012

Blog Archive

About Me