6 Replies Latest reply on Nov 10, 2015 3:41 PM by Dave Nicodemus

    Teiid with Microsoft's Power BI desktop using ODBC

    Dave Nicodemus Newbie

      Has anyone had any luck using Power BI with Teiid ?

       

      Thanks,

      Dave

        • 1. Re: Teiid with Microsoft's Power BI desktop using ODBC
          Ramesh Reddy Master

          I have not, but are you having any specific issues? If Power BI can connect over ODBC, then use that.

          • 2. Re: Teiid with Microsoft's Power BI desktop using ODBC
            Dave Nicodemus Newbie

            [36.864]ERROR from backend during send_query: 'SERROR'

            [36.865]ERROR from backend during send_query: 'C50000'

            [36.869]ERROR from backend during send_query: 'MTEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33.

            Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...'

            [36.875]ERROR from backend during send_query: 'Dorg.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33.

            Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...'

            [37.397]STATEMENT ERROR: func=PGAPI_ForeignKeys, desc='', errnum=1, errmsg='PGAPI_ForeignKeys query error'

             

            From this query

             

            [36.039]conn=0000002E721C6040, query='select 'ScottTestMart'::name as PKTABLE_CAT,

            n2.nspname as PKTABLE_SCHEM,

            c2.relname as PKTABLE_NAME,

            a2.attname as PKCOLUMN_NAME,

            'ScottTestMart'::name as FKTABLE_CAT,

            n1.nspname as FKTABLE_SCHEM,

            c1.relname as FKTABLE_NAME,

            a1.attname as FKCOLUMN_NAME,

            i::int2 as KEY_SEQ,

            case ref.confupdtype

            when 'c' then 0::int2

            when 'n' then 2::int2

            when 'd' then 4::int2

            when 'r' then 1::int2

            else 3::int2

            end as UPDATE_RULE,

            case ref.confdeltype

            when 'c' then 0::int2

            when 'n' then 2::int2

            when 'd' then 4::int2

            when 'r' then 1::int2

            else 3::int2

            end as DELETE_RULE,

            ref.conname as FK_NAME,

            cn.conname as PK_NAME,

            case

            when ref.condeferrable then

            case

            when ref.condeferred then 5::int2

            else 6::int2

            end

            else 7::int2

            end as DEFERRABLITY

            from

            ((((((( (select cn.oid, conrelid, conkey, confrelid, confkey,

            generate_series(array_lower(conkey, 1), array_upper(conkey, 1)) as i,

            confupdtype, confdeltype, conname,

            condeferrable, condeferred

              from pg_catalog.pg_constraint cn,

            pg_catalog.pg_class c,

            pg_catalog.pg_namespace n

              where contype = 'f'

              and confrelid = c.oid

              and relname = 'Categories'

              and n.oid = c.relnamespace

              and n.nspname = 'ScottTestMart'

            ) ref

            inner join pg_catalog.pg_class c1

              on c1.oid = ref.conrelid)

            inner join pg_catalog.pg_namespace n1

              on n1.oid = c1.relnamespace)

            inner join pg_catalog.pg_attribute a1

              on a1.attrelid = c1.oid

              and a1.attnum = conkey[i])

            inner join pg_catalog.pg_class c2

              on c2.oid = ref.confrelid)

            inner join pg_catalog.pg_namespace n2

              on n2.oid = c2.relnamespace)

            inner join pg_catalog.pg_attribute a2

              on a2.attrelid = c2.oid

              and a2.attnum = confkey[i])

            left outer join pg_catalog.pg_constraint cn

              on cn.conrelid = ref.confrelid

              and cn.contype = 'p')

              order by ref.oid, ref.i'

            • 3. Re: Teiid with Microsoft's Power BI desktop using ODBC
              Steven Hawkins Master

              Our pg layer isn't currently handling the name type.  It's not a full pg emulation, rather it's targeted at supporting as much of Teiid SQL as possible.  So this would need to be captured as an issue.

              • 4. Re: Teiid with Microsoft's Power BI desktop using ODBC
                Dave Nicodemus Newbie

                Thanks for the explanation Steven. What would I have to do to capture this as an issue ?

                • 5. Re: Teiid with Microsoft's Power BI desktop using ODBC
                  Steven Hawkins Master

                  Go to Teiid - JBoss Issue Tracker and select create issue at the top.  Fill in the relevant details from this posting and we should be able to take it from there.