Hierarchy in DeepSee

Question

Question

JAQUELINE KRIEGER · May 31, 2018

Hi community,

I’m working in a project for a Chile Police and I need to know if it’s possible to build a hierarchy in Deepsee like Zone- Prefecture – Comisaria – Destacamentos (there are 4 levels of police units) , but these information are only in one table that has the code, the father ,description and UnitType. (it’s recursive) . Example:

UNI_CODIGO	UNI_PADRE	UNI_DESCRIPCION	UNI_TIPOUNIDAD
1	20	OTRA REPARTICION
10	2.380	38A. COM. PUENTE ALTO	50
20		DIR.NAC.SEGUR.Y ORDEN PUBLICO	10
30	8.855	TENENCIA CARRETERA ARICA	70
40	8.740	1RA. COM. ARICA (AGR)	40
45	40	1RA. COM. ARICA	50

The idea it’s to build dimensions bases in expression to build this. Is it possible ?

Thanks a lot.

Discussion (8)1

Log in or sign up to continue

Evgeny Shvarov · May 31, 2018

Hi, Jaqueline!

@Sam Duncan is right below, and please don't consider my example above!

See the updated version of the method for Cube class:

classmethod UnitLevel(unitID, level) as %String {

s unit=##class(Police.Unit).%OpenId(unitID)

if '$IsObject(unit) return ""

return $Case(level,

  1:unit.UNI_PADRE.UNI_PADRE.UNI_PADRE.%Id(),

  2:unit.UNI_PADRE.UNI_PADRE.%Id(),

  3:unit.UNI_PADRE.%Id(),

  4:unit.%id(),:"") }

Introduce the dimension with 4 levels, with expressions:

1 st level:

%cube.UnitLevel(%source.%ID,1)

2nd level:

%cube.UnitLevel(%source.%ID,2)

3 and 4 levels same idea.

AND!!! The key thing!

introduce a calculated or direct field in your class which will indicate a level of the row - say H_LEVEL with values 1,2,3,4 according to the level of administration.

and introduce a build restriction to the cube with:

H_LEVEL=4

So, there would be only 4th level records in the cube, but you'll gather all other needed calculations and values using your hierarchy.

0 0

JAQUELINE KRIEGER · Jul 18, 2018

Hi community, here I show the final solution that resolve my problem. It was necessary create 3 methods. In this solution the hierarchy was built programatically.

ClassMethod GetUnitLevel(pUnidad As %Integer, pLevelReq As %Integer, ByRef pSC As %Status) As %String
{
Set str = ""
Set pSC = $$$OK
Try
{
Set objUnidad = ##class(AUPOL.CT.UnidadPolicial).%OpenId(pUnidad)

Set tUnidadInterno = objUnidad.IdUnidadInterno

#Dim objCTUnidad As AUPOL.CT.Unidad
Set objCTUnidad = ..GetCTUnidadByUniCodigo(tUnidadInterno)

If $IsObject(objCTUnidad)
{
Set objCTUnidadPadre = ..GetUnitLevelRef(objCTUnidad,pSC,.pArray)
Quit:$System.Status.IsError(pSC)

Set total = $order(pArray(""),-1)
Set nivel = 0
Set cursor = ""

If ( (pLevelReq -1) = total)
{
Set str = objCTUnidad.UNIDESCRIPCION
}
ElseIf ((pLevelReq > total))
{
Set str = ""
}
Else
{
For
{
Set nivel = nivel + 1
Set cursor = $order(pArray(cursor),-1)
Set str = pArray(cursor)

Quit:((pLevelReq = nivel))
}
}
w "pLevelReq ",pLevelReq,!
w "total ",total,!
w "str ",str,!
}
}
Catch(e)
{
Set pSC = e.AsStatus()
}
Quit str
}

ClassMethod GetUnitLevelRef(pCTUnidad As AUPOL.CT.Unidad, ByRef pSC As %Status, ByRef pArray) As AUPOL.CT.Unidad
{
#Dim objUnidad As AUPOL.CT.Unidad
Set objUnidad = pCTUnidad
Try
{
Set objPadre = ..GetCTUnidadByUniCodigo(pCTUnidad.UNIPADRE,.pSC)
Quit:$System.Status.IsError(pSC)

If ($IsObject(objPadre))
{
If ($DATA(pArray) = 0)
{
Set pArray(1) = objPadre.UNIDESCRIPCION
}
ELSE
{
Set iNext = $order(pArray(""),-1) + 1

Set pArray(iNext) = objPadre.UNIDESCRIPCION
}

If ( objPadre.UNIPADRE > 0 )
{
Set objPadre = ..GetUnitLevelRef(objPadre, .pSC,.pArray)
Quit:$System.Status.IsError(pSC)
}
Set objUnidad = objPadre
}
}
Catch(e)
{
Set pSC = e.AsStatus()
}
Quit objUnidad
}

ClassMethod GetCTUnidadByUniCodigo(pUniCodigo As %String, ByRef pSC As %Status) As AUPOL.CT.Unidad
{
Set obj = ""
Try
{
Set sql = "SELECT %Id "_
"FROM AUPOL_CT.Unidad "_
"WHERE Unicodigo = ? "

Set res = ##Class(%ResultSet).%New()

Set pSC = res.Prepare(sql)
Quit:$System.Status.IsError(pSC)

Set pSC = res.Execute(pUniCodigo)
Quit:$System.Status.IsError(pSC)

If (res.Next())
{
Set obj = res.GetObject()
}
}
Catch(e)
{
Set pSC = e.AsStatus()
}
Quit obj
}

ClassMethod TieneSubordinados(pUniCodigo As %String, ByRef pSC As %Status) As %Boolean
{
Set bool = 0
Try
{

Set sql = "SELECT SUM(*) As NumeroHijos "_
"FROM AUPOL_CT.Unidad "_
"WHERE UNIPADRE = ? "

Set res = ##Class(%ResultSet).%New()

Set pSC = res.Prepare(sql)
Quit:$System.Status.IsError(pSC)

Set pSC = res.Execute(pUniCodigo)
Quit:$System.Status.IsError(pSC)

If (res.Next())
{
Set iNum = res.Get("NumeroHijos")

if (iNum > 0)
{
Set bool = 1
}
}

}
Catch(e)
{
Set pSC = e.AsStatus()
}
Quit bool
}

}

1 0

score 0 · Answer 1 · 2018-05-31T11:50:19-04:00

Hi, Jaqueline!

Really interesting task. Do you have only 4 levels?

If so, you can introduce a method on a cube which would return a level of the unit.

Say:

classmethod UnitLevel(unitID as %Integer) as %String {

 s unit=##class(Police.Unit).%OpenId(unitID)

 if unit.UNI_PADRE="" return "Zone"

s padre=##class(Police.Unit).%OpenId(unit.UNI_PADRE)

if padre.UNI_PADRE="" return "Prefecrure"

/// continue here!

}

It's for 2 levels, leave other two for you ;)

And use

%cube.UnitLevel(%source.UNI_CODIGO)

for the Dimension level expression.

score 0 · Answer 2 · 2018-05-31T18:25:46-04:00

Hi Samuel,

Thanks for your comment. Each comisaria belongs to only one prefecture , but one prefecture has one or more comisarias. I have many units type, but I can group them and define which hierarchy they belongs. Example: Unit type = 20 and 90 corresponds a Zone, Unit type = 30 corresponds a Prefecture, Unit Type = 40 corresponds a Comisaria, Unit type in 50,60,70,80 corresponds a Destacamento. There are some Unit Type that I don't need.

Regards,

Jaqueline K.

score 1 · Answer 3 · 2018-05-31T16:34:20-04:00

I'd try to rework this method to avoid object access to speed it up. GetStored method can help. Article about it.

score 0 · Answer 4 · 2018-06-01T04:59:32-04:00

Hi, Jaqueline!

Looking forward to see how it works!

BTW, here you can find couple similar solutions with sources regarding territorial hierarchy.

And several demos for different countries, e.g. USA and Kazakhstan.

score 0 · Answer 5 · 2018-05-31T18:16:07-04:00

Hi Evgeny,

Thanks for your help. I'm going to try to implement this tomorrow , then I will tell you how it was.

Regards,

Jaqueline

score 1 · Answer 6 · 2018-05-31T13:58:02-04:00

One thing to keep in mind when creating a hierarchy is that each member of a lower level in the hierarchy must correspond to a single member of each higher level. For instance, in the hierarchy you propose, if you have prefectures named PrefA and PrefB, you would need to make sure that there isn't a Comisaria named Com1 in both PrefA and PrefB, and so on.

One possible way around this, if you notice that this could be a problem, is to include information from the higher levels in the members of the lower levels, so that your Comisaria members are named PrefA-Com1 and PrefB-Com1, for example. In this way, each Comisaria member will belong to only one Prefecture.

This is related to the issue discussed here (Lexi Hayden's comment in particular is useful): https://community.intersystems.com/post/tips-tricks-be-aware-month-week-hierarchy-deepsee

I'm not sure whether the four fields in your sample data correspond to the four levels you want to include in the hierarchy. If so, though, it would be a problem to put UNI_TIPOUNIDAD below UNI_DESCRIPCION in a hierarchy, because the member "50" of the UNI_TIPOUNIDAD level would correspond to both the "38A. COM. PUENTE ALTO" and the "1RA. COM. ARICA" members of the UNI_DESCRIPCION level.