Skip to Content

How to omit the owner from the columns, but keep it in the table in the Information Design Tool

Hello, world!

I am looking to build a Universe in the IDT using an ODBC data source. The client is on a Windows machine, and the BIP is 4.2SP06 running on RHEL7.

When I import in the metadata for the ODBC source, everything works fine. When I click and drag the table into the Data Foundation, this is where things get a little weird.

When I try to right click and preview the data, it generates a query that looks like the following:

It errors out because in the SELECT statement, it includes the dataset name. While I want to keep it in the FROM clause, I need that to not be in the column names. The valid resulting query I need looks like this:

SELECT
  RESULTS.stn,
  RESULTS.wban,
  RESULTS.year,
  RESULTS.mo,
  [...]
  RESULTS.hail,
  RESULTS.thunder,
  RESULTS.tornado_funnel_cloud
FROM
  HeatDeath.RESULTS

I tried changing the I tried tweaking the odbc.prm file in ...\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\extensions\qt on the Windows client machine. It's been suggested I try changing the <Parameter Name="CHECK_OWNER_STATE">N</Parameter> tag from Y to N. The same behavior still persists.

Any suggestions on how to make this work would be appreciated!

Add comment
10|10000 characters needed characters exceeded

  • Also just a note, using a SQL override does indeed work and the query does bring back data properly. I would rather not though since it works for one or two tables, but if one has to leverage tens if not hundreds of tables, that becomes an unfeasible solution.

  • Another update. An alternative I'm looking to see if it's possible is if whether a table alias can be used. A valid way of the database reading the query above is:

    SELECT
      T.stn,
      T.wban,
      [...]
      T.thunder,
      T.tornado_funnel_cloud
    FROM
      HeatDeath.RESULTS T

    The only way I could tell is to manually create a table alias within the Data Foundation, but again, if we're talking about hundreds of tables it also quickly becomes unworkable. Is there a setting or flag somewhere that can force this kind of behavior on this particular connection?

  • Get RSS Feed

2 Answers

  • Apr 23 at 08:33 AM

    Have to tried creating a Derive table and then add it in your universe? If not, you can give that a short

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 26 at 04:02 PM

    It does... sort of. The issue here is that it does remove the owner from the query for the columns, but also does it for the table as well. I'm really looking for it to be removed from the columns in the SELECT clause, but kept in the table in the table in the FROM clause. If it's removed from both, it's not valid for the data source.


    ikor5.png (180.6 kB)
    Add comment
    10|10000 characters needed characters exceeded