MS SQL Server OLAP - Parent-Child Hierarchies and Visual Totals

Author

Parent-Child Hierarchies and Visual Totals

Acius

2006-01-27, 3:23 am

In SSAS 2005 is it possible to use visual totals when defining security on a
parent-child hierarchy?

e.g.If you put
[Employee].[Employees].&[277]
in the allowed member set it works

Then if you check Enable Visual Totals and try browsing as a user with the
role the browser returns an error.
The 'Employees' attribute in the 'Employee' dimension has a generated
dimension security expression that is not valid.

Marius Dumitru

2006-01-28, 8:23 pm

Indeed, this is a limitation of Analysis Services - dimension security
visual totals may only be applied to regular (non-parent-child) attributes.
We're considering removing this restriction in the next product release, or
even a future service pack for SSAS 2005.

--
Hope this helps

Marius

"Acius" <acius@nospam.nospam> wrote in message
news:6481F812-071F-4681-BC9E- 82290632283B@microso
ft.com...
> In SSAS 2005 is it possible to use visual totals when defining security on
> a
> parent-child hierarchy?
>
> e.g.If you put
> [Employee].[Employees].&[277]
> in the allowed member set it works
>
> Then if you check Enable Visual Totals and try browsing as a user with the
> role the browser returns an error.
> The 'Employees' attribute in the 'Employee' dimension has a generated
> dimension security expression that is not valid.

Acius

2006-01-29, 8:23 pm

In 2000 we could apply a maximum level and visual totals to parent-child
dimensions (we're using dynamic dimension security). Any suggestions how to
apply this in SSAS2005 (Given that people aren't allowed to see data above
theirs).

"Marius Dumitru" wrote:

> Indeed, this is a limitation of Analysis Services - dimension security
> visual totals may only be applied to regular (non-parent-child) attributes.
> We're considering removing this restriction in the next product release, or
> even a future service pack for SSAS 2005.
>
> --
> Hope this helps
>
> Marius
>
> "Acius" <acius@nospam.nospam> wrote in message
> news:6481F812-071F-4681-BC9E- 82290632283B@microso
ft.com...
>
>
>

Jiayi Wang [MSFT]

2006-01-30, 3:23 am

Dear Sir,

This is Jia yi from Microsoft SQL Server team.

Just for you information, Dimension security does not support visual totals
on a parent attribute in a parent-child dimension. If a user belongs to a
role that enables visual totals on a parent attribute in a parent-child
dimension, the user will not be able to connect to the cube that contains
the parent-child dimension. Also, when the user tries to connect to the
cube that contains the parent-child dimension, the user may receive an
error message such as one of the following messages:

The '<dimension>' parent-child dimension has dimension security defined on
the key attribute, which is not allowed. You could, alternately, define the
equivalent dimension security on the parent attribute.

The '<attribute>' attribute in the '<dimension>' dimension has a generated
dimension security expression that is not valid.

If you have any further quesitons, please help to take a look at
910228 SQL Server 2005 Readme and installation requirements
http://support.microsoft.com/defaul...kb;EN-US;910228

If you have any questios, please let me know.

Thanks,
Jiayi

Marius Dumitru

2006-01-30, 7:24 am

So far, the only workaround I'm aware of is to flatten the parent-child
dimension into a regular multi-level dimension.

There's a good probability that parent-child visual totals are going to be
supported as part of the SP2 release, but no decision has been made yet.

--
Hope this helps

Marius

"Acius" <acius@nospam.nospam> wrote in message
news:38605426-C952-45F9-8E23- CEC491BC2E3D@microso
ft.com...[color=darkred]
> In 2000 we could apply a maximum level and visual totals to parent-child
> dimensions (we're using dynamic dimension security). Any suggestions how
> to
> apply this in SSAS2005 (Given that people aren't allowed to see data above
> theirs).
>
> "Marius Dumitru" wrote:
>

Acius

2006-01-30, 8:24 pm

Can you please expand on
"You could, alternately, define the equivalent dimension security on the
parent attribute."
Is there an example? (We're currently using a function to return the valid
set for the user)

When is SP2 scheduled? How do we put in our votes for what to include?
(Is there an SP1?)

"Jiayi Wang [MSFT]" wrote:

> Dear Sir,
>
> This is Jia yi from Microsoft SQL Server team.
>
> Just for you information, Dimension security does not support visual totals
> on a parent attribute in a parent-child dimension. If a user belongs to a
> role that enables visual totals on a parent attribute in a parent-child
> dimension, the user will not be able to connect to the cube that contains
> the parent-child dimension. Also, when the user tries to connect to the
> cube that contains the parent-child dimension, the user may receive an
> error message such as one of the following messages:
>
> The '<dimension>' parent-child dimension has dimension security defined on
> the key attribute, which is not allowed. You could, alternately, define the
> equivalent dimension security on the parent attribute.
>
> The '<attribute>' attribute in the '<dimension>' dimension has a generated
> dimension security expression that is not valid.
>
> If you have any further quesitons, please help to take a look at
> 910228 SQL Server 2005 Readme and installation requirements
> http://support.microsoft.com/defaul...kb;EN-US;910228
>
> If you have any questios, please let me know.
>
> Thanks,
> Jiayi
>
>
>
>

Jiayi Wang [MSFT]

2006-01-31, 3:23 am

Dear Acius,

Thanks for your update.

As Marius has mentioned that it's the only workaround currently we are
aware.

Also, we will have a SP1 release. It will be released earlier this year.

Thanks,
Jiayi
--------------------
| Thread-Topic: Parent-Child Hierarchies and Visual Totals
| thread-index: AcYl5+heEG2P6mrXQOun
sqrP6S2WcA==
| X-WBNR-Posting-Host: 203.202.23.100
| From: "=?Utf-8?B?QWNpdXM=?=" <acius@nospam.nospam>
| References: <6481F812-071F-4681-BC9E- 82290632283B@microso
ft.com>
<axdRVEXJGHA.3696@TK2MSFTNGXA02.phx.gbl>
| Subject: RE: Parent-Child Hierarchies and Visual Totals
| Date: Mon, 30 Jan 2006 13:55:39 -0800
| Lines: 45
| Message-ID: <67A0955E-6160-49A5-BBFE- 8BE260CC0992@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:66730
| X-Tomcat-NG: microsoft.public.sqlserver.olap
|
| Can you please expand on
| "You could, alternately, define the equivalent dimension security on the
| parent attribute."
| Is there an example? (We're currently using a function to return the
valid
| set for the user)
|
| When is SP2 scheduled? How do we put in our votes for what to include?
| (Is there an SP1?)
|
|
|
|
| "Jiayi Wang [MSFT]" wrote:
|
| > Dear Sir,
| >
| > This is Jia yi from Microsoft SQL Server team.
| >
| > Just for you information, Dimension security does not support visual
totals
| > on a parent attribute in a parent-child dimension. If a user belongs to
a
| > role that enables visual totals on a parent attribute in a parent-child
| > dimension, the user will not be able to connect to the cube that
contains
| > the parent-child dimension. Also, when the user tries to connect to the
| > cube that contains the parent-child dimension, the user may receive an
| > error message such as one of the following messages:
| >
| > The '<dimension>' parent-child dimension has dimension security defined
on
| > the key attribute, which is not allowed. You could, alternately, define
the
| > equivalent dimension security on the parent attribute.
| >
| > The '<attribute>' attribute in the '<dimension>' dimension has a
generated
| > dimension security expression that is not valid.
| >
| > If you have any further quesitons, please help to take a look at
| > 910228 SQL Server 2005 Readme and installation requirements
| > http://support.microsoft.com/defaul...kb;EN-US;910228
| >
| > If you have any questios, please let me know.
| >
| > Thanks,
| > Jiayi
| >
| >
| >
| >
|

Marius Dumitru \(MS\)

2006-01-31, 7:24 am

> "You could, alternately, define the equivalent dimension security on the
> parent attribute."

This constitutes a workaround for a different issue, and won't help with the
visual totals problem.

> When is SP2 scheduled? How do we put in our votes for what to include? (Is
> there an SP1?)

Service pack dates and planned service pack changes aren't public yet.
You may want to contact customer support in order to put in a request for
addressing this problem...

--
Hope this helps

Marius

"Jiayi Wang [MSFT]" <jiayiw@online.microsoft.com> wrote in message
news:lHlSSQkJGHA.1240@TK2MSFTNGXA02.phx.gbl...
> Dear Acius,
>
> Thanks for your update.
>
> As Marius has mentioned that it's the only workaround currently we are
> aware.
>
> Also, we will have a SP1 release. It will be released earlier this year.
>
> Thanks,
> Jiayi
> --------------------
> | Thread-Topic: Parent-Child Hierarchies and Visual Totals
> | thread-index: AcYl5+heEG2P6mrXQOun
sqrP6S2WcA==
> | X-WBNR-Posting-Host: 203.202.23.100
> | From: "=?Utf-8?B?QWNpdXM=?=" <acius@nospam.nospam>
> | References: <6481F812-071F-4681-BC9E- 82290632283B@microso
ft.com>
> <axdRVEXJGHA.3696@TK2MSFTNGXA02.phx.gbl>
> | Subject: RE: Parent-Child Hierarchies and Visual Totals
> | Date: Mon, 30 Jan 2006 13:55:39 -0800
> | Lines: 45
> | Message-ID: <67A0955E-6160-49A5-BBFE- 8BE260CC0992@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.olap
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:66730
> | X-Tomcat-NG: microsoft.public.sqlserver.olap
> |
> | Can you please expand on
> | "You could, alternately, define the equivalent dimension security on the
> | parent attribute."
> | Is there an example? (We're currently using a function to return the
> valid
> | set for the user)
> |
> | When is SP2 scheduled? How do we put in our votes for what to include?
> | (Is there an SP1?)
> |
> |
> |
> |
> | "Jiayi Wang [MSFT]" wrote:
> |
> | > Dear Sir,
> | >
> | > This is Jia yi from Microsoft SQL Server team.
> | >
> | > Just for you information, Dimension security does not support visual
> totals
> | > on a parent attribute in a parent-child dimension. If a user belongs
> to
> a
> | > role that enables visual totals on a parent attribute in a
> parent-child
> | > dimension, the user will not be able to connect to the cube that
> contains
> | > the parent-child dimension. Also, when the user tries to connect to
> the
> | > cube that contains the parent-child dimension, the user may receive an
> | > error message such as one of the following messages:
> | >
> | > The '<dimension>' parent-child dimension has dimension security
> defined
> on
> | > the key attribute, which is not allowed. You could, alternately,
> define
> the
> | > equivalent dimension security on the parent attribute.
> | >
> | > The '<attribute>' attribute in the '<dimension>' dimension has a
> generated
> | > dimension security expression that is not valid.
> | >
> | > If you have any further quesitons, please help to take a look at
> | > 910228 SQL Server 2005 Readme and installation requirements
> | > http://support.microsoft.com/defaul...kb;EN-US;910228
> | >
> | > If you have any questios, please let me know.
> | >
> | > Thanks,
> | > Jiayi
> | >
> | >
> | >
> | >
> |
>

Sponsored Links