Dimensional Modelling and Userbased Reporting

We are using SSAS 2005 for the cubes and reports will be created in Reporting Services as well as Proclarity Desktop Professional (all using windows integrated authentication). The reports will be displayed using a Sharepoint portal, again using the windows integrated authentication.

We have a Fact Table like FactRevenues which has revenueamount and a few other measures.

It is linked to 2 Dimension tables - DimProjects and DimCustomers through ProjectCode and CustomerCode.

DimProjects contains 2 dimension fields (apart from many others) - ProjectMgr and ProjectDir.

Similarly DimCustomers contains 3 fields - AccountMgr,AccountDir and EngagementDir.

We have an User Dimension which is the referenced dimension. DimUser contains UserID and other details of the user. We store the NT login(or at least something from which NT login can be obtained) in the UserID.

The five fields mentioned above - ProjectMgr, ProjectDir,AccountMgr, AccountDir and EngagementDir stores the UserID and are linked to the UserID in the User Dimension.

We have created hierarchies/pseudo dimensions which link like RevenueFact-ProjectMgr-User, RevenueFact-ProjectDir-User, RevenueFact-AccountMgr-User,RevenueFact-AccountDir-User and RevenueFact-EngagementDir-User. So, for the MDX expression we can use these to check against the logged in User.

The Problem:

Our requirement is, the person logged in should be able to see only the details (measure values) that are relevant to him/her. Basically, the aggregate of RevenueAmount should be done based on the following conditions:

If the logged in user is a Project Manager for some projects, then the person should only be able to see the aggregated measure for the projects for which he/she is the manager (checked from the ProjectMgr field). Similarly for ProjectDir.

If the logged in user is an Account Manager for some clients, then the person should only be able to see the aggregated measure for the clients that he/she handles (checked from the AccountMgrfield). Similarly for AccountDir and EngagementDir.

There are possibilities, though remote, that a single person can be AccountDir as well as ProjectDir. Basically, the system should support the possiblity for a person to be in any combination of the five fields. This shouldn't be an issue as this will be taken care of automatically once we set up the dynamic dimension security for each of these dimensions.

There will a different set of users - SuperUser/Admins - who will be able to view all the details without any restrictions. We HAVE NO ISSUES WITH THIS AS we created a role specifically for Admins without any restrictions and a role for others (everyone) which will be applied with the dynamic dimension security.

What we tried:

We have actually gone through the links that you have sent earlier, when we were trying to solve the issue. But it seems like we are missing some small thing.

Our MDX for dimension security

Filter( [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS] = UserName)

UserName supposedly being the function for obtaining the currently logged in user.

But this didn’t seem to restrict the users.

Your syntax for the filter function does not look correct. You need to pass it a set and then the criteria with which to filter that set. I would expect to see something more like the following:

Filter(

[DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].members

, [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].CurrentMember.Name = UserName()

)

|||

This following is the table I created through SSAS browser having UserID,EGName and Mothwisesales Column

where USerID is the member of [Account Mgr].[UsersID] Attribute

I want to show the only row when perticalur User LogsIn(NTlogin) insted of showing the details of all User.He should see only his details(I want to restrict this in Cube level rather on SSRS/Proclarity).

I created a Hierachy i.e UserHierachy -->Role->UserID

Role attribute always contains{User,Manager}.If UserID is belongs to User Role He ll be see only his details from NTLogin and if UserID is belongs to ManagerRole He ll be see all the userdetails.

Please help me regarding this.

Thank u

with regards

Saroj

userID

Mar

chandrashekar.cs

EG11

$700.00

EG12

$495.63

EG14

$1,500.00

EG21

$15,600.83

EG24

$0.83

EG26

$8,726.37

EG34

$2,103.34

EG39

$300.00

EG3P

$197.55

EG41

$5,866.68

EG42

$16,900.00

EG51

$2,200.00

EG52

$1,400.83

EG53

$0.00

EG61

$11,757.92

EG62

$199.96

EG65

$363.40

EG72

$3,400.00

EG74

$2,400.00

madhankumar.s

$228,385.98

ramaprasath.mss

$6,402.35

sarojkumar.nishanka

$6,308.43

sethumadhavan.sb

$284,112.19

sql

Dimensional modelling advice

I am a former DBA that has moved into a totally new role and
environment, as a data architect on an enterprise data warehousing
project for the national statistics office. I've started reading the
Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
the approach that I'd like to follow with the data modelling, ie.
using the data warehouse bus architecture approach that is advocated
by Kimball.
However, the main concern that I have with this approach is that we
have hundreds of different data sources in the form of surveys that we
conduct as well as data that we share from other govt and non-govt
agencies. So the data that we will be loading into our DW is not
sourced from our OLTP systems but from a multitude of other businesses
and households. If we were to model the data on a business process
basis there is the potential to end up with dozens/hundreds of star
schemas, as well as the ongoing maintenance of systems development for
when new surveys are created. Internally there is some support for
developing a generic dimensional model that has the flexibility to
accept any type of data (be it of an economic, social or environmental
nature). I could see that this would work as far as loading data into
the warehouse goes, but think that it would introduce a layer of
abstraction that would make it difficult for the business users to
understand and query the data (or do BI tools get around this). So
the first question is, how suitable is generic modelling for a data
warehouse implementation and if not, how big an issue is deploying a
large number of star schemas for an integrated data warehouse?
Secondly, a question around granularity. We have many situations
where different surveys request similar data but with slight
variations. eg. We could ask about a businesses export totals in a
number of different ways in several surveys - total exports of all
products for 2003, exports of fruit and veges for April, exports of
apples for Q3 2004, exports of apples for June in US$, total exports
to Europe etc. How is this handled in a dimensional model? Is it
possible to model this in a single star schema when the granularity
appears to be different. NB. Is may not be possible to derive
aggregate totals from data with a finer grain because a different
subset of businesses will have been asked a different set of
questions, so aggregate totals might not include the full subset of
data that is needed for an accurate total.
Lastly, does anybody know of an example of a dimensional model that
could be used as a starting point for survey collection and analysis
data warehouse?
TIA,
Keith.Keith -
I would suggest that you also purchase another Kimball book entitled
"Data Warehouse ETL Toolkit"; they should really be sold as a pair.
Approaching a data warehouse with a strong relational 3nf
bakcground can be difficult to understand and implement the
denormalizing structures needed for a successful dw.
I've implemented large dw systems with numerous external sources of
data, and I've found that if one really boils the inbound information
the complexity is not what it seems at first.
FWIW.
\
b.
Keith Chung wrote:

> I am a former DBA that has moved into a totally new role and
> environment, as a data architect on an enterprise data warehousing
> project for the national statistics office. I've started reading the
> Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
> the approach that I'd like to follow with the data modelling, ie.
> using the data warehouse bus architecture approach that is advocated
> by Kimball.
> However, the main concern that I have with this approach is that we
> have hundreds of different data sources in the form of surveys that we
> conduct as well as data that we share from other govt and non-govt
> agencies. So the data that we will be loading into our DW is not
> sourced from our OLTP systems but from a multitude of other businesses
> and households. If we were to model the data on a business process
> basis there is the potential to end up with dozens/hundreds of star
> schemas, as well as the ongoing maintenance of systems development for
> when new surveys are created. Internally there is some support for
> developing a generic dimensional model that has the flexibility to
> accept any type of data (be it of an economic, social or environmental
> nature). I could see that this would work as far as loading data into
> the warehouse goes, but think that it would introduce a layer of
> abstraction that would make it difficult for the business users to
> understand and query the data (or do BI tools get around this). So
> the first question is, how suitable is generic modelling for a data
> warehouse implementation and if not, how big an issue is deploying a
> large number of star schemas for an integrated data warehouse?
> Secondly, a question around granularity. We have many situations
> where different surveys request similar data but with slight
> variations. eg. We could ask about a businesses export totals in a
> number of different ways in several surveys - total exports of all
> products for 2003, exports of fruit and veges for April, exports of
> apples for Q3 2004, exports of apples for June in US$, total exports
> to Europe etc. How is this handled in a dimensional model? Is it
> possible to model this in a single star schema when the granularity
> appears to be different. NB. Is may not be possible to derive
> aggregate totals from data with a finer grain because a different
> subset of businesses will have been asked a different set of
> questions, so aggregate totals might not include the full subset of
> data that is needed for an accurate total.
> Lastly, does anybody know of an example of a dimensional model that
> could be used as a starting point for survey collection and analysis
> data warehouse?
> TIA,
> Keith.

Dimensional modelling advice

I am a former DBA that has moved into a totally new role and
environment, as a data architect on an enterprise data warehousing
project for the national statistics office. I've started reading the
Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
the approach that I'd like to follow with the data modelling, ie.
using the data warehouse bus architecture approach that is advocated
by Kimball.
However, the main concern that I have with this approach is that we
have hundreds of different data sources in the form of surveys that we
conduct as well as data that we share from other govt and non-govt
agencies. So the data that we will be loading into our DW is not
sourced from our OLTP systems but from a multitude of other businesses
and households. If we were to model the data on a business process
basis there is the potential to end up with dozens/hundreds of star
schemas, as well as the ongoing maintenance of systems development for
when new surveys are created. Internally there is some support for
developing a generic dimensional model that has the flexibility to
accept any type of data (be it of an economic, social or environmental
nature). I could see that this would work as far as loading data into
the warehouse goes, but think that it would introduce a layer of
abstraction that would make it difficult for the business users to
understand and query the data (or do BI tools get around this). So
the first question is, how suitable is generic modelling for a data
warehouse implementation and if not, how big an issue is deploying a
large number of star schemas for an integrated data warehouse?
Secondly, a question around granularity. We have many situations
where different surveys request similar data but with slight
variations. eg. We could ask about a businesses export totals in a
number of different ways in several surveys - total exports of all
products for 2003, exports of fruit and veges for April, exports of
apples for Q3 2004, exports of apples for June in US$, total exports
to Europe etc. How is this handled in a dimensional model? Is it
possible to model this in a single star schema when the granularity
appears to be different. NB. Is may not be possible to derive
aggregate totals from data with a finer grain because a different
subset of businesses will have been asked a different set of
questions, so aggregate totals might not include the full subset of
data that is needed for an accurate total.
Lastly, does anybody know of an example of a dimensional model that
could be used as a starting point for survey collection and analysis
data warehouse?
TIA,
Keith.
Keith -
I would suggest that you also purchase another Kimball book entitled
"Data Warehouse ETL Toolkit"; they should really be sold as a pair.
Approaching a data warehouse with a strong relational 3nf
bakcground can be difficult to understand and implement the
denormalizing structures needed for a successful dw.
I've implemented large dw systems with numerous external sources of
data, and I've found that if one really boils the inbound information
the complexity is not what it seems at first.
FWIW.
\
b.
Keith Chung wrote:

> I am a former DBA that has moved into a totally new role and
> environment, as a data architect on an enterprise data warehousing
> project for the national statistics office. I've started reading the
> Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
> the approach that I'd like to follow with the data modelling, ie.
> using the data warehouse bus architecture approach that is advocated
> by Kimball.
> However, the main concern that I have with this approach is that we
> have hundreds of different data sources in the form of surveys that we
> conduct as well as data that we share from other govt and non-govt
> agencies. So the data that we will be loading into our DW is not
> sourced from our OLTP systems but from a multitude of other businesses
> and households. If we were to model the data on a business process
> basis there is the potential to end up with dozens/hundreds of star
> schemas, as well as the ongoing maintenance of systems development for
> when new surveys are created. Internally there is some support for
> developing a generic dimensional model that has the flexibility to
> accept any type of data (be it of an economic, social or environmental
> nature). I could see that this would work as far as loading data into
> the warehouse goes, but think that it would introduce a layer of
> abstraction that would make it difficult for the business users to
> understand and query the data (or do BI tools get around this). So
> the first question is, how suitable is generic modelling for a data
> warehouse implementation and if not, how big an issue is deploying a
> large number of star schemas for an integrated data warehouse?
> Secondly, a question around granularity. We have many situations
> where different surveys request similar data but with slight
> variations. eg. We could ask about a businesses export totals in a
> number of different ways in several surveys - total exports of all
> products for 2003, exports of fruit and veges for April, exports of
> apples for Q3 2004, exports of apples for June in US$, total exports
> to Europe etc. How is this handled in a dimensional model? Is it
> possible to model this in a single star schema when the granularity
> appears to be different. NB. Is may not be possible to derive
> aggregate totals from data with a finer grain because a different
> subset of businesses will have been asked a different set of
> questions, so aggregate totals might not include the full subset of
> data that is needed for an accurate total.
> Lastly, does anybody know of an example of a dimensional model that
> could be used as a starting point for survey collection and analysis
> data warehouse?
> TIA,
> Keith.

Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanksIn your case Stundent is a Dimension, average, etc are properties of the dim
ension not measures.
It's a better practice to design the dimension with star structure. You can
make a view to group all information an make the dimension based on that vie
w.
Also you can think several cubes that uses Student dimension and time dimens
ion, like test, etc. And then you can calcultate average and other. A way to
join these cubes is with virtual cubes.
Good Luck|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.
4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>|||I disagree that the GPA and test scores should be part of the Student dimens
ion. They are semi-additive facts, and should be considered for a separate
star in your constellation (assuming you are using an architected DW with co
nformed dimensions.)
Think about it this way - if you have student transactions as one "business
process", thus is one star center, then GPA and test scores would merit its
own cetricity in a separate fact table, since GPA and test scores are not tr
ansactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be
calculated on the fly if you have other dimensions that will be used to sli
ce and dice. For example, you might want GPA by students by a particular ma
jor, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions
fact table and a Grades fact table, thus the need for at least two stars in
your constellation that share the conformed Student dimension.

Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanks
In your case Stundent is a Dimension, average, etc are properties of the dimension not measures.
It's a better practice to design the dimension with star structure. You can make a view to group all information an make the dimension based on that view.
Also you can think several cubes that uses Student dimension and time dimension, like test, etc. And then you can calcultate average and other. A way to join these cubes is with virtual cubes.
Good Luck
|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>
|||I disagree that the GPA and test scores should be part of the Student dimension. They are semi-additive facts, and should be considered for a separate star in your constellation (assuming you are using an architected DW with conformed dimensions.)
Think about it this way - if you have student transactions as one "business process", thus is one star center, then GPA and test scores would merit its own cetricity in a separate fact table, since GPA and test scores are not transactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be calculated on the fly if you have other dimensions that will be used to slice and dice. For example, you might want GPA by students by a particular major, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions fact table and a Grades fact table, thus the need for at least two stars in your constellation that share the conformed Student dimension.
sql