Showing posts with label 4. Metadata Modeling. Show all posts
Showing posts with label 4. Metadata Modeling. Show all posts

Sunday, November 1, 2020

How to model unbalanced/rigged hierarchical data - entirely flattened from bottom node only

The sample code below could explain the concept in detail. Please note that this code is SQL Server based. There are three options provided below.

/*

Input:
--------------------------------------------------------------------------------------------------
Input:
0_CEO                                                                 $20
                1_Director                                          $10
                                11_Manager                      $5
                                                111_Worker       $3
                                12_Manager                      $5
                                                121_Worker       $3
                                                122_Worker       $3
                                13_Manager                      $5
Orphan                                                                $1

Output:
--------------------------------------------------------------------------------------------------
L01            L02                       L03                      L04                   Name
0_CEO     1_Director           11_Manager      NULL               111_Worker
0_CEO     1_Director           12_Manager      NULL               121_Worker
0_CEO     1_Director           12_Manager      NULL               122_Worker
0_CEO     1_Director           NULL                   NULL               13_Manager
Orphan    NULL                    NULL                   NULL               Orphan


Solutions

Comment:
--------------------------------------------------------------------------------------------------
This solution is very flexible when modelled in any BI tool, you can use the hierarchy to navigate data as requested.
However, if there are any data related with parent level, then the report won’t be right at all. In this case, any salary from 0_CEO, 1_Director, 11_Manager, 12_Manager won’t be counted
-- Solution Option 1:
--------------------------------------------------------------------------------------------------
select
  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01
, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02
, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03
, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04
, Name
from (
                                select
                                isnull(left(h04.ParentName+replicate(' ',20),20),'')+isnull(left(h03.ParentName+replicate(' ',20),20),'')+isnull(left(h02.ParentName+replicate(' ',20),20),'')+isnull(left(h01.ParentName+replicate(' ',20),20)+left(h01.ChildName+replicate(' ',20),20),'')  FullPath
                                ,isnull(isnull(isnull(isnull(h01.ChildName, h01.ParentName), h02.ParentName), h03.ParentName), h04.ParentName) Name
                                from EmployeeHier h01
                                left join EmployeeHier h02 on (h01.ParentName = h02.ChildName)
                                left join EmployeeHier h03 on (h02.ParentName = h03.ChildName)
                                left join EmployeeHier h04 on (h03.ParentName = h04.ChildName)
                                where h01.ChildName not in ( select distinct ParentName from EmployeeHier)
)  T
union all
--# Stand alone nodes
select Name -- L01
, null -- L02
, null -- L03
, null -- L04
, name
from Employee
where name not in (       select distinct ChildName from EmployeeHier)
  and name not in ( select distinct ParentName from EmployeeHier );



-- Solution Option 2:
--------------------------------------------------------------------------------------------------
With L04 as
(
                select ParentName as L04, ChildName as Name
                from EmployeeHier
                where ChildName not in ( select distinct ParentName from EmployeeHier)
)
, L03 as
(
                select EmployeeHier.ParentName as L03,L04, Name
                from L04 left join EmployeeHier on (L04.L04=EmployeeHier.ChildName)
)
, L02 as
(
                select EmployeeHier.ParentName as L02,L03,L04, Name
                from L03 left join EmployeeHier on (L03.L03=EmployeeHier.ChildName)
)
, L01 as
(
                select EmployeeHier.ParentName as L01,L02,L03,L04, Name
                from L02 left join EmployeeHier on (L02.L02=EmployeeHier.ChildName)
)

select
  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01
, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02
, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03
, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04
, Name
from
(
                select
                                isnull(left(L01+replicate(' ',20),20),'')+isnull(left(L02+replicate(' ',20),20),'')+isnull(left(L03+replicate(' ',20),20),'')++isnull(left(L04+replicate(' ',20),20),'') FullPath
                                ,Name
                from L01
) T
union all
select Name -- L01
, null -- L02
, null -- L03
, null -- L04
, name
from Employee
where name not in (       select distinct ChildName from EmployeeHier)
  and name not in ( select distinct ParentName from EmployeeHier );     


-- Solution Option 3:
--------------------------------------------------------------------------------------------------
with CTE
     as (select ParentName as ChildName,
                ParentName,
                Cast(isnull(left(ParentName + Replicate(' ', 20), 20), '') as varchar(1000)) FullPath
         from   EmployeeHier
         where  ParentName not in (select distinct ChildName from EmployeeHier)
         union all
         select child.ChildName,
                child.ParentName,
                                                                Cast(CTE.FullPath + isnull(left(child.ChildName+Replicate(' ', 20), 20), '') as varchar(1000)) FullPath
         from   CTE inner join EmployeeHier child on child.ParentName = CTE.ChildName)

select FullPath into #FullPath from CTE

delete t1
from #FullPath t1
inner join #FullPath t2
on t2.FullPath like t1.FullPath + '%'
and t2.FullPath!=t1.FullPath


select
  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01
, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02
, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03
, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04
, Name
from (
                                select
                                FullPath
                                ,right(FullPath,20) Name
                                from #FullPath
)  T
union all
--# Stand alone nodes
select Name -- L01
, null -- L02
, null -- L03
, null -- L04
, name
from Employee
where name not in (       select distinct ChildName from EmployeeHier)
  and name not in ( select distinct ParentName from EmployeeHier );


drop table #FullPath;
Posted by at No comments:
Labels:

Monday, June 1, 2020

How to display unbalanced/ragged hierarchy in Cognos DMR

While TM1 and Transformer can naturally handle unbalanced/ragged hierarchy, Cognos framework manager can’t handle it and display it.

Please see sample data below, which is a typical unbalanced and fully extended hierarchy
create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));
insert into datamart.H1 values
 ('L1_1', NULL,  NULL, NULL,'L1_1')
,('L1_1','L2_1', NULL, NULL,'L2_1')
,('L1_1','L2_2', NULL, NULL,'L2_2')
,('L1_1','L2_1','L3_1',NULL,'L3_1')
,('L1_1','L2_1','L3_2',NULL,'L3_2')
,('L1_1','L2_2','L3_3',NULL,'L3_3')
,('L1_1','L2_2','L3_4',NULL,'L3_4')
When displayed as Cognos DMR member, it looks as diagram below


In order to avoid these NULL columns, or members in FM, we fill all empty level with lowest level element. This solution can satisfy the self-services BI requirement, as well as develop complicated report with drill up and down in a normal way.  it is probably better hadnle it in FM business layer.
create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));
insert into datamart.H1 values
 ('L1_1','L1_1','L1_1','L1_1','L1_1')
,('L1_1','L2_1','L2_1','L2_1','L2_1')
,('L1_1','L2_2','L2_2','L2_2','L2_2')
,('L1_1','L2_1','L3_1','L3_1','L3_1')
,('L1_1','L2_1','L3_2','L3_2','L3_2')
,('L1_1','L2_2','L3_3','L3_3','L3_3')
,('L1_1','L2_2','L3_4','L3_4','L3_4')


However, this solution give users to all the levels to the bottom. the best solution is to stop where NULL occurs. Interestingly, Cognos DMR somehow stops at empty string.
 create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));
insert into datamart.H1 values
 ('L1_1','',         '',        '','L1_1')
,('L1_1','L2_1',     '',        '','L2_1')
,('L1_1','L2_2',     '',        '','L2_2')
,('L1_1','L2_1',     'L3_1',    '','L3_1')
,('L1_1','L2_1',     'L3_2',    '','L3_2')
,('L1_1','L2_2',     'L3_3',    '','L3_3')
,('L1_1','L2_2',     'L3_4',    '','L3_4')


Furthermore, we can change Cognos Member caption as below
case when [Data Layer].[H4].[L2] ='' then '-' else  [Data Layer].[H4].[L2] end
case when [Data Layer].[H4].[L3] ='' then '-' else  [Data Layer].[H4].[L3] end
case when [Data Layer].[H4].[L4] ='' then '-' else  [Data Layer].[H4].[L4] end
Then the final hierarchy looks like below

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)