Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Checking of the existence of the foreign key of the base of facts in a dimension

  1. #1
    Join Date
    May 2014

    Unhappy Checking of the existence of the foreign key of the base of facts in a dimension

    Hello to all, I have to realize the following thing: During the load it is possible that records appear in the table of facts for which he does not arrange of the corresponding information in any of the dimensions. It realizes a comparison of the existing information in every dimension before loading every record of the table of facts. In case someone of the information does not exist, it turns the exit aside to a file of text "excepciones.csv". For example, I have a dimension rates, which alone information of the 1 has to 4 (ids). But in the .csv of entry of the base of facts, there are rows that have rates that go even 20. The same thing happens with other dimensions. The base of facts has information that does not possess any dimensions. Because of it instead of jumping the mistake, because he does not find reference to these keys, it would be to turn these rows aside to the file "excepciones.csv"

    The problem is that it does not happen to me like to do it. I need help.


    Hola a todos, tengo que realizar lo siguiente:

    Durante la carga es posible que aparezcan registros en la tabla de hechos para los cuales no se dispone de la información correspondiente en algunas de las dimensiones. Realiza una comparación de los datos existentes en cada dimensión antes de cargar cada registro de la tabla de hechos. En caso de que no exista alguno de los datos, desvía la salida a un fichero de texto "excepciones.csv".

    Por ejemplo, tengo una dimensión tarifas, que solo tiene información de la 1 a la 4(ids). Pero en el .csv de entrada de la base de hechos, hay filas que tienen tarifas que van hasta la 20. Pasa lo mismo con otras dimensiones. La base de hechos tiene información que algunas dimensiones no posee. Por eso en vez de saltar el error, porque no encuentra referencia a esas claves, sería desviar esas filas al archivo excepciones.csv.

    El problema es que no se me ocurre como hacer ese filtrado.

  2. #2
    Join Date
    Jul 2009


    If you are using the Dimension Lookup/Update step to find the dimension values, and the "Update the dimension?" box is unchecked, then you should get a 0 value back for the dimension key. So just before your fact table load step, you could create a Filter rows step that examines the dimension keys to see if any are 0. If none are zero, then send the row to your fact table load step. If any of the dimension keys are zero, then send them to a Text file output step that writes to your exceptions file.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.