How to filter Time Dimension so certain users only can see data before
today (Now-1 day)?

I have Poduct Cube and OrderDate as Time Dimension. My OLAP Cube data
is consumed by internal part and customers. We want to restrict
customers' access right so they cannot see data which order date is
today, they only can see the data that exist before today. How can it
done by using dimension security ?
An example would be greatly appreciated

you can use dynamic security
set the allowed members to a formula like this:
{Time.[1990-01-01]:strtomember("[Time]." + VBA!Format(VBA!Now(),
"yyyy-mm-dd"))

(1990-01-01 is the first member of your time dimension)
I have not the VBA function to get the previous day...
maybe:
dateadd("d", -1, now)


"Resant" <resant_v (AT) yahoo (DOT) com> a écrit dans le message de news:
1102997078.673247.198540 (AT) c13g20...oglegroups.com...

Thanks for ur reply. I've got ur idea (=' '=)

I try to implement your idea, but I prefer to set the denied members -
because it's more simple - with a formula :

StrToMember("[ReceivedDate].[" + "12/14/2004" +"]")

It's work, but when I change it into:

Members("[ReceivedDate].[" + convert(char, Now(), 101 + "]")

It's doesn't work....
Pls help me how to convert Today into "mm/dd/yyyy" format in MDX?
Super Thx

Sorry, it's me again...

I've success in formula syntax :
{StrToMember("[ReceivedDate].[" + VBA!Format(VBA!Now(), "mm/dd/yyyy") +
"]")}

but when I click OK at 'Edit a Database Role' window, there's an error
:
Error occured while processing security for cube 'X' (Security error
[Dimension security: Unable to evaluate MDX expression(s)]...
Please help me out.

Thx a lot

use the vba function to format your date to the target format
StrToMember("[ReceivedDate].[" + VBA!Format(VBA!Now(), "mm/dd/yyyy")+ "]")

the convert functiion is an SQL statement, not an MDX function
also, you can use a property in your ReceivedDate dimension.
a property like "Current day" (which contain 0 or 1)
and a formula like this:
filter([ReceivedDate].[Dates],
[ReceivedDate].currentmember.property("Current day") ="1").item(0)

"Resant" <resant_v (AT) yahoo (DOT) com> a écrit dans le message de news:
1103080079.161255.111930 (AT) f14g20...oglegroups.com...

oups...
filter([ReceivedDate].[Dates].MEMBERS,
[ReceivedDate].currentmember.property("Current day") ="1").item(0)

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> a écrit dans le message de news:
%23BlUM2l4EHA.2288 (AT) TK2MSFTNGP11 (DOT) phx.gbl...

Hi Jéjé,

But I've read that member properties will slow down the performance,
beside that, my time dimension have so many members.
Is there another way without using properties?

Thx a lot

slowing down <> unresponsive ;-)
in your case, dynamic security will take more ressource at the server level,
because AS must create an in memory copy of the dimension to apply the
security.

another way is to use a second time dimension filtered to not display the
current day (using a filter in the source query)
in this case the physical dimension himself doesn't contain the current day.


"Resant" <resant_v (AT) yahoo (DOT) com> a écrit dans le message de news:
1103087406.172133.105270 (AT) c13g20...oglegroups.com...
Hi Jéjé,

But I've read that member properties will slow down the performance,
beside that, my time dimension have so many members.
Is there another way without using properties?

Thx a lot

The first solution's work after I change StrToMember to Members, so the
query will be:
{Members("[ReceivedDate].[" + VBA!Format(VBA!Now(),"mm/dd/yyyy") +
"]")}

But now, the problem is : Today Date is not always exist, so Analysis
Server cannot find dimension member. I try to use 'If function' which
the pseudocode as below :
If Max(ReceiveDate)=Now() Then
Max(ReceivedDate)
Else
-- unfiltered
Is it possible to change it into MDX query? I'm curious....