Data Model and Report Implementation

Hej There!

I’m new to CMDBuild and want to solve the following problem with it:

It would be nice if you have some advice for me how to solve that!

Kind regards

Datamodel

My CIs:

  • GNSS Antenna (Serial Number, Manufacturer, Model)
  • GNSS Radome (Serial Number, Manufacturer, Model)
  • GNSS Receiver (IP-Address, Serial Number, Manufacturer, Model)
  • GNSS Firmware (Manufacturer, Version)
  • Default IT-Devices (Router, Switch, UPS, etc., VMServer, PC, Laptop, Handheld)
  • Mountpoint (Code, Coordinates (X, Y, Z))
  • Station (Code, Name)

My Location:

  • I have several locations where the CIs gonna be located

My Relations

  • A Location can have multiple Mountpoints
  • A GNSS Antenna is mounted on a Mountpoint, with or without a GNSS Radome
  • A GNSS Antenna/Radome mounting is only valid for a specific time window, until unmounted or changed
  • A Mountpoint can have multiple Stations
  • A GNSS Receiver is related to a Station
  • A GNSS Receiver has Firmware-Updates

Reporting

I have to generate a Report in text format which includes information per Station with the following sections:

  • Information about Location (Station -> Mountpoint -> Location)
  • Information about Mountpoint (Station -> Mountpoint)
  • History of all GNSS Antenna/Radome Mountings (Station -> Mountpoint -> GNSS Antenna/Radome mountings)
  • History of all GNSS Receivers separated by Firmware Updates (Station -> GNSS Receiver mountings, GNSS Receiver -> Firmware Updates)

Example:

Models

Location

  • id=Location001, street=Example Street 1, zip=1020, city=Vienna, country=Austria

Mountpoint

  • id=Mountpoint001, locationId=Location001, x=4149519.51, y=1019509.511, z=4194599.41, code=110AUTM001, inventoryId=49104995, othermeta=…

Station

  • id=Station001, mountpointId=Mountpoint001, code=VIE1
  • id=Station002, mountpointId=Mountpoint001, code=VIE2

Manufacturer

  • id=Manufacturer001, name=Leica Geosystems
  • id=Manufacturer002, name=Trimble

Firmware

  • id=Firmware001, manufacturerId=Manufacturer001, version=9.14
  • id=Firmware002, manufacturerId=Manufacturer001, version=9.15
  • id=Frimware003, manufacturerId=Manufacturer002, version=S1.23

GNSS Antenna

  • id=GNSSAnt001, snr=LA03914, manufacturerId=Manufacturer001, model=LEICA.AR25.R3, inventoryId=49104996
  • id=GNSSAnt002, snr=LA04121, manufacturerId=Manufacturer001, model=LEICA.AR25.R4, inventoryId=49104997
  • id=GNSSAnt003, snr=123941, manufacturerId=Manufacturer002, model=ZEPHYR2G, inventoryId=49104998

GNSS Radome

  • id=GNSSRad001, snr=LD49100, manufacturerId=Manufacturer001, model=LEIT, inventoryId=49104999
  • id=GNSSRad002, snr=941010, manufacturerId=Manufacturer002, model=TR01, inventoryId=null

GNSS Receiver

  • id=GNSSRec001, snr=LR59191, manufacturerId=Manufacturer001, model=GR 30, inventoryId=49105000
  • id=GNSSRec002, snr=5991455, manufacturerId=Manufacturer002, model=Alloy, inventoryId=49105001

GNSS Antenna/Radome mounting on Mountpoint

  • id=AntRadMount001, mountpointId=Mountpoint001, antennaId=GNSSAnt001, radomeId=null, valid_from=2001-06-30T09:04:05Z, valid_until=2005-09-12T14:12:28Z
  • id=AntRadMount002, mountpointId=Mountpoint001, antennaId=GNSSAnt002, radomeId=GNSSRad001, valid_from=2005-09-12T14:15:00Z, valit_until=null

GNSS Receiver updates

  • id=RecFirUpdate001, receiverId=GNSSRec001, firmwareId=Firmware001, valid_from=2001-01-04T12:12:43Z, valid_until=2004-04-12T12:00:00Z
  • id=RecFirUpdate002, receiverId=GNSSRec001, firmwareId=Firmware002, valid_from=2004-04-12T12:15:00Z, valid_until=null
  • id=RecFirUpdate003, receiverId=GNSSRec002, firmwareId=Firmware003, valid_from=2006-12-03T07:03:00Z, valid_until=null

GNSS Receiver mounting on Station

  • id=RecStaMount001, receiverId=GNSSRec001, stationId=Station001, valid_from=2001-01-05T06:00:00Z, valid_until=2019-05-13T12:00:00Z
  • id=RecStaMount002, receiverId=GNSSRec002, stationId=Station001, valid_from=2019-05-13T12:05:00Z, valid_until=null
  • id=RecStaMount003, receiveRId=GNSSRec001, stationId=Station002, valid_from=2019-05-13T12:10:00Z, valid_until=null

Generated Output

Output for VIE1 (Station001)

1. Name:            VIE1
   Location:        Example Street 1, 1020 Vienna, Austria
   
2. Code:            110AUTM001
   x:               4149519.510
   y:               1019509.511
   z:               4194599.410
   
3. Antenna/Radome Mountings

3.1 
   Antenna/Radome:  LEICA.AR25.R3    NONE
   Antenna SNR:     LA03914
   Radome SNR:      .
   Valid from:      2001-01-05T06:00:00Z
   Valid until:     2005-09-12T14:12:28Z
   
3.2
   Antenna/Radome:  LEICA.AR25.R3    LEIT
   Antenna SNR:     LA04121
   Radome SNR:      LD49100
   Valid from:      2005-09-12T14:15:00Z
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
3.x
   Antenna/Radome:  .
   Antenna SNR:     .
   Radome SNR:      .
   Valid from:      YYYY-MM-DDThh:mm:ssZ
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
4. Receiver Mountings

4.1
   Receiver:        GR 30
   Receiver SNR:    LR59191
   Firmware:        9.14
   Valid from:      2001-01-05T06:00:00Z
   Valid until:     2004-04-12T12:00:00Z
   
4.2
   Receiver:        GR 30
   Receiver SNR:    LR59191
   Firmware:        9.15
   Valid from:      2004-04-12T12:15:00Z
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
4.x
   Receiver:        .
   Reciever SNR:    .
   Firmware:        .
   Valid from:      YYYY-MM-DDThh:mm:ssZ
   Valid until:     YYYY-MM-DDThh:mm:ssZ

Output for VIE2 (Station002)

1. Name:            VIE2
   Location:        Example Street 1, 1020 Vienna, Austria
   
2. Code:            110AUTM001
   x:               4149519.510
   y:               1019509.511
   z:               4194599.410
   
3. Antenna/Radome Mountings

3.1 
   Antenna/Radome:  LEICA.AR25.R3    NONE
   Antenna SNR:     LA03914
   Radome SNR:      .
   Valid from:      2001-01-05T06:00:00Z
   Valid until:     2005-09-12T14:12:28Z
   
3.2
   Antenna/Radome:  LEICA.AR25.R3    LEIT
   Antenna SNR:     LA04121
   Radome SNR:      LD49100
   Valid from:      2005-09-12T14:15:00Z
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
3.x
   Antenna/Radome:  .
   Antenna SNR:     .
   Radome SNR:      .
   Valid from:      YYYY-MM-DDThh:mm:ssZ
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
4. Receiver Mountings

4.1
   Receiver:        Alloy
   Receiver SNR:    5991455
   Firmware:        S1.23
   Valid from:      2019-05-13T12:10:00Z
   Valid until:     YYYY-MM-DDThh:mm:ssZ
   
4.x
   Receiver:        .
   Reciever SNR:    .
   Firmware:        .
   Valid from:      YYYY-MM-DDThh:mm:ssZ
   Valid until:     YYYY-MM-DDThh:mm:ssZ

To be honest I think you can receive two kinds of contribute to your post:

  1. Even if it is not so clear for me (only because i am lost between antenna,receiver, mount point stuff), it seems you have a well specified data model and all you have to do is:
    a. to play with SQL to get a view with all elemenst needed in your whished report. I mean a view with columns for each attribute of Locations,stationsmountpoints, ecc.
    b. At this point you can get the SQL needed to build such view and use to define a report to be handled by JasperReport…
  2. Get in touch with some service provider who help you in building for the first time this stuff.