.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

Exclude a Member from Appearing in a Dimension

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

Hi ,

I have Dimension Called Stores which takes values from DimStoresTable.

These has two attributes

Store Name

Store Type

Store Type has three values : Modern,Rural,General

When i choose the attribute Store Type I should not be able to see General by default but i should be able to choose it explicitly if i want to. Is This possible. Please let me know how to do it.


View Complete Post

More Related Resource Links

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,

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:

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

MDX - Find last leaf member of Date dimension under selected member of Date dimension, independently


Hi all

I need to reproduce the behaviour of the averageofchildren function with the exception that the function should always build the average of all members (even when the value of a member equals null). As you know average of children function always build the average at the lowest level of the time dimension.

My date dimension looks like the following:

[Date].[Fiscal] user defined hierarchy:




if context of [Date].[Fiscal].CurrentMember corresponds to a Week, I can retrieve all weeks before the current week using

In Period: MTD([Date].[Fiscal].CurrentMember)  -- PeriodsToDate also works fine

In Year: YTD([Date].[Fiscal].CurrentMember)

if context of [Date].[Fiscal].CurrentMember corresponds to a Period

How can I get all weeks (NOT Periods!) contained in all Periods before and within current period since start of year?

What would be a generic expression that retrieves all weeks before and in independently of level of selected Date dimension member




Role playing dimension and member naming question.


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.

Calculated Measure for Children of a Specific Dimension Member


I would like to add a calculated measure to my cube which is only scoped for the desendants of a specific dimension member.  For all other members of that dimension it would return a zero or null value.

I believe it is similar to the code below, however, rather than having the dimension reference in the where clasue "WHERE [Product].[Category].[Bikes]", it would be part of the MEMBER. 

Something like:

   MEMBER [Measures].[Special Discount] AS
   iif([Product].[Category].CurrentMember,  [Product].[Category].[Bikes].children, ( [Measures].[Discount Amount] * 1.5),  0)

Example from MSDN:

   MEMBER [Measures].[Special Discount] AS
   [Measures].[Discount Amount] * 1.5
   [Measures].[Special Discount] on COLUMNS,
   NON EMPTY [Product].[Product].MEMBERS  ON Rows
FROM [Adventure Works]
WHERE [Product].[Category].[Bikes]


ROLAP does not show up the un-balanced dimension hiearchy member as in MOLAP



I have changed one hierchy dimension from MOLAP to ROLAP to get it more update from the relational database. However, after process, the dimension show only those members which have children > grandchildren (and so on) and does not show member without any descendant. In MOLAP mode, it still show all members, both with descedant (where we can drill-down) and without children (can not drill-down more). All the settings between the 2 modes are the same except Storage MOLAP/ROLAP.

How to force the ROLAP dimension also showing up un-balanced hierchy members ?

Many thanks and looking forward to your response,


New Dimension member not selected by default in the Pivot Report


Hi Guys,

We have excel report where the some members of a dimension is selected and the data is browsed based on that. The report works good if there are no new members added in the dimension.

When the dimension is processed and the cube is processed, if there comes a new dimension member then that member get selected by default and the report fails. I am looking for way to remove the default selection for the new members added to the dimension. Please let me know if there is a possible way to suppress the default selection of the new members in a dimension.



Process Update does not reflect change in case of dimension member in SSAS 2008.


Process Update does not reflect change in case of dimension member in SSAS 2008. We can replicated this issue in Adventure Works 2008 with below steps.


1.  Customer Name Aaron Allen is in Title case both in Adventure Works SQL 2008 Data Mart as well as in SSAS 2008.


2.  Change Customer Name Aaron Allen to Upper case in Adventure Works SQL Data Mart.


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