Tuesday, November 25, 2008

How To: Naturalize (Flatten) Child/Parent Hierarchies for use as Dimensions

I keep getting the need to quickly Naturalize hierachies for analysis. So here is a sample script that accomplishes this:

set nocount on
declare @Temp1 table
RecordID int Primary key NOT NULL ,
ParentRecordID int,
Description nvarchar(1000)
/* Start loading of test data */
insert into @Temp1 values(1,null,'CEO')
insert into @Temp1 values(2,1,'VP Marketing')
insert into @Temp1 values(3,1,'VP Operations')
insert into @Temp1 values(4,2,'Marketing Director - Direct Mail')
insert into @Temp1 values(5,2,'Marketing Director - TV')
insert into @Temp1 values(6,1,'VP Research')
insert into @Temp1 values(7,4,'Human Resources Director')
insert into @Temp1 values(8,4,'Program Manager')
insert into @Temp1 values(9,6,'Research Analyst')
set nocount off;
with RecursionCTE (RecordID,ParentRecordID, Description, TOC, L1ID, Level1, L2ID, Level2, L3ID, Level3, L4ID, Level4, LevelCount)
select RecordID,ParentRecordID, Description, convert(varchar(1000),Description) TOC, RecordID as L1ID, Description as Level1, CONVERT(INT, NULL) as L2ID, Convert(nvarchar(1000), '') as Level2, CONVERT(INT, NULL) as L3ID, Convert(nvarchar(1000), '') as Level3, CONVERT(INT, NULL) as L4ID, Convert(nvarchar(1000), '') as Level4, Convert(int, 1) as LevelCount
from @Temp1
where ParentRecordID is null
union all
select R1.RecordID,
case when DataLength(R2.TOC) > 0
then convert(varchar(1000),R2.TOC + '->'
+ cast(R1.Description as varchar(100)))
else convert(varchar(1000),
cast(R1.Description as varchar(100)))
end as TOC,
L1ID = R2.L1ID,
Level1 = R2.Level1,
L2ID = convert(INT, CASE WHEN R2.LevelCount = 1 THEN R1.RecordID ELSE R2.L2ID END),
Level2 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 1 THEN R1.Description ELSE R2.LEVEL2 END),
L3ID = convert(INT, CASE WHEN R2.LevelCount = 2 THEN R1.RecordID ELSE R2.L3ID END),
Level3 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 2 THEN R1.Description ELSE R2.LEVEL3 END),
L4ID = convert(INT, CASE WHEN R2.LevelCount = 3 THEN R1.RecordID ELSE R2.L4ID END),
Level4 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 3 THEN R1.Description ELSE R2.LEVEL4 END),
LevelCount = R2.LevelCount + 1
from @Temp1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
select * from RecursionCTE

Thursday, July 17, 2008

Standard Models for the Service Provider Industry

More and more companies on the internet are providing services to other people, companies, or service providers. These companies are in thier own right "Service Providers".

If you are a modeler or developer that is working for a service provider or wish to provide a new service, you may find the TMForum to be a great place to start. They provide an industry standard framework for service providers. It will also make interoperability and exchange of information easier between service providers (Because most are developing this standard).


These two are the of interest to me, but there is much more there then these two items in the forum such as application frameworks. So dig arround.

SID - Standard Information Framework for a service provider
eTOM - Standard Process Framework for a service provider.

UML vs. IDEF1x

This is very useful article that does a comparitive mapping between UML and IDEF1x for those who are ERWin users and have a need to use a UML tool such as Rational Rose or Sparx.