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


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

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

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

Parent child hierarchy & MDX to fetch particular level & all members below it along with measures us

  
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 - 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

Regards


Dynamic Headers using an SSAS Parent Child Dimension

  

Hi,

 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)

Loans

Deposits

Funds sold

Total Interest Income (rollup of children above)

Interest Expense (as a header)

NOW

CDs

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.

Thanks,

Joe

 

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) 

Parent-Child relationship on a Type 2 (SCD) Dimension

  
For a Type 2 slowly changing dimension (SCD) such as Employee with a Parent-Child relationship, how do you handle the explosion of new rows when a high ranking employee has an attribute change?   It will require terminating the high ranking employee row and adding a new current row with a new surrogate key. Then every employee row below this employee will also have to be terminated and a new current row added with the new surrogate key of the parent.   This will have to be performed 1 level at a time so the subordinate rows will have the new surrogate key of their immediate parent.   For a large organization such as 20,000 employees, this will result in a dimension with hundreds of thousands rows in a relatively short time.   Is there a better way to model this?

Parent-Child, Unbalanced Hierarchy and unexpected Root behaviour

  
I have a chart accounts that contains an account key and parent account key (like the AW Enterprise sample). Each account ultimately has either Assets or Liabilities as parent. However, There also some accounts appear in the root because they have less levels then the other accounts. to put in other words: the acounts with exactly four levels, ultimately show up with either assets or liabilities in the root but the members with less then four levels, also appear in the root instead of under Assets or Liabilities. Where am I going wrong? The dimension settings appear to be exactly the same as the AdventureWorks-sample.

Non parent-child dimension with fact data at different levels... How!

  
Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

Poor Performing Parent Child Hierarchy

  
Q1:  Is my dimension and measures designed properly for scd 2 changes in the dimension? Q2:  If using a parent-child hierarchy is an acceptable approach, why would my query perform so poorly against my customer dimension?  I have developed a customer dimension with scd 2.  Within in the dimension I have two surrogate keys: corporate customer key and customer key and the source system ID: customer ID.  I use the corporate customer key to group or relate fuzzy matched customer records (based on other customer attributes) and to group or relate the scd 2 changes.   CustomerCorpKey CustomerID CustomerKey GeographyKey CreateTimeStamp ExpireTimeStamp 21 28 21 27937 29:18.8 22:14.0 21 28 473359 27937 22:14.0 NULL Now my fact table has the customer corp key, customer id, and customer key. DateKey FullDateAlternateKey CustomerCorpKey CustomerKey CustomerID Some Measure 20100913 9/13/2010 21 21 28 $500 If you notice, the customer dimension has undergone a change.  The new customer key is not in the fact table, but I want to keep the historical changes of the customer dimension, but I can no longer link the fact to the customer dimension unless I use the customer corp key and customer key combination.  I thought I could use a parent-child dimension on the customer corp key and customer key attributes

No Aggregation with parent-child dimension

  

Hi,

I am trying to build a very simple datacube without dynamic aggregations. I only have one fact table and one dimension table. The dimension is a parent child dimension. In the fact table I have a value for each leaf or non-leaf from the dimension (except of one where I wanted to test if it shows null then). I first built the dimension which worked fine and then the cube with the only one measure with "aggregate function" = None. But when I processed the cube and went into the Browser tab and used the dimension I was not able to the see the hierarchy. It only says "Level 2" and "Grand total" and when I double click it I get an error message "Cannot drill into a total member". I went through some posts here where it was said that the dimension cannot have "MemberWithData" = NonLeafDataHidden which I had, so I changed it to visible. I also read that there cannot be a "Calculate" calculation when using no aggretion so I deleted that. Nothing changed the fact that I did not have the hierarchy in the dimension and didnt see any values from my fact table.

I think I need to something with the Calculation but i dont know what. I would be much appreciated if you could help me.

Thanks for your help,

Benni


Parent child dimension

  
I have simple Employee table like this:
EmployeeID char(11), not null
Name Char (50)
SupervisorID char(11), not null

I setup parent-child releationship with SupervisorID as foreign key and employeeID as primarykey, then run Dimension wizard, pick SupervisorID as Parent attribute. However when I process the dimension, I keep getting process fail error:

"Errors in the OLAP storage engine: A loop involving the member with the key 000013744, was detected in the parent-child relationship between the 'Employee' attribute and the 'Parent ID' attribute."

Any advice?

Thanks

Don

How to show primary key of Parent-Child hierarchy

  
I have dimesion with ragged Parent-Child hierarchy. For instance organizational structure of a company. Commonly the hierarchy shows names of hierarchy items but now I need to create MDX query where result on axis 1 will be ID (primary key of dimesion) of item and name of item, but main is to show id.

SELECT {} ON 0,
{[Product].[MetaGroupHierarchy].children} ON 1
FROM MyOLAPCube

With mentioned MDX query the result is :

name1
name2
name3
...

And I would like to have:
ID1, name1
ID2, name2
ID3, name3

or

ID1
ID2
ID3


I do not know how to construct MDX query in this case.  Because main dimesion key has to have NameColumn property set to field with name of item.

Excel Export Report with Parent Child Dimension OLAP

  

 

Hi,

 

I have a problem with a reporting services 2005 report.

The data source is OLEDB for OLAP 9.
 

The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.   
 

The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.

 
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the

Reload of Parent-Child-Hierarchy

  

Hi,

I have a problem with the parent-child-hierarchy after page reload.

Wie use SSAS 2005 and SSRS 2005 with newest service packs and patches. The parent-child-hierarchy works in the reports excellently. But we have a hierarchy with 9 levels. If the users expand some levels and thereafter change the filter or sort the columns, will be the page reloaded and whole hierarchy is collapsed again.

Can you suggest a solution for this problem. Is the problem solved in SSRS 2008?


Security permission for a key attribute which belongs to a parent-child hierarchy.

  

Hello,

I have a dimension with a parent-child hierarchy on the key attribute and another attribute for the parent. I also want to declare a role with a permission to view only a specific member of that dimension, but it doesn't let me define a restriction on the key attribute and says I must define the equivalent restriction on the parent attribute. The problem is that I don't know how to accomplish that because I must use a specific value, so I can't restrict it using this value's parent or children. I might be stupid, but I really have no idea how to do it...

Thanks.


Parent-Child Dimension where the top most element has an ID of zero instead of one

  

I have a parent-child hiearchy where, when I browse it, the top most element is a sibling of the first child instead of the parent of the first child. I seemed to have narrowed down the behavior to the fact that the top most element has an id of 0 instead of 1. When the top most element id is 1, I don't get this behavior.

Is there an attribute property that I may have misconfigured to cause this behavior?

 Consider the following table:

ItemID  ParentItemID  Description
--------  ------------       -----------
0          null                 Top Node
1          0                     Child Node
2          1                     Grandchild node

 I get the following in the Browser tab for the dimension

- All Test Nodes
  - Child Node
    - Grandchild Node
  - Top Node

Notice that the "Top Node" is

Parent-Child Hierarchy

  

Parent-Child Hierarchy

What's the best way to represent a Parent-Child hierarchy in the Cube? I've seen the Usage that can be applied as Parent.
I've applied this, for example,

ID ParentID CompanyName Rank
1  1  AAA  Excellent
2 1  BBB  Good
3  1  CCC  Ok
4 1  DDD  None

I can link the ID and ParentID Usage appropriately by setting the ParentID to a Usage of Parent. I've also set the Parent Key, so that if the Company rolls up to itself, it doesn't show the Parent as its own child (NonLeafDataHidden).

When using only the CompanyName in the Cube Browser / Excel, this is working okay.

The problem occurs when trying to bring in other attributes, such as Rank.

Dragging the following into the Cube Browser:  
- (Parent Company, Company...hierarchy), Rank - If the Rank is at the end of the table, Cube Browser doesn't show the Rank column at all, only the heading appears.
- Rank, (Parent Company, Company...hierarchy) - If the Rank is at the start of the table, Cube Browser displays correctly.

In Excel,
- (Parent Company, Company...hierarchy), Rank - If Rank is at the end of the table, Excel doesn't display the Rank of the Parent Company, but does display the

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:

http://romiller.com/2008/07/29/searchable-dependant-parameters-in-ssrs/

 

Thanks in advance!

Sigfrid-R


Categories: 
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