View Full Version : Adhoc Query Tool and Metadata

05-04-2007, 04:28 PM
Hi all,
Playing with Pentaho BI 1.5.3M3, and attempting to use my own data within the Metadata Editor (1.5.3M3). That seems to work great.

However, I can not get the Adhoc Query Tool to recognize the new metadata info in the replaced solutions/samples/metadata.xmi file (backed-up the old and put in a metadata editor exported file). I've tried swapping the PentahoCWM file as well,with no impact.

Is there any known compatibility or 'visible' flags to make the business views in a model available to the Adhoc Query tool? Or, a simple example made from the Metadata Editor?


ERROR from webtool in 'Available Business View' section:
There are no Business Views defined. Please create one or more Business Views using the Metadata Editor and then publish them to the Pentaho server.

05-05-2007, 06:35 AM
You need to go to the administration page and use the "Refresh" button in the metadata section.
That causes the new XMI file to be reloaded.



05-05-2007, 06:36 AM
And if you already did that, create one or more business views in which you define which columns are visible to your end-users.

05-07-2007, 09:12 AM
Thank you for your response Matt,
I have indeed reloaded (and restored) the repository many times, in fact swapped back and forth between the original metadata.xmi and the new one to make sure it is loading correctly.

I have done the following to my test metadata:
Pre) This is simple setup - one connection, tested fine. I selected only one Business table and all its related columns. I changed all columns to 'Base' to make sure there is no wierd formatting problem. I added the one Business Table as a Business View without any changes. Very simple setup.

1) all the columns were of concept 'Base'. Since this did not have a 'hidden=N' property, added it per recommendation. Apply Changes, Saved, Exported, watched console on re-load to make sure it loaded the changes on the server, no impact.

2)Thinking this may be something else, I setup my security services (according to the Pentaho Workshop lab book I have on Metadata Services, which is coming up fine) and applied 'all' security concepts for 'joe'. Apply Changes, Saved, Exported, watched console on re-load to make sure it loaded the changes on the server, no impact.

I did attempt to load the original samples/metadata.xmi to the Pentaho Editor, but it doesn't look like you can do that just yet. The CWM doesn't look like it changes much, so haven't been updating that - although not sure if I should use the default one with the BI Server, or the one with the Pentaho Metadata Editor.

When attempting to run the Adhoc Query Tool with my test metadata, there is no error or output to the console, so assuming everything 'looks' fine. Not sure if this is a challenge for the Adhoc Query Tool or the data exported from the Pentaho Metadata Editor. Either way, I'm unable to test further.

05-08-2007, 03:52 AM
There is some confusion and that was to be expected.

In the latest versions we renamed
- "Business View" to "Business Model"
- "Business Category" to "Business View"

So in case you're still on 0.7.1, the binary drop we did a while ago, just create one or more Business Categories and put the Business Columns in there that you want your users to see.

Just remember that "Business Category" has been changed to "Business View" in the next versions. Nothing changed metadata wise, it's just terminology. People found the old names a bit confusing so they where changed after lots of debate too :-)



05-08-2007, 11:06 AM
Is there the mdr.btd and mdr.btx file for the BI server /samples/metadata.xmi file that we can examine within the Metadata Editor and re-export as an XMI file?

Starting from a working Metadata Editor sample of what is deployed with the Pentaho BI Demo server may work out nicely :-).

05-08-2007, 11:12 AM
yes, put mdr.* in the directory where you start MetaEditor.bat That should make it pick up the metadata in there.
Then use File/Open to see what's in there.
It's better to use XMI though as it's smaller / non binary

All the best,


05-08-2007, 11:32 AM
I'm confused...

I can not find the mdr.btd or mdr.btx that contains the /samples/metadata.xmi information. Placing the /samples/metadata.xmi file in the editor directory without the related mdr.* files does not make it editable within the Pentaho Metadata Editor (at least 1.5.3M3). Where are these files for the sample deployed with the demo?

The intent is to start with the samples content within the Pentaho Metadata Editor, export it as metadata.xmi, and then make sure the Adhoc Query Tool is loading the exported metadata.xmi file and everything is working together.

I have gone through the pentaho-metadata SVN repo, and there are no sample mdr.* files. There are some sample *.xmi files, but since I can not seem to load these with the Pentaho Metadata Editor, not sure how to move forward.

I do not have the Pentaho Metadata Editor files for the /samples/ metadata provided in the demo. I wish to use the Pentaho Metadata Editor to modify some of the /samples/ metadata to see the reflected changes in the Adhoc Query Tool.

05-08-2007, 11:35 AM
*sigh* need more caffine...

Import XMI file option.

Error: id10t exception thrown

05-08-2007, 02:37 PM
Well, we are improving upon that as well and we will have manuals and samples ready at lauch time ;-)
Why not check out the latest and give it spin once in a while?



05-09-2007, 09:07 AM

I'm not 100% sure that this is your problem, but in 1.5.3, the default ICwmSchemaFactory, configured in the pentaho-solutions/system/pentaho.xml file, is set to the SecurityAwareCwmSchemaFactory. Make sure that your XMI file is security aware. You can add security information to your schema in the Metadata Editor.


05-09-2007, 09:47 AM
Thanks for the tip Will, I'll double-check that on my own metadata file to see if that is what is holding me up.

As a sanity check, I did load the sample metadata.xmi into the Pentaho Metadata Editor, made a change, saved it, reversed the change, saved it, and re-exported the metadata.xmi file back. The file was a couple kb larger, but did load fine into the Adhoc Query Tool.

05-31-2007, 07:13 PM
I've been very successful using the metadata editor and querying the database via Ad Hoc Query. Is there a way other than on a table bases to present the logical model to the user. What Im looking for is view containing columns from multiple tables. Think categories is the way to go, but not sure.

Irv ;)

06-01-2007, 03:47 AM
Hi Irv & Darren,

"Categories" would indeed be the tool to use.
I'm going to do an article/blog/something on the steps to take to add a new XMI file to the Pentaho platform.

One of the tips in there would be to create another directory under pentaho-solutions, for example foo/
You then need to make this directory a solutions directory. (copy index.xml from samples/ for example)
Then you can put another metadata.xmi file in there and it will be picked up.
Remember, only direct sub-directories of pentaho-solutions/ it's not searching everything.



06-06-2007, 10:34 PM
I imported the metadata.xml into the sample editor.
Created a new connection to my DB, was a generic connection
Imported tables created a new business model->view, there are no relationships
created new categories under the businessview
and i exported this as the metadata.xml in the samples directory
i restarted the server
i only see the Orders as businessview in the pentaho web ui.

What am i doing wrong?

06-07-2007, 03:52 AM
FYI, in the next week or so there will be a new version 1.5M4 coming out that will solve most problems you are facing today.