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 Period Over Period Growth Without Hierarchy in Time/Date Dimension?

The time dimension in our AS2005 cube is not hierarchical. Therefore, how can I dynamically do a period over period growth calculated member? I'd like it to determine the growth whether the user is viewing it yearly, quarterly or monthly.

If our time dimension was indeed hierarchical, I would define the metric as follows:

([Time].[Currentmember], [Measures].[Sales] - ([Time].[CurrentMember].[PrevMember], [Measures].[Sales])

Right now, I have only been able to do it for a specific level whether it's year, quarter or month. For example, for a year over year growth, I've defined it as follows:

([Date].[Year].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Year].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

If I wanted to do the same for months, I'd replace "Year" with "Month" as follows:

([Date].[Month].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Month].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

Is it possible to do a dynamic period over period growth in 1 calculated member based on how our Date dimension is setup?

Could you explain what attribute relations exist in your time dimension - are quarters related to months, and years to quarters? Or is the [Month] like a "month-of-year" and quarter like a "quarter-of-year"? In that case, you could try a scoped assignment like:

Create [Measures].[SalesGrowth];

Scope([Measures].[SalesGrowth]);

Scope([Date].[Year].[Year]);

this = [Measures].[Sales USD] - ([Date].[Year].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Quarter].[Quarter]);

this = [Measures].[Sales USD] - ([Date].[Quarter].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Month].[Month]);

this = [Measures].[Sales USD] - ([Date].[Month].PREVMEMBER, [Measures].[Sales USD]);

End Scope;

End Scope;

End Scope;

End Scope;

|||Deepak,

I've never used SCOPE before, but this looks like it could work. Where do I use SCOPE? In the definition of the [SalesGrowth] calculated member? Or do I have to somehow make use of a new SCRIPT command? I've never done this either. The only Script Command I have is at the default "CALCULATE". that goes before all of my calculated members.

Thanks!
|||The code above already includes a statement to create [SalesGrowth], at the beginning - you could append this snippet to your existing script, after the other calculated members.|||Deepak,

Thanks for exposing me to SCOPE! I was able to acheive what I wanted with just a few tweaks.

Dynamic Dimension Security using something else than ROLES

We are incorporating some OLAP cubes into our secure website and what to restrict the cubes information base on the person or client logged in. We are using Dundas OLAP as the front end for displaying the cubes. The Roles approach is not a viable solution since it is impossible for us to gather and keep track of our client’s windows usernames.

Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

> Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

Yes, there is a connection string property called "Roles", where you can specify which Roles should be used.

|||

I did a little bit of digging on connection string properties and came across this in one of your blogs.

There is a new MDX function for SSAS 2005 called “CustomData”.

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

I am using the “Roles” and “CustomData” properties of the connection string to achieve my purpose.

Thanks Mosha

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

|||

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)

Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)

Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

|||

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

dynamic dimension security - user and secretary

Hi

I have defined a dimension with a role with dynamic security like filter({set},CurrentMember.Name=UserName)

This works well while I have only the users.

Does someone have an Idea how this could be handled if the user has a secretary or an assistent who should see the same figures.

Thanks

Hannes

One thing that's not clear is which dimension hierarchy "CurrentMember.Name" refers to - is it like an Employee dimension? If so, maybe this same dimension could be added in a different "DelegateEmployee" role, which has a many-many relation to the original measure group. Each row of the intermediate measure group fact table (DelegateFact) would relate a Delegate to an Employee. This way, each employee could have multiple (0-N) delegates, and these delegates could be determined via the intermediate measure group, like:

Filter(({set, Generate(Exists([DelegateEmployee].[Employee].[Employee], set, "DelegateFact")},

{LinkMember([DelegateEmployee].[Employee].CurrentMember, [Employee].[Employee])}),

[Employee].[Employee].CurrentMember.Name = UserName)

|||

Thats a good idea - thanks.

Dynamic Dimension Buckets

Hi,

I have a measure [count of bookings] and dimensions for [trucking company], [calendar], [booking type]. I need to create a dimension for buckets based on the count of bookings.

The buckets I need to create are ([> 500],[201 - 500],[101 - 200],[51 - 100],[21 - 50],[11 - 20],[1 - 10]) based on the count of bookings. From this dimension I am hoping to create a measure that will show the count of trucking companies in each group (using the calendar dimension), and I am hoping to use the existing count of bookings measure to see how many bookings fall into each category by trucking company.

Eg. For the [> 500] member there may be 1 trucking company, with 857 bookings, 666 bookings that are of a specific booking type in the a specific month. I also will be creating % of measures for these.

I've been able to create a member but I can't get it to show anything but #value. I've used this expression:

filter([trucking company],[count of bookings] > 500)

Any help is really appreciated.

Take a look a creating new attribute in your bookings dimension and then use combination of DiscretizationMetod and DiscretizationBucketCount property to tell server to create groups of members.

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

|||

What do I create the attribute from? Because I need the grouping based on the count of bookings (which is the count of rows in my fact table) I can't just create buckets based on a number (like age in the examples I've seen). And I can't see anywhere within the dimension attribute properties that I can specifiy that I want to use the count of a particular column.

And because I want it to return different results for the count of trucking companies etc when sliced by this new attribute and the time dimension I can't create a view to base the attribute on (at least I don't see how I would do that).

Dynamic (user requested) Dimension

In our project, we constantly have the need to analyse the data in the cubes, but with "restricted" dimension... maybe an example helps:

Like this: "Get the total for orders, but only those placed on a Wednesdays". This would be an easy task, since you only have 7 weekdays. In your cube browsing frontend you simply click away the 6 you do not need, and there is your result.

Now imagine you get a list of 10,000 Customer IDs, or maybe 30,000 Invoice IDs, or only 300 Product IDs that shall be used to restrict the space in which all further analysis takes place. No one wants to (manually) select 10,000 items out of a list of 2.5 million... even selecting the 300 Products out of the Products list (2,500 items) is a task you cannot really explain to someone.

The content of the lists do change, but not very frequent. IF the lists are changed however, they are changed completly, not just added upon! These special jobs happen maybe once a week, and are started only by one user at a time. So there is no need to make this work with several different lists at once or so.

Currently, I am importing the list into SQL Server, then build a query "joining" the list with my tables so I get the restricted data, and then recreate all groupings and calculations the cube does by manual SQL - the last part is the really bad job.

What can be done to have a dimension (or in this case, 3 lists = 3 dimensions) in the cube whose content is based on an "external" list that changes from time to time?

I will solve the "external" part first by creating a either mail-in or html form to upload a txt list file to the server and then run a SSIS package to import the data so it is part of the database - no problem there!

How would "intelligent" processing of this dimension and the cube be handeled? I understand it that "dimension update processing" is not possible, as the content can be totally different. Doing "dimension full processing" will solve that, but require a "full cube processing" as well?

Hi Ralf,

try to have a look at this precious document by Marco Russo and check the Multiple Groups chapter, probably is what you're looking for:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

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/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 design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
Bigalexx
Hi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default...dvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default...rnational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com

DW design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
BigalexxHi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com