.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

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

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

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) 

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

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.

No Aggregation with parent-child dimension



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,


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?



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


Excel Export Report with Parent Child Dimension OLAP





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:

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

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 Dimension - getting parent attributes


I have a parent-child dimension for Clients and Client Groups.  I am able to query the levels properly, however I am wondering how to properly filter these when using SSMS query browser, or Excel.  For example, there is a client code for both the client and client group.  I am able to filter based on the client code for the client, but the only attribute I see for the parent is Client Name.  I see the client code in the member properties of the group, but am unable to use these when browsing.  It would be nice to be able to filter the client group on the same attributes that I can filter the client on. 

Any assitance would be appreciated!  Thanks.

parent-child snowflaked dimension ?


hi all, in AS 2008, is it possible to create a snowflaked parent-child dimension?  It appears to me that that's impossible.

thx much for any thoughts/confirmations.


Parent Columns and Child Columns don't have type-matching columns.



i am making  relation between two datatables and m getting this error

in this ds.Tables(0).Columns("In_ID") datatype is string

ds.Tables(1).Columns("row_id") datatype is integer

how i do the type casting here to make dataset relation

ds.Relations.Add("Rel_1", ds.Tables(0).Columns("In_ID"), ds.Tables(1).Columns("row_id"), True)

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.




finding childrows in parent child relationship.............


Hello everybody,
I want to create parent-child relationship in two tables and to display them as needed. my code is like this :

General master = new General();
string str;
str = "SELECT * FROM master";
 master.getdata(str, "parent", "ID");
str="select * from childtable";
master.getdata(str, "child", "ID");
DataRelation newrelation = new DataRelation("myrelation", General.DS.Tables["parent"].Columns["id"], General.DS.Tables["child"].Columns["masterid"]);
listBox1.DataSource = General.DS.Tables["parent"];
listBox1.DisplayMember = "Master_name";

textBox1.DataBindings.Add("text", General.DS.Tables["parent"], "id");
listBox1.SelectedIndex = 0;

here General is a class where i defined the function like getdata() which take the argument which is query, datatable name, and primary field name.

Then i want to get the child data of the selected listbox item on the valuechanged event of the textbox1. The code is given below :

DataRow myRow = General.DS.Tables["parent"].Rows.Find(textBox1.Text);
Datarow[] MYARRAY = myRow.GetChildRows(General.DS.Relations["myrelation"]);

custom field type works in view properties, but not in list view: 3 parent fields filter child list,


My situation:

A) odd thing is that no matter where I put a stop point in my custom field type, it is NEVER hit when the items are displayed in list view.  Event with <Field Name="CAMLRendering">TRUE</Field> which causes the list to error out, but again no stop point to help me know why.

B) the value of the column in question will be different for every user who views the list, I'm querying a secondary list based on the user's loginName, and two of the fields from the list we're in. 

C) the display is customized based on whether any records were returned and the current DateTime, so possible displays would be "", "! 12/1/2010", and "7/7/2011"

D) and the text displayed links over to a form for them to do their assignment. the link url would be a config setting url + "?[one of the fields form the list we're in]"

My questions:

1) Is it possible for fieldtype code to execute while a list view is being rendered?

2) If not, then should I chase down the path of a content query webpart?  Will it be able to filter on the current users login name?

3) If not on 1 or 2, can you think of any other way to do this beyond creating a custom application page? (need to having filtering and sorting on this page)

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

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