IBM Security i2

Expand all | Collapse all

IBASE Statistics

  • 1.  IBASE Statistics

    Posted Mon August 24, 2020 11:13 AM
    Hello I have a question that I would like to help me if possible.
    
    I have a database at IBASE with several entities created.
    
    I would like to know if it is possible, for example, in the vehicle entity to see the number of vehicles per color, that is, if you have 100 vehicles in the database, see the description of the totals by color.
    
    In other words, 25 blue, 25 black, 25 gray and 25 yellow.
    
    I know that I can search for vehicles by color, but if I don't know what colors there are in vehicles, it was a way of knowing the colors that exist and the quantity.
    
    I gave the example of color, but for example I could use people for the entity, in which they gave me the total, but by nationalities and quantities.
    
    It is almost like a statistical query.
    
    Is it possible to see this type of data ??

    Thanks


    ------------------------------
    Paulo Ribeiro
    ------------------------------


  • 2.  RE: IBASE Statistics

    Posted Mon August 24, 2020 11:51 AM
    Edited by Simon Griffiths Mon August 24, 2020 11:53 AM
    Hi Paulo

    If you have Analyst's Notebook then this can be done by sending the vehicle records from iBase to Analyst's Notebook and using the Bar Charts and Histograms feature to graph the colours. For this to work you need to ensure the iBase charting scheme has been setup to send Data Records for the entity type you are interested in.



    ------------------------------
    Simon Griffiths
    ------------------------------



  • 3.  RE: IBASE Statistics

    Posted Thu August 27, 2020 10:50 AM

    I think the best solution is to create a query in SQL Server - doing a count of each desired attribute - and storing the results in a statistics table. You could the execute that query in a stored procedure

    Example: I have a database with a table containing a number of radio transmission recordings. Modulation types is stored in the 'Modulation' column, values currently can be 'FM', 'WFM' or 'AM'

    To calculate and store the number of transmisisons with each type of modulation, I did the following:

    1. Create a table in my iBase/SQL Server database - called Transmission_Statistics. Columns are 'AM', 'FM', 'WFM', type = number.

    2. Create a single row in the DB - with the value 0 for each column.

    3. Run the following query (note that the transmission registration database is called 'Chart':

    UPDATE
       [dbo].[Transmission_Statistics]

    SET

       [FM] = (SELECT

       COUNT(*)

    FROM

       [dbo].[Chart_]

    WHERE

       [Modulation] = 'FM');

    (And repeat the above block using 'WFM' and 'AM'.

    Running this query inserts the count of each transmission type into the Transmission_Statistics table - which can easily be imported into iBase in case you want an entity containing the count. Do a JDBC import to fetch the single line in the table into an iBase Transmission_Statistics entity.



    ------------------------------
    Michael Bodstrup Hansen
    ------------------------------



  • 4.  RE: IBASE Statistics

    Posted Fri August 28, 2020 10:06 AM
    Hello Paulo

    Similar to the other replies saying you need to use another software package, this is what I would do using Excel.

    Using your example of Vehicle and wanting to know colour, I would run a Browse.  The only field in this instance I would pick is 'colour', but obviously you could do more.  Browses are initially configured to return 100 records, but can go up to a maximum of 100,000 records (or point to a saved Query or Set).
    The reason I would use Browse is twofold
    1) It presents in a table format so is quick to copy to clipboard and Paste in Excel.
    2) Browse will only bring back the fields you pick, which means you get your results more efficiently.  If your Vehicle entity had had dozens of fields, obtaining a similar table using Records (right-mouse menu over any Entity in the Database Explorer) will return the contents of all those fields - wasting time and resources.

    Once I have my Browse results, copy to clipboard, paste into Excel.  In Excel, create a Pivot Table.  

    it is not dissimilar to Simon's ANB suggestion, but it is slightly quicker and perhaps more resource efficient.

    ------------------------------
    Anthony Patamia
    ------------------------------



  • 5.  RE: IBASE Statistics

    Posted Tue August 25, 2020 04:57 AM
    Hello Simon, and thanks for your answer.

    I know i can do like you said, but i whant to do it on IBASE, because if i have a lot of entities, to send to analyst notebook is a very heavy task to the computer.
    There must be a way i think.

    Best regards

    ------------------------------
    Paulo Ribeiro
    ------------------------------



  • 6.  RE: IBASE Statistics

    Posted Wed August 26, 2020 09:09 AM
    If you have access to Power BI in your organisation, you could create a dashboard with either a live direct link to the SQL database at the backend, or create an export of the particular dataset and use that to power the dashboard. You can then configure dropdowns, slicers etc to drill down into the dataset.

    ------------------------------
    Ian Callaghan
    ------------------------------



  • 7.  RE: IBASE Statistics

    Posted Thu August 27, 2020 07:25 AM

    Paulo, 

    The i2 tables will not provide you with much information for what you need, you will have to combine it with information from another sources.



    ------------------------------
    Felipe Moura
    ------------------------------



  • 8.  RE: IBASE Statistics

    Posted Wed August 26, 2020 09:14 AM
    The database is in access format

    ------------------------------
    Paulo Ribeiro
    ------------------------------



  • 9.  RE: IBASE Statistics

    Posted Wed August 26, 2020 09:20 AM
    That's fine, you can connect to an Access Database through Power BI as well.

    ------------------------------
    Ian Callaghan
    Stats Perform
    London
    ------------------------------



  • 10.  RE: IBASE Statistics

    Posted Thu August 27, 2020 10:56 AM

    Just noted the DB is in access format - consider upgrading to SQL - that will give you Search 360 as well - a huge benefit if you have free text fields and need to know if someone included the following statement in a report: "The suspect left the scene in a red Toyota Corolla"

    Searching for 'red' will give you the witness statement even though the word is not in an expected 'color' field of an automobile entity.



    ------------------------------
    Michael Bodstrup Hansen
    ------------------------------