.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Role playing dimension and member naming question.

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server

I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

View Complete Post

More Related Resource Links

filtering a role playing dimension

I have a role playing dimension and need to run a query that will select the intersection of members that are common on a single attribute...    So Dim1 and Dim2, they both have the attribute [Name] (though the fact table has two different foreing keys mapping to the single primary key on the dimension table) I need to select the members where both foreign keys on the fact table map to the same member in the dimension table so for example,  Dim1.Name = 'John Smith' and Dim2.Name = 'John Smith' Any ideas? Javier Guillen

Different attribute name in each dimension for a role playing dimension

Is it possible to name the attribute differently in a role playing dimension. For example I use the Date dimension as the role playing dimension for Ship Date and Order Date. When I use these attributes on the report, both of them show as 'Date' which is the name of the attribute in the Date dimension. Is there any work around to implement these names differently?

MDX Query - Get (Parent-Child) Dimension member regarding another Parent-Child Dimension, then, get

Hi there, I got the following MDX issue. My cube structure looks like that : DIMENSION CATEGORY (Parent-Child) --> (Many to many) FCTLESS_CategoryNode <-- DIMENSION NODE (Parent-Child) ___ DIMENSION NODE (Parent-Child) --> (Many to many) FCTLESS_NodeVariable <-- DIMENSION VARIABLE ___ DIMENSION VARIABLE <-- FACT (Supposed with only one value called VALUE) I'd like aggregate for N nodes linked to a specific category. For example, with the next content : DIMENSION CATEGORY - COUNTRY - SITE - BUILDING DIMENSION NODE US (Category Country)     - Site US_A (Category Site)     - Building US_A_A  (Category Building) FR (Category Country)     - Site FR_A (Category Site)     - Building FR_A_A  (Category Building)                                       - Site FR_A (Category Site)     - Building FR_A_B  (Category Building)                                       - Site FR_B (Category Site) 

can we change the properties of any dimension member as from hidden to unhidden without deploying a

hello , well i just want to known that , can we change the properties of any dimension member/measure as from hidden to unhidden without deploying a cube.? well i have a deployed and process cube and i just want to change some measure properties to visible from hidden without reprocessing the cube so can we do like this..?.. plz help me out ...

Top N by dimension member

Hi All, Adventure Works as an example, I would like to select 100 customers based on last Ship Date. There are three (August 5 2004, August 6 2004, and August 7 2005) members in Ship Date dimension which include more the 100 customers and I would like to select these 100 customers irrespective of any measure. I could not find a MDX to achieve this, please help. Thanks

Dimension member based on period

Hi, I have done several SSAS cubes in the past, but this is the first time I have run into this problem. I have tried searching for an answer online, but I am unsure of how to pose my question/search appropriately. After many hours of searching (someone MUST have run into this before), I thought I'd try the forum. I will be using SQL 2005 SSAS. The issue concerns a dimension that is linked to my fact table via a middle table that creates a many-to-many relationship. This middle table contains a date that the cube would need to be filtered on (which would be a further dimension, I assume). Note that I plan on allowing reporting on the cube via MS Excel (pivot tables). My table structure (highly simplified for testing purposes, it contains upwards of 15 other dimensions) is laid out as follows: --dimension = RecordableInjury CREATE TABLE [dbo].[safe_RecordableInjury](  [RecordableInjuryID] [int] NOT NULL,  [RecordableInjury] [varchar](100) NOT NULL,  [RecordableInjuryShort] [varchar](5) NOT NULL ) --fact = Injury (the measure on this table will be a distinct count of InjuryID) CREATE TABLE [dbo].[test_Injury](  [InjuryID] [int] NOT NULL,  [IncidentDate] [int] NOT NULL ) --joiner between fact table & RecordableInjury dimension CREATE TABLE [dbo].[test_InjuryRecordability](  [InjuryID] [int] NOT NULL,  [StatusDate] [int] NOT NULL,

Tough question: role-aware profiles

Hello,I have been playing a bit with ASP.NET MVC 2 using VWD 2010.After a bit of struggling with profiles(thank you MS for putting 2 web.config files in the solution...) I decided that after all I like this technology. I always thought that dotNET would be great for server side stuff and I've been proven right.Anyhow, I'm gonna start a real project now and I want to do something weird(yes, I know, I shouldn't be wanting to do weird things, but it's just beyond me :-) ): say I have a company website with roles and a forum.When I click on the nickname of a user, I want to bring his/her profile up - just like you would be doing with any decent forum out there. Well, my final idea is a tad more interesting in this regard, but let's stick it at that: click and see profile page.Problem is, I want this profile to be different for company employees and users. I want some general informations such as first and last name, date of birth, photo, etc and then for instance I do not want to show the address property for company users. Yes I know I might want to put the company address there or leave it at all blank and I would rather not display it at all.From what I gather about profiles, this is possible only using views and checking for the roles, doing something like:if User.Role == Company{   // Stuff here}As I understand it, there is no way to do it in the

Question of using CASE and IF statements for a calcuated member using MDX


I have a calculated member called 'MemberCounts = [Total # of members/#of month'] which calculate the number of members per month. For example, Total number of members in 2007 is 12,000. If user is looking at the Year level in the date hierarchy then Membercounts = 12,000/(#of months = 12) = 1000. If a quarter has been selected then MemberCounts = 12,000/(#of months = 3) = 12,000/3 = 4000 and at the month level, it will be 12,000/1 = 12,000.



WHEN [AdmitDate].[Hierarchy].currentmember.level is





      WHEN [AdmitDate].[Hierarchy].currentmember.level is







This works perfect for every year and its quarter until the current year.  

Requirement: For the current quarter (Quat-3) of Year (2010), the [Measures].[Members] field for month of Sept. is 0. (It will be populated at the end of the month).

By default, the calculated member defined by

Percent of Total for Each Member in a Dimension Across Another Dimension


I've got a particularly nasty problem that I've been pounding my head against that I hope someone has encountered and can help me with.  I have a hospital cube that has a Hospital dimension and a Service Line Dimension (among others).  Service Lines are such things like General Medical, Surgical, Cardiology (there are 16 total).  Each member hospital in the Hospital dimension will have Discharges associated with each Service Line.  I'm trying to construct a percent of total calculation for each Hospital and Service Line, so I can look at the percent of total of each Service Line across Hospitals.  I'm trying to create a report in SSRS based off this cube.  Here's what the dimensions look like:

Hospital Name1    General Medical
Hospital Name2    General Medical
Hospital Name3    General Medical

What I want to do is calculate the percent of total of each hospital's Service Lines, using the total for each Service Line across hospitals.  This will show the contribution of a Service Line at a specific Hospital to the overall total for that Service Line for all Hospitals.  Here's the MDX I've been working with that does not work the way I want:

Cross Dimension Role Security

Hello guys,





The Problem:

As you can see the DimSeller is related to the DimCustomer by a non key attribute called "CNPJ", my question is how can i define role security based on that dimension attribute.

For example:

Im a customer with the cnpj 1234, and when i want to see the seller cnpj i can only see the "rows" that the Customer.CNPJ in the DimSeller is equal to my cnpj. 

My future needs i will associate the cnpj with a claims autentication user so than i can use that on sharepoint. At this page I found something like i need, but there i should repeat the steps for each attribute of the dimension, and that would be very hard.

Anyone have some suggestion wich is the best approach for doing this?

SSAS - performance penalty standard dimension without custom member formulas VS standard dimension w


Hi all

I was wondering if there is a big performance penalty if one introduces custom member formulas to a standard dimension.


SSAS - Use simultaneously unary operator and custom member formula in a PC (Parent-Child) dimension


Hi all

I was wondering (even this might a very bad idea from a logical and performance perspective) if it is possible to use unary operator and custom member formula simultaneously? Until now I have used unary operator but now some new calculation can't be expressed using unary operator. The idea is just to add the new members calculation to custom member formulas. Is it possible to make cohabitate both. If I had to switch all unary operators to customer member formulas what would be the equivalent expression for +, - , ~?

thanks for your answer


Save Dimension member only when this have lines in Fact Table


I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)


How save on the dimension only this 40 members?


Best regards




Unable to add member to a write-enabled dimension


I have a dimension TestDim that is write-enabled. It has two attributes, [Test Key] and [Value], with the key attribute being [Test Key]

I am trying to add a dimension member (SS Management Studio) using the ALTER CUBE statement below:

ALTER CUBE TestCube CREATE DIMENSION MEMBER [TestDim].[5000], KEY='[TestDim].[Value Key].[500]', [Value]='5000';

I get this error:

Query (1, 45) The '[5000]' member was not found in the cube when the string, [TestDim].[5000], was parsed.

What is wrong with the above statement ?



Dynamic dimension member filter


Is it possible to set a dimension data rule for a role that will filter dimension members dynamically such that only members relating to at least one fact record will be shown?   In particular, the rule should be applied after all other filters on the fact data for the user have been taken into account.

This situation is there is a single client dimension but users should not be able to see the client information (e.g. name, address) unless it is related to a fact that the user has access to.




How to use Inferred dimension member in SCD component?


I know the Inferred Dimension Members is about handling placeholder records that were added during fact table load. But since the dimension has not been loaded, then how can we load fact table firstly? Could you give me an example what's the usage of this funtion? Thanks

Dimension browse using Role fails with Error retrieving children

Hi, this concerns SSAS 2008 R2. I've created a test user and I want to give this test user re-only access to a cube and all of its dimensions. So, I created a Role as follows: General: Checked the Read Definition for database permissions Membership: users and groups set to Domain\Testuser Data Sources: (cube data source name), Access None, Read definition (grayed and checked) Cubes: (cube name), Access Read, Local Cube/Drillthrough Drillthrough, Process (unchecked) Cell Data: all blank Dimensions: All database dimensions, Access Read, Read Definition (grayed and checked), Process (unchecked) Dimension Data: (default, all checked) Mining Structures: (default, all blank) The Testuser can browse the cube using Management Studio, no problem. This includes access to all dimension members and browing the member hierarchies (as part of the cube). However, Testuser cannot browse the dimensions. The primary error is a blank pane saying that "the dimension does not contain hierarchies". So, I investigated. While logged on as Testuser, I looked at the properties of the Role as displayed in Management Studio. No suprises until I got to Dimension Data. Then, for each dimension, instead of the list of members and checkboxes in the main display pane, I saw "Error retrieving children: The '$dimensionname' object was not found. Parameter name: index" (where dimension
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend