Skip to main content
Question

Problems with Data Models based on a DB view (vs using DB table)

  • March 14, 2023
  • 5 replies
  • 3 views
M
Level 4

Hello, I have a view that has 2084 rows, but when I use it in a Data Model, load the DM and check the details, it says 5772 rows were loaded. As a result, Analysis based on this DM is showing me incorrect data that does not even exist in the DB view. Is this a bug or what is the problem???

    5 replies

    G
    Level 12

    Just to understand:

    • in Data ingestion you have a SQL view that the count gives you 2048 rows?
    • then you add the view to the data model and load the data model?
    • then you go to Studio and create a OLAP table or a KPI and a count_table() gives you 5772 rows?

     

    is it right my understanding?

    M
    Level 4
    • milan.demel
    • Author
    • Level 4
    • 7 replies
    • March 15, 2023

    Just to understand:

    • in Data ingestion you have a SQL view that the count gives you 2048 rows?
    • then you add the view to the data model and load the data model?
    • then you go to Studio and create a OLAP table or a KPI and a count_table() gives you 5772 rows?

     

    is it right my understanding?

    Proprietary Hi, Yes, that is correct. I see the number of loaded rows as part of the DM Load page when I go to details. [cid:image002.png@01D95723.0E06AA50] Actual count: [cid:image001.png@01D95723.6EC86900] I'm guessing it is loading deleted rows. How do I prevent that? Regards Milan Demel
    G
    Level 12
    Proprietary Hi, Yes, that is correct. I see the number of loaded rows as part of the DM Load page when I go to details. [cid:image002.png@01D95723.0E06AA50] Actual count: [cid:image001.png@01D95723.6EC86900] I'm guessing it is loading deleted rows. How do I prevent that? Regards Milan Demel

    Hmmm... I can't see the attached images.

     

    if you have MORE rows in Data Ingestion than the rows you get in Studio with a KPI or OLAP table, that could be because some rows have not relation with any case (or something like that)

    You can use a WHERE EXITS clause to create a VIEW with only those records that have a case associated, then use the VIEW in your DataModel

     

    If it is the opposite, probably you have some join that is duplicating records. Or, less likely, if you are checking with a OLAP, the resulting relationships is counting several times the same record.

     

    Regardind "deleted" records, if you have any column or value that identifies the record, then you can remove them.

     

    HTH

    M
    Level 4
    • milan.demel
    • Author
    • Level 4
    • 7 replies
    • March 15, 2023
    Proprietary Hi, Yes, that is correct. I see the number of loaded rows as part of the DM Load page when I go to details. [cid:image002.png@01D95723.0E06AA50] Actual count: [cid:image001.png@01D95723.6EC86900] I'm guessing it is loading deleted rows. How do I prevent that? Regards Milan Demel
    Proprietary Hi, I have actually removed all other tables from the DM to ensure there is no issue with FKs etc and it did not help. I did figure out how to get around it by adding a where statement to the view to ensure it returns rows that exist and were not deleted, but otherwise this is a Celonis bug - the data model pulls in deleted rows from the table used in the SQL view. Can you report it as a bug or should I do so somehow? Regards Milan Demel
    G
    Level 12
    Proprietary Hi, Yes, that is correct. I see the number of loaded rows as part of the DM Load page when I go to details. [cid:image002.png@01D95723.0E06AA50] Actual count: [cid:image001.png@01D95723.6EC86900] I'm guessing it is loading deleted rows. How do I prevent that? Regards Milan Demel

    Yep, you should open a case


    Community Code of ConductAccessibility statement