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

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

Restrictions on Hierarchy navigation in SSAS

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

One of the table in my database is a tree table and therefore it's dimension is child-parent based.

How do I restrict navigation of the Tree hierarchy from a certain node ( and its subtree) inside SSAS? Some kind of dimension intelligence?

View Complete Post

More Related Resource Links

SSAS 2008 attritube hierarchy doesn't group records and repeat rows

We are having problems with the dimension attributes in lower level hierarchies not grouping under 1 single level. Here is the hierarchy: VP    College      Department         Departmental Course            Course Level               Course Number The top 3 levels are grouping correctly without duplicate rows and they don't have compoud keys. The lowest 3 levels are not grouped correctly and they have compounds keys because the attributes are not unique by themself. The result of the hierarchy looks like this: VP Academic Affairs    Business School of Management        International Business School            Mgm101                 Lower                       Course Number 123            Mgm101                Upper                

SSAS - How to get other members from dimension that has Parent Child hierarchy?

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

SSAS 2008 Ragged /unbalanced hierarchy

Hey all,

I'm trying to configure a ragged hierarchy on a product dimension in SSAS 2008:

  -Subcategory 1
    -Subcategory 2

Some products don't have a 2nd sub category so on the Product Hierarchy I have set the level property 'HideMemberIf' to be 'ParentName' (and also tried 'OnlyChildWithParentName')

This works in SSMS and I can expand the nodes and see details where I'd expect too. However when I drag the product name onto the grid it only shows for those rows where there is a second category; i.e. it seems that the HideMemberIf property affects the whole row, not just the individual level.

Is this expected?

Thanks for your help,


SSAS - Create hierarchy only for classification purposes


Hi all

in a financial reporting cube we have a Scenario dimension (Actuals without adjustments, Adjustments, Actuals with adjustments, Plan, PriorYear, etc.). Until now we only had an attribute hierarchy (with IsAggregatable set to false).

=> all scenarios are at the same level

We added a lot of scenarios to the dimension and now having all these scenarios appearing all at the same level, doesn't make it very user-friendly. We would like to add some sort of classification.

What we would need is for example a classification

For example:


       - Actuals without adjustments

       - Adjustments

       - Actuals with adjustments

- Next scenario family


As you can see I would like to use the hierarchy only for classification purposes. I don't want the scenarios to aggregate up the hierarchy.

Is something like this possible without affecting performance

Thanks for your answer. Regards

SSAS - Unbalanced Hierarchy


Dear friends,

I'm having some problems creating an unbalanced hierarchy on a SSAS Dimension.

I have some records without subcategory values... I can create it, but the fact data doesnt appear... 


product_dimkey subcategory_id subcategory_description category_id category_description

1 NULL NULL 11 category 01

2 NULL NULL 22 category 02

3 01 subcategory 01 55 category 03

4 02 subcategory 02 55 category 03

5 03 subcategory 03 55

howto: display only hierarchy-members (levels) which are selected in the parameter (SSAS and SSRS)


Hi all,

I'm a SSRS/MDX beginner, so this might be a basic question. Sorry for that.

I have a hierarchy, which I use as a parameter. The Hierarchy has 3 levels (level1, level2, level3).

Sample Hierarchy

Now I want to achieve the following:

  • If a user selects CH1 --> the report should show only the CH1 (which is the sum of CH11 + CH12 + CH13)
  • If a user selects CH11 and CH12 and CH13 --> the report should show only these leafs (no aggregations)
  • If a user selects CH1 and CH11 and CH12 and CH13 --> the report should show the leafs and CH1 (seperate)

I guess, this is not that hard, but somehow I don't get it.

Here my sample mdx-query (the hierarchy I'm writing about is "Lieferantenstruktur" and the parameter "@Lieferant" (BOLD)):
(right now, only the leaf-level [Lieferant] is returend by the query)

SELECT NON EMPTY { [Measures].[Einheiten] } 

, NON EMPTY { ([Lieferantenstruktur].[Lieferantenstruktur].[Lieferant].ALLMEMBERS 
* [Artikel].[Artikel Kategorie].[Artikel Kategorie].ALLMEMBERS 
* [Artikel].[Artikel Gruppe Code Name].[Artikel Gruppe Code Name].ALLMEMBERS 
* {[Kundengruppen].[Kundengruppen].[Verkaufskanal].ALLMEMBERS 

How to use Navigation Hierarchy control with ListView Webpart on a custom web page?



Here is what i am trying to do with managed metadata and lists.

List name: MyList, it will have 2 managed metadata columns 'Business process' and 'Management model'. I have enabled metadata navigation settings for this list on mentioned columns

I will have a data view webpart mapped with MyList on one of the custom pages. I want to add Navigation Hierachy Control Or equivalent on this custom page which will be based on managed metadata columns 'Business process' and 'Management model'. On the selection of a node from this hierarchy, i would like to filter the ListView on the page.

Is there a way to directly use Navigation Hierarchy that we get on AllItems.aspx page corresponding to List? or any other out of the box method to achieve this WITHOUT custom code?

Thanks and regards,


Restrict SSAS dimension hierarchy to show based on role


I am having an issue that involves SSAS and Sharepoint.  I don't think I can fix the issue in Sharepoint, I think it has to be in my ssas cube.  THe issue is that in sharepoint I have a ssas filter webpart that displays the geography hierarchy based on the role that is defined in SSAS.  So if I have a user that only has permissions to Switzerland than they will see the geography hierarchy as (Region, Sub Region, Area, Country)

All Sales Region


         Eastern Europe



What I want to know is in SSAS can I restrict the hierarchy to only show country if the user belongs to a certain role.  So what I want to basically say is if the user belongs to SSAS_CH then the hierarchy should just show Switzerland, not All Sales Region > Europe, etc....

Can this be done?



how to increase Performance onloading page and navigation of records ?? Help








this is my aspx and cs code , let me know whether it is correct or not??

onload and performance on navigation of record show be good.

Plz help me

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"

                DisplayGroupTree="False" ReuseParameterValuesOnRefresh="true" HasToggleGroupTreeButton="False" Height="1039px" ReportSourceID="CrystalReportSource1"

                Width="901px" />

            <CR:CrystalReportSource ID="CrystalReportSource1" runat="

Usability in Practice: Strategies for Designing Application Navigation


Good navigation makes for happy users, and happy users are good for your business. See what makes users happy this month.

Dr. Charles B. Kreitzberg and Ambrose Little

MSDN Magazine March 2009

Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Here we explain how the new hierarchyID data type in SQL Server 2008 helps solve some of the problems in modeling and querying hierarchical information.

Kent Tegels

MSDN Magazine September 2008

Extreme ASP.NET: Page Navigation


In my childhood I spent several weeks a year in Holland with my extended family. As a young American boy I was fascinated with the electric Dutch trains, something we didn't see in my hometown of Dallas, Texas.

Rob Howard

MSDN Magazine October 2005

Test Run: The Analytic Hierarchy Process


Most software testing takes place at a relatively low level. Testing an application's individual methods for functional correctness is one example. However, some important testing must take place at a very high level-for example, determining if a current build is significantly better overall than a previous build.

James McCaffrey

MSDN Magazine June 2005

Controls: Plan Your Migration to the Visual Studio 2005 Navigation Controls


Navigation is central to a well functioning Web application and it can be implemented numerous ways using today's technologies. This article highlights some of the key details of the new navigation controls that are part of the upcoming ASP.NET 2.0, and talks about how to design and implement one of these navigation controls today. This article contains a simple Web site that demonstrates the use of these navigation controls, pointing out some of the key items such as a breadcrumb control and things to watch for along the way. The article then describes how to implement a breadcrumb control today.

Dave Donaldson and Steven DeWalt

MSDN Magazine June 2004

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