.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

SSAS - Create hierarchy only for classification purposes

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

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

View Complete Post

More Related Resource Links

Errors attempting to process SSAS Cube. I get Either user does not have permission to create a new o

Hi All, One of my production server deployed cubes i am not able to process the cube.When i tried to process the cubes i will get an Error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Either the 'NA\gsad478' user does not have permission to create a new object in 'JNJAUSYSVR01', or the object does not exist.  (Microsoft.AnalysisServices)   Over the source server i do have sysadmin access and also  i have added read only role of particular cube. I right click on the database and tried to create new database of SSAS that also thrown with same error. Please help me in this regard.   Thanks, Gangadhar

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

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

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,


.NET Interface to Create/Maintain Custom Lists in SSAS


I would like to create a .NET interface to Create and Maintain Custom Lists in SSAS Cubes.  Would anyone know if there are any source code projects going in this direction?? If not, some direction on how to accomplish this?  Anyone have existing code/samples to.. Ex. Create lists from MDX scripts wrapped in Stored procedures 

Any help would be greatly appreciated


How to create Fact Table and Dimention Table in SSAS 2005 ?


Hi friends,

I am very new to SSAS world.

My question, can any one guide me , how to create Fact Table and Dimention Table ?


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

How to create a specific aggregation in SSAS 2005



I have created a SSAS 2005 prj and the related cube. I have created a SSRS report to query the cube, but the execution take more time. I think to create some aggregations related to one-two MDX queries. I have tried to build aggregation in BIDS but this feature seems authomatic. Is it possible to create aggregations based only on one or two MDX queries, please?

Any suggests to me, please? Thanks

Can we create Stored Procedure from SSAS MDX Query


HI All,

Is this possible to create STORED PROCEDURE TO FROM MDX QUERY,if yes please tell me how and also can we use that STORE PROCESDURE in SSRS report.



how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

How to create call statement as object in SSAS


Hi Experts,

                 Any one please tell me how to create as object or attribute for the below Call statement.

Otherwise please guide me how to use the below statement in SSAS.

call SQLQuery.ExecuteSql(
    "Provider=SQLNCLI10.1;Data Source=DWHSRV01;Integrated Security=SSPI;Initial Catalog=Banking"
    ,"Exec Key 1")




How to create a wpf tree with multi-level hierarchy where the children are of the same type as the p


I have a structure of objects as below, where the child elements are of the same type as their parent.  The parent might also contain child elements of different type(s).

My object structure:

Main Site

Main Site

Sub Site

Sub Site

Sub Site


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 

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?



Restrictions on Hierarchy navigation in SSAS


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?

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