13 октября 2010 г. 13:32
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.
My hierarchy looks a bit like
(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
18 октября 2010 г. 12:55Модератор
did you check Example 2: Joining Multiple Views, on
Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
18 октября 2010 г. 14:09
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.
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)
- Помечено в качестве ответа Matt Tolhurst 18 октября 2010 г. 14:09