Subscription on parent-child derived Hierarchy

Question

Hi,

I was looking at using a subscription to extract/export data through on a parent-child dervied hierarchy.  It creates one, but I don't think there is enough information in the view it creates to link the parents to their children.  Just wondering if someone had used a subscription to do parent-child and what SQL they used to kind of denormalise it.

e.g.

My hierarchy looks a bit like

ROOT

  (1) Group A

    - (1) Product 1

    - (2) Product 2

  (2) Group B

    - (3) Product 3

    - (4) Product 4

When I create a subscription, there doesn't appear to be a logical join between child and parent.  The subscription doesn't seem to have the level column which the view mdm.viw_system_X_X_PARENTCHILD_DERVIVED does, which would make a join more plausable.  Joining parent_ID = 1 to child_id = 1 would return two rows instead of one, if I had level I could say something like child level -1 = parent level. 

Hopefully that makes some sense

Thanks

Matt

---

http://mattbi.spaces.live.com/blog/

Answer 1

Hello Matt

 

did you check Example 2: Joining Multiple Views, on

http://sqlblog.com/blogs/mds_team/archive/2010/03/03/subscription-views-part-2-combining-and-customizing-views.aspx

 

 

---

Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France

Answer 2

Hi Xavier,

Thanks for the link.  I have since found out that you can create denormalised subscription views, I was struggling before as I was getting an error and it wouldn't create the view, which is why i was creating a Parent Child subscription.  After some digging around I found out that because I had create an entity that had spaces in the name and it broke the code. Hmmm ...

The denormalised subscription view generated some code which I copied and that works on a derived parent child.  Obviously my code you have to know how deep it is, three deep in my example. Although I think I will just create a "derived levels" subscription on the derived hierarhcy.

select 
	l0.ChildName
	, l1.ChildName
	, l2.ChildName
from 
	mdm.subView l0
	LEFT OUTER JOIN mdm.subView L1 
	ON L1.Parent_ID = L0.Child_ID AND 
		L1.ParentCode = L0.ChildCode  
	LEFT OUTER JOIN mdm.subView L2 
	ON L2.Parent_ID = L1.Child_ID AND 
		L2.ParentCode = L1.ChildCode  
WHERE 
	L0.ParentCode = 'ROOT' 

Someone for MS might want to fix the following proc so if someone does put a space in as an entity name it doesnt give the stupid error "An error occured while saving the subscription view." Or if you create an entity, the interface checks for spaces and does something about it.

[mdm]

.[udpCreateDerivedHierarchyLevelView]

generated for "Business Unit" to "Division" entity

SELECT V.Name AS VersionName, 
				V.Display_ID AS VersionNumber,  
				VF.Name AS VersionFlag,  
		 'Division' AS Hierarchy,  
		'ROOT' AS [ROOT],  
	L0.Child_ID AS Business Unit_ID, 
	L0.ChildCode AS Business Unit_Code, 
	L0.ChildName AS Business Unit_Name, 
  L1.Child_ID AS Division_ID, 
  L1.ChildCode AS Division_Code, 
  L1.ChildName AS Division_Name  
FROM mdm.viw_SYSTEM_6_5_PARENTCHILD_DERIVED L0 
INNER JOIN mdm.tblModelVersion AS V ON V.ID = L0.Version_ID						 
 LEFT JOIN mdm.tblModelVersionFlag AS VF on VF.ID = V.VersionFlag_ID  
	LEFT OUTER JOIN mdm.viw_SYSTEM_6_5_PARENTCHILD_DERIVED L1 
				ON L1.Parent_ID = L0.Child_ID AND 
				  L1.ParentCode = L0.ChildCode AND 
					L1.Entity_ID = L0.NextEntity_ID AND  
					L1.Version_ID = [mdm].[udfModelVersionIDGetbyFlagID](3)  
WHERE L0.ParentCode = 'ROOT'  
 AND L0.Version_ID =  
	 [mdm].[udfModelVersionIDGetbyFlagID](3)

Grumble over

Thanks again

Matt

---

http://mattbi.spaces.live.com/blog/