8 Replies Latest reply on Aug 10, 2014 1:40 PM by Daniel Willis

    SQL Server to Essbase

    2655625

      Hi All,

       

      Need help on SQL Server connection to Essbase.

       

      I am need to pull Dimensions and Fact from the SQL Server to Essbase. And I am adding the Drivers detail into the ODBC.ini. Can you please help me if I have to install any other driver on my Essbase hosted Linux Machine to pull the data from SQL Server (Windows)?

       

      Or my approach is correct?

       

      Regards,

      Vishal

        • 1. Re: SQL Server to Essbase
          Amit Khanna


          Hi,

           

          Refer to this pdf for the complete information regarding Essbase SQL interface

          http://docs.oracle.com/cd/E10530_01/doc/epm.931/esb_sqlint.pdf

           

          Hope this helps.....

           

          Regards,

          Ami Khanna

          • 2. Re: SQL Server to Essbase
            GlennS_3

            as Ami pointed out, the document will help you. When installing on Linux, there are steps you have to take to link the SQL driver to Essbase

            • 3. Re: SQL Server to Essbase
              2655625

              I have copied the odbc.ini to bin folder. But still getting issue.

               

              Here is the Log.. need expert advise...

               

               

               

              [Tue Aug  5 07:25:15 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021020)

              Cannot read SQL driver name for [LocalDW] from [/us2002/Oracle/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1/bin/.odbc.ini]

               

               

              [Tue Aug  5 07:25:15 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021004)

              Connection String is generated

               

               

              [Tue Aug  5 07:25:15 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021041)

              Connection String is [DSN=LocalDW;UID=...;PWD=...;]

               

               

              [Tue Aug  5 07:25:15 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021006)

              SELECT Statement [SELECT * FROM DWH.dimproject] is generated

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021043)

              Connection has been established

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021044)

              Starting to execute query

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021013)

              ODBC Layer Error: [S0002] ==> [[DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'DWH.dimproject'.]

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Info(1021014)

              ODBC Layer Error: Native Error code [208]

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Error(1021001)

              Failed to Establish Connection With SQL Database Server.  See log for more information

               

               

              [Tue Aug  5 07:25:16 2014]Local/Test/Test/admin@Native Directory/47289276684608/Warning(1080014)

              Transaction [ 0x280001( 0x53e0cd2b.0x63547 ) ] aborted due to status [1021001].

              • 4. Re: Re: SQL Server to Essbase
                Daniel Willis

                I think a common mistake is people open the odbc.ini and see the section at the top and not realise it needs to tie to something else further down the file or vice versa. Do you have both these sections?

                 

                [ODBC Data Sources]

                tbcdata=DB2 Source Data on AIX

                 

                and

                 

                [tbcdata]

                Driver=/home/hyperion/hyperion/common/odbc/Merant/5.2/lib/ARdb222.so

                Database=tbcdata

                IpAddress=isaix7

                TcpPort=50000

                • 5. Re: SQL Server to Essbase
                  2655625

                  I am using below added into the odbc.ini

                   

                  LocalDW=DataDirect 7.0 SQL Server Wire Protocol

                   

                  [LocalDW]

                  Driver=/us2002/Oracle/Middleware/EPMSystem11R1/common/ODBC-64/Merant/7.0/lib/ARsqls26.so

                  Description=DataDirect 7.0 SQL Server Wire Protocol

                  AlternateServers=

                  AlwaysReportTriggerResults=0

                  AnsiNPW=1

                  ApplicationName=

                  ApplicationUsingThreads=1

                  AuthenticationMethod=1

                  BulkBinaryThreshold=32

                  BulkCharacterThreshold=-1

                  BulkLoadBatchSize=1024

                  BulkLoadFieldDelimiter=

                  BulkLoadOptions=2

                  BulkLoadRecordDelimiter=

                  ConnectionReset=0

                  ConnectionRetryCount=0

                  ConnectionRetryDelay=3

                  Database=DWHSnap

                  EnableBulkLoad=0

                  EnableQuotedIdentifiers=0

                  EncryptionMethod=0

                  FailoverGranularity=0

                  FailoverMode=0

                  FailoverPreconnect=0

                  FetchTSWTZasTimestamp=0

                  FetchTWFSasTime=1

                  GSSClient=native

                  HostName=GXXXEVSQL.\DEVDWH

                  HostNameInCertificate=

                  InitializationString=

                  Language=

                  LoadBalanceTimeout=0

                  LoadBalancing=0

                  LoginTimeout=15

                  LogonID=Essbase

                  MaxPoolSize=100

                  MinPoolSize=0

                  PacketSize=-1

                  Password=XXXXX

                  Pooling=0

                  PortNumber=<SQL_Server_server_port>

                  QueryTimeout=0

                  ReportCodePageConversionErrors=0

                  SnapshotSerializable=0

                  TrustStore=

                  TrustStorePassword=

                  ValidateServerCertificate=1

                  WorkStationID=

                  XMLDescribeType=-10

                  • 6. Re: SQL Server to Essbase
                    2655625

                    Any Clue why Essbase is not able to read the Drivers?

                    • 8. Re: SQL Server to Essbase
                      Daniel Willis

                      Is that hostname right? Should it have the dot before the slash? If you're specifying a SQL server named instance shouldn't it be just GXXXEVSQL\DEVDWH