Pentaho

Hi,

I dont know what you mean with:

"...I continue to see queries when I modify a document or when I update the rows/columns in the report..."

You have to access your data... it might be through SQL, MQL, MDX, flatfile... you will have some kind of "query" (or data object) around... But sure.. if you are using SQL and expect to  access a meta layer... take a look at metadata models like the ones generated by schema-workbench (OLAP -MDX) or metadata-editor (MQL).

Also 

" ...to be able to do what I want to do?... "

Its not clear to me what do you wants to accomplish... if you are talking about have a simple and clean fact/dimesion struture... well take a look at schema-workbench and jpivot.

Hi David,

  Thank you for taking your time to answer me. What I mean with:

"...I continue to see queries when I modify a document or when I update the rows/columns in the report..."

is that I thought that once you have connected your database to the BI solution, the later exported all the data to his data warehouse, but I think that I was wrong and the only data which goes to this warehouse is only the one the rapport needs or am I wrong?

Its not clear to me what do you want to accomplish...

What I want to accomplish is to import all the data of my staging database into the data warehouse of the BI solution and after that be able to do all the transformations, etc without be forced to make more connections to this staging database cause it will be updated every month and all the data will be deleted thus making impossible to retrieve again the data to make new rapports.

I don't know if it is clear for you. If you have more questions, be free to make them to put some light on it.

There are some BI solutions that import the data and store it apart with some refreshments scheduled, but the majority of BI solutions, and Pentaho is part of them, rely on querying a DW you have already populated and query it on demand of the reports/Dashboards. They cache some of the data (as I try to avoid cacheing my queries I haven't delved on how Pentaho BI handles cache data), but mostly you are querying the database directly except for repeating queries.

So the normal operating way is to handle the ETL part of the DW (using Pentaho PDI or whatever tool acomodates your needs), and building and maintaining the DW database is one part of the operation. Then you build the reports/dashboards in Pentaho BI, this reports/dashboards query the DW database you have already built, but except for the cache part, they don't storage the data apart.

Regards

Hi Ana,

Thank you very much for your clear reponse about how Pentaho works. Do you know the names of the BI solutions that integrates a DW in their solution or do you know if Pentaho offers that kind of service?

Regards

When I tested QlikView eons ago (beware, more than ten years, it has changed a lot) it did that, but check if it continues doing the same. I think Sisense also had a DB embeded when I checked it.

Anyway from my point of view being force to use their DB gives it less flexibility than using the DB of your choosing, Pentaho provides Pentaho Data Integration to build the DB you want to query, but if those other tools adjust better to your needs, check them out.

Regards

Thank you very much for your answer

I think I have what I was looking for. I'll look into the BI you've told me and try to give a try with Pentaho Data Integration to see if it fits our needs.

Hi Ana

I have another question related how BI Server works. It seems like the data in my report are not updated automaticaly once the data in DB has changed.

If I run the transformation, which change the data in the source DB, in my report I still see the previous data. Do you know how can i change it?

Thanks

Look for how to handle cache in the BA Server, I think there was an option in the menu to purge caches, and then in CDE when you are creating the queries, there should be something too. I can't help you more there, we are not using the BA Server ourshelves, I've been evaluating it a couple of years ago, but it wasn't the correct tool for us, so we have move on to other tools, we keep some PRD reports uploaded to the BA Server, but we are finishing migrating to another tool, so those are due to dissapear.

Regards