Pentaho

Hi Laurence Astrid

This is from our older forums, but perhaps it is still helpful to you?

Populating a Fact Table

What version of PDI/Spoon are you using?

Hi Roguen Keller,

First of all, i would like to thank you for your response.

I am going to look at the proposed solution and try to apply it to my case and I will give you a return.

I am using version 8.0.1 of the PDI with MySQL 5.7 as a repository.

I have 7 dimensions in addition to the fact table.

Thank you.

Populating a fact table should be the same as populating a dimension table? You're looking for a step that can convert incoming fields into an sql INSERT query? I've used "Table output" to populate more than 40 fact tables. Works like a charm. If you need something more fancy, "Execute SQL script" works good too.

Thank you guys for your time and responses .

It really helped me a lot because i finally find that I just had to use the "search in database" option under "Lookup"

I am able now to populate my fact table.

But for i have another problem and I do not know if I have to open a new ticket or if I can just continue in this one.

I will at least post my new problem here and you will tell me whether or not to do it here.

So, after loading all my data, when creating the data source on the PUC I have the error "org.pentaho.agilebi.modeler.ModelerException: org.pentaho.di.core .exception.KettleDatabaseException: Could not get field info from (select * from fact)"

I really do not know what it means because if it's a permission problem I have entered the correct username and password and by clicking on the button "test" I received "SUCCESSED" but clicking on validate to complete the process i receive the error message above.

So I can not view my data from the SAIKU plugin either

Thanks in advance

Hi Laurence,

Thanks for asking. 

It would be preferable to mark this question as correct and create a new ticket/question with your follow on. You can copy and paste the link from this question to reference your context.

This does two good things for us: it gives others in the community a clear indication that the answer is correct and they can use it, and it makes sure that your question doesn't get buried under in the previous discussion and this going unanswered.  I can help you with splitting it up if you would like.

Hello Roger,

I thank you for answering my question regarding the separation of topics, you are absolutly right and it would be great if you could do it.

Since i'm new here i probably will not have a way to do it right.

Thank you very much, i really appreciate your help

Happy you solved it, didn't understand how you solved it but that's less important.

Hello Sparkles Sparkles

When you start PDI, you will find the option "Lookup" in the palette on creation and by clicking on the lookup drop-down list you will see "lookup in database".

So i use basically 3 steps:

- extract from a MS EXCEL

- lookup in database (depends on the number of dimensions in which you would like to perform your search to get the IDs)

- Insert into the Fact table.

I hope that i explained a little better

I didn't understand much of that hehe, maybe I'm on an older version. I don't have a "palette" on creation or elsewhere. I go to "Design" tab next to "View" tab, that has a list of available steps. When I need to insert rows, I would go to "Output-->Table output". There is no "lookup in database", but I do find a step called "Database lookup" (also named "Database Value Lookup"), however this is useful for retrieving dimension IDs, not inserting facts.

I think she means that before doing the insert in the fact table, she needs to convert the original codes in the excel file to the dimension ID, and yes, she probably is using the Database Value Lookup or some other lookup step for doing it.