Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

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

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

Dynamic Security and Rool ups

Hi

I thought I had this dynamic security worked out but I guess not.

this is on AS 2000.

I have a Fact table - one of the columns is a companyid, this is joined to the company dimension.

For security I have another table "empcompany" which lists all the users (ntusername) and the company codes they can access. I created a member property against the company code and used MDX similar to this:

filter([Companycode].[Companycode].members,([Companycode].CurrentMember.Properties("ntusername") = username))

The problem is that when I join the "empcompany" table to either the fact or to the company dimension instead of getting 1000 rows I get 80000+ rows and my numbers are wrong. Most of the users have access to more than 1 company.

Any one have any ideas?

Thanks

Steve

Hi Steve,

My suggestion would be to try the "Security Fact Table" approach with the "empcompany" table, as discussed in slides 18-32 of this webcast deck (you're using the "Member Property" approach above). In the 2nd approach, you don't need to join "empcompany" to the fact table - rather, it becomes the fact table for a "Permissions" cube, which is combined in a virtual cube:

http://support.microsoft.com/kb/828343/

>>

Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000 Analysis Services

...

Dynamic Security
The Three Basic Approaches

Member property approach

Permission data (e.g. UserName) is stored at the desired dimension level

Security fact table approach

A permissions cube is combined with original source cube using a virtual cube

Filter members at the leaf level

Filter members at the non-leaf level

User-defined function callout

Call a user-defined function to reference an outside source (e.g. external RDBMS)

Dynamic Measure?

Hi,
I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate.
I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?

Should I create a calculated cell? (I tried that, but mdx does not work)
I used : Count({[Measures].[End Key]})< 366
Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.

Mdxyou can create a count measure. I found out by myself

dwh problem

Hi,
I have to choose between
a) using one single fact table
b) using one fact table and several dimension tables
I know that if you have no dimension tables (i.e. you store all the
information on one single fact table) your sql will not include any joins. I
understand joins penalize performance so it would make sense to go for this
option. Can someone tell me if a star schema approach with surrogate keys on
the fact table pointing to each dimension table would be less performant than
the single fact table approach? What would be more efficient if I want to use
Business Objects as the reporting tool? What are the pros and cons of each
option?
If anyone has previously had to make a similar decision I would very much
appreciate the help.
Thanks a lot,
Roberto
Hi rufian,
I can't completely answer your question, but my experience so far has been
that using the surrogate keys is worth doing. I have built fact tables before
that should everything in one table and it didn't significantly improve
performance.
Analysis services 2005 also seems to be happier with the star schema, but I
don't know much about it.
One of the biggest benefits of the star schema is that you can add more
attributes and dimensions much more easily.
If you know how big your fact table will be, then the decision may be made
for you. As soon as your fact table starts getting big, you should use
dimension tables. If you have 1 million rows in the fact table, there is no
way I would skip the dimension tables. Even for 100,000 I probably wouldn't
skip them.
I hope that helps.
"rufian" wrote:

> Hi,
> I have to choose between
> a) using one single fact table
> b) using one fact table and several dimension tables
> I know that if you have no dimension tables (i.e. you store all the
> information on one single fact table) your sql will not include any joins. I
> understand joins penalize performance so it would make sense to go for this
> option. Can someone tell me if a star schema approach with surrogate keys on
> the fact table pointing to each dimension table would be less performant than
> the single fact table approach? What would be more efficient if I want to use
> Business Objects as the reporting tool? What are the pros and cons of each
> option?
> If anyone has previously had to make a similar decision I would very much
> appreciate the help.
> Thanks a lot,
> Roberto
>
|||I completely agree: the single fact table is not easy to manage in the
future; moreover, it is less performant due to the very high
denormalization (you create an higher I/O work for SQL Server).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Eager to Learn ha scritto:
[vbcol=seagreen]
> Hi rufian,
> I can't completely answer your question, but my experience so far has been
> that using the surrogate keys is worth doing. I have built fact tables before
> that should everything in one table and it didn't significantly improve
> performance.
> Analysis services 2005 also seems to be happier with the star schema, but I
> don't know much about it.
> One of the biggest benefits of the star schema is that you can add more
> attributes and dimensions much more easily.
> If you know how big your fact table will be, then the decision may be made
> for you. As soon as your fact table starts getting big, you should use
> dimension tables. If you have 1 million rows in the fact table, there is no
> way I would skip the dimension tables. Even for 100,000 I probably wouldn't
> skip them.
> I hope that helps.
> "rufian" wrote:

dwh problem

Hi,
I have to choose between
a) using one single fact table
b) using one fact table and several dimension tables
I know that if you have no dimension tables (i.e. you store all the
information on one single fact table) your sql will not include any joins. I
understand joins penalize performance so it would make sense to go for this
option. Can someone tell me if a star schema approach with surrogate keys on
the fact table pointing to each dimension table would be less performant tha
n
the single fact table approach? What would be more efficient if I want to us
e
Business Objects as the reporting tool? What are the pros and cons of each
option?
If anyone has previously had to make a similar decision I would very much
appreciate the help.
Thanks a lot,
RobertoHi rufian,
I can't completely answer your question, but my experience so far has been
that using the surrogate keys is worth doing. I have built fact tables befor
e
that should everything in one table and it didn't significantly improve
performance.
Analysis services 2005 also seems to be happier with the star schema, but I
don't know much about it.
One of the biggest benefits of the star schema is that you can add more
attributes and dimensions much more easily.
If you know how big your fact table will be, then the decision may be made
for you. As soon as your fact table starts getting big, you should use
dimension tables. If you have 1 million rows in the fact table, there is no
way I would skip the dimension tables. Even for 100,000 I probably wouldn't
skip them.
I hope that helps.
"rufian" wrote:

> Hi,
> I have to choose between
> a) using one single fact table
> b) using one fact table and several dimension tables
> I know that if you have no dimension tables (i.e. you store all the
> information on one single fact table) your sql will not include any joins.
I
> understand joins penalize performance so it would make sense to go for thi
s
> option. Can someone tell me if a star schema approach with surrogate keys
on
> the fact table pointing to each dimension table would be less performant t
han
> the single fact table approach? What would be more efficient if I want to
use
> Business Objects as the reporting tool? What are the pros and cons of each
> option?
> If anyone has previously had to make a similar decision I would very much
> appreciate the help.
> Thanks a lot,
> Roberto
>|||I completely agree: the single fact table is not easy to manage in the
future; moreover, it is less performant due to the very high
denormalization (you create an higher I/O work for SQL Server).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Eager to Learn ha scritto:
[vbcol=seagreen]
> Hi rufian,
> I can't completely answer your question, but my experience so far has been
> that using the surrogate keys is worth doing. I have built fact tables bef
ore
> that should everything in one table and it didn't significantly improve
> performance.
> Analysis services 2005 also seems to be happier with the star schema, but
I
> don't know much about it.
> One of the biggest benefits of the star schema is that you can add more
> attributes and dimensions much more easily.
> If you know how big your fact table will be, then the decision may be made
> for you. As soon as your fact table starts getting big, you should use
> dimension tables. If you have 1 million rows in the fact table, there is n
o
> way I would skip the dimension tables. Even for 100,000 I probably wouldn'
t
> skip them.
> I hope that helps.
> "rufian" wrote:
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techin...scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>