.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

Multiple Parent Child Hierarchies in SSAS 2008 R2

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

I'm trying to create a dimension containing financial accounts in SSAS 2008 R2 where individual accounts can belong to one or more hierarchies.  I also need to be able to specify a unary operator since a leaf level account may be subtracted from one parent but additive to another parent.  For example revenues and expenses may be grouped one way for statutory reporting and a completly different way for management reporting and further belong to some arbitraty groupings that users find necessary.

I can't seem to find a way to have a leaf level member in a dimension more than one time in a Parent child hierarchy, and I can't seem to find any way to include the unary operator in a hierarchy where I use attributes to define the hierarchy.

Are there any work arounds that anyone knows about?  What I found from the help file was that multiple parent child hierarchies which supported in SSAS 2000 are not supported in 2008.  However there is no suggestion for what to do.

Any insight would be greatly appreciated.


Bill Webster

View Complete Post

More Related Resource Links

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

Can multiple users connect simulateously to an offline cube created by SSAS 2008?


We have created an offline cube on a network drive from SSAS 2008 and users are able to successfully access this serially using the Excel 2003 cube add-in or Excel 2007, in concert with version 10 driver, but when a user is already connected, the second attempt to connect will get the message :

Connection to <filename> server, <dbname> database, <cubename> cube failed.  OLAP Server error: The following system error occurred:  The process cannot access the file because it is being used by another process. ., File system error: An error occurred while opening the '<filename>' local cube file.

This is regardless of whether users connect specifying read-only and we have found it impossible to connect to a cube if the file has the read-only attribute set.

Is there any way to solve this without users taking copies onto their workstations?



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


SSAS 2008 Dimension: Multiple FK Relationships to the same Dimension



I have kind of a fundamental design question: In my DWH are 2 tables TICKET and EMPLOYEE. EMPLOYEE is referenced by TICKET 2 times: TICKET_OWNER_ID and TICKET_ASSIGNED_TO_ID.

I have created a Ticket and a Employee dimesion in my SSAS Project. The Ticket Dimension should no have two atrributes: Owner and Assigned To. I can create the attributes by dragging the EMPLOYEE.NAME column from the DSV window into the attributes list but there is now to specify which relation will be used.

And of course I get pretty intresting results when I process the dimension.

I could create a named query in the DSV selecting * from EMPLOYEE and create a second Employee dimension but is that the only way I can get this to work ?

Thanks, ITD

Best design option for multiple parent / multiple child list?


I made 2 lists.  "User Groups" and "Resource Groups".  In User Groups, I setup a column to do a multi-select lookup to the Resource Groups lists title column.  Obviously as this gets built, groupings will form in the other direction as well... Is there a way to programatically do that, so I don't have to complete a multi-select colum in the Resource Groups list that does a lookup to the User Groups list to get info I logically already have.

I tried to do it with a workflow, but was only able to update 1 item so the multi-select concept was pointless. 

I want to have the multi-select information displayed regardless of which list your looking in.  If you look in a User Groups list item, you can see all the resources the users have access to... if you look at a Resource Group list item, you can see what User Groups have access to it (linked by multi-select lookup), and i'd like to only have to populate the lookup in 1 direction and build the reverse relationships on automatically.

Hope that makes sense.  You'd think you could auto import this info but I can't get Domain Local groups to show up, seems to be a limitation of the WSDL for connecting to AD (forget the correct term for that piece), so I'll add them manually..



SSRS 2008 R2 - Parent Child Hierarchy - Searchable Parameter List


Hi dear,

I'm, workin at the moment on a report for my company. I have a problem with searching in a parameter list which is based upon a parent child hierarchy. The users asked me for type in the first few characters to search the parameter faster (like in this post ) , because it's a large hierarchy with about 1700 values. In a "normal" list ("linear" without  a hierarchy) this feature is available.

So my question:

Isn't it possible to search in the parameter list in general if this is based upon a hierarchy?

Are there any possibilities or suggestions to get this feature?

Or is this the only way to do this:



Thanks in advance!


visualization of multiple parent hierarchies


I have table like

HierarchyId     EmployeeId     ParentId


1                     1                      0

2                     2                      3

3                     3                      1

4                     3                      2

see, employee id 3 has two parents, 1 and 2. The same situation may arise for other employees also. How can I visualize this hierarchy in my webpage? what would be the best

TreeView with parent to single child relation from multiple children c#



how to populate treeview  with a child node and with all its parent nodes





       -----a11 // if selected i want to draw treeview with its all parents






Dynamic Headers using an SSAS Parent Child Dimension



 We are trying to build some finanical reports (Balance Sheet, Income Statement, etc) in reporting services using Analysis Services as a data source.  The problem we are running into is more of a formatting issue around the use of custom headers.  We want to have the ability to do the following:

Interest Income (as a header)



Funds sold

Total Interest Income (rollup of children above)

Interest Expense (as a header)



Total Interest Expense (rollup of children above)

Provision for loan losses (single line item, no group needed)

Getting the parent to appear after the child is pretty simple by using the hierarchize function.  The problem is getting the parent as a header to appear.  The crux is that we only want to use certain levels (not every single level) in the parent child hierarchy for doing a header. For example, above, Interest Income and Interest Expese would have a header, whereas Provision for loan losses would not.

This is pretty common formatting features for financial reports, plus its very common to use a parent child for Chart of Accounts, thus somebody has had to have run into this in the past.




GridView Parent Child View with JQuery Effects

I was just checked how to add some more cool effects to the GridView control using JQuery. In this post I will display parent-child data in the GridView control with some help of the wonderful JQuery library.

Let's first populate the GridView control.

How to keep the hover for Parent menu and set the selected menu item css for child menu item?



I'm using cssfriendly control adapter for menu. My asp.net menu control's datasource is a sitemap file I'm using which contains all the urls.

I have the following questions:

  1. Is there a way to keep the parent menu item hovered while hovering over the respective child menu items?
  2. Is there a way to keep the child menu item selected? I am able to set the css for the parent menu, however I was wondering if the same is possible with the child items.

Please help! Smile


Moneka Smile

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

way of calling "javascript function of parent window from child window."

The article way of calling "javascript function of parent window from child window." was added by raja.s on Sunday, June 06, 2010.

Hi,Here i am going to explain how to call the javascript function of a parent window from the child window(pop-Up).Some may know before reading this post.Parent Java Script Function:script language="Javascript" type="text/javascript">

Two modalpopups as a parent-child


When I  open two modalpopups, one over another, there is unexpected order.

After click on "Open Parent Panel", and click on "Open Child Panel",

Child Panel (newone) is under the Parent. 

Uploaded with ImageShack.us

What should I do to get it in "normal order"?

The code is:

    <asp:ScriptManager ID="A" runat="Server">
    <asp:Button id="Z1"  runat="server" Text="Open Parent Panel" 

Display a nested child ListView on PostBack while using DataPager on parent ListView


Hello ASP.NET community,

I am having trouble finding how to solve the following issue :

I am using nested listviews to display Sales and Sales details.

The main ListView displays General Information about Sales and the child ListView displays the detailed information about one sale. the child listview is shown only when the user clicks on a link (see included code, DataBind is made on PageLoad) :

ASPX Markup Code :

<asp:listview id="lvSales" runat="server" onitemdatabound="lvSales_ItemDataBound">
<table cellspacing="0" border="1" width="250" style="border-collapse:collapse;">
<tr runat="server" id="headerRow">
<th scope="col" style="width:30px;"></th>
<th scope="col">SaleNumber</th>
<tr id="itemPlaceHolder" runat="server" enableviewstate="true"/>
<tr id="row" runat="server">
<td style="text-align:center;"><asp:button id="btnShowHideDetails" runat="server" oncommand=&qu
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